1

I'm trying to use column name that is a reserved keyword in hibernate. Even with quotes i'm still getting errors.

I need to create a subquery using @Formula.

Table looks like this below, notice the column named "row", we are querying this from an external system and this cannot be changed. This is the source of the problem.

CREATE TABLE MyTestTable (
    id int,
    `row` int NOT NULL,
    column1 int NOT NULL DEFAULT 0,
    column2 int NOT NULL DEFAULT 0,
    PRIMARY KEY(id, `row`)
);

Entity class looks like this:

@Entity
public class MyTestTable implements Serializable {
    @Id
    private int id;

    @Id
    @Column(name = "[row]")
    private int row;

    @Formula("select o.column1 + o.column2" +
            " from MyTestTable o where o.row = `row` AND o.id = id")
    private int calculatedSum;

    ... getters and setters
}

(calculatedSum uses 20+ columns in the real world)

When trying to get the value of calculatedSum i get the error:

Syntax error in SQL statement "select select o.column1 + o.column2 from MyTestTable o where o.row[*] = mytesttabl0_.""row"" AND o.id = mytesttabl0_.id as col_0_0_ from MyTestTable mytesttabl0_ where mytesttabl0_.id=1 and mytesttabl0_.""row""=1 limit ?"; expected "identifier";

The problem here is that since row is a reserved keyword, the query ends up giving a syntax error (in o.row). This is the point where you would think that adding quotes around it would be the solution.

@Formula("select o.column1 + o.column2" +
            " from MyTestTable o where `o.row` = `row` AND o.id = id")
    private int calculatedSum;

But this gives another error:

16:18:30.547 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Column "mytesttabl0_.o.row" not found; SQL statement: select select o.column1 + o.column2 from MyTestTable o where mytesttabl0_."o.row" = mytesttabl0_."row" AND o.id = mytesttabl0_.id as col_0_0_ from MyTestTable mytesttabl0_ where mytesttabl0_.id=1 and mytesttabl0_."row"=1 limit ? [42122-199]

The o.row has now been prefixed with an additional alias giving me mytesttabl0_.o.row Which should just be o.row

How do i write a working reference to MyTestTable.row in this scenario?

Tom Bombadil
  • 53
  • 1
  • 6
  • What is your underlying SQL database (e.g. MySQL, SQL Server, Oracle, Postgres, etc.) ? – Tim Biegeleisen Nov 13 '19 at 15:34
  • Unit tests are on H2, Production on MySQL. If we can get this passing on unit tests ill mark that as the answer. – Tom Bombadil Nov 13 '19 at 15:41
  • Just as an aside, it's usually a bad idea to use two different database for testing and development. So, you might want to just use MySQL everywhere. I suspect that you're not really doing unit testing in some cases, but rather _integration_ testing, where you actually hit the H2 database running in memory. – Tim Biegeleisen Nov 13 '19 at 15:44
  • Ideally this would be the way to go, but i have yet to figure out a way to run MySQL as an in-memory database for testing. And it is true that we should call these integration tests, and not unit tests. I think these terms may have become a bit mixed. – Tom Bombadil Nov 13 '19 at 15:54

1 Answers1

0

According to this accepted answer, what appears inside @Formula has to be a valid SQL fragment, and it's not possible to refer to any fields inside your class; you may only refer to SQL columns and tables. Assuming you just want Hibernate to populate the calculatedSum field with the sum of column1 and column2, you should be using:

@Formula(" column1 + column2 ")
private int calculatedSum;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Well done sir! Seems i overcomplicated things, matching on primary keys were unnescessary. – Tom Bombadil Nov 13 '19 at 15:48
  • Yeah...so the thing is, the logic you were using seemed to just be ensuring that the sum uses the values from the current entity, but `@Formula` already handles this for you. – Tim Biegeleisen Nov 13 '19 at 15:49
  • Ye, i thought those could only be referenced like that if they were already mapped up in the entity class through fields. – Tom Bombadil Nov 13 '19 at 15:52