Im working in an app that has a spring boot server. the thing is that i have a query, not auto generated one, that throws me a bad sql exception and i cant seem to find out why. Also im using MySQL as a database this is my repository code :
@Repository
public interface TeamRepository extends JpaRepository<TeamDAO, String> {
boolean existsByName(String name);
Optional<TeamDAO> findByName(String name);
@Query("select t.projects from TeamDAO t where t.name = :name")
Page<ProjectDAO> getProjectsByName(@Param("name") String name, Pageable page);
default TeamDAO getTeam(String name) {
return findByName(name).orElseThrow(() -> new NotFound404Exception(notFound(TEAM, name)));
}
default TeamDAO getDependency(String name) {
return findByName(name).orElseThrow(() -> new FailedDependency424Exception(failedDependency(TEAM, name)));
}
default void existsDependency(String name) {
if(!existsByName(name))
throw new FailedDependency424Exception(failedDependency(TEAM, name));
}
}
The one with the @Query annotation throws me:
java.sql.SQLSyntaxErrorException: 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_0_0_ from teams teamdao0_ cross join projects_teams projects1_, project' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2341)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2094)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2056)
at org.hibernate.loader.Loader.doQuery(Loader.java:953)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:350)
at org.hibernate.loader.Loader.doList(Loader.java:2887)
at org.hibernate.loader.Loader.doList(Loader.java:2869)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2701)
at org.hibernate.loader.Loader.list(Loader.java:2696)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1415)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1565)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533)
at org.hibernate.query.Query.getResultList(Query.java:165)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:409)
at com.sun.proxy.$Proxy305.getResultList(Unknown Source)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.count(JpaQueryExecution.java:183)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.lambda$doExecute$0(JpaQueryExecution.java:178)
at org.springframework.data.repository.support.PageableExecutionUtils.getPage(PageableExecutionUtils.java:62)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:177)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy302.getProjectsByName(Unknown Source)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205)
at com.sun.proxy.$Proxy288.getProjectsByName(Unknown Source)
at com.media.services.TeamService.getMyProjects(TeamService.java:75)
at com.media.services.TeamService$$FastClassBySpringCGLIB$$5d21d83f.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
at org.springframework.aop.interceptor.AsyncExecutionInterceptor.lambda$invoke$0(AsyncExecutionInterceptor.java:115)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at org.springframework.security.concurrent.DelegatingSecurityContextRunnable.run(DelegatingSecurityContextRunnable.java:84)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Can anyone tell me what im doing wrong? I’ve tried reworking the code but it seems to me it just doesnt
logs as requested (i think its this)
Hibernate: select projectdao2_.id as id1_2_, projectdao2_.created_by as created_2_2_, projectdao2_.created_date as created_3_2_, projectdao2_.description as descript4_2_, projectdao2_.features as features5_2_, projectdao2_.last_modified as last_mod6_2_, projectdao2_.last_modified_by as last_mod7_2_, projectdao2_.name as name8_2_, projectdao2_.os as os9_2_ from teams teamdao0_ inner join projects_teams projects1_ on teamdao0_.id=projects1_.teams_id inner join projects projectdao2_ on projects1_.projects_id=projectdao2_.id where teamdao0_.name=? order by teamdao0_.name asc limit ? Hibernate: select count(.) as col_0_0_ from teams teamdao0_ cross join projects_teams projects1_, projects projectdao2_ where teamdao0_.id=projects1_.teams_id and projects1_.projects_id=projectdao2_.id and teamdao0_.name=?
EDIT: I can tell is something to do with the pageable object. I mean its when there are more objects that what can fit in a page. I had pages of ten by default, and there are 12 objects, didnt work, when they fit in a single page (here i increased page size to 15) it works.
EDIT: Project and Team classes
public class ProjectDAO extends BaseEntity implements DAO {
private static final long serialVersionUID = -2990447415589237412L;
@Column(unique = true, updatable = false)
private String name;
@Column(length = 1024)
private String description;
private String features, os;
@LastModifiedDate
private long lastModified;
@CreatedDate
private long createdDate;
@CreatedBy
private String createdBy;
@LastModifiedBy
private String lastModifiedBy;
@ToString.Exclude
@EqualsAndHashCode.Exclude
@ManyToMany(cascade = { CascadeType.PERSIST , CascadeType.MERGE, CascadeType.REFRESH })
private Set<TeamDAO> teams;
@ToString.Exclude
@EqualsAndHashCode.Exclude
@ManyToMany(cascade = { CascadeType.PERSIST , CascadeType.MERGE, CascadeType.REFRESH })
private Set<ClientDAO> clients;
public class TeamDAO extends BaseEntity implements DAO {
private static final long serialVersionUID = 8649943850027398166L;
@Column(length = 124, unique = true)
private String name;
private String leader;
@LastModifiedDate
private long lastModified;
@CreatedDate
private long createdDate;
@CreatedBy
private String createdBy;
@LastModifiedBy
private String lastModifiedBy;
@ToString.Exclude
@EqualsAndHashCode.Exclude
@OneToMany(mappedBy = "team", cascade = { CascadeType.PERSIST , CascadeType.MERGE, CascadeType.REFRESH })
private Set<UserDAO> members;
@ToString.Exclude
@EqualsAndHashCode.Exclude
@ManyToMany(mappedBy = "teams", cascade = { CascadeType.PERSIST , CascadeType.MERGE, CascadeType.REFRESH })
private Set<ProjectDAO> projects;
Advertisement
Answer
Seems like hibernate is having problems generating the count statement for calculating the amount of pages of the results, might be due to the lack of an explicit definition of a joint table for your many to many relationship, in any case you can try explicitly defining a count statement for your query like this
@Query(value = "select t.projects from TeamDAO t where t.name = :name", countQuery = "select count(p) from TeamDAO t inner join t.projects as p where t.name = :name", )
Page<ProjectDAO> getProjectsByName(@Param("name") String name, Pageable page);