“無法準備宣告”OneToMany-有解無憂

這是我想要實作的目標：

撰寫一個提供存盤庫詳細資訊的 Web 服務：id、名稱、用戶名和提交串列。

?? 需要以JSON格式回傳，例如：

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

您將在下面找到資料庫結構： “無法準備宣告” OneToMany

這是我CrudRepository嘗試構建的查詢：

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

我的User班級：

@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;
}

我的Repository班級：

@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;
}

我的Commit班級：

@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;
}

最后，這是堆疊跟蹤：

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)
[...]

我相信問題出在我試圖選擇一個OneToMany關系的事實，因為我在選擇所有者時沒有這個問題，因為我有一個@JsonIgnore注釋和無限回圈......
除了，在這種情況下，我需要選擇交易串列:(

在此先感謝您的幫助！

uj5u.com熱心網友回復：

您的 sql 查詢中的錯誤是r.commits部分。這是價值觀和SQL列的串列只接受一個single (or scalar)值型別一樣number，varchar等

由于Repository和Commit物體之間的關系是One-To-Many關聯，所以r.commits值串列也是如此，因此 Hibernate 無法準備 sql 陳述句。

您可以從 sql 查詢中洗掉 r.commits 部分，它會起作用。

如果您想獲取存盤庫的提交串列，您可以為此實作特定的方法。就像是

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

您可以將repository要獲取所有相關提交的物件傳遞給此方法。

此外，由于Repository.repositoryId是Long資料型別，因此您必須將RepositoriesDB防御更改為

public interface RepositoriesDB extends JpaRepository<Repository, Long>

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/402057.html

標籤：春天弹簧靴 jpa spring-repositories

上一篇：當表名包含DOT時，JPA會拋出“INSERT命令被拒絕給表'XXX'的用戶”

下一篇：如何按不同的連接表列動態排序