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;
    }
}