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

yinl_li

　　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_pathfrom 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表的idint 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;}}