Menu
▾
▴
Re: [Springframework-developer] SqlUpdate and insert functionality
|
From: Isabelle M. <isa...@me...> - 2003-05-24 09:16:04
|
Hi everyone,
The javadoc comment is a major booboo on my part. The column should not be auto-increment, because the sequence table does that.
HOWEVER: I have been rethinking the whole approach, and I think I need to get rid of the KeyBinder and use the auto-increment feature instead. The user would then pass NULL, the column IS auto-increment, and there are no sequence tables).
I'm giving this top priority, we cannot release with a bug. I'm hoping to get it done by sunday night.
Reading in the MySQL Cookbook, there is a way to retrieve the last inserted id in one round-trip using MySQL-specific API methods. The alternative is 2 use 2 statements : first the insert with a with a NULL, then call last-insert-id(). The last inserted id is kept on a per-connection basis, so I think everything should be OK when used in a container that pools connections.
It seems to me that, when inserting with a sequence, we have to have something like
preprocess()
update()
postprocess()
return id
Depending on the database, either or both of preprocess and postprocess may be empty.
Comments are welcome.
Isabelle
On Fri, May 23, 2003 at 05:09:28PM -0500, Ken Krebs wrote:
> 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
|
