Spring Data JPA“列名Id无效"

问题描述：

我正在尝试使用 Springboot 项目对 SQL Server 数据库执行原始 SQL 查询.

I am trying to execute a raw SQL query against a SQL Server db using a Springboot project.

我有以下存储库:

public interface BatchRepository extends PagingAndSortingRepository<Transaction, String> {

@Override
@Query(value = "SELECT DISTINCT(T.BatchNumber), T.Operator FROM TABLE T", nativeQuery = true)
Iterable<Transaction> findAll();

}

我支持查询的实体是:

@Entity
@Table(name = "TABLE")
public class Transaction implements EntityId<String>, CreatedDateTime, ModifiedDateTime{

 ....omitted for brevity

@Id
@GenericGenerator(name = "generator", strategy = "guid")
@GeneratedValue(generator = "generator")
@Column(name = "Id", columnDefinition = "uniqueidentifier")
public String getId() {
    return id;
}

public void setId(String id) {
    this.id = id;
}

@Column(name = "BatchNumber")
public String getBatchNumber() {
    return batchNumber;
}

public void setbatchNumber(String batchNumber) {
    this.batchNumber = batchNumber;
}

@Column(name = "Operator")
public String getOperator() {
    return operator;
}

public void setOperator(String operator) {
    this.operator = operator;
}

我不断收到错误消息:

Hibernate: 
SELECT
    DISTINCT(T.BatchNumber),
    T.Operator
FROM
    TABLE T
2018-06-14 09:10:06.744  WARN 2844 --- [apr-8080-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S1093
2018-06-14 09:10:06.744 ERROR 2844 --- [apr-8080-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : **The column name Id is not valid.**

这是表定义.(注意我已经替换了一些列/表名)

Here is the table definition. (Note I have replaced some of the column / table names)

CREATE TABLE [dbo].[Table](
[Id] [uniqueidentifier] NOT NULL,
[BatchNumber] [nvarchar](max) NULL,
[JobType] [nvarchar](max) NULL,
[Operator] [nvarchar](max) NULL,
[TransactionNumber] [int] NULL,
[Status] [nvarchar](100) NULL,
[CreatedOn] [datetime] NOT NULL,
[ModifiedOn] [datetime] NOT NULL,

此外，此查询在 SSMS 和 Intellij 中的数据库窗口中也能正常执行.这不仅仅是查询.

Also this query executes fine in SSMS and the database window in Intellij. It is not the query alone.

我的查询根本没有引用 Id 字段?

My query does not reference an Id field at all?

答

所以我知道发生了什么.

So I figured out what is going on.

我需要将数据返回到的模型需要为 select 子句中的每一列设置 setter，并且 setter 需要匹配 select 子句中的内容.换句话说，模型中不能有空属性.

The model I need to return the data to needs to have setters for every column in the select clause AND the setters need to match what is in the select clause. In other words you cannot have empty properties in the model.

就我而言，我有一个界面

In my case I have an interface

public interface EntityId<T> {
T getId();
void setId(T id);

}

我用在我所有的实体上.基本上，我有基础抽象类，允许我在所有实体中按 Id 进行搜索.

That I use on all of my Entities. Basically I have base abstract classes that allow me to search by Id across all of my entities.

使用该接口 - 我最终在我的支持实体中得到了一个无关的 setter (id)，而这些 setter (id) 不在我的 select 语句中.

Using that interface - I end up with an extraneous setter (id) in my backing entity that were not in my select statement.

因此，对于 Springboot 和 JPA 的任何其他新人 - 当您冒险超出实体类和数据库表之间的 1:1 映射(即连接或任何其他形式的原始 sql/JPQL 查询)时 - 您需要确保 select 子句中的列与模型中的属性对齐.

So for any other new comer to Springboot and JPA - when you venture outside of a 1:1 mapping between your entity class and database table (i.e. a join or any other form of raw sql / JPQL query) - you need to make sure your columns in your select clause line up with the properties in the model.

我只是创建了一个具有正确属性的新 @Entity 模型.

I simply created a new @Entity model with the correct properties.

Spring Data JPA“列名Id无效"

相关推荐