MyBatis 3中实现一对多的插入和查询

summary：

MyBatis3中实现一对多的查询比较简单，可以自动完成。但插入操作要复杂一些，需要相关的DAO配合完成，这点不如Hibernate。

场景描述：

类：Mail和Attachment类

关系描述：一封邮件（Mail）可以有0个或多个附件（Attachment），附件（Attachment）仅对应一封邮件。

表格：mail表（主键：id_mail）和attachment表（外键：id_mail）。

POJO：

Mail.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

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:

<?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

<?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中

            的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

<?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

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

public class MailDAO {    private SqlSessionFactory sqlSessionFactory;

     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();        }return 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();        }return 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();        }return id;    }}