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

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

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

    <select id="selectLastId" resultType="int">
         select LAST_INSERT_ID()
    select>
mapper>

的namespace--> insert into note(sender, from_address, subject, content, send_time) values(#{sender}, #{fromAddress}, #{subject}, #{content}, #{sendTime}) select LAST_INSERT_ID() select * from mail where id_mail = #{id} select * from note Note select LAST_INSERT_ID() AttachmentMapper.xml

xml version="1.0" encoding="UTF-8"?>
>
<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>

    <select id="selectByMailId" parameterType="int" resultMap="result">
        select  id, id_mail, name, relative_path
         from attachments where id_note = #{id}
    select>
mapper>

insert into attachments(id_mail, name, relative_path) values(#{mailId}, #{name}, #{relativePath}) select id, id_mail, name, relative_path from attachments where id_note = #{id}
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();
             }
    }
}

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 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 selectAllMails(){
    ArrayList 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;
}