java - Spring Hibernate - 查询以将附加列映射到我的实体 - java.sq.SQLException : Column not found

Question

我有一个 Team 实体，我想将一个额外的 String 列映射到结果集。为此，我使用了 @SqlResultSetMapping 并创建了一个名为 TeamListInformation 的新类。

我的团队实体:

@Entity
@Table(name="teams")
@SqlResultSetMapping(
        name = "TeamInformation",
        entities={
                @EntityResult(
                        entityClass=Team.class, 
                            fields={
                                    @FieldResult(name="id", column="id"),
                                    @FieldResult(name="name", column="name"), 
                                    @FieldResult(name="rating", column="rating"), 
                                    @FieldResult(name="id_country", column="id_country"), 
                                    @FieldResult(name="id_division", column="id_division")
                                }
                        )
                },
        columns={
                @ColumnResult(name="country"),
                @ColumnResult(name="division"),
                @ColumnResult(name="games")
            }
        )
public class Team implements Serializable{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 40, nullable = false)
    private String name;

    @Column(name = "rating", length = 6, nullable = false)
    private Integer rating;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "id_country", nullable = false)
    private Country country;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "id_division", nullable = false)
    private Division division;

    @Column(name = "desc_image", length = 100, nullable = true)
    private String descImage;

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "team")
    private List<ActividadeEquipa> actividadesEquipa;

我的 TeamListInformation 类:

public class TeamListInformation {

private Integer id;
private String name;
private Integer rating;
private String id_country;
private String country;
private String id_division;
private String division;
private String games;

getters and setters...

}

然后，在我的 DAO 类中，我创建了一个函数来生成我的查询:

public List<TeamListInformation> getTeams(){

    Query q = em.createNativeQuery(
            "SELECT " +
                    "T.id as id, " +
                    "T.name as name, " +
                    "T.rating as rating, " +
                    "T.id_country as id_country, " +
                    "C.name as country, " +
                    "D.id_division as id_division, " +
                    "D.name as division, " +
                    "group_concat(J.nome SEPARATOR ', ') as games " +
            "FROM teams T " +
            "INNER JOIN countries C " +
                "ON T.id_country = C.id_country " +
            "INNER JOIN divisions D " +
                "ON T.id_division = D.id_division " +
            "INNER JOIN actividade_equipa AE " +
                "ON T.id = AE.id_equipa " +
            "INNER JOIN jogo J " +
                "ON AE.id_jogo = J.id " +
            "GROUP BY T.id, T.name, T.rating, T.id_country, C.name, D.id_division, D.name",
        "TeamInformation");

    List<TeamListInformation> teams = q.getResultList();

    return teams;   
}

生成的查询:

SELECT
    T.id as id,
    T.name as name,
    T.rating as rating,
    T.id_country as id_country,
    C.name as country,
    D.id_division as id_division,
    D.name as division,
    group_concat(J.nome SEPARATOR ', ') as games 
FROM
    teams T 
INNER JOIN
    countries C 
        ON T.id_country = C.id_country 
INNER JOIN
    divisions D 
        ON T.id_division = D.id_division 
INNER JOIN
    actividade_equipa AE 
        ON T.id = AE.id_equipa 
INNER JOIN
    jogo J 
        ON AE.id_jogo = J.id 
GROUP BY
    T.id,
    T.name,
    T.rating,
    T.id_country,
    C.name,
    D.id_division,
    D.name

生成的查询是正确的，当我在 MySQL Workbench 中直接执行它时，它返回了理想的结果，但是，我收到以下异常:java.sql.SQLException: Column 'id_count5_5_0_' not found。

我错过了什么？

错误:

HTTP Status 500 - Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query

type Exception report

message Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query

description The server encountered an internal error that prevented it from fulfilling this request.

exception

java.sql.SQLException: Column 'id_count5_5_0_' not found.
    com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:545)
    com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
    com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:505)
    com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:479)
    com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:574)
    com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:892)
    org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$2.doExtract(VarcharTypeDescriptor.java:62)
    org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
    org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:260)
    org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:256)
    org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:246)
    org.hibernate.type.ManyToOneType.hydrate(ManyToOneType.java:179)
    org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2844)
    org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1741)
    org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1667)
    org.hibernate.loader.Loader.getRow(Loader.java:1556)
    org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:740)
    org.hibernate.loader.Loader.processResultSet(Loader.java:985)
    org.hibernate.loader.Loader.doQuery(Loader.java:943)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
    org.hibernate.loader.Loader.doList(Loader.java:2615)
    org.hibernate.loader.Loader.doList(Loader.java:2598)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
    org.hibernate.loader.Loader.list(Loader.java:2425)
    org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
    org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2160)
    org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:992)
    org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:148)
    org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
    org.hibernate.query.Query.getResultList(Query.java:146)
    org.o7planning.SpringMVCTeams.dao.imp.TeamDAOImpl.getTeamsTest(TeamDAOImpl.java:195)
    org.o7planning.SpringMVCTeams.service.imp.TeamServiceImpl.getNumOfPages(TeamServiceImpl.java:50)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    java.lang.reflect.Method.invoke(Unknown Source)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
    org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    com.sun.proxy.$Proxy72.getNumOfPages(Unknown Source)
    org.o7planning.SpringMVCTeams.controller.TeamController.getlistOfTeams(TeamController.java:154)
    org.o7planning.SpringMVCTeams.controller.TeamController.listOfTeams(TeamController.java:179)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    java.lang.reflect.Method.invoke(Unknown Source)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:826)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:964)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:856)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:841)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
    org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
    org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
    org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
    org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
    org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)   

Answer 1

我设法通过更改 @SqlResultSetMapping 来做到这一点:

@SqlResultSetMapping(
        name = "TeamInformation",
                classes = {
                         @ConstructorResult(targetClass = TeamListInformation.class,
                           columns = {@ColumnResult(name="id", type = Integer.class) , 
                                   @ColumnResult(name="name", type = String.class), 
                                   @ColumnResult(name="rating", type = Integer.class), 
                                   @ColumnResult(name="country", type = String.class),  
                                   @ColumnResult(name="division", type = String.class), 
                                   @ColumnResult(name="games", type = String.class)}
                         )}

        )

尽管如此，我仍然不知道我之前的实现有什么问题。