MyBatis 3中实现一对多的插入和查询
summary:
MyBatis3中实现一对多的查询比较简单,可以自动完成。但插入操作要复杂一些,需要相关的DAO配合完成,这点不如Hibernate。
场景描述:
类:Mail和Attachment类
关系描述:一封邮件(Mail)可以有0个或多个附件(Attachment),附件(Attachment)仅对应一封邮件。
表格:mail表(主键:id_mail)和attachment表(外键:id_mail)。
POJO:
Mail.java
Java代码
- public class Mail implements Serializable {
- private static final long serialVersionUID = 7427977743354005783L;
- private Integer id;
- private String sender;
- private String subject;
- private String content;
- private String fromAddress;
- ...
- getters and setters...
- }
Attachment.java
Java代码
- public class Attachment implements Serializable {
- private static final long serialVersionUID = -1863183546552222728L;
- private String id;
- private String mailId;
- private String name;
- private String relativePath;
- ...
- getters and setters...
- }
SqlMapConfig:
Html代码
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
- <configuration>
- <properties resource="test/properties/mysql.properties"></properties>
- <typeAliases>
- <typeAlias type="test.model.Mail" alias="Mail"/>
- <typeAlias type="test.model.Attachment" alias="Attachment"/>
- </typeAliases>
- <environments default="development">
- <environment id="development">
- <transactionManager type="JDBC" />
- <dataSource type="UNPOOLED">
- <property name="driver" value="${db_driver}" />
- <property name="url" value="${db_url}" />
- <property name="username" value="${db_user}" />
- <property name="password" value="${db_password}"/>
- </dataSource>
- </environment>
- </environments>
- <mappers>
- <mapper resource="test/data/MailMapper.xml"/>
- <mapper resource="test/data/AttachmentMapper.xml"/>
- </mappers>
- </configuration>
Mappers
MailMapper.xml
Html代码
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
- <mapper namespace="test.data.MailMapper">
- <cache />
- <resultMap type="Mail" id="result_base">
- <id property="id" column="id_mail" />
- <result property="sender" column="sender"/>
- <result property="fromAddress" column="from_address" />
- <result property="subject" column="subject"/>
- <result property="content" column="content"/>
- <result property="sendTime" column="send_time" />
- ....
- </resultMap>
- <!--这里是关键,一对多映射的“魔法”几乎都在<collection>的配置里。select=...中"test.data.AttachmentMapper"对应于AttachmentMapper中
Html代码
- 的namespace-->
- <resultMap type="Mail" id="result" extends="result_base">
- <collection property="attachments" javaType="ArrayList" column="id_mail" ofType="Attachment"
- select="test.data.AttachmentMapper.selectByMailId"/>
- </resultMap>
- <insert id="insert" parameterType="Mail" useGeneratedKeys="true" keyProperty="id_note">
- insert into note(sender, from_address, subject, content, send_time)
- values(#{sender}, #{fromAddress}, #{subject}, #{content}, #{sendTime})
- <selectKey keyProperty="id_mail" resultType="int">
- select LAST_INSERT_ID()
- </selectKey>
- </insert>
- <select id="selectById" parameterType="int" resultMap="result" >
- select * from mail where id_mail = #{id}
- </select>
- <select id="selectAllMails" resultMap="result">
- select * from note Note
- </select>
- <!--这里可以获得刚插入表格的id,为后面attachment的插入提供了mailId字段-->
- <select id="selectLastId" resultType="int">
- select LAST_INSERT_ID()
- </select>
- </mapper>
AttachmentMapper.xml
Html代码
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
- <mapper namespace="test.data.AttachmentMapper">
- <cache />
- <resultMap type="Attachment" id="result">
- <result property="id" column="id_accessory" />
- <result property="name" column="name" />
- <result property="relativePath" column="relative_path" />
- <result property="mailId" column="id_mail" />
- </resultMap>
- <insert id="insert" parameterType="Attachment">
- insert into attachments(id_mail, name, relative_path) values(#{mailId}, #{name}, #{relativePath})
- </insert>
- <!--MailMapper中的ResultMap调用这个方法来进行关联-->
- <select id="selectByMailId" parameterType="int" resultMap="result">
- select id, id_mail, name, relative_path
- from attachments where id_note = #{id}
- </select>
- </mapper>
DAO
AttachmentDAO
Java代码
- public class AttachmentDAO {
- private SqlSessionFactory sqlSessionFactory;
- public AttachmentDAO(){
- this.sqlSessionFactory = TestConnectionFactory.getSqlSessionFactory();
- }
- public void insert(Attachment attachment){
- SqlSession session = sqlSessionFactory.openSession();
- AttachmentMapper attachmentMapper = session.getMapper(AttachmentMapper.class);
- try {
- attachmentMapper.insert(attachment);
- session.commit();
- } finally {
- session.close();
- }
- }
- }
MailDAO
Java代码
- public class MailDAO {
- private SqlSessionFactory sqlSessionFactory;
Java代码
- public MailDAO(){
- sqlSessionFactory = TestConnectionFactory.getSqlSessionFactory();
- }
- public void insertMailOnly(Mail mail){
- SqlSession session = sqlSessionFactory.openSession();
- MailMapper mailMapper = session.getMapper(MailMapper.class);
- try {
- mailMapper.insert(mail);
- session.commit();
- } finally {
- session.close();
- }
- }
- //inset
- public void insertMail(Mail mail){
- SqlSession session = sqlSessionFactory.openSession();
- MailMapper mailMapper = session.getMapper(MailMapper.class);
- AttachmentMapper attachmentMapper = session.getMapper(AttachmentMapper.class);
- try{
- mailMapper.insert(mail);
- //这里必须commit,再执行Attachment的插入操作。否则会导致null pointer异常
- session.commit();
- //获得最近插入到note表的id
- int mailId = mailMapper.selectLastId();
- for(Attachment attach : mail.getAttachments()){
- attach.setMailId(String.valueOf(mailId));
- attachmentMapper.insert(attach);
- }
- session.commit();
- }finally{
- session.close();
- }
- }
- public ArrayList<Mail> selectAllMails(){
- ArrayList<Mail> mailList = null;
- SqlSession session = sqlSessionFactory.openSession();
- MailMapper mailMapper = session.getMapper(MailMapper.class);
- try {
- mailList = mailMapper.selectAllMails();
- session.commit();
- } finally {
- session.close();
- }
- urn mailList;
- }
- public Mail selectMailById(int i){
- Mail mail = null;
- SqlSession session = sqlSessionFactory.openSession();
- MailMapper mailMapper = session.getMapper(MailMapper.class);
- try {
- mail = mailMapper.selectById(i);
- session.commit();
- } finally {
- session.close();
- }
- urn mail;
- }
- public int selectLastId(){
- int id = -1;
- SqlSession session = sqlSessionFactory.openSession();
- MailMapper mailMapper = session.getMapper(MailMapper.class);
- try {
- id = mailMapper.selectLastId();
- session.commit();
- } finally {
- session.close();
- }
- urn id;
- }
分享到:
RCP基础总结 | Ubuntu11.04地址栏调整为文字模式
- 2012-04-10 17:46