前提・実現したいこと
勉強はじめてまだ1か月のプログラミング初心者です。
現在課題で、従業員管理システムを作っています。(eclipse : java EE)
社員番号、氏名(漢字)、氏名(ふりがな)、所属部署のいずれかを入力することで従業員を検索できるものです。
一覧画面では、社員番号、氏名、所属部署が表示されます。
そこで表示される所属部署の欄に、従業員テーブルと所属部署テーブルを紐づけて、検索後の一覧画面に所属部署名を表示させたいです。
従業員テーブルにある所属部署コードと所属部署テーブルにある所属部署コードの外部キーをSQLで内部結合させたのですが、それらをコードに組み込む方法がわかりません。
SQL文を書いてはみたものの自信はないです。
複数テーブルの結合について勉強不足ではあります。
これらのサーブレットやjspへの組み込み方を知りたいです。
ご教授願います。
発生している問題・エラーメッセージ
HTTPステータス 500 - Cannot create TypedQuery for query with more than one return using requested result type [models.BelongsNum] type 例外レポート メッセージ Cannot create TypedQuery for query with more than one return using requested result type [models.BelongsNum] 説明 The server encountered an internal error that prevented it from fulfilling this request. 例外 java.lang.IllegalArgumentException: Cannot create TypedQuery for query with more than one return using requested result type [models.BelongsNum] org.hibernate.internal.AbstractSharedSessionContract.resultClassChecking(AbstractSharedSessionContract.java:740) org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:782) org.hibernate.internal.AbstractSharedSessionContract.buildQueryFromName(AbstractSharedSessionContract.java:763) org.hibernate.internal.AbstractSharedSessionContract.createNamedQuery(AbstractSharedSessionContract.java:869) org.hibernate.internal.AbstractSessionImpl.createNamedQuery(AbstractSessionImpl.java:23) controllers.employees.EmployeesIndexServlet.doPost(EmployeesIndexServlet.java:91) javax.servlet.http.HttpServlet.service(HttpServlet.java:648) javax.servlet.http.HttpServlet.service(HttpServlet.java:729) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) filters.LoginFilter.doFilter(LoginFilter.java:74) filters.EncodingFilter.doFilter(EncodingFilter.java:42) 注意 原因のすべてのスタックトレースは、Apache Tomcat/8.0.43のログに記録されています Apache Tomcat/8.0.43
該当のソースコード
doPost
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { EntityManager em = DBUtil.createEntityManager(); String search_code = request.getParameter("code"); String search_name = request.getParameter("name") + "%"; String search_belongs = request.getParameter("belongs_num"); if(search_code.equals("")){ search_code = null; } if(search_name.equals("%")){ search_name = null; } if(search_belongs.equals("")){ search_belongs = null; } int page = 1; try { page = Integer.parseInt(request.getParameter("page")); } catch(NumberFormatException e){} List<Employee> employees = em.createNamedQuery("search", Employee.class) .setParameter("code", search_code) .setParameter("name", search_name) .setParameter("belongs_num", search_belongs) .setFirstResult(15 * (page - 1)) .setMaxResults(15) .getResultList(); long employees_count = (long)employees.size(); List<BelongsNum> belongsnum = em.createNamedQuery("getAllBelongsNum", BelongsNum.class).getResultList(); List<BelongsNum> belongs = em.createNamedQuery("connect", BelongsNum.class).setParameter("belongs_id",search_belongs).getResultList(); em.close(); request.setAttribute("belongs", belongs); request.setAttribute("belongs_id", search_belongs); request.setAttribute("belongsnum", belongsnum); request.setAttribute("code", search_code); request.setAttribute("name", request.getParameter("name")); request.setAttribute("belongs_num", search_belongs); request.setAttribute("employees", employees); request.setAttribute("employees_count", employees_count); request.setAttribute("page", page); if(request.getSession().getAttribute("flush") != null){ request.setAttribute("flush", request.getSession().getAttribute("flush")); request.getSession().removeAttribute("flush"); } RequestDispatcher rd = request.getRequestDispatcher("/WEB-INF/views/employees/index.jsp"); rd.forward(request, response); }
所属部署テーブル
package models; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.NamedQueries; import javax.persistence.NamedQuery; import javax.persistence.Table; @Table(name = "belongsNum") @NamedQueries({ @NamedQuery( name = "getAllBelongsNum", query = "SELECT b FROM BelongsNum AS b ORDER BY b.belongs_id ASC" ), @NamedQuery( name = "connect", query = "SELECT e.belongs_num, b.belongs_id FROM BelongsNum b, Employee e WHERE e.belongs_num = b.belongs_id OR e.belongs_num IS NULL" ) }) @Entity public class BelongsNum { @Id @Column(name = "belongs_id", nullable = false, unique = true) private String belongs_id; @Column(name = "belongs_name", nullable = false) private String belongs_name; public String getBelongs_id(){ return belongs_id; } public void setBelongs_id(String belongs_id){ this.belongs_id = belongs_id; } public String getBelongs_name(){ return belongs_name; } public void setBelongs_name(String belongs_name){ this.belongs_name = belongs_name; } }
従業員テーブル
package models; import java.sql.Date; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.NamedQueries; import javax.persistence.NamedQuery; import javax.persistence.Table; @Table(name = "employees") @NamedQueries({ @NamedQuery( name = "getAllEmployees", query = "SELECT e FROM Employee AS e ORDER BY e.id DESC" ), @NamedQuery( name = "getEmployeesCount", query = "SELECT COUNT(e) FROM Employee AS e" ), @NamedQuery( name = "checkRegisteredCode", query = "SELECT COUNT(e) FROM Employee AS e WHERE e.code = :code" ), @NamedQuery( name = "search", query = "SELECT e FROM Employee AS e WHERE (e.code = :code OR :code IS NULL) " + "AND ((e.name_kanzi LIKE :name OR :name IS NULL) OR (e.name_kana LIKE :name OR :name IS NULL)) " + "AND (e.belongs_num = :belongs_num OR :belongs_num IS NULL) ORDER BY e.id DESC" ) }) @Entity public class Employee{ @Id @Column(name = "id") @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "code", nullable = false, unique = true) private String code; @Column(name = "name_kanzi", nullable = false) private String name_kanzi; @Column(name = "name_kana", nullable = false) private String name_kana; @Column(name = "belongs_num", nullable = false) private String belongs_num; public Long getId(){ return id; } public void setId(Long id){ this.id = id; } public String getCode(){ return code; } public void setCode(String code){ this.code = code; } public String getName_kanzi(){ return name_kanzi; } public void setName_kanzi(String name_kanzi){ this.name_kanzi = name_kanzi; } public String getName_kana(){ return name_kana; } public void setName_kana(String name_kana){ this.name_kana = name_kana; } public String getBelongs_num(){ return belongs_num; } public void setBelongs_num(String belongs_num){ this.belongs_num = belongs_num; } }
jsp
<table id="employee_list"> <tbody> <tr> <th>社員番号</th> <th>氏名</th> <th>所属</th> </tr> <c:forEach var="employee" items="${employees}" varStatus="status"> <tr class="row${status.count % 2}"> <td><c:out value="${employee.code}" /></td> <td><c:out value="${employee.name_kanzi}" /></td> <td> <c:set var="belongs_id" value="belongs" /> <c:when test="${ belongs_num = belongsnum.belongs_id }"> <c:out value="${ belongsnum.belongs_name }" /> </c:when> </td> </tr> </c:forEach> </tbody> </table>
補足情報(FW/ツールのバージョンなど)
eclipse version.10
Tomcat 8 (java8)
MySQL 5.7
Hibernate
あなたの回答
tips
プレビュー