1 mybatis-Dao的代理开发模式
Dao:数据访问对象
原来：定义dao接口，在定义dao的实现类
dao的代理开发模式
只需要定义dao接口，由mybatis产生dao接口的实现类。
1.1定义Mapper接口
1 package org.guangsoft.mapper;
2
3 import java.util.List;
4
5 import org.guangsoft.entity.Dept;
6 import org.guangsoft.vo.DeptVo;
7
8 public interface DeptMapper
9 {
10 public List<DeptVo> getDeptPost();
11 public void saveDept(Dept dept);
12 }
1.2定义Mapper.xml文件
定义Mapper接口中方法对应的操作
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 <mapper namespace="org.guangsoft.mapper.DeptMapper">
4 <!-- 使用dao的代理开发模式的时候
5 1，namespace必须和map接口的完全限定名完全一样
6 2.对应的数据库库擦操作id必须和接口中抽象放安防名一致
7 3,parameterType必须和抽闲接口的抽象方法参数类型一致
8 4，resultType必须和接口抽象方法的返回类型一样
9 -->
10 <select id="getDeptPost" resultType="org.guangsoft.vo.DeptVo">
11 select dept.did,dname,pname from dept inner join post on dept.did=post.did
12 </select>
13 <insert id="saveDept" parameterType="org.guangsoft.entity.Dept">
14 insert into dept values(null,#{dname})
15 </insert>
16 </mapper>
1.3通过session产生Mapper接口的代理对象
1 public class TestDeptMapper
2 {
3 SqlSessionFactory ssf = null;
4 @Before
5 public void before()
6 {
7 SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
8 ssf = ssfb.build(this.getClass().getClassLoader().getResourceAsStream("MyBatis.xml"));
9 }
10
11 @Test
12 public void testGetDeptPost()
13 {
14 SqlSession sqlSession = ssf.openSession();
15 DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
16 List<DeptVo> deptVoList = deptMapper.getDeptPost();
17 for(DeptVo deptVo : deptVoList)
18 {
19 System.out.println(deptVo);
20 }
21 sqlSession.close();
22 }
23
24 @Test
25 public void testSaveDept()
26 {
27 SqlSession sqlSession = ssf.openSession();
28 DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
29 Dept dept = new Dept();
30 dept.setDname("danme");
31 deptMapper.saveDept(dept);
32 sqlSession.commit();
33 sqlSession.close();
34 }
35 }
2 mybatis的关联查询
Mybatis多表查询。
2.1 one-to-one 查询
需求：查询某个订单和订单对应的用户信息
订单编号 用户名 时间 金额 描述
2.1.1建立数据库模型
用户表，订单表。
1 /*
2 Navicat MySQL Data Transfer
3
4 Source Server : MySQL
5 Source Server Version : 50715
6 Source Host : localhost:3306
7 Source Database : test
8
9 Target Server Type : MYSQL
10 Target Server Version : 50715
11 File Encoding : 65001
12
13 Date: 2016-12-14 20:47:27
14 */
15
16 SET FOREIGN_KEY_CHECKS=0;
17
18 -- ----------------------------
19 -- Table structure for orders
20 -- ----------------------------
21 DROP TABLE IF EXISTS `orders`;
22 CREATE TABLE `orders` (
23 `oid` int(11) NOT NULL AUTO_INCREMENT,
24 `odate` datetime DEFAULT NULL,
25 `ototal` double DEFAULT NULL,
26 `odesc` varchar(255) DEFAULT NULL,
27 `uid` int(11) DEFAULT NULL,
28 PRIMARY KEY (`oid`),
29 KEY `fk_uid` (`uid`),
30 CONSTRAINT `fk_uid` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
31 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
32
33 -- ----------------------------
34 -- Table structure for user
35 -- ----------------------------
36 DROP TABLE IF EXISTS `user`;
37 CREATE TABLE `user` (
38 `uid` int(11) NOT NULL AUTO_INCREMENT,
39 `username` varchar(255) DEFAULT NULL,
40 `password` varchar(255) DEFAULT NULL,
41 PRIMARY KEY (`uid`)
42 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
2.1.2产生java实体模型
1 package org.guangsoft.entity;
2
3 import java.util.Set;
4
5 public class User
6 {
7 private Integer uid;
8 private String username;
9 private String password;
10 private Set<Orders> orders;
11 public Integer getUid()
12 {
13 return uid;
14 }
15 public void setUid(Integer uid)
16 {
17 this.uid = uid;
18 }
19 public String getUsername()
20 {
21 return username;
22 }
23 public void setUsername(String username)
24 {
25 this.username = username;
26 }
27 public String getPassword()
28 {
29 return password;
30 }
31 public void setPassword(String password)
32 {
33 this.password = password;
34 }
35 public Set<Orders> getOrders()
36 {
37 return orders;
38 }
39 public void setOrders(Set<Orders> orders)
40 {
41 this.orders = orders;
42 }
43
44 }
1 package org.guangsoft.entity;
2
3 import java.util.Date;
4 import java.util.Set;
5
6
7 public class Orders
8 {
9 private Integer oid;
10 private Date odate;
11 private Double ototal;
12 private String odesc;
13 private User user;
14 //关联订单下的明细
15 private Set<Detail> details;
16 public Integer getOid()
17 {
18 return oid;
19 }
20 public void setOid(Integer oid)
21 {
22 this.oid = oid;
23 }
24 public Date getOdate()
25 {
26 return odate;
27 }
28 public void setOdate(Date odate)
29 {
30 this.odate = odate;
31 }
32 public Double getOtotal()
33 {
34 return ototal;
35 }
36 public void setOtotal(Double ototal)
37 {
38 this.ototal = ototal;
39 }
40 public String getOdesc()
41 {
42 return odesc;
43 }
44 public void setOdesc(String odesc)
45 {
46 this.odesc = odesc;
47 }
48 public User getUser()
49 {
50 return user;
51 }
52 public void setUser(User user)
53 {
54 this.user = user;
55 }
56 public Set<Detail> getDetails()
57 {
58 return details;
59 }
60 public void setDetails(Set<Detail> details)
61 {
62 this.details = details;
63 }
64
65 }
2.1.3定义Mapper接口
1 package org.guangsoft.mapper;
2
3 import org.guangsoft.entity.Orders;
4
5 public interface OrdersMapper
6 {
7 public Orders loadOrdersUser(Integer oid);
8 }
2.1.4定义Mapper.xml
描述接口中方法对应的操作。
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 <mapper namespace="org.guangsoft.mapper.OrdersMapper">
4 <resultMap type="org.guangsoft.entity.Orders" id="ordersUser">
5 <id column="oid" property="oid" />
6 <result column="odate" property="odate" />
7 <result column="odesc" property="odesc" />
8 <result column="ototal" property="ototal" />
9 <association property="user" javaType="org.guangsoft.entity.User">
10 <id column="uid" property="uid" />
11 <result column="username" property="username" />
12 <result column="password" property="password" />
13 </association>
14 </resultMap>
15 <select id="loadOrdersUser" parameterType="java.lang.Integer" resultMap="ordersUser">
16 select oid ,odate,ototal,odesc,username
17 from orders inner join user
18 on orders.uid = user.uid where orders.oid = #{oid}
19 </select>
20 </mapper>
2.1.5获得Mapper接口代理对象
1 public class TestDeptMapper
2 {
3 SqlSessionFactory ssf = null;
4 @Before
5 public void before()
6 {
7 SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
8 ssf = ssfb.build(this.getClass().getClassLoader().getResourceAsStream("MyBatis.xml"));
9 }
10
11 @Test
12 public void testGetDeptPost()
13 {
14 SqlSession sqlSession = ssf.openSession();
15 DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
16 List<DeptVo> deptVoList = deptMapper.getDeptPost();
17 for(DeptVo deptVo : deptVoList)
18 {
19 System.out.println(deptVo);
20 }
21 sqlSession.close();
22 }
23 }
2.2one-to-many查询
对表关联查询
给Users加入orders的集合属性
2.2.1定义Mapper接口
1 package org.guangsoft.mapper;
2
3 import java.util.List;
4
5 import org.guangsoft.entity.User;
6
7 public interface UserMapper
8 {
9 public User loadUserOrders(Integer uid);
10 public List<User> loadUserOrdersDetail();
11 public List<User> loadUserOrdersDetail2();
12 }
2.2.2定义Mapper文件
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 <mapper namespace="org.guangsoft.mapper.UserMapper">
4 <resultMap type="org.guangsoft.entity.User" id="userOrders">
5 <id column="uid" property="uid" />
6 <result column="username" property="username"></result>
7 <collection property="orders" ofType="org.guangsoft.entity.Orders">
8 <id column="oid" property="oid" />
9 <result column="odate" property="odate" />
10 <result column="odesc" property="odesc" />
11 <result column="ototal" property="ototal" />
12 </collection>
13 </resultMap>
14 <select id="loadUserOrders" parameterType="java.lang.Integer" resultMap="userOrders">
15 select user.uid ,oid ,odate,ototal,odesc,username
16 from orders inner join user
17 on orders.uid = user.uid where user.uid = #{uid}
18 </select>
19 <resultMap type="org.guangsoft.entity.User" id="userOrdersDetail">
20 <id column="uid" property="uid" />
21 <result column="username" property="username" />
22 <collection property="orders" ofType="org.guangsoft.entity.Orders">
23 <id column="oid" property="oid" ></id>
24 <result column="odate" property="odate" ></result>
25 <collection property="details" ofType="org.guangsoft.entity.Detail">
26 <id column="did" property="did"></id>
27 <result column="price" property="price"></result>
28 <result column="pname" property="pname"></result>
29 <result column="cts" property="cts"></result>
30 </collection>
31 </collection>
32 </resultMap>
33 <select id="loadUserOrdersDetail" resultMap="userOrdersDetail">
34 select user.uid,orders.oid,username,odate,pname,price,cts
35 from user left join orders on user.uid=orders.uid
36 left join detail on orders.oid = detail.oid
37 </select>
38
39 <select id="loadUserOrdersDetail2" resultMap="userOrdersDetail">
40 select user.uid,username from user
41 </select>
42 <select id="loadOrders" parameterType="java.lang.Integer" resultType="org.guangsoft.entity.Orders">
43 select oid,odate from orders where orders.uid=#{uid}
44 </select>
45 <select id="loadDetail" parameterType="java.lang.Integer" resultType="org.guangsoft.entity.Detail">
46 select pname,price,cts,odid from detail where detail.oid=#{oid}
47 </select>
48 </mapper>
2.2.3获得Mapper接口的代理对象
代码见上
2.3many-to-many查询(代码见上)
查询所有用户的所有订单信息和订单明细
订单号 ，用户名，日期，商品名称，单价，数量，小计
2.3.1建立订单明细表
订单明细表和订单表之间存在者主外键.
多个用户对应者多个订单，多个订单对应多个明细
2.3.2定义Mapper接口
2.3.3定义Mapper.xml文件
2.3.4获得Mapper接口代理对象
3 mybatis的懒加载
将多表关联查询的sql语句，分开执行
3.1开启懒加载
1 <!-- 开启mybatis的懒加载 -->
2 <setting name="lazyLoadingEnabled" value="true"/>
3 <setting name="aggressiveLazyLoading" value="false"/>
3.2Mapper接口中添加方法
3.3拆分sql语句
3.4获得Mapper代理对象
4 mybatis的动态sql
更具业务需要，可以对sql完成动态的构造。
4.1 if标签
需求：查询订单明细，可以根据订单的编号，商品的名称，商品数量，商品的单价查询。
问题：select * from ordersdetails where (?)
4.1.1定义Mapper接口
1 package org.guangsoft.mapper;
2
3 import java.util.List;
4
5 import org.guangsoft.entity.Detail;
6
7 public interface DetailMapper
8 {
9 /**
10 * 按照订单的编号，商品的名称，商品的数量，商品单价查询订单信息
11 * @return
12 */
13 public List<Detail> loadDetail(Detail detail);
14 public void deleteDetails(Integer dids[]);
15 }
4.1.2定义Mapper.xml文件
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 <mapper namespace="org.guangsoft.mapper.DetailMapper">
4 <sql id="cond">
5 <where>
6 <if test="pname != null"><!-- 商品名称 -->
7 and pname = #{pname}
8 </if>
9 <if test="price != null"><!-- 商品价格 -->
10 and price = #{price}
11 </if>
12 <if test="cts != null"><!-- 商品 -->
13 and cts = #{cts}
14 </if>
15 </where>
16 </sql>
17 <select id="loadDetail" resultType="org.guangsoft.entity.Detail">
18 select * from detail
19 <!-- 动态关联where关键字 -->
20 <include refid="cond"></include>
21 </select>
22 <delete id="deleteDetails" parameterType="org.guangsoft.entity.Detail">
23 delete from detail
24 <!--
25 collection需要遍历的集合
26 item集合汇总的每个元素
27 open第一次遍历
28 close最后一次遍历
29 separator将遍历的元素使用什么隔开
30 -->
31 <foreach collection="dids" item="did" open="where did in (" close=")"
32 separator=","></foreach>
33 </delete>
34 <insert id="saveDept" parameterType="org.guangsoft.entity.Dept">
35 insert into dept values(null,#{dname})
36 </insert>
37 </mapper>
4.1.3获得Mapper代理对象
1 package org.guangsoft.test;
2
3 import java.util.List;
4 import java.util.Set;
5
6 import org.apache.ibatis.session.SqlSession;
7 import org.apache.ibatis.session.SqlSessionFactory;
8 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
9 import org.guangsoft.entity.Dept;
10 import org.guangsoft.entity.Detail;
11 import org.guangsoft.entity.Orders;
12 import org.guangsoft.entity.User;
13 import org.guangsoft.mapper.DeptMapper;
14 import org.guangsoft.mapper.DetailMapper;
15 import org.guangsoft.mapper.OrdersMapper;
16 import org.guangsoft.mapper.UserMapper;
17 import org.guangsoft.vo.DeptVo;
18 import org.junit.Before;
19 import org.junit.Test;
20
21 public class TestDeptMapper
22 {
23 SqlSessionFactory ssf = null;
24 @Before
25 public void before()
26 {
27 SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
28 ssf = ssfb.build(this.getClass().getClassLoader().getResourceAsStream("MyBatis.xml"));
29 }
30
31 @Test
32 public void testGetDeptPost()
33 {
34 SqlSession sqlSession = ssf.openSession();
35 DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
36 List<DeptVo> deptVoList = deptMapper.getDeptPost();
37 for(DeptVo deptVo : deptVoList)
38 {
39 System.out.println(deptVo);
40 }
41 sqlSession.close();
42 }
43
44 @Test
45 public void testSaveDept()
46 {
47 SqlSession sqlSession = ssf.openSession();
48 DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
49 Dept dept = new Dept();
50 dept.setDname("danme");
51 deptMapper.saveDept(dept);
52 sqlSession.commit();
53 sqlSession.close();
54 }
55
56 @Test
57 public void testGetUserOrders()
58 {
59 SqlSession sqlSession = ssf.openSession();
60 UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
61 User user = userMapper.loadUserOrders(1);
62 Set<Orders> ordersSet = user.getOrders();
63 for(Orders orders : ordersSet)
64 {
65 System.out.println(orders);
66 }
67 }
68
69 @Test
70 public void testGetOrdersUser()
71 {
72 SqlSession sqlSession = ssf.openSession();
73 OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
74 Orders orders = ordersMapper.loadOrdersUser(1);
75 System.out.println(orders.getUser().getUsername());
76 sqlSession.close();
77 }
78
79 @Test
80 public void testDetail()
81 {
82 SqlSession sqlSession = ssf.openSession();
83 UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
84 List<User> ulist = userMapper.loadUserOrdersDetail();
85 for(User user : ulist)
86 {
87 Set<Orders> orders = user.getOrders();
88 if(orders != null)
89 {
90 for(Orders o : orders)
91 {
92 Set<Detail> details = o.getDetails();
93 for(Detail d : details)
94 {
95 System.out.println(d.getPname());
96 }
97 }
98 }
99 }
100 }
101
102 @Test
103 public void testDetail2()
104 {
105 SqlSession sqlSession = ssf.openSession();
106 UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
107 OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
108 List<User> userList = userMapper.loadUserOrdersDetail2();
109 for(User user : userList)
110 {
111 System.out.println(user.getUsername());
112 Set<Orders> orders = user.getOrders();
113 if(orders != null)
114 {
115 for(Orders o : orders)
116 {
117 Set<Detail> details = o.getDetails();
118 for(Detail d : details)
119 {
120 System.out.println(d.getPname());
121 }
122 }
123 }
124 }
125 }
126 }
4.2foreach标签
完成订单明细的批量删除
Delete from ordersdetails where odid in (1,3,4,5)
4.2.1定义Mapper接口
4.2.2定义Mapper.xml
4.2.3获得Mapper代理对象
5定义sql片段
使用sql标签定义sql片段，
提高sql语句复用性.
使用include标签引用sql片段
6mybatis的缓存机制
查询缓存：只有在做查询操作的时候，将数据进行缓存
6.1 mybaits的一级缓存
Session级别的缓存，不同的客户端访问数据库，缓存是独立的。
在进行查询操作的时候，数据自动放入一级缓存。
缓存数据消失：
提交事务的时候。
关闭数据库会话。
数据进行缓存的key:namespace+id+params+limit（缓存的界定，通过namespace+id+查询参数+结果集的限定），产生本次查询缓存对应的key
6.2二级缓存
二级sessionFactory级别的缓存（共享缓存）
开启二级缓存
加入缓存插件包(二级缓存为外部缓存插件)
配置缓存策略，在需要进行缓存的Mapper.xml
1 <cache readOnly="true" type="org.mybatis.caches.ehcache.EhcacheCache"></cache>
提供ehcache的配置文件