Jooq cache layer

[Aurélien Manteaux]

Hi Lukas,

I want to create a cache layer on top of Jooq. For now, I only have thought on how I want the cache layer to behave and how it should connect to Jooq. I would like to have your opinion on the layer "architecture". Moreover I would like to have an advice on how I could extract the table names used in a Jooq query : I have found 2 solutions, but none of them are satisfactory.

Let's start with the code generation. I want for a table named "user" to generate this hierarchy :

beans

  User.java (extends UserPojo, generated only if the file does not exist)

daos

  UserDao.java (extends AbstractUserDao<User>, generated only if the file does not exist)

generated

  table

    daos

      AbstractUserDao.java (public abstract class AbstractUserDao<T extends UserPojo> extends CachedDAOImpl<UserRecord, T, Long>)

    interfaces

      IUser.java (remain the same)

    pojos

      UserPojo.java (only the class name change, I want to distinct the "real" User object from the generated ones)

    records

      UserRecord.java (remain the same)

    UserTable.java (only the class name change, I want to distinct the "real" User object from the generated ones)

  Keys.java (remain the same)

  Tables.java (remain the same)

  BaseName.java (remain the same)

To summarize, I want to change two things from the original Jooq codegen :

- One time generation of the User and UserDao classes. It enables to extend the generated AbstractUserDao and UserPojo classes.

- UserPojo and UserTable rename. It enables to easily distinct each generated classes. I don't like having many public classes with the same name in a project.

Now the real layer : CachedDAOImpl class. This class should inherit the original DAOImpl class. It should add :

- a useCache() method overridable (default implementation returns false),

- the possibility to cache a query result,

- the clearCache() method.

If the useCache() method returns true, all "simple" accesses should be cached, ie fetchById and fetchOneByUniqueField. The cache properties (maxSize, expire etc) should be parametrized in the application, but it could be good to be able to override the default cache properties in the Dao. Anyway, when an object is cached with fetchById, the cache is clean for the id "k", if the row with the id "k" is updated or removed (works only if the delete, or update methods are called from the Dao). That part is easy.

Now comes the problematic part. I want to provide a way to cache any queries with possibly Joins. To cache a query like that, I need to :

- identify the query : DSLContext.renderNamedParams(query),

- concatenate all the query parameters to create the cache entry : DSLContext.extractBindValues(query),

- identify the table names used in the query (to be able to clear the cache for the query when any of the tables used in the query are being updated).

I have found two ways to cache a query result in the dao :

- adding selectFromCached(Table) and selectCached(Fields) methods. It would returned a slightly modified SelectImpl. However, the delegate class (currently SelectQueryImpl), will be replaced. This way I can get the table names with the getFrom() method and I can override the fetch() method from AbstractResultQuery to be able to either execute the query or return the cached result. Wait, the fetch() method is final, it cannot be overridden :'(. I have to directly override the AbstractResultQuery class to remove the final modifier :/.

- the second solution is to provide methods like fetchCached(Select query): List<User>. This way I have to hack though the query with Java reflection to get the from table names.

I would prefer to use the first solution. However, if you specified the fetch() method as final in the AbstractResultQuery class, I think you had a reason. So it would be wrong to just override the AbstractResultQuery class to remove the final keyword.

What would you do to provide a way to easily cache a query ?

Thank you for your time. I would be very happy to have a Jooq cache layer working :)

Aurélien

PS: About caching, I am well aware that too much caching kills the cache system. An application cache system should be set up only if the application really needs it. However, caching DB query results is often a very simple way to increase the application execution speed.

[Lukas Eder]

Hi Aurélien,

Thank you for the interest you put in jOOQ. Comments inline:

2013/8/6 Aurélien Manteaux <aman...@gmail.com>

Hi Lukas,

I want to create a cache layer on top of Jooq. For now, I only have thought on how I want the cache layer to behave and how it should connect to Jooq. I would like to have your opinion on the layer "architecture". Moreover I would like to have an advice on how I could extract the table names used in a Jooq query : I have found 2 solutions, but none of them are satisfactory.

Let's start with the code generation. I want for a table named "user" to generate this hierarchy :

beans

  User.java (extends UserPojo, generated only if the file does not exist)

daos

  UserDao.java (extends AbstractUserDao<User>, generated only if the file does not exist)

generated

  table

    daos

      AbstractUserDao.java (public abstract class AbstractUserDao<T extends UserPojo> extends CachedDAOImpl<UserRecord, T, Long>)

    interfaces

      IUser.java (remain the same)

    pojos

      UserPojo.java (only the class name change, I want to distinct the "real" User object from the generated ones)

    records

      UserRecord.java (remain the same)

    UserTable.java (only the class name change, I want to distinct the "real" User object from the generated ones)

  Keys.java (remain the same)

  Tables.java (remain the same)

  BaseName.java (remain the same)

To summarize, I want to change two things from the original Jooq codegen :

- One time generation of the User and UserDao classes. It enables to extend the generated AbstractUserDao and UserPojo classes.

- UserPojo and UserTable rename. It enables to easily distinct each generated classes. I don't like having many public classes with the same name in a project.

Custom code generation can be achieved in various ways. Most of them are documented here:

http://www.jooq.org/doc/3.1/manual/code-generation/codegen-advanced

Of course, you can always implement your own code generation extension, should you want to generate custom DAOs.

If something is not (yet) feasible, feel free to issue a feature request.

Now the real layer : CachedDAOImpl class. This class should inherit the original DAOImpl class. It should add :

- a useCache() method overridable (default implementation returns false),

- the possibility to cache a query result,

- the clearCache() method.

If the useCache() method returns true, all "simple" accesses should be cached, ie fetchById and fetchOneByUniqueField. The cache properties (maxSize, expire etc) should be parametrized in the application, but it could be good to be able to override the default cache properties in the Dao. Anyway, when an object is cached with fetchById, the cache is clean for the id "k", if the row with the id "k" is updated or removed (works only if the delete, or update methods are called from the Dao). That part is easy.

Just in case you were thinking of requesting a feature addition in jOOQ:

In particular, I don't think that the solution described above is versatile enough for the many use-cases that might appear in jOOQ's users' applications.

Nonetheless, it's interesting to be able to add such features at your side, by custom code generation.

If some sort of caching were introduced in jOOQ, it would have to be on a lower level, closer to JDBC.

 

Now comes the problematic part. I want to provide a way to cache any queries with possibly Joins. To cache a query like that, I need to :

- identify the query : DSLContext.renderNamedParams(query),

- concatenate all the query parameters to create the cache entry : DSLContext.extractBindValues(query),

Note, you can also generate a SQL string with inlined bind variables to have a unique cache key:

http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#renderInlined(org.jooq.QueryPart)

(there are also other ways to achieve the same)

 

- identify the table names used in the query (to be able to clear the cache for the query when any of the tables used in the query are being updated).

This will be simplified in jOOQ 3.2, when the new SQL rendering and variable binding SPI is in place:

https://github.com/jOOQ/jOOQ/issues/2665

You will be able to intercept events, such as the rendering of a Table QueryPart, in the context of the surrounding statement and clause (e.g. SELECT -> SELECT_FROM -> FROM_JOIN -> TABLE).

However, to stay on the safe side, you might also think about using DB-messaging with triggers:

http://stackoverflow.com/a/12619031/521799

This way, you can be very sure that caches are invalidated every time data is modified.

 

I have found two ways to cache a query result in the dao :

- adding selectFromCached(Table) and selectCached(Fields) methods. It would returned a slightly modified SelectImpl. However, the delegate class (currently SelectQueryImpl), will be replaced. This way I can get the table names with the getFrom() method and I can override the fetch() method from AbstractResultQuery to be able to either execute the query or return the cached result. Wait, the fetch() method is final, it cannot be overridden :'(. I have to directly override the AbstractResultQuery class to remove the final modifier :/.

Yes, jOOQ was not yet disclosed to WikiLeaks:

http://blog.jooq.org/2012/10/26/wikileaks-to-leak-5000-open-source-java-projects-with-all-that-privatefinal-bullshit-removed

;-)

 

- the second solution is to provide methods like fetchCached(Select query): List<User>. This way I have to hack though the query with Java reflection to get the from table names.

I would prefer to use the first solution. However, if you specified the fetch() method as final in the AbstractResultQuery class, I think you had a reason.

So it would be wrong to just override the AbstractResultQuery class to remove the final keyword.

No, it wouldn't be "wrong". But jOOQ's using final almost everywhere is a strong indicator that overriding behaviour is not recommended.

I prefer to provide public "DefaultXXX" or "CustomYYY" classes to allow for injecting custom behaviour. Arbitrary overriding of concrete methods is not recommended.

It is always better to discuss such features on the user group to try to get some traction for new, highly reusable SPIs, rather than hacking into jOOQ's internals. But that choice is up to you, of course.

 

What would you do to provide a way to easily cache a query ?

I'd think about using the jOOQ MockConnection:

http://blog.jooq.org/2013/02/20/easy-mocking-of-your-database

The MockDataProvider receives the rendered SQL String and bind values. It can then decide whether it will return a cached jOOQ Result object, or whether it will delegate query execution to the "real" JDBC Connection.

That is just one way of doing this, of course. Another might be to use third-party products, such as EhCache, either on the DAO layer or on the JDBC layer:

http://ehcache.org/documentation/integrations/jdbc

As always, I'm very interested what other people on this group think about this.

Cheers

Lukas

 

Thank you for your time. I would be very happy to have a Jooq cache layer working :)

Aurélien

PS: About caching, I am well aware that too much caching kills the cache system. An application cache system should be set up only if the application really needs it. However, caching DB query results is often a very simple way to increase the application execution speed.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ben Hood]

Hey Lukas,

On Wed, Aug 7, 2013 at 10:58 AM, Lukas Eder <lukas...@gmail.com> wrote:
> However, to stay on the safe side, you might also think about using
> DB-messaging with triggers:
> http://stackoverflow.com/a/12619031/521799

Very cool - I can think of a few use cases for this.

Cheers,

Ben

[Aurélien Manteaux]

Hey Lukas,

Thank you for your quick answer !

I'll take your advice and try to write a cache close to jdbc, your solution with jOOQ MockConnection seems promising.
I will also wait for the new SQL rendering and variable binding SPI to enable the cache to be correctly cleared.

As soon as I have something working, I will share it here !

Cheers
Aurélien

[Lukas Eder]

Hello Aurélien,

2013/8/10 Aurélien Manteaux <aman...@gmail.com>

Hey Lukas,

Thank you for your quick answer !

I'll take your advice and try to write a cache close to jdbc, your solution with jOOQ MockConnection seems promising.
I will also wait for the new SQL rendering and variable binding SPI to enable the cache to be correctly cleared.

As soon as I have something working, I will share it here !

Great! Feature requests and improvement hints to the MockConnection are very welcome, too!

Cheers

Lukas

[she...@sminq.com]

Hi Lukas,

I tried the technique provided by Aakash, and it works well. But i have come across another issues.

Since the above method uses a custom VisitListener, does this have any effect on record inserts?. I am currently facing the issues with a null id returned when a record is stored in DB.

e.g record.store() the autoincrement id is null, but if is switch back to the original VisitListener things work fine.

Am i missing something here?

[Lukas Eder]

Hello Sheldon,

Thank you very much for the feedback. Great to know that it has worked for you.

Yes, VisitListener (and all the other SPI like ExecuteListener) also apply for Record.store(), Record.insert(), etc. as these store(), insert(), methods are just convenience to access the more general SQL API, such as DSLContext.insertInto(...).values(...).execute();

Now, there are two possibilities here:

1. You have discovered a bug in jOOQ

2. You have discovered a flaw in that VisitListener implementation by Aakash or in the adaptations that you may have made.

I guess I would need to see a fully reproducible test case to be sure on my side. Did you continue investigating, e.g. by debugging?

Cheers,
Lukas

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Sheldon D'Souza]

Hi Lukas,

The issue is on my side, and i need some advice on how to resolve it. I am currently using Jooq with Dropwizard.

So the jooq configuration is created at app launch and then injected into my services, so the configuration is global across the app.

Now introducing the caching layer i have to include MockDataProvider

Original jooq connection

DSLContext create = DSL.using(configuration)

Jooq with caching

configuration.set(new DefaultVisitListenerProvider(new CacheVisitListener()));

configuration.set(new MockConnection(new ResultCache(configuration.connectionProvider().acquire())));

return DSL.using(configuration);

Now since the configuration is global, i think because of the MockConnection object whenever i insert an UpdatableRecord the primary key id is null. As a result my test cases are failing.

How do i overcome this issue? is there a way for me to create a new connection from the original one and is that a good idea?

Regards,

Sheldon

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/xSjrvnmcDHw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hello Sheldon,

2015-08-11 19:00 GMT+02:00 Sheldon D'Souza <she...@sminq.com>:

Hi Lukas,

The issue is on my side, and i need some advice on how to resolve it. I am currently using Jooq with Dropwizard.

So the jooq configuration is created at app launch and then injected into my services, so the configuration is global across the app.

Now introducing the caching layer i have to include MockDataProvider

Original jooq connection

DSLContext create = DSL.using(configuration)

That's fine.

 

Jooq with caching

configuration.set(new DefaultVisitListenerProvider(new CacheVisitListener()));

Note, though, if you have a single global Configuration, you must not call any setters on it. You may, however, call derive() to create a new Configuration from your global one.

That means that your CacheVisitListener must not have any (non-global) state. You're initialising it only once for all configuration access. What I meant was something like:

configuration.set((VisitListenerProvider) () -> new CacheVisitListener());

This means that while the VisitListenerProvider is shared across all configuration accesses, the CacheVisitListener is created afresh for every query.

configuration.set(new MockConnection(new ResultCache(configuration.connectionProvider().acquire())));

You should never call acquire() yourself, because every call to acquire must be matched with a call to release(), once the query has executed. It's better for your ResultCache to simply know the Configuration or the ConnectionProvider, and possibly acquire a connection on the fly.

 

return DSL.using(configuration);

Now since the configuration is global, i think because of the MockConnection object whenever i insert an UpdatableRecord the primary key id is null. As a result my test cases are failing.

Unfortunately, I cannot comment on that from the description I've seen so far.

 

How do i overcome this issue? is there a way for me to create a new connection from the original one and is that a good idea?

I don't think you're on track here. jOOQ makes the assumption that connection management has been taken care of externally. I.e. by your transaction provider, container, Spring, etc. jOOQ will never explicitly create connections or have a need for creating connections. Also, note that a JDBC connection models a session from your Java client with your database. Sessions are mutually independent from one another, and you cannot access data inserted in one session from another session directly.

Although, again, I'm not sure what made you think that connection handling is causing the null primary key issue - I'd need to see more code, perhaps.

I hope this helped, so far. If you have additional questions, just let me know

Cheers,

Lukas 

[Sheldon D'Souza]

Hi Lukas,

thanks for the information, instead of the connection i am now passing the configuration to the ResultCache

configuration.set((VisitListenerProvider) () -> new CacheVisitListener());

DSL.using(new MockConnection(new ResultCache(configuration)),SQLDialect.MYSQL);

But when the ResultCache executes the sql the CacheVisitListener is not called.

Regards,

Sheldon

--

[Lukas Eder]

DSL.using(Connection, SQLDialect) 

is short for 

DSL.using(new DefaultConfiguration().set(connection).set(dialect));

If you have your own custom Configuration, you should use that, instead of creating a new one via DSL.using(connection, dialect).

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Sheldon D'Souza]

Hi Lukas,

Thanks for the information, it finally works when i use it this way

Configuration newConfiguration = configuration.derive();

        newConfiguration.set(new MockConnection(new ResultCache(configuration))).set(SQLDialect.MYSQL);

        return DSL.using(newConfiguration);

[Lukas Eder]

Yes, that looks reasonable.

Should you find any additional issues, just let us know!

Cheers

Lukas