Hello Everybody, I am new to jOOQ community, I discovered lately this database framework thanks to my collegue and i really love it. Me and my collegue wanted to use this framework in already made web application (initially written by another software company) to replace the Hibernate Criteria API.
My collegue already used it for new functionalities but to replace Criteria API we encountered a problem due to the software architecture. Infact, the DAO Layer of this web application, think in terms of Hibernate Entity (for the input (sometimes) and the output (always)), and it's impossible replace the the Criteria API without modify all the stack in order to remove the reference to Hibernate Entity. Also if we populate the root Entity doesn't work, because the upper layer asks for associations (eagerly or lazely fetched by Hibernate) and if the Entity is not attached to a Hibernate Session, a LazyInitializationException will be thrown. So I just thought about a way to resolve this problem and to use jOOQ to replace Criteria API without modifying the DAO interfaces and the upper layer (actually we don't have such time, we prefer to focus only on the query optimization). These are my initial consideration: - jOOQ can used to only generate SQL Code (.getSQL() method) - Hibernate can execute SQL Code through the SQLQuery API So at the start i tried this approach: DSLContext dsl = ...; String sql = dsl.select().from(...).join(...).on(...).getSQL(); SQLQuery query = session.createSQLQuery(sql); query = query.addEntity(Entity.class); query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); List<Entity> result = query.list(); or if I expect only one result from the query: Entity result = query.uniqueResult(); This approach seems to work fine. SQLQuery return the entities with an attached session of hibernate, so if I ask for the associations, hibernate can load it through the proxy making ad-hoc query. But it's unaccettable for our web application to load only the root entities, because the upper layer ask often for the associations, and replace old implementation based on Criteria with these approach brings to inefficient procedure. So I searched the SQLQuery API and I found these reference: https://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch13.html#d5e3571 It's possible to tell to load also the association if we declare explicitly with the method .addJoin(String tableAlias, String path); So for example if it's envolved a new entity in the query and we want to map it we can write: query = query.addEntity(Entity.class, "e") .addJoin(Entity2.class, "e.entity2"); In the chapter "Returning multiple entities", is described that to avoid problem with column name duplicates, we should write in the select query {tableAlias1.*}, {tableAlias2.*} and so on. So after all the first phase of experimentation, I decided to encapsulate all these feature and behaviour in what i called "HibernateDSLContext" that is able to do the following things: 1) discovery the tables involved in the query (excluding subquery, only the "primary" tables) 2) discovery all the join conditions (excluding subquery, only on the "primary" tables) 3) methods: SelectSelectStep<Record> select() to return the SelectSelectStep without declaring the fields (it's useless declaring fields, please read the following) <T> List<T> list(Select<?> select) <T> T uniqueResult list and uniqueResult take in input the select statement generated with jOOQ and build the SQLQuery code using 1 and 2. with the tables discovery, I take the first table and use it for the root entity. And then I use them to generate select statement with this form: select {tableAlias1.*}, {tableAlias2.*} from with the join conditions, i am able to found (using the Hibernate Session "Metadata" API) the Entity relations between tables I tested with some cases (simple and complex) and seems to work. This is the version of the first prototipal code using HibernateDSLContext: Session session = ...; HibernateDSLContext hdsl = HibernateDSLContext.using(session); Select<?> select = hdsl.select().from(...).join(...).on(...); List<Entity> hdsl.list(select); For the point 1 and 2 at first i used GSP (General SQL Parser), at the end i tried to use VisitListener of jOOQ (in the next post i will write some "issues" that i encountered and my "workaround"). I want to know what do you think about this HibernateDSLContext for a better integration with Hibernate. The project is just in development and the code is not already available on github. I would to improve it (maybe better integration with DSLContext and SQLQuery), for now it's just a prototype. Thank you all for your time. I am sorry for my bad english :P Antonio -- 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/d/optout.
