why does spring throw me an exception about bad sql code?

Question

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;

Please [log your SQL statements](https://www.baeldung.com/sql-logging-spring-boot) and update your question, many thanks! — hd1, Sep 08 '20 at 02:05
First thing , Remove those default methods from Repository class, move them to some Service class. 2) Is your code working after removing Pageable attribute.? 3) It seems there are 3 entity classes Teams, Projects_teams, Projects. Can you share them also. Why dont you write a complete inner join query inside @Query ? — SauriBabu, Sep 08 '20 at 02:33
It does work without pageable. Its the second hibernate log that blows up. That query only runs when the data doesnt fit in one page, it checks to see how many are left. If it fits or there is no page at all works fine. And the page is well built. Ive checked — Goncalo Condeco, Sep 08 '20 at 02:42
@SauriBabu about the Projects_teams is the table auto generated by hibernate to map the manyToMany relationship between teams and projects, there is no class to it I tried @Query("select t.projects from TeamDAO t inner join fetch t.projects where t.name = :name") and it threw another exception saying the owner of the fetched association was not present in the select list — Goncalo Condeco, Sep 08 '20 at 02:44
As you have used pagination , first query has limit clause which is as expected . I cant get why 2nd query is getting trigged and its doing cross join. — SauriBabu, Sep 08 '20 at 03:12

score 0 · Accepted Answer · answered Sep 08 '20 at 04:59

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

why does spring throw me an exception about bad sql code?

1 Answers1