Mybatis学习小总结
快速入门
Mybatis开发步骤:
在pom.Xml中引入坐标:
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<!--具体版本看自己的mysql数据库版本-->
<version>8.0.25</version>
</dependency>
在com/xzh/mapper/UserMapper.xml里面配置xml
<?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="userMapper">
<!--resultType设置返回的类型-->
<select id="finAll" resultType="com.xzh.doman.User">
select *
from user
</select>
</mapper>
配置 sqlMapConfig.xml
映射文件的一些配置说明
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--配置数据源环境-->
<environments default="developement">
<environment id="developement">
<!--事务管理器-->
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<!--这里因为用的是8.0以上的mysql所以这样配置-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis"/>
<property name="password" value="000000"/>
<property name="username" value="root"/>
</dataSource>
</environment>
</environments>
<!--加载映射文件-->
<mappers>
<mapper resource="com/xzh/mapper/UserMapper.xml"/>
</mappers>
</configuration>
测试类:
package com.xzh.test.Mybatis;
import com.xzh.domain.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.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @Author XuZhuHong
* @CreateTime 2021/5/27 21:25
*/
public class MyBatisTest {
@Test
public void testSelect() throws IOException {
//调用方法获取sqlSession
SqlSession sqlSession = getSqlSession();
//执行操作 参数就是UserMapper.xml配置里面 的
// <mapper namespace="userMapper">和 <select id="finAll"
//的namespace 和id的值
List< User > userList = sqlSession.selectList("userMapper.finAll");
//打印输出
for (User user : userList) {
System.out.println(user);
}
//释放资源
sqlSession.close();
}
public static SqlSession getSqlSession() throws IOException {
//获取核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得session会话对象:
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
}
MyBatis的增删改查操作
基于之前配置的环境,只需要在com/xzh/mapper/UserMapper.xml里面添加配置
insert操作
<!--插入操作-->
<!--parameterType是设置传入的数据类型-->
<insert id="save" parameterType="com.xzh.domain.User">
insert into user
values (#{id}, #{username}, #{password})
</insert>
<!-- #{id} #{}这里面填的是User对应的属性-->
@Test
public void testInsert() throws IOException {
User user = new User();
//这里有点奇怪 如果把id改成Integer类型的数据 不set就会报异常 但是int就不会
user.setUsername("陆六");
user.setPassword("123");
//调用方法获取sqlSession
SqlSession sqlSession = getSqlSession();
//执行操作 参数就是UserMapper.xml配置里面 的
// <mapper namespace="userMapper">和 <select id="save"
//的namespace 和id的值 这个后面的参数是传递一个对象进去 对象是要对应映射里的对象
sqlSession.insert("userMapper.save",user);
//提交事务 这个Mybatis是不会自动提交的需要手动提交
sqlSession.commit();
//释放资源
sqlSession.close();
}
注意点:
Update操作
基于之前配置的环境,只需要在com/xzh/mapper/UserMapper.xml里面添加配置
<!--更新操作-->
<!--parameterType是设置传入的数据类型-->
<update id="update" parameterType="com.xzh.domain.User">
update user
set username=#{username},
password=#{password}
where id = #{id}
</update>
<!-- #{id} #{}这里面填的是User对应的属性-->
测试类:
@Test
public void testUpdate() throws IOException {
User user = new User();
user.setId(11);
user.setUsername("陆六1");
user.setPassword("1231");
//调用方法获取sqlSession
SqlSession sqlSession = getSqlSession();
sqlSession.update("userMapper.update",user);
//提交事务 这个Mybatis是不会自动提交的需要手动提交
sqlSession.commit();
//释放资源
sqlSession.close();
}
delete操作
基于之前配置的环境,只需要在com/xzh/mapper/UserMapper.xml里面添加配置
<!--删除操作-->
<delete id="delete" parameterType="java.lang.Integer">
delete
from user
where id = #{id}
</delete>
<!--#{id}这里里面因为只需要一个数据 所以怎么写都ok-->
@Test
public void testDelete() throws IOException {
//调用方法获取sqlSession
SqlSession sqlSession = getSqlSession();
sqlSession.delete("userMapper.delete",13);
sqlSession.commit();
sqlSession.close();
}
知识小结
MyBatis核心配置文件层级关系 (sqlMapConfig.xml)配置关系
environments标签
其中,事务管理器(transactionManager)类型有两种:
JDBC:这个配置就是直接使用了JDBC 的提交和回滚设置,它依赖于从数据源得到的连接来管理事务作用域。(一般是这个)
MANAGED:这个配置几乎没做什么。它从来不提交或回滚一个连接,而是让容器来管理事务的整个生命周期(比如 JEE 应用服务器的上下文)。 默认情况下它会关闭连接,然而一些容器并不希望这样,因此需要将 closeConnection 属性设置为 false 来阻止它默认的关闭行为。
其中,数据源(dataSource)类型有三种:
UNPOOLED:这个数据源的实现只是每次被请求时打开和关闭连接。
POOLED:这种数据源的实现利用“池”的概念将 JDBC 连接对象组织起来。(一般是这个)
JNDI:这个数据源的实现是为了能在如 EJB 或应用服务器这类容器中使用,容器可以集中或在外部配置数据源,然后放置一个 JNDI 上下文的引用。
mapper标签
该标签的作用是加载映射的,加载方式有如下几种:
使用相对于类路径的资源引用,例如:
使用完全限定资源定位符(URL),例如:
使用映射器接口实现类的完全限定类名,例如:
将包内的映射器接口实现全部注册为映射器,例如:
Properties标签
typeAliases标签
知识小结
代理开发模式
模式介绍和条件
对应的关系如下图:
对应UserMapper.xml
<?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">
<!--这个文件里面主要描述的说是sql语句-->
<mapper namespace="com.xzh.dao.UserMapper">
<!--删除操作-->
<delete id="delete" parameterType="java.lang.Integer">
delete
from user
where id = #{id}
</delete>
<!--#{id}这里里面因为只需要一个数据 所以怎么写都ok-->
<!--更新操作-->
<!--parameterType是设置传入的数据类型 这里是设置了别名 所以只写的user-->
<update id="update" parameterType="user">
update user
set username=#{username},
password=#{password}
where id = #{id}
</update>
<!-- #{id} #{}这里面填的是User对应的属性-->
<!--插入操作-->
<!--parameterType是设置传入的数据类型 这里是设置了别名 所以只写的user-->
<insert id="save" parameterType="user">
insert into user
values (#{id}, #{username}, #{password})
</insert>
<!-- #{id} #{}这里面填的是User对应的属性-->
<!--查询操作-->
<!--resultType设置结果集合返回的类型 这里是设置了别名 所以只写的user-->
<select id="findAll" resultType="user">
select *
from user
</select>
<select id="findById" resultType="user" parameterType="int">
select * from user where id=#{id}
</select>
</mapper>
对应接口:
package com.xzh.dao;
import com.xzh.domain.User;
import java.io.IOException;
import java.util.List;
/**
* @Author XuZhuHong
* @CreateTime 2021/5/28 9:14
*/
public interface UserMapper {
List< User > findAll() throws IOException;
void save(User user);
void update(User user);
void delete(int id);
User findById(int id);
}
### 对应测试类:
package com.xzh.service;
import com.xzh.dao.UserMapper;
import com.xzh.domain.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.crypto.Mac;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @Author XuZhuHong
* @CreateTime 2021/5/28 9:20
*/
public class ServiceDemo {
@Test
public void testUpdate() throws IOException {
//调用函数得到sqlSession
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
userMapper.update(new User(6, "琪琪", "123"));
}
@Test
public void testFindById() throws IOException {
//调用函数得到sqlSession
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User byId = userMapper.findById(6);
System.out.println(byId);
}
@Test
public void testFindAll() throws IOException {
//调用函数得到sqlSession
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List< User > all = userMapper.findAll();
for (User user : all) {
System.out.println(user);
}
}
//得到sqlSession函数
public static SqlSession getSqlSession() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession(true);
return sqlSession;
}
动态Sql语句
if语句
<select id="findByCondition" parameterType="user" resultType="user">
select *
from user
<where>
<if test="id!=0">and id = #{id}</if>
<if test="username!=null">and username = #{username}</if>
<if test="password!=null">and password = #{password}</if>
</where>
</select>
对应测试类:
@Test
public void testFindByCondition() throws IOException {
User user = new User();
// user.setPassword("123");
user.setUsername("张三");
//调用函数得到sqlSession
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List< User > userList = mapper.findByCondition(user);
for (User user1 : userList) {
System.out.println(user1);
}
}
foreach语句:
collection表示参数
open表示开始
close表示结束
separator表示分隔符
item就和foreach中的item一样
<!--根据提供的多个条件查询 foreach语句-->
<select id="findByIds" parameterType="list" resultType="user">
select * from user
<where>
<foreach collection="list" open="id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
测试类:
@Test
public void testFindByIds() throws IOException {
//调用函数得到sqlSession
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List< Integer > list = new ArrayList<>();
list.add(1);
list.add(2);
list.add(3);
List< User > userList = mapper.findByIds(list);
for (User user1 : userList) {
System.out.println(user1);
}
sql语句抽取和引入
<!--sql语句抽取-->
<sql id="selectUser">
select * from user
</sql>
<!--通过include标签填写对应的id 进行引入-->
<select id="findAll" resultType="user">
<include refid="selectUser"></include>
</select>
知识小结
自定义转换器
开发步骤:
编写需要转换的数据类的配置:
package com.xzh.handler;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
/**
* @Author XuZhuHong
* @CreateTime 2021/5/28 13:11
*/
//定义的类型必须要继承BaseTypeHandler方法 并重写他的函数 泛型参数就是要转换的参数
public class DateTypeHandler extends BaseTypeHandler< Date > {
//将java转换为数据库需要的类型 这里就是将java数据格式化为sql需要的数据
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
long time = date.getTime();
preparedStatement.setLong(i,time);
}
//将数据库中的数据转换为java类型
//String参数是要转换的名称
//ResultSet查询的结果集合
@Override
public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
long aLong = resultSet.getLong(s);
Date date=new Date(aLong);
return date;
}
@Override
public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
long aLong = resultSet.getLong(i);
Date date=new Date(aLong);
return date;
}
@Override
public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
long aLong = callableStatement.getLong(i);
Date date=new Date(aLong);
return date;
}
}
然后在sqlMapConfig中添加 自定义类型转换器
<!--自定义类型转换器 里面的handler必须要是转换器的全类名-->
<typeHandlers>
<typeHandler handler="com.xzh.handler.DateTypeHandler"/>
</typeHandlers>
plugins标签(分页标签)
在pom.xml中导入坐标:
<!--分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
在sqlMapConfig.xml中添加这个插件
<!--配置分页助手插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
测试类:
@Test
public void testFindAllPage() throws IOException {
//调用函数得到sqlSession
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//配置分页参数 startPage(页码,每页显示条数)
PageHelper.startPage(1,3);
List< User > userList = userMapper.findAll();
for (User user : userList) {
System.out.println(user);
}
//获取分页相关的参数 泛型是 查询的数据类型 那个最后的参数是查询到的数据集合
PageInfo<User> pageInfo= new PageInfo<>(userList);
System.out.println("当前页"+pageInfo.getPageNum());
System.out.println("每页显示条数"+pageInfo.getPageSize());
System.out.println("总条数"+pageInfo.getTotal());
System.out.println("总页数"+pageInfo.getPages());
System.out.println("上一页"+pageInfo.getPrePage());
System.out.println("下一页"+pageInfo.getNextPage());
System.out.println("是否是首页"+pageInfo.getFirstPage());
System.out.println("是否是尾页"+pageInfo.getLastPage());
}
Mybatis多表查询
一对一
把查询到的数据封装到对应的属性中
column:对应数据表的字段名称
property:对应实体的属性名称
对应所查询出的数据:
对应的domain的属性:
<?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">
<!--这个文件里面主要描述的说是sql语句-->
<mapper namespace="com.xzh.dao.OrderMapper">
<!--数据封装集合--> <!--type是全类名 因为这里在外面写了别名 所以就是order-->
<resultMap id="orderMap" type="order">
<!--手动指定属性和映射实体的关系
column:数据表的字段名称
property:实体的属性名称-->
<id column="oid" property="id"/>
<result column="total" property="total"/>
<result column="ordertime" property="ordertime"/>
<result column="total" property="total"/>
<!--User配置方式一-->
<!-- <result column="uid" property="user.id"/>-->
<!-- <result column="password" property="user.password"/>-->
<!-- <result column="username" property="user.username"/>-->
<!-- <result column="birthday" property="user.birthday"/>-->
<!--User配置方式二-->
<association property="user" javaType="user">
<id column="uid" property="id"/>
<result column="password" property="password"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
</association>
</resultMap>
<!--查询语句-->
<select id="findAll" resultMap="orderMap">
SELECT *, orders.id oid
FROM USER,
orders
WHERE orders.`uid` = user.`id`
</select>
</mapper>
多对一:
在上面一个一对一的基础环境上配置:
sql查询到的数据:
对应user对象:
对应order对象:
<!--多对一-->
<resultMap id="userMap" type="user">
<id column="uid" property="id"/>
<result column="password" property="password"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<!--配置集合:
property 对应属性中集合的名称
ofType 对应属性中集合的类型-->
<collection property="orderList" ofType="order">
<!--数据封装-->
<id property="id" column="oid"/>
<result column="ordertime" property="ordertime"/>
<result column="total" property="total"/>
</collection>
</resultMap>
<select id="findAllOrder" resultMap="userMap">
SELECT *,orders.`id` oid FROM USER,orders WHERE user.`id`=orders.`uid`
</select>
多对多
查询到的数据:
对应user和userorderinfo的doman
<!--多对多-->
<resultMap id="infoMap" type="user">
<id column="userId" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<!--配置集合:
property 对应属性中集合的名称
ofType 对应属性中集合的类型-->
<collection property="userOrderInfo" ofType="UserOrderInfo">
<id column="ordersId" property="orderNumber"/>
</collection>
</resultMap>
<select id="findOrderInfo" resultMap="infoMap">
SELECT * FROM USER,userorders,orders WHERE USER.`id`=userorders.`userId` AND orders.`id`=userorders.`ordersId`
</select>
小结:
注解开发Mybits
常用注解:
简单查询
首先修改sqlMapConfig.xml文件的Mappers属性
<!--加载映射文件-->
<mappers>
<!--这是普通xml方式-->
<!-- <mapper resource="com/xzh/mapper/UserMapper.xml"/>-->
<!-- <mapper resource="com/xzh/mapper/OrderMapper.xml"/>-->
<!--注解方式-->
<!--指定接口在的包-->
<package name="com.xzh.dao"/>
</mappers>
接口类 :只需要在对应的方法上面加上sql语句即可:
@Insert("insert into user values (#{id}, #{username}, #{password},#{birthday})")
void saveBirthday(User user);
@Update("update user set username=#{username},password=#{password} where id = #{id}")
void update(User user);
@Delete(" delete from user where id = #{id}")
void delete(int id);
@Select(" select * from user where id = #{id}")
User findById(int id);
测试类:和普通方法一样
首先得到配置文件的Resrources
然后构建SqlSessionFactory
最后得到SqlSession
就可以进行增删改查了
@Test
public void testFindById() throws IOException {
//调用函数得到sqlSession
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User byId = userMapper.findById(14);
System.out.println(byId );
}
@Test
public void testSave() throws Exception{
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setBirthday(new Date());
user.setUsername("啊");
user.setPassword("12");
//这里需要转换
mapper.save(user);
sqlSession.close();
}
@Test
public void testDelete() throws IOException {
//调用函数得到sqlSession
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.delete(18);
}
@Test
public void testUpdate() throws IOException {
//调用函数得到sqlSession
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
userMapper.update(new User(6, "琪琪", "123",null));
}
复杂查询
首先我们要知道几个注解的作用
一对一查询:
总体来说配置和之前的差不多
都是配置映射 配置的方式和xml的一样 不过映射的地址要改成这个接口的包路径
然后测试
OrderMapper 接口
public interface OrderMapper {
@Select("SELECT *,orders.id oid FROM USER,orders WHERE orders.`uid` = user.`id`")
//数据封装的 Results注解 这个注解里面是一个Result的集合
@Results({
@Result(column = "oid", property = "id"),
@Result(column = "total", property = "total"),
@Result(column = "ordertime", property = "ordertime"),
@Result(column = "total", property = "total"),
//对user的封装
// 方式一:
// @Result(column = "uid", property = "user.id"),
// @Result(column = "password", property = "user.password"),
// @Result(column = "username", property = "user.username"),
// @Result(column = "birthday", property = "user.birthday"),
//方式二:
@Result(
property = "user",//设置封装的属性名
column = "uid",//设置用哪个字段的数据去查询user
javaType = User.class,//要封装的数据类型
//调用其他的方法进行查询并且对数据进行封装
one = @One(select = "com.xzh.dao.UserMapper.findById")
)
})
List< Order > findAll();
}
UserMapper接口的find方法:
@Select(" select * from user where id = #{id}")
User findById(int id);
测试类:
@Test
public void testFindAll() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List< Order > all = mapper.findAll();
for (Order order : all) {
System.out.println(order);
}
sqlSession.close();
}
多对一查询:
@Select("select * from user")
@Results({
@Result(column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
//封装查询结果到list的属性中
@Result(
column = "id",
property = "orderList",
javaType = List.class,//这里因为查询的结果是多个 所以要封装到List中
many = @Many(select = "com.xzh.dao.OrderMapper.findById")
)
})
List< User > findAllOrder();
OrderMapper.findById方法:
@Select("select * from orders where uid=#{uid}")
List<Order> findById(int uid);
多对多
@Select("select * from user")
@Results({
@Result(column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(
column = "id",
property = "userOrderInfo",
javaType = List.class,
many = @Many(select = "com.xzh.dao.InfoMapper.findByUID")
)
})
List< User > findOrderInfo();
com.xzh.dao.InfoMapper.findByUID方法:
@Select("SELECT * FROM userorders, orders WHERE orders.`id` = userorders.`ordersId` AND userorders.`userId`=#{id}")
@Results({
@Result(column = "ordersId",property = "orderNumber")
})
List< UserOrderInfo > findByUID(int id);