Inserting a collection using MyBatis
rdgoite
I just want to know how to insert a collection of objects from an
instance of a class. Say I have a class User related to a Note in one-
to-many relationship. I just like to mention that I built my schema
using JPA 2 annotations via Hibernate's hbm2ddl. I'll add the key JPA
annotations I used in the sample code below.
Here's a sample:
@Entity
public class User {
...
@OneToMany
@JoinColumn(name="user")
public List<Note> getNotes() {...}
...
}
Now, everytime I insert something into User table I have to insert
instances into the Note table if the list is not empty. Take note of
the @JoinColumn in Note table which should have the id of the inserted
User, which I have set to be autogenerated.
Has anyone got something like this working? Thanks.
Nathan Maves
start a transaction
insert/update the user
loop over the notes and insert them
end the transaction
Nathan
Clinton Begin
Clinton
rdgoite
> Nothing in MB3 will do this for you. Your best option is something like...
>
> start a transaction
> insert/update the user
> loop over the notes and insert them
> end the transaction
>
> Nathan
though, as I've noted above, is that I'd need to get the id of any
User instance once I persist it into the database so that I'd be able
to reference it from the Note instances I will store after. The
problem is, the id is auto generated; an auto_increment column and I
don't think there's a way MyBatis can return the generated value back
to the calling method after insert.
I could probably manage the primary key generation myself but that
would need additional work. I wonder if MyBatis could help me with
this.
Thanks.
Simone Tripodi
> The
> problem is, the id is auto generated; an auto_increment column and I
> don't think there's a way MyBatis can return the generated value back
> to the calling method after insert.
MyBatis supports as well auto generated keys, please see page 24-25 of
the manual[1] for more detailed informations:
"useGeneratedKeys: (insert only) This tells MyBatis to use the JDBC
getGeneratedKeys method to retrieve keys generated internally by the
database (e.g. auto increment fields in RDBMS like MySQL or SQL
Server). Default: false"
Good luck!
Simo
[1] http://mybatis.googlecode.com/svn/trunk/doc/en/MyBatis-3-User-Guide.pdf
http://people.apache.org/~simonetripodi/
http://www.99soft.org/
陈抒
<insert id="addExemption" parameterType="business.oracle.exemption.ExemptionRecord">
<selectKey keyProperty="exemptionId" resultType="INTEGER" order="BEFORE">
select exemption_seq.nextval from dual
</selectKey>
insert into exemption (id,account_id,customer_id,customer_name,address1,address2,city,state,country_code,zipcode,
federal_id_type,federal_id,phone_number,email,active)
values(#{exemptionId,jdbcType=DECIMAL},#{accountId,jdbcType=DECIMAL},#{customerId,jdbcType=VARCHAR},#{customerName,jdbcType=VARCHAR},
#{address1,jdbcType=VARCHAR},#{address2,jdbcType=VARCHAR},#{city,jdbcType=VARCHAR},#{state,jdbcType=VARCHAR},
#{country,jdbcType=VARCHAR},#{zipcode,jdbcType=VARCHAR},#{federalIdType,jdbcType=VARCHAR},#{federalId,jdbcType=VARCHAR},
#{phoneNumber,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{active,jdbcType=INTEGER})
</insert>
For Oracle example,MyBatis generates the id first using selectKey,then calls setExmeptionId method.Finally,it will insert the ExemptionRecord object into DB.
The following is an example for MySQL
<insert id="addExemption" parameterType="freebird.exemption.business.ExemptionRecord"
useGeneratedKeys="true" keyProperty="exemptionId" >
insert into exemption (account_id,customer_id,customer_name,address1,address2,city,state,country,zipcode,
federal_id_type,federal_id,phone_number,email,active,created_at)
values(#{accountId,jdbcType=DECIMAL},#{customerId,jdbcType=VARCHAR},#{customerName,jdbcType=VARCHAR},
#{address1,jdbcType=VARCHAR},#{address2,jdbcType=VARCHAR},#{city,jdbcType=VARCHAR},#{state,jdbcType=VARCHAR},
#{country,jdbcType=VARCHAR},#{zipcode,jdbcType=VARCHAR},#{federalIdType,jdbcType=VARCHAR},#{federalId,jdbcType=VARCHAR},
#{phoneNumber,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{active,jdbcType=INTEGER},#{createdDate,jdbcType=DATE})
</insert>
For MySQL example,MyBatis insert the ExemptionRecord object into DB first,and then call setExemptionId method with the id generated by MySQL automatically.
陈抒
Best regards
http://blog.csdn.net/sheismylife
rdgoite
>
> For MySQL example,MyBatis insert the ExemptionRecord object into DB
> first,and then call setExemptionId method with the id generated by MySQL
> automatically.
>
> 陈抒
> Best regardshttp://blog.csdn.net/sheismylife
>
say saveUser, it's going to mutate the user instance? So when the
insert is successful, the instance will have a value on its id field.
陈抒
PeiSong
Larry Meadors
out of 100, the answer is the same.
That said, doing a 10,000 record batch without commits would strain
even a pretty beefy database server, but I wouldn't expect MyBatis to
be the breaking point there.
Larry
陈抒
François Schiettecatte
If you are doing a clean insert such as:
INSERT INTO categories
(category_name)
VALUES (#{categoryName})
where the table look like this:
CREATE TABLE IF NOT EXISTS categories (
category_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(128) NOT NULL,
UNIQUE INDEX ( category_name )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
You can indeed use 'getGeneratedKeys' to grab the new ID, in this case category_id.
However if you need to handle duplicate inserts, you will need to use the 'selectKey' statement as follows:
/* dictionaries.Category.insertObject */
INSERT INTO categories
(category_name)
VALUES (#{categoryName})
ON DUPLICATE KEY UPDATE
category_id=LAST_INSERT_ID(category_id);
<selectKey resultType="Integer" keyProperty="categoryID" order="AFTER">
SELECT LAST_INSERT_ID();
</selectKey>
because 'getGeneratedKeys' will return the next ID in the sequence and not the ID of the duplicate. Obviously a bug somewhere in MySQL or JDBC.
Cheers
François
François Schiettecatte
Would I be right in saying that putting 'jdbcType=...' in the mapper files for nullable columns in no longer a requirement in mybatis-3.0.1?
Cheers
François
Clinton Begin
Cheers,
Clinton