我对编程非常陌生,有一个相当复杂的问题。我希望有人知道如何解决以下问题:
我有一个springboot项目,通过jpa/hibernate将java连接到mysql数据库。它作为restfulapi工作,我主要使用注解。
在mysql中,“contacts”表(除其他外)通过连接表(“cont\u proj”)链接到“projects”,并通过连接表“cont$competencycenter”链接到“coces”(请尽量忽略主键是double的事实,在我必须使用的数据库中它已经是这样了,不能更改)
在java代码中,我制作了一个@manytomany链接到一组项目(一个人可以有多个项目,几个人可以在同一个项目上),而人只能在一个coce中(因此我使用了@manytomone)
这是我的“联系人”实体:
@Entity
@Table(name = "contacts")
@JsonInclude(Include.NON_DEFAULT)
public class Contact implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private double k_Contact;
@NotNull
private String name;
// There are several other variables here
@ManyToMany
@JoinTable(
name = "PROJ_CONT",
joinColumns = @JoinColumn(name = "K_CONTACT", referencedColumnName="K_CONTACT"),
inverseJoinColumns = @JoinColumn(name = "K_PROJECT", referencedColumnName="K_PROJECT"))
private Set<Project> projects = new HashSet<>();
@ManyToOne
@JoinTable(
name = "CONT$COMPETENCECENTER",
joinColumns = @JoinColumn(name = "K_CONTACT", referencedColumnName="K_CONTACT"),
inverseJoinColumns = @JoinColumn(name = "F_COCE_MAIN", referencedColumnName="K_CONT_MEDE_COCE"))
private Coce coce;
public Contact() {
}
public Contact(double k_Contact) {
this.k_Contact = k_Contact;
}
public Contact(double k_Contact, String name, String firstName, String email1, Byte[] picture) {
this.k_Contact = k_Contact;
this.name = name;
this.firstName = firstName;
this.email1 = email1;
this.picture = picture;
}
public Contact(double k_Contact, String name, String firstName, String email1, Byte[] picture, Coce coce, Set<Project> projects) {
this.k_Contact = k_Contact;
this.name = name;
this.firstName = firstName;
this.email1 = email1;
this.picture = picture;
this.coce = coce;
this.projects = projects;
}
// plus getters and setters
}
其他实体非常基本(此时@manytomy和@manytone是单向的)。有能手和能手在场。
这是一个项目:
@Entity
@Table(name = "projects")
@JsonInclude(Include.NON_DEFAULT)
public class Project implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private double k_Project;
@NotNull
private String name;
@NotNull
private LocalDateTime d_Start;
@NotNull
private LocalDateTime d_End;
public Project() {
}
public Project(double K_PROJECT, String NAME) {
this.k_Project = K_PROJECT;
this.name = NAME;
}}
还有coce:
@Entity
@Table(name = "LK_CONT_MEDE_COCE")
@JsonInclude(Include.NON_NULL)
public class Coce implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private double k_Cont_Mede_Coce;
@NotNull
private String competence_Cente;
@NotNull
private String unipartners;
public Coce() {
}
public Coce(double k_Cont_Mede_Coce, String competence_Cente, String unipartners) {
this.k_Cont_Mede_Coce = k_Cont_Mede_Coce;
this.competence_Cente = competence_Cente;
this.unipartners = unipartners;
}}
我只需要几个变量(这就是为什么我不能做全选)。我还认为我不应该编写带有内部连接的扩展查询,所以我没有这样做(它适用于大多数查询,除了列表变量)。
这是contactrepository的相关部分:
public interface ContactRepository extends JpaRepository<Contact, Double> {
@Query("select c.k_Contact, c.name, c.firstName, c.email1, c.picture, c.coce, c.project from Contact c where c.k_Contact is :id")
Optional<Contact> findContactById(@Param("id") Double id);
}
我得到以下错误,但仅当我尝试添加c.projects时:
MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as col_6_0_, coce1_.k_Cont_Mede_Coce as k_Cont_M1_3_0_, project3_.k_Project as k' at line 1
当我对联系人使用findall方法时,它也正确地给出了所有信息,包括项目(因此@manytomany可以工作)。我不能用这个,因为性能会很差。
我认为这是我请求“projects”(这是一个列表)的方式,因为coce(single result,@manytoone)可以完美地工作并返回正确的对象,没有任何问题。
我希望在查询中有一个简单的错误。
如果有什么重要的事情需要我补充,请告诉我。提前谢谢!
编辑:应用程序确实在运行,sql错误只在我尝试在运行时访问特定数据时发生。如前所述,这里是完整的错误,我没有添加它,因为它很长:
com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception:您的sql语法有错误;请查看与您的mysql服务器版本对应的手册,以了解使用near'as col\u 6\u 0\coce1\coce1.k\u cont\u mede\u coce as k\u cont\u m1\u 3\u 0\coce的正确语法,在sun.reflect.nativeconstructoraccessorimpl.newinstance0(本机方法)~[na:1.8.0\u 161]的sun.reflect.nativeconstructoraccessorimpl.newinstance(nativeconstructoraccessorimpl)的第1行,project3\u项目为k'。java:62)~[na:1.8.0_]在sun.reflect.delegatingconstructoraccessorimpl.newinstance(delegatingconstructoraccessorimpl。java:45) ~[na:1.8.0\u 161]位于java.lang.reflect.constructor.newinstance(constructor。java:423)~[na:1.8.0\u 161]位于com.mysql.jdbc.util.handlenewinstance(util。java:425)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.mysql.jdbc.util.getinstance(util。java:408)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.mysql.jdbc.sqlerror.createsqlexception(sqlerror。java:944)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio。java:3976)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio。java:3912)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.mysql.jdbc.mysqlio.sendcommand(mysqlio。java:2530)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.mysql.jdbc.mysqlio.sqlquerydirect(mysqlio。java:2683)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.mysql.jdbc.connectionimpl.execsql(connectionimpl。java:2486)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.mysql.jdbc.preparedstatement.executeinternal(preparedstatement。java:1858)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.mysql.jdbc.preparedstatement.executequery(preparedstatement。java:1966)~[mysql-connector-java-5.1.46。jar:5.1.46]在com.zaxxer.hikari.pool.proxypreparedstatement.executequery(proxypreparedstatement。java:52) [hikaricp-2.7.8。jar:na]在com.zaxxer.hikari.pool.hikariproxypreparedstatement.executequery(hikariproxypreparedstatement.java)~[hikaricp-2.7.8。jar:na]在org.hibernate.engine.jdbc.internal.resultsetreturnimpl.extract(resultsetreturnimpl。java:60)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.loader.getresultset(loader。java:2168)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.loader.executequerystatement(loader。java:1931)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.loader.executequerystatement(loader。java:1893)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.loader.doquery(加载器。java:938)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.loader.doqueryandinitializenonlazycollections(loader。java:341)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.loader.dolist(loader。java:2692)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.loader.dolist(loader。java:2675)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.loader.listignorequerycache(loader。java:2507)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.loader.list(loader。java:2502)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.loader.hql.queryloader.list(queryloader。java:502)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.hql.internal.ast.querytranslatorimpl.list(querytranslatorimpl。java:392)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.engine.query.spi.hqlqueryplan.performlist(hqlqueryplan。java:216) ~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.internal.sessionimpl.list(sessionimpl。java:1489)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.query.internal.abstractproducedquery.dolist(abstractproducedquery。java:1445)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.hibernate.query.internal.abstractproducedquery.list(abstractproducedquery)。java:1414)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]位于org.hibernate.query.internal.abstractproducedquery.getsingleresult(abstractproducedquery)。java:1463)~[hibernate-core-5.2.16.最终版本。jar:5.2.16.final]在org.springframework.data.jpa.repository.query.jpaqueryexecution$singleentityexecution.doexecute(jpaqueryexecution)。java:214)~[spring-data-jpa-2.0.6.发布。jar:2.0.6.release]位于org.springframework.data.jpa.repository.query.jpaqueryexecution.execute(jpaqueryexecution)。java:91)~[spring-data-jpa-2.0.6.发布。jar:2.0.6.release]在org.springframework.data.jpa.repository.query.abstractjpaquery.doexecute(abstractjpaquery。java:136)~[spring-data-jpa-2.0.6.发布。jar:2.0.6.release]在org.springframework.data.jpa.repository.query.abstractjpaquery.execute(abstractjpaquery。java:125)~[spring-data-jpa-2.0.6.发布。jar:2.0.6.release]在org.springframework.data.repository.core.support.repositoryfactorysupport$queryexecutormethodinterceptor.doinvoke(repositoryfactorysupport)。java:590)~[spring-data-commons-2.0.6.发布。jar:2.0.6.release]在org.springframework.data.repository.core.support.repositoryfactorysupport$queryexecutormethodinterceptor.invoke(repositoryfactorysupport)。java:578)~[spring-data-commons-2.0.6.发布。jar:2.0.6.release]在org.springframework.aop.framework.reflectivemethodinvocation.procedue(reflectivemethodinvocation。java:185) ~[spring-aop-5.0.5.发布。jar:5.0.5.release]位于org.springframework.data.projection.defaultmethodinvokingmethodinterceptor.invoke(defaultmethodinvokingmethodinterceptor)。java:59)~[spring-data-commons-2.0.6.发布。jar:2.0.6.release]在org.springframework.aop.framework.reflectivemethodinvocation.procedue(reflectivemethodinvocation。java:185) ~[spring-aop-5.0.5.发布。jar:5.0.5.release]在org.springframework.transaction.interceptor.transactionspectsupport.invokewithintransaction(transactionspectsupport。java:294)~[spring-tx-5.0.5.释放。jar:5.0.5.release]在org.springframework.transaction.interceptor.transactioninterceptor.invoke(transactioninterceptor。java:98) ~[spring-tx-5.0.5.释放。jar:5.0.5.release]在org.springframework.aop.framework.reflectivemethodinvocation.procedue(reflectivemethodinvocation。java:185)~[spring-aop-5.0.5.发布。jar:5.0.5.release]位于org.springframework.dao.support.persistenceexceptiontranslationinterceptor.invoke(persistenceexceptiontranslationinterceptor)。java:139) ~[spring-tx-5.0.5.释放。jar:5.0.5.release]在org.springframework.aop.framework.reflectivemethodinvocation.procedue(reflectivemethodinvocation。java:185)~[spring-aop-5.0.5.发布。jar:5.0.5.release]在org.springframework.data.jpa.repository.support.crudmethodmetadatapostprocessor$crudmethodmetadatapopulatingmethodinterceptor.invoke(crudmethodmetadatapostprocessor。java:135)~[spring-data-jpa-2.0.6.发布。jar:2.0.6.release]在org.springframework.aop.framework.reflectivemethodinvocation.procedue(reflectivemethodinvocation。java:185) ~[spring-aop-5.0.5.发布。jar:5.0.5.release]在org.springframework.aop.interceptor.exposeinvocationinterceptor.invoke(exposeinvocationinterceptor。java:92)~[spring-aop-5.0.5.发布。jar:5.0.5.release]在org.springframework.aop.framework.reflectivemethodinvocation.procedue(reflectivemethodinvocation。java:185) ~[spring-aop-5.0.5.发布。jar:5.0.5.release]位于org.springframework.data.repository.core.support.surroundingtransactiondetectormethodinterceptor.invoke(surroundingtransactiondetectormethodinterceptor)。java:61)~[spring-data-commons-2.0.6.发布。jar:2.0.6.release]在org.springframework.aop.framework.reflectivemethodinvocation.procedue(reflectivemethodinvocation。java:185)~[spring-aop-5.0.5.发布。jar:5.0.5.release]在org.springframework.aop.framework.jdkdynamicaopproxy.invoke(jdkdynamicaopproxy。java:212)~[spring-aop-5.0.5.发布。jar:5.0.5.release]位于com.sun.proxy.$proxy104.findcontactbyid(未知源)~[na:na]位于org.unipartners.services.contactserviceimpl.findcontactbyid(contactserviceimpl。java:25)~[classes/:na]位于org.unipartners.web.appcontroller.findcontactbyid(appcontroller。java:67)在sun.reflect.nativemethodaccessorimpl.invoke0(本机方法)~[classes/:na]在sun.reflect.nat上~[na:1.8.0\u 161]
暂无答案!
目前还没有任何答案,快来回答吧!