Cedric Durmont - 2009-08-07

Hi there,
I'm starting to use sql2java for a new product (yes the KISS principle keeps me away from Hibernate and the like)
I have made some tweaks to the java templates. Some of the are too large to be copied directly here, so I'm posting the list and if someone is interested in my code, feel free to contact me :

-perschema/manager.java.vm
Seems like there was some problems with connections management.
* First, the code was (apparently) not really thread-safe due to the use of InheritableThreadLocal (see http://www.ibm.com/developerworks/java/library/j-threads3.html#h14240 , they even take a jdbc connection as the example of objets you should NOT store in an InheritableThreadLocal)
* the transaction system was kinda broken, as every request in a *Manager class closes the connection at the end. If you write in your code : beginTransaction - loadByPrimaryKey - setWhaterverAttribute - save - endTransaction, then your code is not threadsafe, because loadByPrimaryKey closes the connection (and thus terminates the transaction), and the save operation uses a new connection

-pertable/manager.java.vm
* Due to the transaction problem, I removed all calls to releaseConnection. One has now to close explicitly the connection, or let the GC do it (I heard it works...)
This has solved another issue with Oracle : I was getting connectionRefused exceptions after a dozen of sql queries. The reason is that Oracle takes a long time to open a new connection. It's so slow that only my dozen of calls have put it to stress (even with a single used on client and server)
* multiple relations between 2 tables :the code currently assumes that if several fields point to another table, then this constitutes a multi-attributes foreign key. The bean getter and setter take this into acount to fetch the right record. My case was different : some of my tables have multiple reference to another table (say I have a table "invoice", a table "person", and "invoice" has 2 attributes referencing "person" : "salesman" and "customer". My template generates getCustomerBean,getSalesmanBean, etc. The current template of sql2java generates a single getPersonBean which is of no use in this case. Anyway I left the 2 generators for the 2 different cases. It's kinda dirty, but I won't solve this completely until I actually need it ;o)
* those get*Bean methods are created of both sides of the relation (in the example : you can have an array of InvoiceBean that reference a given Person through the "customer" attribute)
* and the last tweak : the new get*Bean methods have a sort-of-lazy-loading ability : in the example above, if I have an instance of InvoiceBean, I can call getCustomerBean() and get the right bean without manually instanciating it. Once fetched, the bean is stored for later use. The code keeps the attribute and the bean in sync (ie if you call getCustomerBean, the setCustomer(newValue) and call getCustomerBean again, you'll get the bean that correspond to newValue)

Of course, should someone be interested in that I did, I'd release that code under LGPL, just like sql2java !