summary:
MyBatis3中实现一对多的查询比较简单,可以自动完成。但插入操作要复杂一些,需要相关的DAO配合完成,这点不如Hibernate。
场景描述:
类:Mail和Attachment类
关系描述:一封邮件(Mail)可以有0个或多个附件(Attachment),附件(Attachment)仅对应一封邮件。
表格:mail表(主键:id_mail)和attachment表(外键:id_mail)。
- 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 Attachment implements Serializable {
- private static final long serialVersionUID = -1863183546552222728L;
- private String id;
- private String mailId;
- private String name;
- private String relativePath;
- ...
- getters and setters...
- }
- xml version="1.0" encoding="UTF-8"?>
- nbsp;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>
- xml version="1.0" encoding="UTF-8"?>
- nbsp;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>
- select id="selectLastId" resultType="int">
- select LAST_INSERT_ID()
- select>
- mapper>
- xml version="1.0" encoding="UTF-8"?>
- nbsp;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>
- select id="selectByMailId" parameterType="int" resultMap="result">
- select id, id_mail, name, relative_path
- from attachments where id_note = #{id}
- select>
- mapper>
DAO
- 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 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;
- }