Database agnostic way to filter entities?

Question

I use Spring Data, Hibernate and Microsoft SQL Server. I have 2 entities: Role and Privilege. One role has many privileges. Privileges have VALID_TO column. I want every role to have only privileges that are still valid (i.e., VALID_TO <= GETDATE()).

The simplest way I found to implement this is by annotating the Privilege entity with the Hibernate @Where annotation, like this:

// ...
@Where(clause = "VALID_TO <= GETDATE()")
class Privilege {
    // ...
}

But the disadvantage of this approach is that it uses Transact-SQL; it will cause additional rework in case of migration to another database. Also, this approach makes the code dependent on Hibernate annotations.

Is there a way to filter out the privileges using pure Spring Data?

@SeanLange, good point. :) Going to delete the sql-server tag. — yaskovdev, Apr 17 '18 at 18:37
Unless there are a large number of privileges per role you could just filter on the in-memory instance rather than at the database layer i.e. do not expose the privileges collection and have a method `Role.getActivePrivileges();` Otherwise you could map the relationship to a view (if the data is writable them this would require the db to support updateable views ) — Alan Hay, Apr 18 '18 at 07:45
@AlanHay, thank you for the hint! I think I'll stick with the in-memory filtering indeed. Just out of curiosity, am I correct that the second approach (with the view) still depends on a concrete database? Correct me if I'm wrong, but to create the view I still need to write the SQL query for it somewhere (using plain SQL). — yaskovdev, Apr 19 '18 at 06:02

score 0 · Answer 1 · answered Aug 20 '21 at 07:42

You could use jOOQ to translate the SQL to any other dialect of your choice, either using the programmatic API (in case of which you could generate your predicate dynamically), or the parser, which can also be placed in between your application and the JDBC driver in the form of a JDBC driver proxy. That way, you can continue to use GETDATE(), and depending on the target dialect, you'd be getting hte right output.

Of course, for the specific case of your question, using CURRENT_TIMESTAMP may be your safest bet, because it's widely supported (including SQL Server), so just for this case you won't need any other third party.

Disclaimer: I work for the company behind jOOQ

Database agnostic way to filter entities?

1 Answers1