java - spring mvc herbinate 与额外列的多对多关系存在问题

Question

我有三个表，tables in sql servers ，Solved 是由多对多关系创建的。Solved 表有一个复合键，所以我为其复合键写了一个 SolvedKey SolvedKey

@Embeddable
public class SolvedKey implements Serializable {

    @Column(name="userMailer")
    private String usermail;

    @Column(name="challengeID")
    private int challengeid;

我的实体:

已解决:solved entity

@Entity
@Table(name = "Solved")
public class Solved {

    @EmbeddedId
    private SolvedKey solvedKey;

    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("usermail")
    private User user;

    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("challengeid")
    private Challenge chall;

    @Temporal(TemporalType.DATE)
    @DateTimeFormat(pattern="yyyy/mm/dd")
    @Column(name = "dateSolved")
    private Date dateSolved;

用户:user entity

@Entity
@Table(name="Users")
public class User implements Serializable{
    // declare field
    @Id
    @Column(name="Mail")
    private String mail;
    @Column(name="Username")
    private String userName;    
    @Column(name="Password")
    private String passWord;
    @Column(name ="Validation")
    private boolean valid;
    @Column(name="Isadmin")
    @Nullable
    private boolean isAdmin;

    @OneToMany(mappedBy = "user",cascade = CascadeType.ALL)
    private List<Solved> challs = new ArrayList<Solved>();

挑战:Challenge entity

@Entity
@Table(name="Challenges")
public class Challenge implements Serializable {
    @Id
    @GeneratedValue
    @Column(name = "ID")
    private int id;

    @Column(name="challengName")
    private String challengeName;

    @Column(name = "score")
    private int score;

    @Column(name="fileAttach")
    private String fileAttach;

    @Column(name="description")
    private String description;

    @Column(name="hint")
    private String hint;

    @Column(name="major")
    private String major;

    @Column(name = "flag")
    private String flag;

    @OneToMany(mappedBy = "chall",cascade = CascadeType.ALL)
    private List<Solved> users = new ArrayList<Solved>();

但我不确定它是否正确。当我尝试请求用户的挑战列表时:

public void getListSolved(String mail){
        User user = getUserByMail(mail);
        System.out.println("User: " + user.getUserName());
        System.out.println("Challs: " + user.getChalls());
        user.getChalls().forEach(
                (chall) -> System.out.println("Chall: "+ chall.getChall().getChallengeName()+ " | Date: " + chall.getDateSolved())
        );
    }

由于无效列“user_Mail”而失败，但我找不到代码中该列的位置。

HTTP Status 500 - Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
type Exception report

message Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

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

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)
root cause

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:89)
    org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:449)
    org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:202)
    org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:137)
    org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:102)
    org.hibernate.loader.collection.plan.AbstractLoadPlanBasedCollectionInitializer.initialize(AbstractLoadPlanBasedCollectionInitializer.java:100)
    org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:693)
    org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:92)
    org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1893)
    org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:555)
    org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:260)
    org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:551)
    org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:140)
    org.hibernate.collection.internal.PersistentBag.toString(PersistentBag.java:526)
    java.lang.String.valueOf(Unknown Source)
    java.lang.StringBuilder.append(Unknown Source)
    wargame.dao.UserDAO.getListSolved(UserDAO.java:154)
    wargame.mainController.UserController.userInfo(UserController.java:285)
    wargame.mainController.UserController$$FastClassByCGLIB$$b3d9caac.invoke(<generated>)
    org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:710)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
    org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:643)
    wargame.mainController.UserController$$EnhancerByCGLIB$$9bfbb734.userInfo(<generated>)
    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.invoke(InvocableHandlerMethod.java:214)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:690)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)
root cause

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'user_Mail'.
    com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
    com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1458)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
    com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4016)
    com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1414)
    com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:176)
    com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:151)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
    org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
    org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:449)
    org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:202)
    org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:137)
    org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:102)
    org.hibernate.loader.collection.plan.AbstractLoadPlanBasedCollectionInitializer.initialize(AbstractLoadPlanBasedCollectionInitializer.java:100)
    org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:693)
    org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:92)
    org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1893)
    org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:555)
    org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:260)
    org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:551)
    org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:140)
    org.hibernate.collection.internal.PersistentBag.toString(PersistentBag.java:526)
    java.lang.String.valueOf(Unknown Source)
    java.lang.StringBuilder.append(Unknown Source)
    wargame.dao.UserDAO.getListSolved(UserDAO.java:154)
    wargame.mainController.UserController.userInfo(UserController.java:285)
    wargame.mainController.UserController$$FastClassByCGLIB$$b3d9caac.invoke(<generated>)
    org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:710)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
    org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:643)
    wargame.mainController.UserController$$EnhancerByCGLIB$$9bfbb734.userInfo(<generated>)
    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.invoke(InvocableHandlerMethod.java:214)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:690)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)

Answer 1

如果您希望已解决实体中的用户引用列为用户邮件，我建议您按如下方式设计您的实体。主要区别在于，不是在 Solved 中使用 User 和 Challenge，而是在 SolvedKey 中使用它作为引用实体，并且不要忘记覆盖 SolvedKey 中的 equal 和 hashCode。

SolvedKey 实体

@Embeddable
public class SolvedKey implements Serializable {

       @ManyToOne(optional = false, fetch = FetchType.LAZY)
       @JoinColumn(name = "usermail" , referencedColumnName = "Mail")
       private User user;

       @ManyToOne(optional = false, fetch = FetchType.LAZY)
       private Challenge chall;

主权实体

@Entity
public class Solved {

    @EmbeddedId
    private SolvedKey solvedKey;

用户 -> 已解决的关系

@Entity
public class User {

     @OneToMany(mappedBy = "solvedKey.user",cascade = CascadeType.ALL)
     private List<Solved> challs = new ArrayList<Solved>();

挑战 -> 解决关系

@Entity
public class Challenge {


     @OneToMany(mappedBy = "solvedKey.chall",cascade = CascadeType.ALL)
     private List<Solved> users = new ArrayList<Solved>();

当我执行 spring boot app 时，hibernate ddl 如下。

create table Solved (
   dateSolved date,
    chall_ID integer not null,
    usermail varchar(255) not null,
    primary key (chall_ID, usermail)
)


alter table Solved 
   add constraint FKx2kp7aliijq7g7xq0j4huqb4 
   foreign key (chall_ID) 
   references Challenge

alter table Solved 
   add constraint FKhycu95r961terhbackiixqwns 
   foreign key (usermail) 
   references User