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;
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);