MyBatis框架
第一章 原始dao的开发方式
- 实现步骤
- 定义dao层接口
- 定义dao层接口实现类
public interface EmployeeDao {
User queryEmployeeById(Integer id);
}
public class EmployeeImpl implements EmployeeDao {
private SqlSessionFactory sqlSessionFactory;
public UserDaoImpl(SqlSessionFactory sqlSessionFactory){
this.sqlSessionFactory = sqlSessionFactory;
}
@Override
public User queryUserById(Integer id) {
SqlSession sqlSession = sqlSessionFactory.openSession();
User user = sqlSession.selectOne("com.atguigu.mybatis.dao.EmployeeMapper.queryEmployeeById", id);
sqlSession.close();
return user;
}
}
第二章 动态代理开发
1.1 动态代理开发的好处
定义一个Mapper接口,这个接口其实和我们EmployeeDao接口是一样的,从Mybatis框架中拿到一个代理对象(代理的是这个Mapper接口),通过代理对象调用接口当中的方法完成业务。
传统dao开发方式中的实现类其实起了一个连接、承上启下的作用,连接了接口和xml映射文件,效果就是调用接口方法时能够找到xml映射文件。
1.2 动态代理开发规范
- sql映射文件的namespace必须和mapper接口的全限定类名保持一致
- mapper接口的接口方法名必须和xml中的sql语句id保持一致
- mapper接口的接口方法形参类型必须和sql语句的输入参数类型保持一致
- mapper接口的接口方法返回类型必须和sql语句的resultType保持一致
动态代理方式:SqlSession接口方法getMapper(被代理接口的class对象)获取到该接口实现类,实现类是动态代理技术临时生成,源码中并不存在
public interface EmployeeMapper {
Employee queryEmployeeById(Integer id);
}
<mapper namespace="com.atguigu.mapper.EmployeeMapper">
<select id="queryEmployeeById" resultType="com.atguigu.pojo.Employee" parameterType="int">
select emp_id empId,emp_name empName,emp_salary empSalary from t_emp where emp_id= #{id}
</select>
</mapper>
SqlSession sqlSession = sqlSessionFactory.openSession();
//getMapper方法,代理接口,返回接口实现类对象
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.queryEmployeeById(2);
System.out.println("employee = " + employee);
sqlSession.close();
1.3 动态代理新增数据
<insert id="insertEmployee" parameterType="com.atguigu.pojo.Employee">
insert into t_emp values(#{empId},#{empName},#{empSalary})
</insert>
int insertEmployee(Employee employee);
SqlSession sqlSession = sqlSessionFactory.openSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setEmpSalary(100D);
employee.setEmpName("Tom");
employee.setEmpId(null);
int row = mapper.insertEmployee(employee);
System.out.println("row = " + row);
sqlSession.commit();
1.4 动态代理更新数据
<update id="updateEmployee" parameterType="com.atguigu.pojo.Employee">
update t_emp set emp_name = #{empName},emp_salary = #{empSalary} where emp_id = #{empId}
</update>
SqlSession sqlSession = sqlSessionFactory.openSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setEmpSalary(300D);
employee.setEmpName("Toms");
employee.setEmpId(2);
int row = mapper.updateEmployee(employee);
System.out.println("row = " + row);
sqlSession.commit();
sqlSession.close();
1.5 动态代理删除数据
<update id="deleteEmployeeById" parameterType="int">
delete from t_emp where emp_id = #{empId}
</update>
SqlSession sqlSession = sqlSessionFactory.openSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
int row = mapper.deleteEmployeeById(2);
System.out.println("row = " + row);
sqlSession.commit();
sqlSession.close();
第三章 MyBatis框架的全局配置
1.1 全局properties配置文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8
jdbc.username=root
jdbc.password=root
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
1.2 全局typeAliases配置
别名 | 映射的类型 |
---|---|
_byte | byte |
_long | long |
_short | short |
_int | int |
_integer | int |
_double | double |
_float | float |
_boolean | boolean |
string | String |
byte | Byte |
long | Long |
short | Short |
int | Integer |
integer | Integer |
double | Double |
float | Float |
boolean | Boolean |
date | Date |
decimal | BigDecimal |
bigdecimal | BigDecimal |
map | Map |
结果集数据类型定义别名,别名不区分大小写
<typeAliases>
<!-- 为pojo对象定义别名-->
<typeAlias type="com.atguigu.pojo.Employee" alias="employee"></typeAlias>
</typeAliases>
<!-- 使用别名即可-->
<select id="queryEmployeeById" parameterType="int" resultType="employee">
select * from t_emp where emp_idid=#{id}
</select>
1.3 全局配置文件mappers
mappers注册sql映射文件的
- resource属性加载sql映射文件,万能型选手(crud、原始dao、mapper动态代理)
- 针对Mapper动态代理进行一个增强(增强两种用法)
- mapper class 单个注册
- package 批量扫描注册
- 以上两种方式有规范要求
<mappers>
<mapper resource="sqlmapper/UserMapper.xml" />
<mapper resource="mapper/UserMapper.xml" />
<package name="com.atguigu.mapper"></package>
</mappers>
1.4 参数类型是另一个pojo对象
public class EmployeeVo {
private Employee employee;
}
<select id="queryEmployeeByIdVo" resultType="com.atguigu.pojo.Employee" parameterType="com.atguigu.pojo.EmployeeVo">
select emp_id empId,emp_name empName,emp_salary empSalary from t_emp where emp_id= #{employee.empId}
</select>
SqlSession sqlSession = sqlSessionFactory.openSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setEmpId(1);
EmployeeVo employeeVo = new EmployeeVo();
employeeVo.setEmployee(employee);
Employee employee1 = mapper.queryEmployeeByIdVo(employeeVo);
System.out.println("employee1 = " + employee1);
sqlSession.close();
第四章 手动映射
1.1 什么情况下使用手动映射
当数据表中的列名和pojo中类的属性名不同时,将会出现封装数据失败的现象,MyBatis无法将数据表中的数据准确的封装到pojo对象中,因此必须使用手动映射的方式来实现。
1.2 resultMap标签
- resultMap标签可以实现手动映射
- id属性:自定义的唯一值
- type属性:手动映射后的查询结果集类型
- id子标签:映射主键使用
- property属性:对应pojo对象的字段名
- column属性:对应数据表的列名
- result子标签:映射数据表其他字段
- property属性:对应pojo对象的字段名
- column属性:对应数据表的列名
- select标签属性resultMap,关联resultMap标签的id值
<select id="queryEmployeeByAs" resultMap="queryEmployeeByAsResultMap">
select emp_id ,emp_name ,emp_salary from t_emp
</select>
<resultMap id="queryEmployeeByAsResultMap" type="com.atguigu.pojo.Employee">
<id column="emp_id" property="empId"/>
<result column="emp_name" property="empName"/>
<result column="emp_salary" property="empSalary"/>
</resultMap>
第五章 动态SQL标签
1.1 数据准备
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`birthday` date DEFAULT NULL COMMENT '生日',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', '张三', '1', '2018-07-10', '北京');
INSERT INTO `user` VALUES ('2', '李四', '1', '2018-07-10', '上海');
INSERT INTO `user` VALUES ('3', '王五', '1', '2018-07-10', '广州');
INSERT INTO `user` VALUES ('4', '王六', '1', '2018-07-10', '深圳');
1.2 if和where标签
需求:根据用户名和性别查询用户
<select id="queryUserByWhere" resultType="user" parameterType="user">
select * from user where sex=#{sex} and username like #{username}
</select>
public void queryUserByWhere(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setSex("2");
user.setUsername("%王%");
List<User> list = mapper.queryUserByWhere(user);
if(list!=null && list.size()>0){
for(User user1 : list){
System.out.println(user1);
}
}
}
以上查询是可以查询到相关的数据的,假定不传递sex的值,那么就会出现什么也查不到的结果。
通过日志可以看出,MyBatis在执行SQL语句的时候,参数sex的值是null,因此没有查询到结果。在以往的做法是判断参数是否为空,并进行字符串的拼接。在MyBatis框架中,提供了where标签和if标签来实现动态SQL语句。
- where标签:处理SQL语句,自动添加where关键字,并去掉紧跟他后面的一个and或者or
- if标签,test属性,判断表达式真假
<select id="queryUserByWhere" resultType="user" parameterType="user">
select * from user
<where>
<if test="sex!=''and sex!=null">
and sex=#{sex}
</if>
<if test="username!=''and username!=null">
and username like #{username}
</if>
</where>
</select>
1.3 foreach标签
foreach标签传递集合
// 删除多条数据的SQL语句,delete from user where id in(1,2,3)
String sql = "delete from user where id in("
for(int i=0; i<list.size();i++) {
if(i != list.size()-1)
sql += list.get(i) + ","
else
sql += list.get(i)+")"
}
foreach标签遍历拼接SQL语句
- collection属性:遍历传入的集合,当参数是集合时collection属性值固定为list
- open属性:遍历拼接前
- close属性:遍历拼接后
- separator属性:拼接的符号
- item属性:遍历到的元素
<select id="queryUserByIdsList" parameterType="list" resultType="user">
select * from user
<foreach collection="list" open="where id in(" close=")" separator="," item="item">
#{item}
</foreach>
</select>
public void queryUserByIdsList(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Integer> idsList = new ArrayList<Integer>();
idsList.add(1);
idsList.add(2);
idsList.add(3);
List<User> list = mapper.queryUserByIdsList(idsList);
if(list!=null && list.size()>0){
for (User user : list){
System.out.println(user);
}
}
sqlSession.close();
foreach标签传递数组
foreach标签遍历拼接SQL语句
- collection属性:遍历传入的集合,当参数是数组时collection属性值固定为array
- open属性:遍历拼接前
- close属性:遍历拼接后
- separator属性:拼接的符号
- item属性:遍历到的元素
<select id="queryUserByIdsArray" parameterType="int[]" resultType="user">
select * from user
<foreach collection="array" open="where id in(" close=")" separator="," item="item">
#{item}
</foreach>
public void queryUserByIdsArray(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int[] idsArray= {1,2,3};
List<User> list = mapper.queryUserByIdsArray(idsArray);
if(list!=null && list.size()>0){
for (User user : list){
System.out.println(user);
}
}
sqlSession.close();
}
foreach标签传递pojo对象
foreach标签遍历拼接SQL语句
- collection属性:遍历传入的pojo对象中的集合,collection属性配置pojo中成员变量名
- open属性:遍历拼接前
- close属性:遍历拼接后
- separator属性:拼接的符号
- item属性:遍历到的元素
public class QueryVo {
private List<Integer> idsList;
public List<Integer> getIdsList() {
return idsList;
}
public void setIdsList(List<Integer> idsList) {
this.idsList = idsList;
}
}
<select id="queryUserByQueryVo" parameterType="queryVo" resultType="user">
select * from user
<foreach collection="idsList" open="where id in(" close=")" separator="," item="item">
#{item}
</foreach>
</select>
public void queryUserByQueryVo(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
QueryVo queryVo = new QueryVo();
List<Integer> idsList = new ArrayList<Integer>();
idsList.add(1);
idsList.add(2);
idsList.add(3);
queryVo.setIdsList(idsList);
List<User> list = mapper.queryUserByQueryVo(queryVo);
if(list!=null && list.size()>0){
for (User user : list){
System.out.println(user);
}
}
sqlSession.close();
}
第六章 MyBatis多表查询
1.1 多表关系分析
多表关联:至少两个表关联。分析多表关系的经验技巧:从一条记录出发,不要从表整体去分析,比如分析A表和B表关系,A表中的一条记录对应B表中的几条记录,如果是一条,那么A到B就是一对一;如果多条,那么A到B就是一对多
- 一对一,从订单表出发,到用户表,是一对一的关系
- 一对多,从用户表出发,到订单表,一条用户数据可以在订单表中存在多条记录,这就是一对多。通过主外键来体现一对多的表结构。一的一方是主表,多的一方是从表,从表当中有一个字段是外键,指向了主表的主键
- 多对多,用户和角色表,一个用户可以有很多角色,一个角色有很多用户,多对多通过中间表来体现
1.2 多表关联的SQL语句表达分析
- 笛卡尔积
- SELECT *FROM USER,orders
- 关联查询
- 内关联 innder join on
- SELECT * FROM USER u,orders o WHERE u.id=o.user_id
- SELECT * FROM USER u INNER JOIN orders o ON u.id=o.user_id;
- 左外连接
- SELECT * FROM USER u LEFT JOIN orders o ON u.id=o.user_id
- 右外连接
- SELECT * FROM USER u RIGHT JOIN orders o ON u.id=o.user_id
- 内关联 innder join on
1.3 一对一查询
需求:查询订单表全部数据,关联查询出订单对应的用户数据(username address)。
Sql语句自己来写,Mybatis只是帮我们执行sql语句同时封装结果集。
SQL语句:
SELECT o.id,o.user_id,o.number,o.note,u.address,u.username FROM orders o LEFT JOIN USER u ON o.user_id=u.id
对于查询结果集,没有对应的pojo对象,因此MyBatis框架也不能封装结果集,但是可以将User对象放在Orders对象中,因为一对一关系,一个Orders对象可以对应一个User对象。需要手动映射方式,实现查询结果集封装。
- Orders.java
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
}
- association(联合)标签,实现手动映射
- propery属性:封装的pojo对象
- javaType属性:封装的pojo对象类型
<select id="queryOrdersUser" resultMap="ordersUserResultType" >
SELECT o.id,o.user_id,o.number,o.note,u.address,u.username FROM orders o LEFT JOIN USER u
ON o.user_id=u.id
</select>
<resultMap id="ordersUserResultType" type="orders">
<id column="id" property="id"></id>
<result column="user_id" property="userId"></result>
<result column="number" property="number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
<!-- 手动映射,配置User对象-->
<association property="user" javaType="user">
<id column="user_id" property="id"></id>
<result column="address" property="address"></result>
<result column="username" property="username"></result>
</association>
</resultMap>
public void queryOrdersUser(){
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> list = mapper.queryOrdersUser();
if(list != null && list.size() > 0){
for (Orders orders : list){
System.out.println(orders);
}
}
sqlSession.close();
1.4 一对多查询
查询全部用户数据,关联查询出订单数据。
SQL语句:SELECT u.id,u.username,u.sex,u.birthday,u.address,o.user_id,o.id,o.number,o.createtime,o.note FROM USER u LEFT JOIN orders o ON u.id=o.user_id
个用户对应多个订单数据,因此在pojo中,订单对象是存储在集合中,并保存在User对象中
- User.java
public class User {
private int id;
private String username;
private String sex;
private Date birthday;
private String address;
private List<Orders> ordersList;
}
- collection标签:
- property属性:封装的对应的属性名
- ofType属性:已经指定为一个集合List,需要指定集合中的泛型
<select id="queryUserOrders" resultMap="userOrdersResultMap">
SELECT u.id,u.username,u.sex,u.birthday,u.address,o.user_id,o.id,
o.number,o.createtime,o.note
FROM USER u LEFT JOIN orders o
ON u.id=o.user_id
</select>
<resultMap id="userOrdersResultMap" type="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
<result column="address" property="address"></result>
<collection property="ordersList" ofType="orders">
<id column="id" property="id"></id>
<result column="user_id" property="userId"></result>
<result column="number" property="number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
</collection>
</resultMap>
public void queryUserOrders(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> list = mapper.queryUserOrders();
if(list != null && list.size() > 0){
for(User user : list){
System.out.println(user);
}
}
sqlSession.close();
}
1.5 多对多查询
多对多:双向的一对多,从A表到B表出发是一对多的关系,从B表出发到A表也是一对多的关系
SQL语句:
SELECT r.RID,r.RNAME,r.RDESC,u.id,u.username,u.sex,u.birthday,u.address FROM role r LEFT JOIN user_role ur
ON r.RID=ur.RID LEFT JOIN USER u ON u.id=ur.UID
Role.java
public class Role {
private Integer rid;
private String rname;
private String rdesc;
private List<User> userList;
}
<mapper namespace="com.atguigu.mapper.RoleMapper">
<select id="queryRoleUser" resultMap="roleUserResultMap">
SELECT r.RID,r.RNAME,r.RDESC,u.id,u.username,u.sex,u.birthday,u.address FROM role r LEFT JOIN user_role ur ON r.RID=ur.RID LEFT JOIN USER u ON u.id=ur.UID
</select>
<resultMap id="roleUserResultMap" type="role">
<id column="rid" property="rid"></id>
<result column="rname" property="rname"></result>
<result column="rdesc" property="rdesc"></result>
<result column="" property=""></result>
<collection property="userList" ofType="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
<result column="address" property="address"></result>
</collection>
</resultMap>
</mapper>
public void queryRoleUser(){
SqlSession sqlSession = sqlSessionFactory.openSession();
RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
List<Role> roleList = mapper.queryRoleUser();
if(roleList != null && roleList.size() > 0){
for (Role role : roleList){
System.out.println(role);
}
}
sqlSession.close();
}