|
Hi
Albert,
thanks a lot for the
answer. Indeed it was the reason of the problem. I added the following selectKey
to my mapping file and it works:
<insert
id="saveInstrument" parameterClass="instrument">
INSERT INTO instrument (internalNumber) VALUES
(#internalNumber#)
<selectKey keyProperty="id"
resultClass="int">SELECT
last_insert_id()</selectKey >
</insert>
I'm now facing another
problem: By deleting an instrument iBATIS detects the dependency of the option
data and removes it from the corresponding table. I don't need to do anything,
just calling "deleteInstrument" which has been defined as
follows:
<delete
id="deleteInstrument" parameterClass="instrument">
DELETE FROM instrument WHERE id=#id#
</delete>
By inserting a new
instrument, I first have to call inserting of the new instrument and then call
manually the insert of eventually corresponding option. See Java and mapping
part below:
public
void save(Instrument instrument) {
// is it
save or update?
if (instrument.getId()
== 0) {
getSqlMapClientTemplate().insert("saveInstrument",
instrument);
if
(instrument.getOptionComponent() != null)
{
getSqlMapClientTemplate().insert("saveInstrumentOption",
instrument);
}
} else
{
//
... update
}
}
<insert id="saveInstrumentOption"
parameterClass="instrument">
INSERT INTO instrument_option
(instrument_id, name) VALUES (#id#,
#optionComponent.name#);
</insert>
My question is: why can't I
call both inserts toghther. I mean, how can I call statements
like:
<insert
id="saveInstrument" parameterClass="instrument">
INSERT INTO instrument (internalNumber) VALUES
(#internalNumber#)
<selectKey keyProperty="id"
resultClass="int">SELECT last_insert_id()</selectKey >
INSERT INTO instrument_option (instrument_id, name) VALUES (#id#, #optionComponent.name#);
</insert>
Thanks,
Reza
Reza,
I don't
know if this is what is causing the problem, but I think there might be a
problem with your table definition for the option table. instrument_id
is supposed to reference the id in the instrument table, but you have it set
up as a auto_increment field. I would think that you would need to
retrieve the generated id from the instrument insert to populate the
instrument_id field before doing the insert. Do you do that? Don't
use MySQL, but thought it might be worth
mentioning.
Regards,
Al
At 01:56 AM 8/29/2005, you
wrote:
Hi all,
following
scenario:
I'va an object Instrument. It references an another object
option.
create table instrument
(
id int not null auto_increment primary key ,
internalNumber varchar (12) not null unique
);
create table option (
instrument_id int not null auto_increment primary key ,
name varchar (12)
not null unique
constraint instrument_option_fk_01 foreign key (instrument_id) references instrument (id) ON DELETE CASCADE ON UPDATE CASCADE
);
An option object can not be
exist without corresponding instrument. That means, deleting of an
instrument object from the data base implies the need of deletion of the
referenced option (if not null) before.
Deleting and update of
instruments work well. But inserting a new instrument throws a lot of
questions:
1. first I tried to call multiple inserts within an insert
element in mapping file. The hibernate doesn't accept this.
org.springframework.jdbc.BadSqlGrammarException:
SqlMapClient operation; bad SQL grammar []; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
Instrument.xml.
--- The error
occurred while applying a parameter map.
--- Check the instrument-InlineParameterMap.
--- Check the statement (update
failed).
--- Cause:
java.sql.SQLException: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near '; INSERT INTO option(instrument_id, name at line
1
2. To solve this problem, I wrote two insert fragments. One
for instrument and one for option. In my DAO implementaiotn then call they
in right order (first inserting instrument and then option using id of the
stored instrument.
My Question is:
how does iBATIS handle the
inserting of coposed objects? If my instrument is referenced by N other
objects, shoud I take care of inserting all of them? Why are delete and
insert not symmetric? During delete is done by just one step, why does
insert several steps?
I'm using iBATIS 2.1.5 in combination with
spring 1.2.x.
Thanks in advance and best regards
Reza
|
