Hi Aurélien, Thank you for the interest you put in jOOQ. Comments inline:
2013/8/6 Aurélien Manteaux <[email protected]> > 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 [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
