1、MyBatis简介
1.1 简介
MyBatis是目前主流的ORM框架
MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
1.2 什么是ORM
对象关系映射(Object Relational Mapping,简称ORM)是通过使用描述对象和数据库之间映射的元数据,将面向对象语言程序中的对象自动持久化到关系数据库中。本质上就是将数据从一种形式转换到另外一种形式。
1.3 MyBatis和Hibernate的区别
Hibernate
1) 是全自动框架,不用写SQL,全部自动生成
2) 不利于SQL的优化
3) 入门相对复杂
MyBatis
1) 半自动框架,需要写SQL,由框架完成映射
2) 方便SQL的优化
3) 入门相对容易
2、MyBatis的基本使用
1)导入依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
2)添加配置文件mybatis-config.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="development">
<environment id="development">
<!--事务管理-->
<transactionManager type="JDBC"/>
<!--数据源 POOLED代表连接池-->
<dataSource type="POOLED">
<!--JDBC的配置-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/bookms_db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!--配置映射文件的位置-->
<mappers>
<mapper resource="mappers/BookMapper.xml"></mapper>
</mappers>
</configuration>
3)定义mapper接口
只定义一个insert方法
/**
* 书籍表的映射接口
*/
public interface BookMapper {
void insert(Book book);
}
4) 映射文件
在resources的mappers目录下添加映射文件BookMapper.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="com.blb.mybatis_demo.dao.BookMapper">
<!--insert配置插入操作 id是接口中方法的名称 parameterType是参数类型-->
<insert id="insert" parameterType="com.blb.mybatis_demo.entity.Book">
insert into tb_book(book_name, price, type_id, author, publish_org, publish_time, state,book_image)
values(#{bookName},#{price},#{typeId},#{author},#{publishOrg},#{publishTime},#{state},#{bookImage});
</insert>
</mapper>
讲解配置:
-
mapper 用于配置Mapper接口,实现其中增删改查方法的配置
-
namespace 指定接口的类型的完整限定名
-
insert 配置插入相关的方法,类似的还有:update(更新)、delete(删除)、select(查询)
-
id 方法名称
-
parameterType 方法的参数类型
-
#{xx} 占位符,用于在SQL中插入参数值,xx是参数的名称,如果参数类型是Java对象,xx则是对象的属性名称
5) 单元测试
public class TestBookMapper {
@Test
public void testInsert(){
//创建SQLSessionFactoryBuilder对象,用于创建SQLSessionFactory
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//创建SqlSessionFactory
try {
SqlSessionFactory factory = builder.build(Resources.getResourceAsStream("mybatis-config.xml"));
//SqlSessionFactory用于创建SqlSession会话
try(SqlSession session = factory.openSession()){
//通过session获得Mapper对象
BookMapper mapper = session.getMapper(BookMapper.class);
//执行操作
mapper.insert(new Book(0L,"test-11",88,1,"xx","xx","2011-1-1",0,"xx.jpg"));
//提交修改
session.commit();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
3、MyBatis的查询
1) 给接口加方法
List<Book> findAll();
Book findById(int id);
2) 返回值的配置
需要给select标签配置返回值的类型resultType
<!--select配置查询方法,resultType配置返回类型-->
<select id="findAll" resultType="com.blb.mybatis_demo.entity.Book">
select * from tb_book
</select>
<select id="findById" resultType="com.blb.mybatis_demo.entity.Book">
select * from tb_book where id = #{id}
</select>
3) 测试
@Test
public void testSelectAll(){
//创建SQLSessionFactoryBuilder对象,用于创建SQLSessionFactory
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//创建SqlSessionFactory
try {
SqlSessionFactory factory = builder.build(Resources.getResourceAsStream("mybatis-config.xml"));
//SqlSessionFactory用于创建SqlSession会话
try(SqlSession session = factory.openSession()){
//通过session获得Mapper对象
BookMapper mapper = session.getMapper(BookMapper.class);
//执行操作
List<Book> books = mapper.findAll();
books.forEach(book -> System.out.println(book));
}
} catch (IOException e) {
e.printStackTrace();
}
}
问题:查询出的名称为多个单词的字段出现null值
原因:数据库的字段单词以下划线分隔,Java的属性以驼峰命名,导致部分名称不一致无法实现映射
tb_book表的字段:
id、book_name、price、type_id、author、publish_org、publish_time、state、book_image
Book类的属性:
id、bookName、price、typeId、author、publishOrg、publishTime、state、bookImage
两种解决方法:
1) 添加resultMap,实现数据库字段和属性的映射,把resultType改为resultMap
<!--实现Book到tb_book映射-->
<resultMap id="bookMap" type="com.blb.mybatis_demo.entity.Book">
<!--配置主键 property是java属性名 column是表字段名-->
<id property="id" column="id"></id>
<!--普通字段-->
<result property="bookName" column="book_name"></result>
<result property="price" column="price"></result>
<result property="typeId" column="type_id"></result>
<result property="author" column="author"></result>
<result property="publishOrg" column="publish_org"></result>
<result property="publishTime" column="publish_time"></result>
<result property="state" column="state"></result>
<result property="bookImage" column="book_image"></result>
</resultMap>
2) 在mybatis配置文件里,配置下划线转换为驼峰命名风格
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
对比resultMap和resultType:
-
返回值的属性名称与数据库字段一致时,使用resultType resultType用于定义返回值的类型,值就是Java类的完整限定名。
-
返回值的属性名称与数据库字段不一致时,使用resultMap resultMap用于数据库字段和属性的映射 resultMap的属性有:id(标识id)和type(Java类的完整限定名)
resultMap子节点有:id(主键)和result(普通字段)
id和result的属性有:
-
property 属性名
-
column 字段名
-
javaType 属性类型(可以省略,自动读取)
-
sqlType 字段类型(可以省略,自动读取)
-
12.分页查询
12.1.使用Limit分页
使用Mybatis实现分页,核心SQL
接口
//limit分页 List<User> getUserByLimit(Map<String,Integer> map);
mapper.xml
<!--limit分页--> <select id="getUserByLimit" parameterType="java.util.Map" resultType="com.zpc.mybatis.pojo.User"> select * from tb_user limit #{startIndex},#{pageSize}; </select>
测试
-
@Test public void getUserByLimit(){ Map<String,Integer> map=new HashMap<String,Integer>(); map.put("startIndex",0); map.put("pageSize",1); List<User> userByLimit = this.userMapper.getUserByLimit(map); for (User user : userByLimit) { System.out.println(user); } }
12.2.使用RowBounds分页
不再使用SQL实现分页
接口
//RowBounds分页5 List<User> getUserByRowBounds();
mapper.xml
<!-- RowBounds分页 --> <select id="getUserByRowBounds" resultType="com.zpc.mybatis.pojo.User"> select * from tb_user </select>
测试
-
@Test public void getUserByRowBoundsTest(){ RowBounds rowBounds = new RowBounds(0,1); List<User> users=sqlSession.selectList("com.zpc.mybatis.dao.UserMapper.getUserByRowBounds",null,rowBounds); for (User user : users) { System.out.println(user); } }
12.3.分页插件pagehelper
pom.xml
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.2</version> </dependency>
mybatis-config.xml
<plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin> </plugins>
测试
@Test public void getUserByPageHelper(){ UserMapper userMapper = sqlSession.getMapper(UserMapper.class); PageHelper.startPage(1,1); List<User> users = userMapper.queryUserAll(); users.forEach(user -> System.out.println(user)); }
13.使用注解
接口
package com.zpc.mybatis.dao; import com.zpc.mybatis.pojo.User; import org.apache.ibatis.annotations.*; /*** * 注解方式的CRUD */ public interface UserMapper2 { //根据id查询用户 @Select("select * from tb_user where id = #{id}") User selectUserById(@Param("id") String id); //添加一个用户 @Insert("INSERT INTO tb_user(id,user_name,password,name,age,sex,birthday,created,updated) VALUES(#{id},#{userName},#{password},#{name},#{age},#{sex},#{birthday},NOW(),NOW())") int addUser(User user); //修改一个用户 @Update("update tb_user set name=#{name},password=#{password} where id = #{id}") int updateUser(User user); //根据id删除用 @Delete("delete from tb_user where id = #{id}") int deleteUser(@Param("id") String id); }
mybatis-config.xml
<mappers> <mapper resource="mappers/UserMapper.xml"/> <mapper class="com.zpc.mybatis.dao.UserMapper2"/> </mappers>
测试
package com.zpc.mybatis.test; import com.zpc.mybatis.dao.UserMapper2; import com.zpc.mybatis.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.Before; import org.junit.Test; import java.io.InputStream; public class UserMapperTest2 { public SqlSession sqlSession; public UserMapper2 userMapper2; @Before public void setUp() throws Exception { // 指定配置文件 String resource = "mybatis-config.xml"; // 读取配置文件 InputStream inputStream = Resources.getResourceAsStream(resource); // 构建sqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); // 获取sqlSession sqlSession = sqlSessionFactory.openSession(true); this.userMapper2 = sqlSession.getMapper(UserMapper2.class); } @Test public void testSelectUserById() { User user = userMapper2.selectUserById("2"); System.out.println(user); } @Test public void testAddUser() { User user= new User(); user.setId("11111"); userMapper2.addUser(user); } @Test public void testUpdateUser() { User user = new User(); user.setId("11111"); user.setName("haha"); userMapper2.updateUser(user); } @Test public void testDeleteUser() { userMapper2.deleteUser("11111"); } }
14.高级查询14.1.表关系说明
-
数据库脚本:
CREATE TABLE tb_order (
id int(11) NOT NULL AUTO_INCREMENT,
user_id char(32) DEFAULT NULL,
order_number varchar(255) DEFAULT NULL,
created datetime DEFAULT NULL,
updated datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE tb_item (
id int(11) NOT NULL,
itemName varchar(255) DEFAULT NULL,
itemPrice decimal(10,2) DEFAULT NULL,
itemDetail varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb_orderdetail (
id int(11) NOT NULL AUTO_INCREMENT,
order_id int(11) DEFAULT NULL,
total_price decimal(10,0) DEFAULT NULL,
item_id int(11) DEFAULT NULL,
status int(10) unsigned zerofill DEFAULT NULL COMMENT '0成功非0失败',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
需求说明:
14.2.一对一查询
在Order对象中添加User属性:
public class Order {
private Integer id;
private String userId;
private String orderNumber;
private Date created;
private Date updated;
private User user;
//get/set/toString
}
接口:
public interface OrderMapper {
/**
* 根据订单号查询订单用户的信息
* @param number
* @return
*/
Order queryOrderWithUserByOrderNumber(@Param("number") String number);
}
OrderMapper.xml
<mapper namespace="com.zpc.mybatis.dao.OrderMapper">
<resultMap id="OrderUserResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
<id column="id" property="id"/>
<!--association:完成子对象的映射-->
<!--property:子对象在父对象中的属性名-->
<!--javaType:子对象的java类型-->
<!--autoMapping:完成子对象的自动映射,若开启驼峰,则按驼峰匹配-->
<association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true">
<id column="user_id" property="id"/>
</association>
</resultMap>
<select id="queryOrderWithUserByOrderNumber" resultMap="OrderUserResultMap">
select * from tb_order o left join tb_user u on o.user_id=u.id where o.order_number = #{number}
</select>
</mapper>
mybatis-config.xml
<mappers>
<mapper resource="mappers/UserMapper.xml"/>
<mapper class="com.zpc.mybatis.dao.UserMapper2"/>
<mapper resource="mappers/OrderMapper.xml"/>
</mappers>
测试:
public class OrderMapperTest {
public OrderMapper orderMapper;
@Before
public void setUp() throws Exception {
// 指定配置文件
String resource = "mybatis-config.xml";
// 读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
// 构建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
// 1. 映射文件的命名空间(namespace)必须是mapper接口的全路径
// 2. 映射文件的statement的id必须和mapper接口的方法名保持一致
// 3. Statement的resultType必须和mapper接口方法的返回类型一致
// 4. statement的parameterType必须和mapper接口方法的参数类型一致(不一定)
this.orderMapper = sqlSession.getMapper(OrderMapper.class);
}
@Test
public void queryOrderWithUserByOrderNumber() throws Exception {
Order order = orderMapper.queryOrderWithUserByOrderNumber("20201014");
System.out.println(order);
}
}
14.3.一对多查询
一对多查询:查询订单,查询出下单人信息并且查询出订单详情。
OrderDetail
public class OrderDetail {
private Integer id;
private Integer orderId;
private Double totalPrice;
private Integer status;
}
Order类:
public class Order {
private Integer id;
private Long userId;
private String orderNumber;
private Date created;
private Date updated;
private User user;
private List<OrderDetail> detailList;
}
接口:
/**
* 根据订单号查询订单用户的信息及订单详情
* @param number
* @return
*/
Order queryOrderWithUserAndDetailByOrderNumber(@Param("number") String number);
Mapper映射:
<resultMap id="OrderUserDetailResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
<id column="order_id" property="id"/>
<!--collection:定义子对象集合映射-->
<!--association:完成子对象的映射-->
<!--property:子对象在父对象中的属性名-->
<!--javaType:子对象的java类型-->
<!--autoMapping:完成子对象的自动映射,若开启驼峰,则按驼峰匹配-->
<association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true">
<id column="user_id" property="id"/>
</association>
<collection property="detailList" javaType="List" ofType="com.zpc.mybatis.pojo.OrderDetail" autoMapping="true">
<id column="detail_id" property="id"/>
</collection>
</resultMap>
<select id="queryOrderWithUserAndDetailByOrderNumber" resultMap="OrderUserDetailResultMap">
select *,od.id as detail_id,u.id as user_id,o.id as order_id from tb_order o
left join tb_user u on o.user_id=u.id
left join tb_orderdetail od on o.id=od.order_id
where o.order_number = #{number}
</select>
测试:
@Test
public void queryOrderWithUserAndDetailByOrderNumber() throws Exception {
Order order = orderMapper.queryOrderWithUserAndDetailByOrderNumber("20201014");
System.out.println(order);
}
14.4.多对多查询
多对多查询:查询订单,查询出下单人信息并且查询出订单详情中的商品数据。
Item
private String itemName;
OrderDetail
public class OrderDetail {
private Integer id;
private Integer orderId;
private Double totalPrice;
private Integer status;
private Item item;
}
接口:
/**
* 根据订单号查询订单用户的信息及订单详情及订单详情对应的商品信息
* @param number
* @return
*/
Order queryOrderWithUserAndDetailItemByOrderNumber(@Param("number") String number);
Mapper配置:
<resultMap id="OrderUserDetailItemResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
<id column="id" property="id"/>
<association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true">
<id column="user_id" property="id"/>
</association>
<collection property="detailList" javaType="List" ofType="com.zpc.mybatis.pojo.OrderDetail" autoMapping="true">
<id column="detail_id" property="id"/>
<association property="item" javaType="com.zpc.mybatis.pojo.Item" autoMapping="true">
<id column="item_id" property="id"/>
</association>
</collection>
</resultMap>
<select id="queryOrderWithUserAndDetailItemByOrderNumber" resultMap="OrderUserDetailItemResultMap">
select * from tb_order o
left join tb_user u on o.user_id=u.id
left join tb_orderdetail od on o.id=od.order_id
left join tb_item i on od.item_id=i.id
where o.order_number = #{number}
</select>
测试:
@Test
public void queryOrderWithUserAndDetailItemByOrderNumber() throws Exception {
Order order = orderMapper.queryOrderWithUserAndDetailItemByOrderNumber("20201014");
System.out.println(order);
}
14.5.resultMap的继承
14.6.高级查询的整理
resutlType无法帮助我们自动的去完成映射,所以只有使用resultMap手动的进行映射 type 结果集对应的数据类型 id 唯一标识,被引用的时候,进行指定
<resultMap type="Order" id="orderUserLazyResultMap">
<!—定义pojo中的单个对象的 property 定义对象的属性名, javaType 属性的类型,
<association property="user" javaType="User" autoMapping="true">
<id />
</association>
<!—如果属性是集合使用collection ,javaType 集合的类型,ofType 表示集中的存储的元素类型
<collection property="details" javaType="List" ofType="OrderDetail" autoMapping="true">
<id />
</resultMap>
15.延迟加载
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
编写接口:
/**
* 延迟加载
* @param number
* @return
*/
Order queryOrderAndUserByOrderNumberLazy(@Param("number") String number);
Mapper配置:
<resultMap id="LazyOrderUserResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
<id column="id" property="id"></id>
<!--select:子查询的id,column:子查询的条件-->
<association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true" select="queryUserById" column="user_id"></association>
</resultMap>
<select id="queryOrderAndUserByOrderNumberLazy" resultMap="LazyOrderUserResultMap">
select * from tb_order where order_number=#{number}
</select>
<select id="queryUserById" resultType="com.zpc.mybatis.pojo.User">
select * from tb_user where id=#{id}
</select>
测试:
@Test
public void queryOrderAndUserByOrderNumberLazy() throws Exception {
Order order = orderMapper.queryOrderAndUserByOrderNumberLazy("20201014");
System.out.println(order);
}
结果:
开启延迟加载:
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
修改测试用例:
@Test
public void queryOrderAndUserByOrderNumberLazy() throws Exception {
Order order = orderMapper.queryOrderAndUserByOrderNumberLazy("20201014");
System.out.println(order.getOrderNumber());
System.out.println("-----------------------------------");
System.out.println(order.getUser());
}
执行,报错:
添加cglib:
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>3.1</version>
</dependency>
执行:
16.xml中的字符实体
1.使用xml中的字符实体
因为业务,需要在mybatis中,使用到大于号,小于号,所以就在SQL中直接使用了。 SELECT * FROM test WHERE 1 = 1 AND start_date <= CURRENT_DATE AND end_date >= CURRENT_DATE
可是,在执行时,总报错误:
Error creating document instance. Cause: org.xml.sax.SAXParseException; lineNumber: 74; columnNumber: 17;
元素内容必须由格式正确的字符数据或标记组成。 把AND start_date >= CURRENT_DATE AND end_date <= CURRENT_DATE
去掉,就没有问题,所以确定是因为大于号,小于号引起的问题。
于是就想到了特殊符号,于是用了转义字符把>和<替换掉,然后就没有问题了。 SELECT * FROM test WHERE 1 = 1 AND start_date <= CURRENT_DATE AND end_date >= CURRENT_DATE
案例:
1.<if test="startDateTime!=null"> and mm.ttime > to_date(#{startDateTime},'yyyy-mm-dd hh24:mi:ss')</if> 2.<if test="endDateTime!=null"> and mm.ttime <= to_date(#{endDateTime},'yyyy-mm-dd hh24:mi:ss')</if>
2.使用<![CDATA[ < ]]>
案例1:
1.<![CDATA[ 2. and mm.ttime > to_date(#{startDateTime},'yyyy-mm-dd hh24:mi:ss') 3. and mm.ttime <= to_date(#{endDateTime},'yyyy-mm-dd hh24:mi:ss') 4.]]>
案例2:
mapper文件示例代码 :
and (t1.status <![CDATA[ >= ]]> 1 and t1.status <![CDATA[ <= ]]> 2) 上述代码其实对应的sql: and (t1.status > =1 andt1.status <= 2)
注意:
使用标记的sql语句中的 等标签不会被解析。