RE : [Springframework-developer] JDBC Insert functionality

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Hi Ken,

I am always for avoiding duplicate code and pluggability is also
important, even more.
So I am globally for these works.

The last point: "column name" from MySql is a quite different.=20
If we compare the strategies on Oracle, HSql and MySql, we can see that
MySql is the only one allowing the assembling of many identifiers in the
same table.=20

Fixing the column name for MySql will put it back to the "shared"
possibilities, and having so a more homegeneous abstract view, indeed
more pluggable.
But making so, MySql users will no longer be able to put all the
incrementers in the same table due uniquely on other databases
possibilities.
As an alternative, why not add the "column name" in the
AbstractDataField... Setting the value for other databases having no
effect, having just an unusable feature.

I will not be impacted personaly at all, as I use only one Incrementer
for the whole database, having identities unique in the database. So I
am not dependant of object/relational mapping strategies. But inserting
data out of the application is more difficult. Typically, I have to use
such scenarios:

>update ach2_identites set nextval =3D LAST_INSERT_ID(nextval+1);
>SET @id=3DLAST_INSERT_ID();
>INSERT INTO ach2_typescolisage VALUES (@id,'PCE',1);
>
>update ach2_identites set nextval =3D LAST_INSERT_ID(nextval+1);
>SET @locid=3DLAST_INSERT_ID();
>INSERT INTO ach2_typescolisage_loc VALUES (@locid,@id,'fr','la
pi=E8ce','les {0} pi=E8ces',1);

So, if Isabelle agree to your last proposal, why not finally.

Jean-Pierre

> -----Message d'origine-----
> De : Ken Krebs [mailto:kk...@kk...]=20
> Envoy=E9 : mardi 3 juin 2003 17:23
> =C0 : tri...@tr...; jp....@ti...; Isabelle Muszynski
> Cc : 'spring-dev-list'
> Objet : [Springframework-developer] JDBC Insert functionality
>=20
>=20
> On Friday, I was working on an HSQL version of the=20
> incrementer but had=20
> not completed the work before leaving for an extended holiday. I was=20
> intending to use it so as to provide support in the Petclinic=20
> demo for=20
> both Mysql and Hsql. I was pleased to find it done when I got=20
> back :=3D).=20
> I have built the demo using both db's now and the insert use=20
> case works=20
> well on both versions.
>=20
> I would like to propose a bit of refactoring to the=20
> incrementers class=20
> hierarchy to enhance pluggability. As it stands now, there is=20
> a lot of=20
> common functionality that is redundant in the concrete=20
> classes and could=20
> be pushed up to the AbstractDataFieldMaxValueIncrementer class:
>=20
> 1. cacheSize field and setter
> 2. prefixWithZero field and setter
> 3. dataSource field and setter
> 4. sequenceName (Oracle) or tableName (Mysql and Hsql) field=20
> and setter,=20
> perhaps renamed to incrementerName
>=20
> NOTE: The interface for RdmsMaxValueIncrementer is=20
> inconsistent with the=20
> others and out-of-date. As it is also unsafe, perhaps it should be=20
> removed altogether. It should at least be brought up-to-date.
>=20
>=20
> I have provided an example to show how these changes can benefit the=20
> user (developer):
>=20
>=20
> In Petclinic, I provide beans in applicationContext.xml to=20
> configure the=20
> dataSource and incrementer for my DAO bean. At present, I only have 1=20
> insert use case, addVisit.
>=20
> NOTE: I have elected to show the use of pooled datasources in a later=20
> iteration of the demo.
>=20
>=20
> For mysql:
>=20
>   <bean name=3D"dataSource"=20
> class=3D"com.interface21.jdbc.datasource.DriverManagerDataSource" >
>     <property =
name=3D"driverClassName">com.mysql.jdbc.Driver</property>
>     <property=20
> name=3D"url">jdbc:mysql://localhost:3306/petclinic</property>
>     <property name=3D"username">pc</property>
>     <property name=3D"password">pc</property>
>   </bean>
>   =20
>   <bean name=3D"visitIncrementer"=20
> class=3D"com.interface21.jdbc.core.support.MySQLMaxValueIncrementer" >
>     <property name=3D"dataSource" =
beanRef=3D"true">dataSource</property>
>     <property name=3D"tableName">visits_seq</property>
>     <property name=3D"columnName">seq</property>
>   </bean>
>=20
> =20
> For hsql:
>=20
>   <bean name=3D"dataSource"=20
> class=3D"com.interface21.jdbc.datasource.DriverManagerDataSource" >
>     <property =
name=3D"driverClassName">org.hsqldb.jdbcDriver</property>
>     <property =
name=3D"url">jdbc:hsqldb:hsql://localhost:9001</property>
>     <property name=3D"username">sa</property>
>   </bean>
>   =20
>   <bean name=3D"visitIncrementer"=20
> class=3D"com.interface21.jdbc.core.support.HsqlMaxValueIncrementer" >
>     <property name=3D"dataSource" =
beanRef=3D"true">dataSource</property>
>     <property name=3D"tableName">visits_seq</property>
>   </bean>
>=20
>=20
> My DAO bean:
>=20
>   <bean name=3D"clinicDAO" class=3D"petclinic.dao.ClinicJdbcDAO" >
>     <property name=3D"dataSource" =
beanRef=3D"true">dataSource</property>
>     <property name=3D"visitIncrementer"=20
> beanRef=3D"true">visitIncrementer</property>
>   </bean>
>   =20
>=20
>=20
> As it stands, I need to configure a separate incrementer bean=20
> for each=20
> new insert use case and add a field and setter for each one=20
> to the DAO=20
> or alternatively embed code using the specific concrete class=20
> names in=20
> the DAO which is now generic JDBC.
>=20
>=20
> What I would like to do is something like this:
>=20
>   <bean name=3D"incrementer"
>         singleton=3D"false"
>        =20
> class=3D"com.interface21.jdbc.core.support.MySQLMaxValueIncrementer" > =

> <!-- or some other concrete incrementer -->
>     <property name=3D"dataSource" =
beanRef=3D"true">dataSource</property>
>   </bean>
> =20
>   <bean name=3D"clinicDAO" class=3D"petclinic.dao.ClinicJdbcDAO" >
>     <property name=3D"dataSource" =
beanRef=3D"true">dataSource</property>
>     <property name=3D"incrementer" =
beanRef=3D"true">incrementer</property>
>   </bean>
>   =20
> =20
> I could then use this as a prototype bean for each of the insert use=20
> cases which would then instantiate a prototype object using=20
> getBean() on=20
> an AbstractDataFieldMaxValueIncrementer  reference,=20
> subsequently setting=20
> the incrementerName property for that object for the=20
> particular use case=20
> in code. The code remains completely generic and all the database=20
> configuration/selection is done in xml. A problem remains=20
> with the Mysql=20
> version in that the columnName property needs to be set. This=20
> could be=20
> accomplished without breaking genericity by conventionally using a=20
> default columnName, such as "seq".
>=20
>=20
>=20
> What do you think ???
>=20
>=20
> Ken
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20