Menu
▾
▴
Re: RE : RE : [Springframework-developer] SqlUpdate and insert functionality
|
From: Isabelle M. <isa...@me...> - 2003-05-24 16:30:21
|
Hi Jean-Pierre,
I was away all afternoon and did some more thinking about the problem, and came to the same conclusion as you apparently did : the sequence table should stay, but the KeyBinder interface needs to go away. Instead of the KeyBinder argument, the caller needs to pass the index of the key column so that JdbcTemplate can do the binding.
The difference with your sample is that the caller doesn't do the binding, JdbcTemplate does.
Are we on the same wavelength?
Best regards,
Isabelle
On Sat, May 24, 2003 at 12:41:20PM +0200, JP Pawlak wrote:
> Hi Isabelle,
>
>
>
> Why should code such as below not work?
>
> Both for Oracle and MySql, the Incrementer is able to provide a key before the main statement. Letting the developer request for a key and letting him handling this value as it came from the user request will always be possible. There is no always need for sophistication.
>
> If the keys are picked by bunches, we don’t have two database requests.
>
>
>
> Modified test:
>
> /**
>
> * Test an insert that is using sequencing
>
> * @throws Exception if anything goes wrong
>
> */
>
> public void testInsertWithSequence() throws Exception {
>
>
>
> int[] types = new int[] { Types.INTEGER, Types.INTEGER };
>
> Object[] params = new Object[] { null, new Integer(1) };
>
>
>
> JdbcTemplate tpl = new JdbcTemplate(ds);
>
> MySQLMaxValueIncrementer incr = new MySQLMaxValueIncrementer(ds, "insert_test_seq", "seq2");
>
> params[0] = new Integer(incr.nextIntValue());
>
> PreparedStatementCreator psc =
>
> PreparedStatementCreatorFactory.newPreparedStatementCreator("insert into insert_test values(?, ?)", types, params);
>
> numRows = tpl.update(psc);
>
> assertTrue("Row was not inserted", 1 == result.getRowsAffected());
>
> // assertTrue("Key should have been 101", 101 == ((Integer)result.getKey()).intValue());
>
> // Don't need getKey() as we have used incr.nextIntValue()
>
> }
>
>
>
> Best Regards,
>
> Jean-Pierre
>
>
>
> > -----Message d'origine-----
>
> > De : Isabelle Muszynski [mailto:isa...@me...]
>
> > Envoyé : samedi 24 mai 2003 11:18
>
> > À : JP Pawlak
>
> > Cc : spr...@li...
>
> > Objet : Re: RE : [Springframework-developer] SqlUpdate and insert
>
> > functionality
>
> >
>
> > Hi Jean-Pierre,
>
> >
>
> > As I said in the mail I sent a minute ago, the fundamental difference
>
> > between Oracle and MySQL is that in the first you can get the next value
>
> > beforehand, while in the latter you get if afterwards. So I don't think
>
> > your way would work.
>
> >
>
> > Anyway, the thing is badly broken right now.
>
> >
>
> > Isabelle
>
> >
>
> > On Sat, May 24, 2003 at 12:41:36AM +0200, JP Pawlak wrote:
>
> > > Ken,
>
> > >
>
> > > If the javadoc says that a column with auto_increment is to be used,
>
> > > it's clearly a mistake.
>
> > > But that doesn't solve the issue!
>
> > > I used a similar approach with the old framework, but without the Binder
>
> > > technique. I get manually the nextValue in each DAO and set its value
>
> > > like the others parameters in the callback method and it works fine.
>
> > > For now, the Binder mechanism has an open issue (ref last Isabelle's
>
> > > post).
>
> > >
>
> > > Regards,
>
> > > Jean-Pierre
>
> > >
>
> > > -----Message d'origine-----
>
> > > De : spr...@li...
>
> > > [mailto:spr...@li...] De la
>
> > > part de Ken Krebs
>
> > > Envoyé : samedi 24 mai 2003 00:09
>
> > > À : Isabelle Muszynski
>
> > > Cc : spr...@li...
>
> > > Objet : [Springframework-developer] SqlUpdate and insert functionality
>
> > >
>
> > > Isabelle,
>
> > >
>
> > > I don't understand this. I thought auto_increment on the visits id
>
> > > column is what makes it work. The javadoc for MySQLMaxValueIncrementer
>
> > > says that is to be used with an auto_increment column.
>
> > >
>
> > > I tried removing the auto_increment as you suggested and it no longer
>
> > > works.
>
> > >
>
> > > Ken
>
> > >
>
> > > As I said earlier:
>
> > >
>
> > > "The data is written correctly to the DB using the auto-incremented
>
> > > visit_id. The problem is that the getKey() function of the
>
> > > returned InsertRetval returns 0, not the id that was used for the
>
> > > insert. I wanted to use the value to update my cache directly without
>
> > > having to requery the DB for all this pet's visits. I am working around
>
> > > it by doing just that."
>
> > >
>
> > > Isabelle Muszynski wrote:
>
> > >
>
> > > Hi Ken,
>
> > >
>
> > > Looking at the mail you sent me this morning (included below), in table
>
> > > visits, column id is auto-increment, and it shouldn't be. Neither should
>
> > > the sequence column be, which is correct in the code below.
>
> > >
>
> > > The definition of column id should be "int not null primary key"
>
> > >
>
> > > Does this help?
>
> > >
>
> > > Isabelle
>
> > >
>
> > > On Fri, May 23, 2003 at 09:04:01AM -0500, Ken Krebs wrote:
>
> > >
>
> > > Isabelle,
>
> > >
>
> > > My key column isn't auto-incremented, only the actual id column is.
>
> > >
>
> > > My latest code snapshot is attached.
>
> > >
>
> > > Ken
>
> > >
>
> > >
>
> > > Isabelle Muszynski wrote:
>
> > >
>
> > >
>
> > > Hi Ken,
>
> > >
>
> > > Your key column should NOT be auto-increment.
>
> > >
>
> > > If that doesn't solve the problem, let me know, and pls attach the code
>
> > > so
>
> > > I don't have top type it over. Will look at it tonight or tomorrow. I'll
>
> > >
>
> > > also look into the strange log output, probably a stupid booboo.
>
> > >
>
> > > Isabelle
>
> > >
>
> > > On Thu, May 22, 2003 at 11:25:08PM -0500, Ken Krebs wrote:
>
> > >
>
> > >
>
> > >
>
> > > Hi Isabelle,
>
> > >
>
> > > I have a problem using the following petclinic class that inserts a new
>
> > > visit into the DB :
>
> > >
>
> > > class NewVisit extends SqlUpdate {
>
> > >
>
> > > public NewVisit(DataSource ds) {
>
> > > super(ds, "INSERT INTO visits VALUES(?,?,?,?)");
>
> > > declareParameter(new SqlParameter(Types.INTEGER));
>
> > > declareParameter(new SqlParameter(Types.INTEGER));
>
> > > declareParameter(new SqlParameter(Types.DATE));
>
> > > declareParameter(new SqlParameter(Types.VARCHAR));
>
> > > compile();
>
> > > }
>
> > >
>
> > > public int insert(Visit visit) {
>
> > > KeyBinder keybinder = new KeyBinder() {
>
> > > public void bind(PreparedStatement ps, Object obj)
>
> > > throws SQLException {
>
> > > ps.setObject(1, obj);
>
> > > }
>
> > > };
>
> > >
>
> > > MySQLMaxValueIncrementer incr = new
>
> > > MySQLMaxValueIncrementer(getDataSource(), "visits_seq", "seq", 1);
>
> > >
>
> > > logger.info("Visit petId = " + visit.getPetId());
>
> > >
>
> > > Object[] objs = new Object[] {
>
> > > null,
>
> > > new Integer(visit.getPetId()),
>
> > > visit.getVisitDate(),
>
> > > visit.getDescription()
>
> > > };
>
> > >
>
> > > JdbcTemplate.InsertRetval retVal = update(objs, keybinder,
>
> > > incr, Integer.class);
>
> > > visit.setId(((Integer) retVal.getKey()).intValue());
>
> > >
>
> > > logger.info("Visit id = " + visit.getId() + " petId = " +
>
> > > visit.getPetId());
>
> > >
>
> > > return retVal.getRowsAffected();
>
> > > }
>
> > >
>
> > > }
>
> > >
>
> > > My table definitions for visits and its sequencer are :
>
> > >
>
> > > CREATE TABLE visits (
>
> > > id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
>
> > > pet_id INT(4) UNSIGNED NOT NULL REFERENCES pets(id),
>
> > > visit_date DATE,
>
> > > description VARCHAR(255),
>
> > > PRIMARY KEY(id),
>
> > > INDEX(pet_id)
>
> > > );
>
> > > INSERT INTO visits VALUES (1, 7, '1996-03-04', 'rabies shot');
>
> > > INSERT INTO visits VALUES (NULL, 8, '1996-03-04', 'rabies shot');
>
> > > INSERT INTO visits VALUES (NULL, 8, '1996-06-04', 'neutered');
>
> > > INSERT INTO visits VALUES (NULL, 7, '1996-09-04', 'spayed');
>
> > >
>
> > > CREATE TABLE visits_seq (
>
> > > seq INT(4) UNSIGNED NOT NULL
>
> > > );
>
> > > INSERT INTO visits_seq VALUES (5);
>
> > >
>
> > >
>
> > > The data is written correctly to the DB using the auto-incremented
>
> > > visit_id. The problem is that the getKey() function of the
>
> > > returned InsertRetval returns 0, not the id that was used for the
>
> > > insert. I wanted to use the value to update my cache directly without
>
> > > having to requery the DB for all this pet's visits. I am working around
>
> > > it by doing just that.
>
> > >
>
> > >
>
> > > I include below a relevant snippet from the INFO log:
>
> > >
>
> > > 2003-05-22 22:53:37,966 INFO [petclinic.support.ClinicImpl$NewVisit] -
>
> > > <Compiled OK>
>
> > > 2003-05-22 22:53:37,996 INFO [petclinic.support.ClinicImpl$NewVisit] -
>
> > > <Visit petId = 7>
>
> > > 2003-05-22 22:53:38,006 INFO [com.interface21.jdbc.object.SqlUpdate] -
>
> > > <Compiled OK>
>
> > > 2003-05-22 22:53:38,016 INFO [com.interface21.jdbc.core.JdbcTemplate] -
>
> > > <JDBCTemplate: update affected 1 rows>
>
> > > 2003-05-22 22:53:38,016 INFO [com.interface21.jdbc.object.SqlUpdate] -
>
> > > <1 rows affected by SQL update [update visits_seq set seq =
>
> > > last_insert_id(seq + 1)]>
>
> > > 2003-05-22 22:53:38,026 INFO [com.interface21.jdbc.object.SqlFunction] -
>
> > >
>
> > > <Compiled OK>
>
> > > 2003-05-22 22:53:38,036 INFO [com.interface21.jdbc.core.JdbcTemplate] -
>
> > > <Executing SQL query using PreparedStatement:
>
> > > [PreparedStatementCreatorFactory.PreparedStatementCreatorImpl:
>
> > > sql={select last_insert_id()}: params={}]>
>
> > > 2003-05-22 22:53:38,036 INFO [com.interface21.jdbc.core.JdbcTemplate] -
>
> > > <JDBCTemplate: update affected
>
> > > com.interface21.jdbc.core.JdbcTemplate$InsertRetval@1ebe3f0 rows>
>
> > > 2003-05-22 22:53:38,036 INFO [petclinic.support.ClinicImpl$NewVisit] -
>
> > > <Visit id = 0 petId = 7>
>
> > >
>
> > > Note that the last line shows the visit_id == 0.
>
> > > Also note the odd output of the 2nd last line where instead of
>
> > > outputting the no. of rows affected it's printing a toString() of the
>
> > > InsertRetval !!!???
>
> > >
>
> > >
>
> > > Am I doing something wrong here ?
>
> > >
>
> > >
>
> > > Regards,
>
> > >
>
> > > Ken
>
> > >
>
> >
>
> > --
>
> > Isabelle Muszynski
>
> > Software Engineer
>
> > Zandweellaan 4
>
> > 2660 Antwerpen
>
> > Belgium
>
> > Tel. 32-(0)3-830 18 54
>
> > Mobile: 32-(0)485 49 50 89
>
> > Email: isa...@me...
>
> > Website: www.meta-logix.com
>
--
Isabelle Muszynski
Software Engineer
Zandweellaan 4
2660 Antwerpen
Belgium
Tel. 32-(0)3-830 18 54
Mobile: 32-(0)485 49 50 89
Email: isa...@me...
Website: www.meta-logix.com
|
