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"?>
-
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"?>
Mappers
MailMapper.xml
- 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>
的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"?>
-
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>
<?xml version="1.0" encoding="UTF-8"?>
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();
- }
- }
-
-
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);
-
- session.commit();
-
-
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;
- }