Search code examples
springspring-bootjpaspring-repositories

"Could not prepare statement" OneToMany

Here is what I'm trying to achieve :

Write a web service that gives the details of a repository: id, name, user name and list of commits.

💡 It needs to be returned as a JSON format, for example :

{
  "repository_id": "1",
  "repository_name": "My repo",
  "owner": "Noah",
  "commits": [
    "First commit",
    "Second commit",
    "Third commit"
  ]
}

You will find the database structure below : Database structure

Here is my CrudRepository with the query I am trying to build:

public interface RepositoriesDB extends CrudRepository<Repository, String> {
    @Query(value = "SELECT r.repositoryId, r.repositoryName, r.owner.userName, r.commits FROM Repository r WHERE r.repositoryId = :repoId")
    List<Object[]> getRepo(@Param("repoId") long repoId);
}

My User class :

@Entity
@NoArgsConstructor
@Data
public class User {
    @NotNull
    @Id
    private String userLogin;

    @NotBlank
    @NotNull
    private String userName;

    @OneToMany(mappedBy = "owner", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JsonIgnore
    private List<Repository> repositories;
}

My Repository class :

@Entity
@NoArgsConstructor
@Data
public class Repository {
    @NotNull
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long repositoryId;

    @NotNull
    @NotBlank
    @Size(min = 3, max = 25)
    private String repositoryName;

    @OneToMany(mappedBy = "repository", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JsonIgnore
    private List<Commit> commits;

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "owner_login", nullable = false)
    private User owner;
}

My Commit class :

@Entity
@NoArgsConstructor
@Data
public class Commit {
    @NotNull
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long commitId;

    @NotBlank
    @NotNull
    private LocalDateTime date = LocalDateTime.now();

    @NotNull
    @NotBlank
    @Size(min = 1, max = 255)
    private String message;

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "repository_id", nullable = false)
    private Repository repository;
}

And finally, here is the stacktrace :

There was an unexpected error (type=Internal Server Error, status=500).
could not prepare statement; SQL [select repository0_.repository_id as col_0_0_, repository0_.repository_name as col_1_0_, user1_.user_name as col_2_0_, . as col_3_0_, commits2_.commit_id as commit_i1_0_, commits2_.date as date2_0_, commits2_.message as message3_0_, commits2_.repository_id as reposito4_0_ from repository repository0_ cross join user user1_ inner join commit commits2_ on repository0_.repository_id=commits2_.repository_id where repository0_.owner_login=user1_.user_login and repository0_.repository_id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select repository0_.repository_id as col_0_0_, repository0_.repository_name as col_1_0_, user1_.user_name as col_2_0_, . as col_3_0_, commits2_.commit_id as commit_i1_0_, commits2_.date as date2_0_, commits2_.message as message3_0_, commits2_.repository_id as reposito4_0_ from repository repository0_ cross join user user1_ inner join commit commits2_ on repository0_.repository_id=commits2_.repository_id where repository0_.owner_login=user1_.user_login and repository0_.repository_id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
[...]
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
[...]
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT REPOSITORY0_.REPOSITORY_ID AS COL_0_0_, REPOSITORY0_.REPOSITORY_NAME AS COL_1_0_, USER1_.USER_NAME AS COL_2_0_, .[*] AS COL_3_0_, COMMITS2_.COMMIT_ID AS COMMIT_I1_0_, COMMITS2_.DATE AS DATE2_0_, COMMITS2_.MESSAGE AS MESSAGE3_0_, COMMITS2_.REPOSITORY_ID AS REPOSITO4_0_ FROM REPOSITORY REPOSITORY0_ CROSS JOIN USER USER1_ INNER JOIN COMMIT COMMITS2_ ON REPOSITORY0_.REPOSITORY_ID=COMMITS2_.REPOSITORY_ID WHERE REPOSITORY0_.OWNER_LOGIN=USER1_.USER_LOGIN AND REPOSITORY0_.REPOSITORY_ID=?"; expected "*, NOT, EXISTS, INTERSECTS, UNIQUE"; SQL statement:
select repository0_.repository_id as col_0_0_, repository0_.repository_name as col_1_0_, user1_.user_name as col_2_0_, . as col_3_0_, commits2_.commit_id as commit_i1_0_, commits2_.date as date2_0_, commits2_.message as message3_0_, commits2_.repository_id as reposito4_0_ from repository repository0_ cross join user user1_ inner join commit commits2_ on repository0_.repository_id=commits2_.repository_id where repository0_.owner_login=user1_.user_login and repository0_.repository_id=? [42001-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
    at org.h2.message.DbException.getSyntaxError(DbException.java:243)
    at org.h2.command.Parser.getSyntaxError(Parser.java:1053)
[...]

I believe the problem comes from the fact that I'm trying to select a OneToMany relation, since I don't have this issue when selecting the owner because I have an @JsonIgnore annotation and infinite loops...
Except, in this case, I NEED to select the list of transaction :(

Thanks in advance for your help !

Solution

  • The error in your sql query is the r.commits part. It is a list of values and sql column accept only a single (or scalar) value type like number, varchar etc

    Since the relation between Repository and Commit entities isOne-To-Many association so r.commits is a list of values so Hibernate fails to prepare the sql statement.

    You can remove the r.commits part from your sql query and it will works.

    If you would like to get the list of commits for your repository, you can implement a specefic method for that. Something like

    public interface CommitRepository extends JpaRepository<Commit, Long> {
      List<Commit> findAllByRepository(Repository repository);
}

    you can pass to this method the repository object that you want to get all the associated commits.

    Also, since the Repository.repositoryId is Long data type so you have to change the RepositoriesDB defenition to

    public interface RepositoriesDB extends JpaRepository<Repository, Long>