MyBatis 3中实现一对多的插入和查询-CSDN博客

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>  <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>  <select id="selectLastId" resultType="int"> select LAST_INSERT_ID() </select> </mapper>

AttachmentMapper.xml

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