Menu
▾
▴
[Springframework-developer] SqlUpdate and insert functionality
|
From: Ken K. <kk...@kk...> - 2003-05-23 22:14:26
|
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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>
>
>
>
>
|
