Supporting SQL Function around inserting value [DATAJDBC-468]

[spring-projects-issues]

LaoTsing opened DATAJDBC-468 and commented

We can use annotations to insert value as any SQL function:

@UpdateStrategy(JdbcStrategy.UpdateIsNotNull)

public class DocEntity {

   @Id private Long id;

   private String english;

   @Fun("translate('french', english)") private String french;

}

 

in annotation Fun("translate('french', ?)") we can select character "?" and get value from any primitive type of "french" field

then repository.save(doc) with english == null generate:

update doc set ``french=translate('french', ``english``) where id=?

this will greatly simplify the logic and source code.

In SELECT case, the annotated functions can reveal their full potential

---

Issue Links:

• DATAJDBC-397 SpEL support for @Query annotation

[spring-projects-issues]

Jens Schauder commented

I have multiple questions about this:

Where is translate supposed to be defined. Is it supposed to be a SQL function or a Java function?

If it is a Java function why isn't it simply applied in the constructor or in an Entity Callback

If it is a SQL function it sounds more like a case for a trigger

[spring-projects-issues]

LaoTsing commented

The database has a lot of built-in functions, for example, a to_tsvector, as well as user-defined functions - in high-loaded systems, often most of the business logic is found in the database

[spring-projects-issues]

Jens Schauder commented

So you are saying translate resides in the database.

Then why not use simply a database trigger? And possibly make the field read only?

[spring-projects-issues]

LaoTsing commented

Because when you work with big data, the tables are divided into partitions, and in them the triggers have only the AFTER property, not possible insert new value

[spring-projects-issues]

Jens Schauder commented

That is interesting. For which database is that the case?

You only mentioned insert but I'd expect the same logic to be applied on update.

As for the syntax, I'd imagine something that allows to provide arbitrary SQL snippets with SpEL support so that attributes of the entity may be accessed

[spring-projects-issues]

LaoTsing commented

The database is a PostgreSQL and right, the same logic will apply when updating.

As for the syntax, I'd imagine something that allows to provide arbitrary SQL snippets with SpEL support so that attributes of the entity may be accessed.

fully support! it is a killer feature

[spring-projects-issues]

LaoTsing commented

Native support of SQL Function annotations in entity will significantly reduce and simplify the source code on the back end side and DB side, as well as circumvent the current database restriction regarding triggers in inherited tables