MyBatis
1.概述
MyBatis是一款优秀的持久层框架,它支持自定义SQL,存储过程以及高级映射,MyBatis免去了几乎所有的JDBC代码 以及设置参数,获取结果集对象的工作。MyBatis可以通过简单的XML配置或注解来配置映射关系。
复习JDBC
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver")
//2.获取连接
Connection connection = Drivermanager.getConnection("jdbc:mysql://localhost:3306/test","root","123456abcd");
//3.定义sql
String sql = "selet * from student where id=?";
//4.预编译
PreparedStatement statment = connection.prepareStatement(sql);
//5.设置参数
statement.setInteger(1,100);
//6.执行sql,封装结果
ResultSet result = statement.executeQuery();
2.为什么使用MyBatis
- JDBC
- SQL编写在代码中,耦合度比较高
- 实际开发中SQL经常被更新,维护不易
- Hibernate
- 内部自动生成SQL,不方便特殊优化
- 长难复制的SQL,对Hibernate来说处理很不容易
- 基于全映射的全自动框架,进行字段部分映射比较困难,并且会导致数据库性能下降。
- MyBatis
- SQL和Java编码分离,功能划分清楚,一个专注数据,一个专注业务
- 核心SQL可以自己编写,优化比较方便
3.MyBatis环境搭建
3.1环境搭建
步骤:
- 创建Maven工程,并导入相关依赖
- 创建实体类
- 创建dao接口:UserDao
- 创建MyBatis主配置文件:SqlMapConfig.xml
- 创建映射配置文件:UserDao.xml
注意事项
- 创建directory 和 package的区别
- directory : com.wdzl.dao创建的是一级目录
- package : com.wdzl.dao 创建了三级目录
- MyBatis的映射配置文件所在目录层级要与对应的dao层接口目录层级相同
- 映射配置文件
mapper
标签namespace属性的值必须对应接口的全限定类名 - 映射配置文件
select
标签id
属性必须与对应接口中的方法名一致,不能随便写 - 只要遵循2,3,4的规定,我们就不用去写接口的实现类
3.2测试案例
步骤
- 读取配置文件
- 创建SqlSessionFactory —> 通过SqlSessionFactoryBuilder对象获取
- 使用工厂对象生产SqlSession对象
- 使用SqlSession对象获取接口的代理对象
- 使用代理对象调用方法
- 释放资源
4.自定义MyBatis
MyBatis在使用代理模式实现增删改查都做了什么
- 创建代理对象
- 在代理对象中创建方法
5.CRUD
public interface UserDao {
/**
* 查询所有用户
*/
List<User> findAll();
/**
* 添加用户
* @param user
*/
void addUser(User user);
/**
* 修改用户信息
*/
void updateUser(User user);
/**
* 删除用户
* @param user
*/
void delUser(User user);
/**
* 模糊查询 根据用户名模糊查询
*/
List<User> findUserByName(String name);
}
<?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="com.wdzl.dao.UserDao"> <!--对应dao层接口的全限定类名-->
<!--查询所有用户信息-->
<select id="findAll" resultType="com.wdzl.pojo.User">
select * from user;
</select>
<!--添加用户-->
<insert id="addUser" parameterType="com.wdzl.pojo.User">
insert into user(username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})
</insert>
<update id="updateUser" parameterType="com.wdzl.pojo.User">
update user set address = #{address} where id = #{id}
</update>
<delete id="delUser" parameterType="com.wdzl.pojo.User">
delete from user where id=#{id}
</delete>
<select id="findUserByName" parameterType="String" resultType="com.wdzl.pojo.User">
select * from user where username like #{name}
</select>
</mapper>
package com.wdzl.dao;
import com.wdzl.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import javax.xml.crypto.Data;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* UserDao测试类
* MyBatis 入门案例测试实现步骤
* 1.读取配置文件
* 2.创建sqlSessionFactory工厂
* 3.用工厂生产SqlSession
* 4.使用SqlSession对象创建dao层代理对象
* 5.使用代理对象执行方法
* 6.释放资源
*/
public class UserDaoTest {
@Test
public void findAllTest() throws IOException {
//1.读取配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建sqlSessionFactory工厂
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(resourceAsStream);
//3.用工厂生产SqlSession
SqlSession session = factory.openSession();
//4.使用SqlSession对象创建dao层代理对象
UserDao userDao = session.getMapper(UserDao.class);
//5.使用代理对象执行方法
List<User> users = userDao.findAll();
for (User user : users) {
System.out.println(user);
}
//6.释放资源
session.close();
resourceAsStream.close();
}
@Test
public void addUserTest() throws IOException {
/* 1.读取配置文件
* 2.创建sqlSessionFactory工厂
* 3.用工厂生产SqlSession
* 4.使用SqlSession对象创建dao层代理对象
* 5.使用代理对象执行方法
* 6.释放资源*/
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(resourceAsStream);
SqlSession sqlSession = factory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
Date date = new Date();
userDao.addUser(new User(0,"张三",date,"男","西安"));
sqlSession.commit();
sqlSession.close();
resourceAsStream.close();
}
@Test
public void updateUserTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(resourceAsStream);
SqlSession sqlSession = factory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
Date date = new Date();
userDao.updateUser(new User(3,"1",date,"1","1"));
sqlSession.commit();
sqlSession.close();
resourceAsStream.close();
}
@Test
public void delUserTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(resourceAsStream);
SqlSession sqlSession = factory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = new User();
user.setId(1);
userDao.delUser(user);
sqlSession.commit();
sqlSession.close();
resourceAsStream.close();
}
@Test
public void findUserByNameTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(resourceAsStream);
SqlSession sqlSession = factory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> users = userDao.findUserByName("%南%");
for (User user : users) {
System.out.println(user);
}
sqlSession.commit();
sqlSession.close();
resourceAsStream.close();
}
}
<!--添加用户-->
<insert id="addUser" parameterType="com.wdzl.pojo.User">
<!--keyProperty : 返回的值 order : sql语句执行之后或者之前-->
<selectKey keyProperty="id" order="BEFORE" resultType="int">
select last_insert_id()
</selectKey>
insert into user(username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})
</insert>
6.当属性名与字段名不同时
7.自建impl
package com.wdzl.dao.impl;
import com.wdzl.dao.UserDao;
import com.wdzl.pojo.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import java.util.List;
public class UserDaoImpl implements UserDao {
private SqlSessionFactory factory;
public UserDaoImpl() {
}
public UserDaoImpl(SqlSessionFactory factory) {
this.factory = factory;
}
@Override
public List<User> findAll() {
//获取sqlSession对象
SqlSession sqlSession = factory.openSession();
//
List<User> users = sqlSession.selectList("com.wdzl.dao.UserDao.findAll");
return users;
}
@Override
public void addUser(User user) {
SqlSession sqlSession = factory.openSession();
sqlSession.insert("com.wdzl.dao.UserDao.addUser",user);
sqlSession.commit();
sqlSession.close();
}
@Override
public void updateUser(User user) {
SqlSession sqlSession = factory.openSession();
sqlSession.update("com.wdzl.dao.UserDao.updateUser", user);
sqlSession.commit();
sqlSession.close();
}
@Override
public void delUser(User user) {
SqlSession sqlSession = factory.openSession();
sqlSession.delete("com.wdzl.dao.UserDao.delUser", user);
sqlSession.commit();
sqlSession.close();
}
@Override
public User findUserByName(String name) {
SqlSession sqlSession = factory.openSession();
User user = sqlSession.selectOne("com.wdzl.dao.UserDao.findUserByName", name);
return user;
}
@Override
public int findTotal() {
SqlSession sqlSession = factory.openSession();
int i = sqlSession.selectOne("com.wdzl.dao.UserDao.findTotal");
return i;
}
}
package com.wdzl.dao;
import com.wdzl.dao.impl.UserDaoImpl;
import com.wdzl.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import javax.xml.crypto.Data;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
public class UserDaoTest {
private InputStream resourceAsStream;
private SqlSessionFactoryBuilder builder;
private SqlSessionFactory factory;
@Before
public void first() throws IOException {
//1.读取配置文件
resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建sqlSessionFactory工厂
builder = new SqlSessionFactoryBuilder();
factory = builder.build(resourceAsStream);
}
@After
public void last() throws IOException {
resourceAsStream.close();
}
@Test
public void findAllTest() throws IOException {
UserDao userDao = new UserDaoImpl(factory);
List<User> users = userDao.findAll();
for (User user : users) {
System.out.println(user);
}
}
@Test
public void addUserTest() {
Date date = new Date();
User user = new User();
user.setUsername("1");
user.setAddress("1");
user.setBirthday(date);
user.setSex("1");
UserDao userDao = new UserDaoImpl(factory);
userDao.addUser(user);
}
@Test
public void updateUserTest() {
User user = new User();
user.setAddress("111111111");
user.setId(3);
UserDao userDao = new UserDaoImpl(factory);
userDao.updateUser(user);
}
@Test
public void delUserTest() {
User user = new User();
user.setId(7);
UserDao userDao = new UserDaoImpl(factory);
userDao.delUser(user);
}
@Test
public void findUserByName() {
UserDao userDao = new UserDaoImpl(factory);
User user = userDao.findUserByName("%三%");
System.out.println(user);
}
@Test
public void findTotalTest() {
UserDao userDao = new UserDaoImpl(factory);
int i = userDao.findTotal();
System.out.println(i);
}
}
8.配置别名
全部与大小写无关
9.自动提交
在创建SqlSession sqlSession = factory.openSession();
向 openSession(true)
10.复合条件查询
/**
* 根据给定的条件对满足条件的用户进行复合条件查询
*
* 查询满足姓名 张三 性别 男
*/
List<User> findUserByCondition(User user);
<!--根据条件 复合条件查询-->
<select id="findUserByCondition" parameterType="user" resultType="user">
select * from user where
<where>
<if test="username != null">
and username=#{username}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</where>
</select>
11.查询id为,1,3的 用户
/**
* 查询id为,1,3的 用户
*/
List<User> findUserByIds(QueryVo queryVo);
<!--查询id为1,3-->
<select id="findUserByIds" parameterType="queryVo" resultType="user">
select * from user
<where>
<if test="ids != null and ids.size()>0">
<foreach collection="ids" open="and id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
12.从外部连接
13.sql语句提取
14.多表查询
在MySQL中,表与表有三种关系
- 一对一
- 一对多
- 多对多
在MyBatis中把多对一的关系按一对一的情况处理
多对一 和 一对多
需求:查询所有账户信息的同时,显示所属用户的信息
确定SQL语句
select a.id,a.money,u.id,u.username,u.sex,u.address from account a,user u where u.id=a.uid
<?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="com.wdzl.dao.AccountDao"> <!--对应dao层接口的全限定类名-->
<!--定义封装account 和 user的resultMap-->
<resultMap id="accountUserMap" type="account">
<id property="id" column="id"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!--配置一对一关系映射,配置user的内容-->
<association property="user" column="uid" javaType="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</association>
</resultMap>
<select id="findAccount" resultMap="accountUserMap">
select a.id,a.money,u.id,u.username,u.sex,u.address from account a,user u where u.id=a.uid
</select>
</mapper>
一对多
<?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="com.wdzl.dao.UserDao"> <!--对应dao层接口的全限定类名-->
<resultMap id="userAccountMap" type="user">
<id property="uid" column="uid"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<collection property="accounts" ofType="account" column="id">
<id property="aid" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
</collection>
</resultMap>
<!--查询用户列表-->
<select id="findAll" resultMap="userAccountMap">
select * from user u left outer join account a on u.uid=a.uid
</select>
</mapper>
多对多
<mapper namespace="com.wdzl.dao.RoleDao"> <!--对应dao层接口的全限定类名-->
<resultMap id="roleMap" type="role">
<id property="rid" column="rid"></id>
<result property="role_name" column="role_name"></result>
<result property="role_desc" column="role_desc"></result>
<collection property="users" ofType="user" column="uid">
<id property="uid" column="uid"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</collection>
</resultMap>
<!--查询所有角色-->
<select id="findAll" resultMap="roleMap">
select r.*,u.* from role r left outer join user_role ur on r.rid = ur.rid left outer join user u on u.uid=ur.uid
</select>
</mapper>
<mapper namespace="com.wdzl.dao.UserDao"> <!--对应dao层接口的全限定类名-->
<resultMap id="userAccountMap" type="user">
<id property="uid" column="uid"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<collection property="roles" ofType="role" column="uid">
<id property="rid" column="rid"></id>
<result property="role_name" column="role_name"></result>
<result property="role_desc" column="role_desc"></result>
</collection>
</resultMap>
<!--查询用户列表-->
<select id="findAll" resultMap="userAccountMap">
SELECT u.*,r.* FROM USER u LEFT OUTER JOIN user_role ur ON ur.`UID`=u.`uid` LEFT OUTER JOIN role r ON r.`rid`=ur.`RID`
</select>
</mapper>
15.懒加载
16.缓存
一级缓存
二级缓存
二级缓存中只存储数据,不存储对象
17.注解方式
package com.wdzl.dao;
import com.wdzl.pojo.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
public interface UserDao {
@Select("select * from user")
User findAll();
/**
* 添加用户
* @param user
*/
@Insert("insert into user (username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})")
void addUser(User user);
/**
* 修改
* @param user
*/
@Update("update user set address=#{address} where uid=#{uid}")
void updateUser(User user);
@Delete("delete from user where uid=#{uid}")
void delUser(int id);
/**
* 根据id查
* @param uid
* @return
*/
@Select("select * from user where uid=#{uid}")
User findUserById(Integer uid);
/**
* 模糊查询
* @param name
* @return
*/
@Select("select * from user where username like #{username}")
List<User> findUserByName(String name);
/**
* 查询总记录数
* @return
*/
@Select("select count(*) from user")
int findTotal();
}
18.当表名与属性名不相同时
引用
19.多表查询