Hello Antonio, This sounds like an extremely interesting project.
As a framework, jOOQ will never really engage in attempting to solve the object graph persistence problem, simply because Hibernate has solved that very well already. We've discussed this strategy recently on a blog post: http://blog.jooq.org/2015/03/24/jooq-vs-hibernate-when-to-choose-which/ Nonetheless, this doesn't mean that users shouldn't use both APIs in the same project. This can make perfect sense - although, the project still needs to adhere to the architecture assumptions made by Hibernate - which you do. I like your idea of using Hibernate's session.createSQLQuery(sql); API. We were experimenting with that in the past, although because of time constraints, we didn't get very far - at least not to a point where we could publish something. But we believe that this will be the way to go for use-cases like yours. And this: Session session = ...; > HibernateDSLContext hdsl = HibernateDSLContext.using(session); > Select<?> select = hdsl.select().from(...).join(...).on(...); > List<Entity> hdsl.list(select); ... is exactly where we wanted to go eventually, with our ideas. For the point 1 and 2 at first i used GSP (General SQL Parser) We didn't get too much traction with this GSP (i.e. almost none at all) - we're thinking of no longer shipping it with future versions of jOOQ. > 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"). > Yes, please! We're very happy to learn about those issues. Any indications that help us improve this SPI are very welcome. > I want to know what do you think about this HibernateDSLContext for a > better integration with Hibernate. > That's the future! > 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. I'd love to see the prototype, and I'm sure, others will be very interested as well. Cheers, Lukas 2015-04-13 12:32 GMT+02:00 Antonio Gallo <[email protected]>: > 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. > -- 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.
