CODEWITHSUNDEEP
javahibernatejpahibernate-mapping
Jeff Walker
Jeff Walker

Reputation: 1704

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

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:

  1. 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

  2. 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]

  3. I've looked at Generated columns, but those are for values generated by the database. mapping-generated

  4. 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.

Upvotes: 1

Views: 3291

Answers (2)

Jade
Jade

Reputation: 784

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

Upvotes: 1

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153780

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

Upvotes: 3

Related Questions