Spring Data JPA "The column name Id is not valid"

Question

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

I have the follwing repository:

public interface BatchRepository extends PagingAndSortingRepository<Transaction, String> {

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

}

My entity backing the query is:

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

I keep getting an error:

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,

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

My query does not reference an Id field at all?

What if you capitalize Id here: ```public void setId(String id) { this.id = id; } ``` — user1443098, Jun 14 '18 at 14:28
No change. I didn't think that would work as I am using this same entity in a standard CrudRepository without issue. The change here is I am using raw sql to get a projected object. — JBenn, Jun 14 '18 at 14:33
I updated the question with the table def. I do not think it is the query. The query runs fine in SSMS. Is there something specific with Hibernate ORM mapping? — JBenn, Jun 14 '18 at 14:41
Try with `@GenericGenerator(name = "generator", strategy = "uuid2")` — EzLo, Jun 14 '18 at 15:13
Also worth mentioning https://stackoverflow.com/questions/42559938/hibernate-uuid-with-postgresql-and-sql-server/48918942#48918942 — EzLo, Jun 14 '18 at 15:16
@EzLo - that didn't fix it - but did solve a build warning I was getting (deprecated method) - just hadn't gotten to fixing it yet. I found out what I was doing wrong. Posted my answer below. — JBenn, Jun 14 '18 at 16:28

score 0 · Answer 1 · answered Jun 14 '18 at 16:27

So I figured out what is going on.

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

}

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.

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

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.

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

Spring Data JPA "The column name Id is not valid"

1 Answers1

Linked