Hibernate/JPA property mapping result of database function with other columns

Question

I have a situation where I'm using Hibernate (5.2.16) to map a table and one of the column values is constructed via a database function that takes the values of two other properties.

For some background, this is a SDE spacial table with a ST_GEOMETRY column. As far as I can tell, this isn't compatible with the two types of spacial APIs supported by Hibernate, but even if it was, I'm not doing any spacial manipulation, so I don't really need them, I just want to insert and update the geometry column.

I have absolutely no control over the structure of the table because it's dictated by another group using another tool (GIS).

Things I've tried:

Using a Hibernate UserType. The problem with this is that I only see a way to get and set the value with a PreparedStatement, without the ability to dictate actual SQL used. basic-custom-type
Using a Hibernate ColumnTransformer. This gives me direct control over the SQL used, but I can't use the values of two other properties in the SQL. mapping-column-read-and-write
```
@Column(name="LATITUDE")
private BigDecimal latitude;
@Column(name="LONGITUDE")
private BigDecimal longitude;

@ColumnTransformer(
read="sde.st_astext(shape)",
write="sde.st_transform(sde.st_point(LONGITUDE,LATITUDE, 4326), 3857)"
)
@Generated(value=GenerationTime.ALWAYS)
@Column(name="SHAPE")
private String shape;
```
I get:

org.hibernate.AnnotationException: @WriteExpression must contain exactly one value placeholder ('?') character: property [shape] and column [SHAPE]
I've looked at Generated columns, but those are for values generated by the database. mapping-generated
I've looked at Formula columns, but those are for values calculated and usable in Java, but aren't inserted or updated. mapping-column-formula
```
@Formula(value="sde.st_astext(shape)")
private String shape;
```
It's useful for some things, but I can't insert or update this.

I'm hoping that I've missed something. At this point I'm considering non-Hibernate/JPA solutions. This would be relatively easy with raw SQL and JDBC, but the rest of the table would be annoying and not match the rest of my code. I'd also have to do my own dirty checking and stuff.

Vlad Mihalcea · Accepted Answer · 2018-04-20 18:36:39Z

3

You can use the Hibernate database generated value. It allows you to call database functions to generate entity properties.

edited Apr 20, 2018 at 18:36

answered Apr 19, 2018 at 21:12

Vlad Mihalcea

156k85 gold badges598 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jeff Walker Over a year ago

I think I can use this to group the 3 properties, but how can I apply the database function to construct the SHAPE value? I'm thinking that the value generated would be passed to a prepared statement parameter. Or does the ColumnTransformer come into play here?

Vlad Mihalcea Over a year ago

Use a DB trigger for the calculated column. This way, you only need to update two columns. The third can a Formula

Jeff Walker Over a year ago

It's a good suggestion, but unfortunately, that's not an option in this case. I am merely one of many updating this table and the trigger would interfere with the other uses of the table.

Jeff Walker Over a year ago

Sorry if I'm being dense, but what do I do in there? I can use the regular process to update regular the properties on this entity, but can I then do a SQL database update for this weird column? I don't think I can change the SQL used or anything.

Vlad Mihalcea Over a year ago

I updated my answer with an approach Hibernate offers for this use case.

|

Erin · Accepted Answer · 2019-08-27 02:31:30Z

1

This is a database-specific answer, but given your GIS problem domain and the dominance of PostGIS it may be relevant (if you use PostgreSQL and your DBA is OK with an upgrade).

PosgreSQL 12 introduces generated columns which you could define using something similar to the following:

@Column(columnDefinition = "GEOMETRY GENERATED ALWAYS AS st_transform(st_point(LONGITUDE,LATITUDE, 4326), 3857)) STORED")

answered Aug 27, 2019 at 2:31

Erin

7937 silver badges17 bronze badges

2 Comments

Jeff Walker Over a year ago

That doesn't apply to me, but thanks for this. It will be helpful for others in the future.

Erin Over a year ago

PostgreSQL is relatively late in supporting generated columns - they are part of the SQL standard and may be supported in your DB already. But it behaves much like a trigger in that it is part of the table definition and will apply to all inserts/updates which it seems is not an option for you.

Collectives™ on Stack Overflow

Hibernate/JPA property mapping result of database function with other columns

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related