Hi Garret, 2014-11-13 20:42 GMT+01:00 Garret Wilson <[email protected]>:
> Thanks for the reply, Lukas. So here's where it gets interesting, taking > us back to the whole "with jOOQ you don't need an ORM" issue, which I don't > think is as clear-cut as some comments might make it appear. > Let's say I have a Car class (identified by some vehicle identification > number <http://en.wikipedia.org/wiki/Vehicle_identification_number> VIN) > and a Wheel class, and I want to store a car in a database along with its > four wheels. If two people try to store the Car in the database at the same > time, I don't care who wins, but I want the whole operation to be atomic, > and I want it to replace whatever Car is already in the database (if any) > with the same VIN. > > I also have a CarManager class (which has an implementation > JOOQCarManager) along with a WheelManager class (with implementation > JOOQWheelManager). > > I set up a dbcp2 PoolingDataSource (for example) and then create a jOOQ > configuration using: > > final org.jooq.Configuration jooqConfiguration = new > DefaultConfiguration() > .set(dataSource).set(SQLDialect.POSTGRES).set(new > Settings().withExecuteLogging(true)); > > Somehow that jooqConfiguration instance gets put in some dependency > injection container; we don't care how, we only care that the managers have > access to it. > > So inside JOOQCarManager.storeCar(Car car) I create a DSLContext: > > DSLContext dslContext=DSL.using(getConfiguration()) > > Question 1: So what do I do now? Do I just start using the DSLContext and > it will get a connection as needed? Or do I do this? > > Connection connection = > dslContext.configuration().connectionProvider().acquire(); > No, you don't need to do this. jOOQ does this. ConnectionProvider is a SPI (Service Provider Interface) <http://en.wikipedia.org/wiki/Service_provider_interface>. You don't call the SPI, you implement it (or choose a default implementation). In other words, your first suggestion is correct: DSLContext dslContext=DSL.using(getConfiguration()) > Question 2: How do I start a transaction? The code I'm reviewing (a mix of > several people's code) does this: > > DSLContext dslTransaction = DSL.using(connection); > dslTransaction.execute("SET CONSTRAINTS ALL DEFERRED"); > ... //and then goes on to delete the Car if it exists > final int deleteCount = > dslTransaction.delete(CAR).where(CAR.VIN.equal(vin).execute(); > final CarRecord carRecord = dslTransaction.newRecord(CAR); > ... //etc. to store the Car info > This dslTransaction object isn't really a transaction. I'm not sure why it is named this way. You essentially have two options 1. Use jOOQ's transaction API. This is mainly useful for standalone batch processes. The default implementation of the jOOQ transaction API uses JDBC transactions, not container transactions. 2. Use your JavaEE container / Spring to manage transactions for you. In the long run, this is probably the way to go I'm pretty sure that 2) is the way to go in your case. > Question 3: So what happens when we want to store the wheels? Does > JOOQCarManager call its instance of JOOQWheelManager four times? > I guess it does, in your case. Another option would be to make a single call to DSLContext.batchStore(): http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#batchStore-org.jooq.UpdatableRecord...- > But how does JOOQWheelManager make sure that it's part of the same > transaction? > Ideally, you'll be using container-managed transactions via JavaEE or Spring. > If JOOQWheelManager creates a new DSLContext from the jOOQ Configuration > from the DI container, won't that start a new transaction? > That depends entirely on how you configure your DataSource. The jOOQ / Spring tutorial shows how to use jOOQ with Spring either with - explicit transactions (using Spring's DataSourceTransactionManager) - declarative transactions (using Spring's @Transactional annotations) http://www.jooq.org/doc/latest/manual/getting-started/tutorials/jooq-with-spring/ I personally find the @Transactional annotations easier to get right. As you can see on the Transactional.propagation() Javadoc, the default is Propagation.REQUIRED, which means: > Support a current transaction, create a new one if none exists. > Analogous to EJB transaction attribute of the same name. This is exactly what you need. If JOOQWheelManager.save() is called in the context of an existing transaction, then reuse that existing transaction. Otherwise, create a new one. - http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/transaction/annotation/Transactional.html#propagation-- - http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/transaction/annotation/Propagation.html#REQUIRED Do we have to pass the current DSLContext to the WheelManager? Or do we > pass the JDBC Connection to the WheelManager, which will use it to form a > DSLContext that uses the same connection? > When using a DataSource, the concrete Connection instance is abstracted by the DataSource implementation (e.g. DBCP, or Spring). Typically, you will be using a pool of Connections where every Connection is temporarily (for the scope of a transaction) associated with exactly one Thread (internally via ThreadLocal). Whenever that Thread accesses the DataSource.getConnection() method within the same transaction, the DataSource implementation will make sure that the *same* Connection instance will be provided. jOOQ's ConnectionProvider fits very well in that very common transaction and connection pool model. By default, when you supply jOOQ with a DataSource (via jOOQ's DataSourceConnectionProvider), jOOQ will hook into the above lifecycle, and every query will "just work" in the scope of the transaction that it is executed in. All that's left for you to do is: - Configure such a DataSource, e.g. as in the aforementioned jOOQ/Spring tutorial - Make all relevant methods in your jOOQCarManager and jOOQWheelManager @Transactional > I apologize if some of these questions have obvious answers. In fact I > hope they do. I am not a relational database expert, but unfortunately I'm > the one who gets to research all this stuff for my client's application. > Why not switch the above sentence into: "Heck, I need to know this anyway. I'll need this in *every* future application that I'll ever build with Java, because it's always the same, even regardless if I'm using jOOQ or some other SQL API" You *want* to research all this stuff, believe me :) > I would think that the above scenario is very common and in fact > "standard" to some extent when storing an instance tree in a database. I'm > hoping there is a straightforward and "standard" solution. > As far as transactions are concerned, yes. All of the above is "standard" and has been "standard" since the early days of JavaEE. In fact, declarative transactions (via XDoclet) were the very essence of JavaEE (or J2EE as it was called back then). Instance tree (or object graph) persistence, well, that is also "very standard" - when you're using Hibernate. The design goal for jOOQ was to implement SQL, which is something quite different from instance tree persistence, much more useful for reporting, bulk data manipulation, and querying. I might have linked to this page before in our discussions: http://www.hibernate-alternative.com. When you're doing SQL, you're not really thinking in terms of trees (object orientation), but in terms of relations (relational). But that is an entirely different discussion, and I believe, it is a bit late in your project :) Hope this helps, Have a nice weekend, 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
