Menu
▾
▴
RE : [Springframework-developer] SqlUpdate and insert functionality
|
From: JP P. <jp....@ti...> - 2003-05-23 22:46:49
|
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=A0: spr...@li...
[mailto:spr...@li...] De la
part de Ken Krebs
Envoy=E9=A0: samedi 24 mai 2003 00:09
=C0=A0: Isabelle Muszynski
Cc=A0: spr...@li...
Objet=A0: [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=20
visit_id. The problem is that the getKey() function of the=20
returned InsertRetval returns 0, not the id that was used for the=20
insert. I wanted to use the value to update my cache directly without=20
having to requery the DB for all this pet's visits. I am working around=20
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:
=20
Isabelle,
My key column isn't auto-incremented, only the actual id column is.
My latest code snapshot is attached.
Ken
Isabelle Muszynski wrote:
=20
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=20
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:
=20
Hi Isabelle,
I have a problem using the following petclinic class that inserts a new=20
visit into the DB :
class NewVisit extends SqlUpdate {
=20
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();
}
=20
public int insert(Visit visit) {
KeyBinder keybinder =3D new KeyBinder() {
public void bind(PreparedStatement ps, Object obj)=20
throws SQLException {
ps.setObject(1, obj);
}
};
=20
MySQLMaxValueIncrementer incr =3D new=20
MySQLMaxValueIncrementer(getDataSource(), "visits_seq", "seq", 1);
=20
logger.info("Visit petId =3D " + visit.getPetId());
=20
Object[] objs =3D new Object[] {
null,
new Integer(visit.getPetId()),
visit.getVisitDate(),
visit.getDescription()
};
=20
JdbcTemplate.InsertRetval retVal =3D update(objs, keybinder,=20
incr, Integer.class);
visit.setId(((Integer) retVal.getKey()).intValue());
=20
logger.info("Visit id =3D " + visit.getId() + " petId =3D " + =
visit.getPetId());
=20
return retVal.getRowsAffected();
}
=20
}
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=20
visit_id. The problem is that the getKey() function of the=20
returned InsertRetval returns 0, not the id that was used for the=20
insert. I wanted to use the value to update my cache directly without=20
having to requery the DB for all this pet's visits. I am working around=20
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] -=20
<Compiled OK>
2003-05-22 22:53:37,996 INFO [petclinic.support.ClinicImpl$NewVisit] -=20
<Visit petId =3D 7>
2003-05-22 22:53:38,006 INFO [com.interface21.jdbc.object.SqlUpdate] -=20
<Compiled OK>
2003-05-22 22:53:38,016 INFO [com.interface21.jdbc.core.JdbcTemplate] -=20
<JDBCTemplate: update affected 1 rows>
2003-05-22 22:53:38,016 INFO [com.interface21.jdbc.object.SqlUpdate] -=20
<1 rows affected by SQL update [update visits_seq set seq =3D=20
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] -=20
<Executing SQL query using PreparedStatement:=20
[PreparedStatementCreatorFactory.PreparedStatementCreatorImpl:=20
sql=3D{select last_insert_id()}: params=3D{}]>
2003-05-22 22:53:38,036 INFO [com.interface21.jdbc.core.JdbcTemplate] -=20
<JDBCTemplate: update affected=20
com.interface21.jdbc.core.JdbcTemplate$InsertRetval@1ebe3f0 rows>
2003-05-22 22:53:38,036 INFO [petclinic.support.ClinicImpl$NewVisit] -=20
<Visit id =3D 0 petId =3D 7>
Note that the last line shows the visit_id =3D=3D 0.
Also note the odd output of the 2nd last line where instead of=20
outputting the no. of rows affected it's printing a toString() of the=20
InsertRetval !!!???
Am I doing something wrong here ?
Regards,
Ken
=20
=20
=20
=20
|
