Mybatis（第一篇）
Mybatis（第一篇）
目录
- Mybatis（第一篇）
- Mybatis进行CRUD
- 一、①指令ID方式——Mybatis基础案例项目搭建
- 二、代码的优化封装
- 2.1 数据库链接信息的封装
- 2.2 Mybatis工具类对获取sqlSession连接进行了封装
- 2.3 typeAliases给类配置别名
- 三、②接口代理方式
- 四、日志配置
- 4.1 引入日志相关jar包依赖
- 4.2 创建log4j.properties的日志配置文件
- 4.3 修改mybatis-config.xml配置文件
- 五、结果映射
- 六、#和$
- 6.1 #和$的引入
- 使用@Param注解
- 6.2 #和$的对比
- 七、SQL片段
- 八、模糊查询
- 九、多个参数问题
- 方案一：封装成对象
- 方案二：封装成map
- 方案三：使用@Param注解
- 十、分页查询（PageHelper)
- 十一、新增自增长
- 十二、动态SQL
- 1.if标签
- 2.where标签
- 3.Foreach标签
- 3.1 批量查询
- 3.1.1 数组
- ① 普通数组
- ② 数组+@Param注解
- 3.1.2 集合
- ①普通集合
- ② 集合+@Param注解
- 3.2 批量新增
- 4 choose标签
- 5.set标签
- 十三、缓存
- 一级缓存
- 二级缓存
- 十四、Mybatis注解
Mybatis进行CRUD
一、①指令ID方式——Mybatis基础案例项目搭建
user.sql
/*
Navicat Premium Data Transfer
Source Server : mysql80
Source Server Type : MySQL
Source Server Version : 80029
Source Host : localhost:3306
Source Schema : mybatis
Target Server Type : MySQL
Target Server Version : 80029
File Encoding : 65001
Date: 02/10/2022 23:26:46
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`age` int NULL DEFAULT NULL,
`sex` bit(1) NULL DEFAULT NULL,
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (2, 'tony', 12, b'1', '成都', '2022-10-02');
INSERT INTO `user` VALUES (3, 'tony', 12, b'1', '成都', '2022-10-02');
INSERT INTO `user` VALUES (4, 'tony', 12, b'1', '成都', '2022-10-02');
SET FOREIGN_KEY_CHECKS = 1;
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.bjpowernode</groupId>
<artifactId>hellomybatis</artifactId>
<version>1.0.0</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!--junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
com.bjpowernode.domain包
User.java类
package com.bjpowernode.domain;
import java.util.Date;
public class User {
private Long id;
private String name;
private Integer age;
private int sex;
private String address;
private Date birth;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", sex=" + sex +
", address='" + address + '\'' +
", birth=" + birth +
'}';
}
}
com.bjpowernode.mapper包
UserMapper.java接口
package com.bjpowernode.mapper;
public interface UserMapper {
}
resources资源文件夹下
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>
<!--配置开发环境
在真实开发中，我们的环境的问题 ， 一般 分三个环境
开发环境
测试环境
生产环境
三个环境中，链接的数据库是不同，那么 我么如果手动切换环境，是很容易输错
所以我么可以提前配置好多个 环境，然后通过 default 关键字来进行切换
default 属性 的 值 必须 要和子标签中的 id 属性的值 保持一直
-->
<environments default="dev">
<environment id="dev">
<!--事务管理器，目前 采用的是 myBatis提供的 -->
<transactionManager type="JDBC"/>
<!--数据源 （链接池） ，目前 采用的是 MyBatis 提供的 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--挂载 sql的映射文件-->
<mapper resource="com/bjpowernode/mapper/UserMapper.xml"/>
</mappers>
</configuration>
resources文件夹下
com,bjpowernode.mapper文件夹下
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">
<!--namespace 命名空间 作用： 起到整个项目的唯一标示
要求： 在mapper包中，定义一个 XxxMapper 的 接口
然后使用该接口的全限定名的值作为namespace 的值-->
<mapper namespace="com.bjpowernode.mapper.UserMapper">
<!-- insert 是 插入 标签 ， 表示 插入数据，编写插入的sql ，
id 属性 ，作用 ： 在当前文档中 ， 唯一的一个标示符
#{变量} ，这个整体 是 占位符 相当于 ？
里面的变量 必须使用 对应的类中的 属性的名称
-->
<insert id="insertUser" >
insert into user (name,age,sex,address,birth)
values (#{name},#{age},#{sex},#{address},#{birth})
</insert>
<update id="updateUser">
update user set name = #{name},age=#{age},
sex = #{sex},address=#{address},birth = #{birth}
where id = #{id}
</update>
<!--
resultType 属性 表示 查询结果中存储的对象的真实类型
-->
<select id="selectUsers" resultType="com.bjpowernode.domain.User">
select * from user
</select>
<delete id="deleteById">
delete from user where id = #{id}
</delete>
</mapper>
test包下
java包下
com,bjpowernode.text包下
CRUDTest.java测试类
package com.bjpowernode.test;
import com.bjpowernode.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 java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
public class CRUDTest {
@Test
public void testInsert() throws IOException {
//1.加载MyBatis 主配置文件
// Thread.currentThread().getContextClassLoader()
// .getResourceAsStream("mybatis-config.xml");
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
//2. 构建 SqlSessionFactory 工厂对象 SqlSessionFactory 想像 成 连接池 对象
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
//3. 通过工厂对象 获取sqlSession 会话对象 想象 成 链接对象
SqlSession sqlSession = build.openSession();
//4. 调用MyBatis 框架 提供该 接口方法
/*
* SqlSession扩展方法(过时不建议使用)
* 说明: 可以通过namespace名称.ID的方式直接调用sql
*/
/*
* insert 方法 有两个参数
* 第一个参数（statement）：namespace的值 + "." + sql的id的值 (找寻sql的 唯一凭证)
* 第二个参数 (替换sql语句中的占位符的参数): 如果占位符有很多那么封装成一个对象或者容器对象
*
* */
User user1 = new User();
user1.setName("tony");
user1.setAge(12);
user1.setSex(1);
user1.setBirth(new Date());
user1.setAddress("成都");
sqlSession.insert("com.bjpowernode.mapper.UserMapper.insertUser",user1);
//5.提交事务
sqlSession.commit();
//6.释放资源
sqlSession.close();
}
@Test
public void testUpdate() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession();
User user1 = new User();
user1.setId(1L);
user1.setName("lili");
user1.setAge(18);
user1.setSex(0);
user1.setBirth(new Date());
user1.setAddress("成都");
sqlSession.update("com.bjpowernode.mapper.UserMapper.updateUser",user1);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testSelect() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession();
List<User> users = sqlSession.selectList("com.bjpowernode.mapper.UserMapper.selectUsers");
for (User user : users) {
System.out.println("user = " + user);
}
sqlSession.commit();
sqlSession.close();
}
@Test
public void testDelete() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession();
sqlSession.delete("com.bjpowernode.mapper.UserMapper.deleteById",1L);
sqlSession.commit();
sqlSession.close();
}
}
二、代码的优化封装
2.1 数据库链接信息的封装
在resources文件夹下新建db.properties配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test?useSSL=false
username=root
password=kong1234
在mybatis-config.xml配置文件中，<configuration></configuration>中关联数据库的链接配置信息
<!--关联数据库的配置信息-->
<properties resource="db.properties"/>
在environment中的dataSource里面的property通过“$”符号引用db.properties中的变量
<!--配置环境-->
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driverClassName}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
2.2 Mybatis工具类对获取sqlSession连接进行了封装
com.bjpowernode.util下的
Mybatis.util类
package com.bjpowernode.util;
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 java.io.IOException;
import java.io.InputStream;
public class MybatisUtil {
private MybatisUtil(){
}
private static SqlSessionFactory factory = null;
static {
try {
//把配置文件转成IO流
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
//通过构建器 加载流
factory = new SqlSessionFactoryBuilder().build(resourceAsStream);
} catch (IOException exception) {
exception.printStackTrace();
}
}
public static SqlSession openSession(){
return factory.openSession();
}
}
test类
java
com.bjpowenode.text
CRUDTest2.java
package com.bjpowernode.test;
import com.bjpowernode.domain.User;
import com.bjpowernode.util.MybatisUtil;
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 java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
public class CRUDTest2 {
@Test
public void insertUser() throws IOException {
SqlSession sqlSession = MybatisUtil.openSession();
User user = new User();
user.setName("tony");
user.setAge(12);
user.setSex(1);
user.setBirth(new Date());
user.setAddress("成都");
//4. 调用MyBatis 框架 提供该 接口方法
/*
* SqlSession扩展方法(过时不建议使用)
* 说明: 可以通过namespace名称.ID的方式直接调用sql
*/
/*
* insert 方法 有两个参数
* 第一个参数（statement）：namespace的值 + "." + sql的id的值 (找寻sql的 唯一凭证)
* 第二个参数 (替换sql语句中的占位符的参数): 如果占位符有很多那么封装成一个对象或者容器对象
*
* */
sqlSession.insert("com.bjpowernode.mapper.UserMapper.insertUser",user);
//5.提交事务
sqlSession.commit();
//6.释放资源
sqlSession.close();
}
@Test
public void updateUser() throws IOException {
SqlSession sqlSession = MybatisUtil.openSession();
User user = new User();
user.setId(1L);
user.setName("lili");
user.setAge(18);
user.setSex(0);
user.setBirth(new Date());
user.setAddress("成都");
sqlSession.update("com.bjpowernode.mapper.UserMapper.updateUser",user);
sqlSession.commit();
sqlSession.close();
}
@Test
public void selectUsers() throws IOException {
SqlSession sqlSession = MybatisUtil.openSession();
List<User> userList = sqlSession.selectList("com.bjpowernode.mapper.UserMapper.selectUsers");
for (User user : userList) {
System.out.println("user = " + user);
}
sqlSession.commit();
sqlSession.close();
}
@Test
public void deleteUser() throws IOException {
SqlSession sqlSession = MybatisUtil.openSession();
sqlSession.delete("com.bjpowernode.mapper.UserMapper.deleteUserById",1L);
sqlSession.commit();
sqlSession.close();
}
}
2.3 typeAliases给类配置别名
在mybatis-config.xml配置文件中，给类配置别名
<!-- 2.1 给类配置别名 -->
<typeAliases>
<!--包扫描：Mybatis框架，它会扫描domain这个包下面的所有的类，然后给类取别名
默认别名为 类 的名称，不区分大小写-->
<package name="com.bjpowernode.domain"/>
</typeAliases>
那么在UserMapper.xml中原来对User的查询语句的写法
<select id="selectUsers" resultType="com.bjpowernode.domain.User">
select * from user
</select>
就可以优化简写成如下写法
<!-- 在mybatis-config.xml文件中使用了typeAliases -->
<select id="selectUsers" resultType="user">
select * from user
</select>
三、②接口代理方式
student.sql
/*
Navicat Premium Data Transfer
Source Server : mysql57
Source Server Type : MySQL
Source Server Version : 50738
Source Host : localhost:13306
Source Schema : test
Target Server Type : MySQL
Target Server Version : 50738
File Encoding : 65001
Date: 02/10/2022 20:08:52
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`age` int(10) UNSIGNED NULL DEFAULT NULL,
`gender` bit(1) NULL DEFAULT NULL,
`create_time` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `index_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三丰111', 23, b'1', '2022-03-01');
INSERT INTO `student` VALUES (2, '李四民', 11, b'0', '2022-03-30');
INSERT INTO `student` VALUES (3, '结衣', 12, b'1', '2022-08-08');
INSERT INTO `student` VALUES (4, 'Lisa', 26, b'1', '2022-03-30');
INSERT INTO `student` VALUES (5, 'tony', 32, b'1', '2022-08-07');
INSERT INTO `student` VALUES (6, '露露', 2, b'1', '2022-08-07');
INSERT INTO `student` VALUES (7, '丽丽', 13, b'1', '2022-08-12');
INSERT INTO `student` VALUES (8, '花花', 14, b'1', '2022-08-12');
INSERT INTO `student` VALUES (9, '猫咪', 15, b'1', '2022-08-12');
INSERT INTO `student` VALUES (10, '如花', 17, b'1', '2022-08-12');
INSERT INTO `student` VALUES (12, 'hhh', 23, b'0', '2022-09-18');
SET FOREIGN_KEY_CHECKS = 1;
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.bjpowernode</groupId>
<artifactId>proxyProject</artifactId>
<version>1.0.0</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<!--junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
resources资源文件下
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>
<!-- 关联数据库的配置信息 -->
<properties resource="db.properties"/>
<!--给所有的 javaBean 起别名-->
<typeAliases>
<package name="com.bjpowernode.domain"/>
</typeAliases>
<!-- 配置环境 -->
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driverClassName}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- 挂载sql的映射文件 -->
<mappers>
<mapper resource="com\bjpowernode\mapper\StudentMapper.xml"/>
</mappers>
</configuration>
db.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///mybatis?useSSL=false
username=root
password=root
com.powernode
domain包下
package com.bjpowernode.domain;
import lombok.Data;
import java.util.Date;
@Data
public class Student {
private Long id;
private String name;
private Integer age;
private int gender;
private Date create_time;
}
util包
MybatisUtil.java
package com.bjpowernode.util;
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 java.io.IOException;
import java.io.InputStream;
public class MyBatisUtil {
private MyBatisUtil(){}
private static SqlSessionFactory factory = null;
static {
try {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(resourceAsStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession openSession() {
//设置为true则自动提交了事务
return factory.openSession(true);
}
}
mapper包
StudentMapper.java
package com.bjpowernode.mapper;
import com.bjpowernode.domain.Student;
import java.util.List;
public interface StudentMapper {
void insertStudent(Student student);
void updateStudent(Student student);
void deleteStudentById(long id);
Student selectStudentById(long id);
List<Student> selectStudentList();
}
StudentMapper.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.bjpowernode.mapper.StudentMapper">
<!--
parameterType 表示传入的参数的类型，写类的全限定类名或别名
由于mybatis框架的强大 它能识别传入的参数类型，无需告知给Mybatis
-->
<insert id="insertStudent" parameterType="student">
insert into student(name,age,gender,create_time)
values (#{name},#{age},#{gender},#{create_time})
</insert>
<update id="updateStudent">
update student
set
name = #{name},
age=#{age},
gender=#{gender},
create_time=#{create_time}
where id = #{id}
</update>
<delete id="deleteStudentById">
delete from student where id = #{id}
</delete>
<!-- <select id="selectStudentById" resultType="com.bjpowernode.domain.Student">-->
<select id="selectStudentById" resultType="student">
select * from student where id = #{id}
</select>
<!-- <select id="selectStudentList" resultType="com.bjpowernode.domain.Student">-->
<select id="selectStudentList" resultType="student">
select * from student
</select>
</mapper>
测试类
package com.bjpowernode.test;
import com.bjpowernode.domain.Student;
import com.bjpowernode.mapper.StudentMapper;
import com.bjpowernode.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
import java.util.Date;
public class CRUDTest {
@Test
public void insertStudent(){
// 获取SqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
Student student = new Student();
student.setAge(12);
student.setName("cat");
student.setCreate_time(new Date());
student.setGender(1);
// 调用方法 使用代理对象调用接口中的方法
studentMapper.insertStudent(student);
//在MybatisUtil中openSession(true)设置为true，则会自动提交事务
// //提交事务
// sqlSession.commit();
//释放资源
sqlSession.close();
}
@Test
public void updateStudent(){
// 获取sqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
Student student = new Student();
student.setId(12L);
student.setAge(12);
student.setName("mybatis");
student.setCreate_time(new Date());
student.setGender(1);
// 调用方法 使用代理对象调用接口中的方法
studentMapper.updateStudent(student);
//提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
@Test
public void deleteStudentById(){
// 获取sqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
// 调用方法 使用代理对象调用接口中的方法
studentMapper.deleteStudentById(16L);
//提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
@Test
public void selectStudentById() {
// 获取sqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
// 调用方法 使用代理对象调用接口中的方法
Student student = studentMapper.selectStudentById(3L);
System.out.println("student = " + student);
// 提交事务
sqlSession.commit();
// 释放资源
sqlSession.close();
}
@Test
public void selectStudentList() {
// 获取sqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
// 调用方法 使用代理对象调用接口中的方法
List<Student> studentList = studentMapper.selectStudentList();
for (Student student : studentList) {
System.out.println("student = " + student);
}
// 提交事务
sqlSession.commit();
// 释放资源
sqlSession.close();
}
}
四、日志配置
4.1 引入日志相关jar包依赖
<!-- 日志相关依赖 -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.13.3</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
4.2 创建log4j.properties的日志配置文件
注意：圈出来的部分要配置成你自己的mapper包
log4j.rootLogger=ERROR, stdout
log4j.logger.com.bjpowernode.mapper=TRACE
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
4.3 修改mybatis-config.xml配置文件
在mybatis-config.xml设置日志
注意：设置日志部分代码只能放在configuration里面的第2个
<!-- 设置日志 -->
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
此时再去测试类运行，就可以看到sql语句了
五、结果映射
domain包下的Student
package com.bjpowernode.domain;
import lombok.Data;
import java.util.Date;
@Data
public class Student {
private Long id;
private String name;
private Integer age;
private int gender;
// private Date create_time;
private Date createTime;
}
StudentMapper.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.bjpowernode.mapper.StudentMapper">
<!--
parameterType 表示传入的参数的类型，写类的全限定类名或别名
由于mybatis框架的强大 它能识别传入的参数类型，无需告知给Mybatis
-->
<insert id="insertStudent" parameterType="student">
insert into student(name,age,gender,create_time)
values
(
#{name},
#{age},
#{gender},
#{createTime}
)
</insert>
<update id="updateStudent">
update student
set
name = #{name},
age=#{age},
gender=#{gender},
create_time=#{createTime}
where id = #{id}
</update>
<delete id="deleteStudentById">
delete from student where id = #{id}
</delete>
<resultMap id="baseMap" type="com.bjpowernode.domain.Student">
<!--
id标签是应用在主键上性能比较高
property是类中的属性名称
colunn是表中的字段名称
result标签是应用在非主键上
-->
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="age" column="age"/>
<result property="createTime" column="create_time"/>
</resultMap>
<select id="selectStudentById" resultMap="baseMap">
select * from student where id = #{id}
</select>
<select id="selectStudentList" resultMap="baseMap">
select * from student
</select>
</mapper>
CRUD.Test
package com.bjpowernode.test;
import com.bjpowernode.domain.Student;
import com.bjpowernode.mapper.StudentMapper;
import com.bjpowernode.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
import java.util.Date;
public class CRUDTest {
@Test
public void insertStudent(){
// 获取SqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
Student student = new Student();
student.setAge(12);
student.setName("cat");
// student.setCreate_time(new Date());
student.setCreateTime(new Date());
student.setGender(1);
// 调用方法 使用代理对象调用接口中的方法
studentMapper.insertStudent(student);
//在MybatisUtil中openSession(true)设置为true，则会自动提交事务
// //提交事务
// sqlSession.commit();
//释放资源
sqlSession.close();
}
@Test
public void updateStudent(){
// 获取sqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
Student student = new Student();
student.setId(12L);
student.setAge(12);
student.setName("mybatis");
// student.setCreate_time(new Date());
student.setCreateTime(new Date());
student.setGender(1);
// 调用方法 使用代理对象调用接口中的方法
studentMapper.updateStudent(student);
//提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
@Test
public void deleteStudentById(){
// 获取sqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
// 调用方法 使用代理对象调用接口中的方法
studentMapper.deleteStudentById(16L);
//提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
@Test
public void selectStudentById() {
// 获取sqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
// 调用方法 使用代理对象调用接口中的方法
Student student = studentMapper.selectStudentById(3L);
System.out.println("student = " + student);
// 提交事务
sqlSession.commit();
// 释放资源
sqlSession.close();
}
@Test
public void selectStudentList() {
// 获取sqlSession对象
SqlSession sqlSession = MyBatisUtil.openSession();
// 获取 mapper 接口的代理对象
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.getClass());
// 调用方法 使用代理对象调用接口中的方法
List<Student> studentList = studentMapper.selectStudentList();
for (Student student : studentList) {
System.out.println("student = " + student);
}
// 提交事务
sqlSession.commit();
// 释放资源
sqlSession.close();
}
}
7.1.2注意
映射关系中，column查询结果的列别名，不是原始列名
<resultMap id="baseMap" type="com.bjpowernode.domain.Student">
<!--
id标签是应用在主键上性能比较高
property是类中的属性名称
colunn是表中的字段名称
result标签是应用在非主键上
-->
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="age" column="age"/>
<!-- <result property="createTime" column="create_time"/> -->
<result property="createTime" column="time"/>
</resultMap>
<select id="selectStudentList" resultMap="baseMap">
select id,name,age,gender,create_time time from student
</select>
在查询指令中，resultMap属性，指向定义的映射关系的ID
六、#和$
6.1 #和$的引入
将#号改成$符号
<select id="selectStudentById" resultMap="baseMap">
-- select * from student where id = #{id}
select * from student where id = ${id}
</select>
再运行test类会报错：没有getter方法
解决办法
使用@Param注解
在Student.Mapper中添加@Param注解
//使用${}来查询 @Param("id") 表示提供了getter
Student selectStudentById$(@Param("id") Long id);
6.2 #和$的对比
在mybatis中，会将开发者定义的sql进行解析，解析分为了2类sql：
1.静态sql ，在解析时，直接将参数拼接到sql中，这种就是静态sql
2.动态sql，在解析时，会使用?这个占位符，替代参数
这两种解析方式，mybatis是根据${}和#{}进行区分的
${}的sql是静态sql
#{}的sql是动态sql
不论是静态sql，还是动态sql都能获取传递参数，但是${}是使用的字符拼接，#{}使用PreparedStatement进行参数的预处理。
在一定程度上说，${}能实现的功能，#{}都能实现，并且由于#{}PreparedStatement进行SQL的预处理，一定程度上可以防止SQL注入攻击。所以在开发中，能使用#{}尽量使用#{}。
PreparedStatement预处理的本质是将参数进行转换为字符串，当做参数字符串处理。所以，如果参数信息是一个特殊的关键字，例如： 数据库名，表名，函数名，内置关键字，使用预处理，则关键字转为了字符串，无效了，此时必须使用字符串拼接也即是statement，也即意味着必须只能使用${}.
#{变量} 占位符 一般情况下的都是使用这个
${变量} 拼接符 如果sql语句中 有分组，排序，则分组的字段和 排序的条件 必须使用$(变量)
StudentMapper.java
package com.bjpowernode.mapper;
import com.bjpowernode.domain.Student;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface StudentMapper {
//使用${}来查询 @Param("id") 表示提供了getter
Student selectStudentById$(@Param("id") Long id);
//使用#{}来查询
Student selectStudentById(Long id);
List<Student> selectStudentOrderByAge(@Param("age") String age);
}
StudentMapper.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.bjpowernode.mapper.StudentMapper">
<resultMap id="baseMap" type="com.bjpowernode.domain.Student">
<!--
id标签是应用在主键上性能比较高
property是类中的属性名称
colunn是表中的字段名称
result标签是应用在非主键上
-->
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="age" column="age"/>
<result property="createTime" column="create_time"/>
</resultMap>
<select id="selectStudentById" resultMap="baseMap">
select * from student where id = #{id}
</select>
<select id="selectStudentById$" resultMap="baseMap">
select * from student where id = ${id}
</select>
<select id="selectStudentOrderByAge" resultMap="baseMap">
select * from student order by ${age}
</select>
</mapper>
test测试类
package com.bjpowernode.test;
import com.bjpowernode.domain.Student;
import com.bjpowernode.mapper.StudentMapper;
import com.bjpowernode.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
import java.util.List;
public class VS$Test {
//#{}
/*
*select * from student where id = ? 先解析把#{}转成了"?"，然后再把真实的值替换掉"?"
* 底层使用的是PreparedStatement 防止注入
*
*/
@Test
public void selectStudentById(){
SqlSession sqlSession = MyBatisUtil.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student student = studentMapper.selectStudentById(3L);
System.out.println("student = " + student);
}
//${}
/*
*${变量}：这个变量必须提供getter方法 在不同的版本中 要求不同，有的要求提供getter，有的不要求提供
*
* select * from student where id = 3 直接把参数拼接到了sql中，直接替换了${}
* 底层使用的是statement 拼接的方式
* 很多时候 都是使用 #{} 来做占位符
* ${} 是拼接符，使用的场景有：排序、分组、动态的拼接字段和表名
*/
@Test
public void selectStudentById$(){
SqlSession sqlSession = MyBatisUtil.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student student = studentMapper.selectStudentById$(3L);
System.out.println("student = " + student);
}
@Test
public void selectStudentOrderByAge(){
SqlSession sqlSession = MyBatisUtil.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = studentMapper.selectStudentOrderByAge("age");
for (Student student : studentList) {
System.out.println("student = " + student);
}
sqlSession.close();
}
}
七、SQL片段
使用<sql id=“baseColumn”><sql>标签和<include refid=“baseColumn”/>实现
StudentMapper.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.bjpowernode.mapper.StudentMapper">
<resultMap id="baseMap" type="com.bjpowernode.domain.Student">
<!--
id标签是应用在主键上性能比较高
property是类中的属性名称
colunn是表中的字段名称
result标签是应用在非主键上
-->
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="age" column="age"/>
<result property="createTime" column="create_time"/>
</resultMap>
<sql id="baseColumn">
id,name,age,gender,create_time
</sql>
<select id="selectStudentById" resultMap="baseMap">
select <include refid="baseColumn"/> from student where id = #{id}
</select>
<select id="selectStudentById$" resultMap="baseMap">
select <include refid="baseColumn"/> from student where id = ${id}
</select>
<select id="selectStudentOrderByAge" resultMap="baseMap">
select <include refid="baseColumn"/> from student order by ${age}
</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.bjpowernode.mapper.StudentMapper">
<resultMap id="baseMap" type="com.bjpowernode.domain.Student">
<!--
id标签是应用在主键上性能比较高
property是类中的属性名称
colunn是表中的字段名称
result标签是应用在非主键上
-->
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="age" column="age"/>
<result property="createTime" column="create_time"/>
</resultMap>
<sql id="baseColumn">
select id,name,age,gender,create_time from student
</sql>
<select id="selectStudentById" resultMap="baseMap">
<include refid="baseColumn"/> where id = #{id}
</select>
<select id="selectStudentById$" resultMap="baseMap">
<include refid="baseColumn"/> where id = ${id}
</select>
<select id="selectStudentOrderByAge" resultMap="baseMap">
<include refid="baseColumn"/> order by ${age}
</select>
</mapper>
八、模糊查询
UserMapper.java
package com.bjpowernode.mapper;
import com.bjpowernode.domain.User;
import java.util.*;
public interface UserMapper {
// 模糊查询
List<User> selectUserByKeyword(String keyword);
}
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">
<mapper namespace="com.bjpowernode.mapper.UserMapper">
<select id="selectUserByKeyword" resultType="user">
select * from user where name like concat('%',#{keyword},'%');
</select>
</mapper>
测试类
@Test
public void selectByCondition(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
List<User> userList = userMapper.selectUserByKeyword("lili");
userList.forEach(System.out::println);
}
九、多个参数问题
方案一：封装成对象
UserMapper.java中
//多个参数封装
// 方案一：封装成对象
void insertByJavaBean(User user);
UserMapper.xml
<insert id="insertByJavaBean">
insert into user(name,age,sex,address,birth) values (#{name},#{age},#{sex},#{address},#{birth});
</insert>
测试类
@Test
public void insertByJavaBean(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
User user = new User();
user.setSex(1);
user.setAddress("武侯");
user.setBirth(new Date());
user.setAge(19);
user.setName("武则天");
userMapper.insertByJavaBean(user);
}
方案二：封装成map
UserMapper.java
// 方案二：封装成map #{变量} 这个变量必须是map中的key
User selectUserByMap(Map map);
UserMapper.xml
– 封装成map #{变量} 这个变量必须是map中的key
<select id="selectUserByMap" resultType="user">
select * from user where name = #{userName} and address = #{userAddress}
</select>
测试类
@Test
public void selectUserByMap(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
Map map = new HashMap<>();
map.put("userName","tony");
map.put("userAddress","成都");
User user = userMapper.selectUserByMap(map);
System.out.println("user = " + user);
sqlSession.close();
}
方案三：使用@Param注解
UserMapper.java
/* 方案三：使用@Param注解 底层是map map.put("nameKey","nameValue");map.put("addressKey","addressValue")
#{变量} 这个变量必须是@Param注解中的名称
*/
User selectUserByParam(@Param("nameKey") String nameValue,@Param("addressKey")String addressValue);
UserMapper.xml
<select id="selectUserByParam" resultType="user">
select * from user where name = #{nameKey} and address = #{addressKey}
</select>
测试类
@Test
public void selectUserByParam(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
User user = userMapper.selectUserByParam("lili","成都");
System.out.println("user = " + user);
sqlSession.close();
}
十、分页查询（PageHelper)
PageHelper（GitHub）https://pagehelper.github.io/
官方文档：https://pagehelper.github.io/docs/howtouse/
使用 Maven
在 pom.xml 中添加如下依赖：
<!-- 分页插件jar -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
在mybatis-config.xml文件中typeAliases后面写分页插件的配置
<!--分页插件的 配置-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!--通过这个配置，来动态添加分页的功能-->
<property name="helperDialect" value="mysql"/>
<!--通过pageSizeZero属性可以灵活处理分页 当pageSize 设置为0就查询全部数据-->
<property name="pageSizeZero" value="true"/>
<!--通过reasonable 属性 可以合理化分页-->
<property name="reasonable" value="true"/>
</plugin>
</plugins>
UserMapper.java
List<User> selectAll();
UserMapper.xml
<select id="selectAll" resultType="user">
select * from user
</select>
测试类
@Test
public void selectByPageHelper(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
// 分页代码
//当pageSize = 0时，相当于没有分页，查询所有的数据
PageHelper.startPage(2, 2);
List<User> userList = userMapper.selectAll();
for (User user : userList) {
System.out.println("user = " + user);
}
// 封装一个pageInfo对象，就可以知道它的其他信息（上一页，下一页，总页数，总条数）
PageInfo<User> userPageInfo = new PageInfo<>(userList);
System.out.println("总页数："+userPageInfo.getPages());
System.out.println("下一页："+userPageInfo.getNextPage());
System.out.println("上一页："+userPageInfo.getPrePage());
System.out.println("总条数："+userPageInfo.getTotal());
System.out.println("当前页："+userPageInfo.getPageNum());
System.out.println("每页显示条数:"+userPageInfo.getPageSize());
sqlSession.close();
}
十一、新增自增长
Mybatis在insert指令中，提供了2个属性：useGeneratedKeys、keyProperty、keyColumn
useGeneratedKeys：表示使用数据库自增长
keyProperty：自增长的列对应的类中的属性
keyColumn：自增长的列
Mybatis会自动将增长的值，封装到传入参数的属性中。
UserMapper.xml
<insert id="insertByJavaBean" useGeneratedKeys="true" keyProperty="id">
insert into user(name,age,sex,address,birth) values (#{name},#{age},#{sex},#{address},#{birth});
</insert>
测试类
@Test
public void insertByJavaBean(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
User user = new User();
user.setSex(1);
user.setAddress("武侯");
user.setBirth(new Date());
user.setAge(19);
user.setName("武则天");
userMapper.insertByJavaBean(user);
System.out.println("user.getId() = " + user.getId());
sqlSession.close();
}
十二、动态SQL
1.if标签
在if标签中，test属性是必须有，test属性值是一个表达式，如果表达式值为true,则if标签包裹的内容会拼接在当前sql上。
and 并且
or 或者
== 等于
!= 不等于
用户搜索模糊查询如果输入为null或空的字符串
@Test
public void selectUserByKeyword(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
// List<User> userList = userMapper.selectUserByKeyword("lili");
List<User> userList = userMapper.selectUserByKeyword(null);
userList.forEach(System.out::println);
}
<select id="selectUserByKeyword" resultType="user">
select * from user
<if test="keyword!=null and keyword!='' ">
where name like concat('%',#{keyword},'%');
</if>
</select>
查询多个条件的情况下
UserMapper.java
List<User> selectByCondition(@Param("keyword") String keyword,
@Param("birthday")Date birth);
UserMapper.xml
<select id="selectByCondition" resultType="user">
select * from user
<if test="keyword!=null and keyword!=''" >
where name like concat('%',#{keyword},'%')
</if>
<if test="birthday!=null">
and birth like concat ('%',year(#{birthday}),'%')
</if>
</select>
测试类
// 查询指定某一年的用户信息，并且支持名字模糊查询
@Test
public void selectByCondtion(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectByCondition("lili",new Date());
userList.forEach(System.out::println);
}
如果keyword为空字符串
// 查询指定某一年的用户信息，并且支持名字模糊查询
@Test
public void selectByCondtion(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectByCondition("",new Date());
userList.forEach(System.out::println);
}
sql报错
如何改进？
<select id="selectByCondition" resultType="user">
select * from user where 1=1
<if test="keyword!=null and keyword!=''" >
and name like concat('%',#{keyword},'%')
</if>
<if test="birthday!=null">
and birth like concat ('%',year(#{birthday}),'%')
</if>
</select>
产生的新的问题：where 1=1 会导致索引失效
引入where标签
2.where标签
在mybatis中，存在sql条件，当有多个sql条件时，需要处理and关键字问题，因为where后面的第一个条件不需要and，解决方案：
①在where后面 拼接 1=1 类似的条件，这样其他条件都不是第一个条件，都需要拼接and
②mybatis 提供了where标签，取代where关键字，默认去掉第一个条件 and
注意：
建议，只在查询语句中使用where标签，因为当where标签中的条件都不成立时，会没有where关键字。
<!-- where 可以把第一个and 去掉 -->
<select id="selectByCondition" resultType="user">
select * from user
<where>
<if test="keyword!=null and keyword!=''">
and name like concat('%',#{keyword},'%')
</if>
<if test="birthday!=null">
and birth like concat ('%',year(#{birthday}),'%')
</if>
</where>
</select>
3.Foreach标签
Select * from user where id in (1,2,3,4)
循环标签，循环标签多用于批量操作。
例如：批量新增，批量删除,批量查询等等
1.collection 待循环的容器 （容器为数组值为array，容器为集合值为list）
2.item 指代 每次循环容器中的元素
3.open 开始循环是拼接字符串
4.close 循环结束拼接字符串
5.separator 每次循环之间拼接的字符串
6.index 循环索引
3.1 批量查询
3.1.1 数组
① 普通数组
UserMapper.java
/*
* 批量查询
* 需求：查询id1、2、3、4的用户信息
* sql:select * from user where id in(1,2,3,4)
*/
List<User> selectByIdsWithArray(Long[] ids);
UserMapper.xml
<!--
select * from user where id in 固定 动态拼接：(1,2,3,4)
属性：
collection 表示 传入参数的类型 是数组 就写 array 如果是集合，就写list
open 表示 开始的部分 例如：小括号的左半边是开始的部分
close 表示 结束的部分 例如：小括号的右半边是结束的部分
item 表示 接收元素的变量，（用来接收遍历容器中的 每一个元素）
separator 表示元素与元素之间的 分隔符号 例如：逗号
-->
<select id="selectByIdsWithArray" resultType="user">
select * from user where id in
<foreach collection="array" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
测试类
@Test
public void selectByIdsWithArray() {
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
Long[] ids = {2L,3L,4L};
List<User> userList = userMapper.selectByIdsWithArray(ids);
userList.forEach(System.out::println);
}
② 数组+@Param注解
xml中的collection的值可以为@Param中的key
UserMapper.java
这里用了@Param注解
List<User> selectByIdsWithArrayAndParam(@Param("ids") Long[] ids);
UserMapper.xml
xml中collection的值可以为@Param中的key
<select id="selectByIdsWithArrayAndParam" resultType="user">
select * from user where id in
<foreach collection="ids" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
测试类
@Test
public void selectByIdsWithArrayAndParam(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
Long[] ids = {2L,3L,4L};
List<User> userList = userMapper.selectByIdsWithArrayAndParam(ids);
userList.forEach(System.out::println);
}
3.1.2 集合
①普通集合
UserMapper.java
List<User> selectByIdsWithList(List<Long> ids);\
UserMapper.xml
<select id="selectByIdsWithList" resultType="user">
select * from user where id in
<foreach collection="list" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
测试类
@Test
public void selectByIdsWithList(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
List<Long> ids = Arrays.asList(2L,3L,4L);
List<User> userList = userMapper.selectByIdsWithList(ids);
userList.forEach(System.out::println);
}
② 集合+@Param注解
xml中的collection的值可以为@Param中的key
UserMapper.java
这里用了@Param注解
List<User> selectByIdsWithListAndParam(@Param("ids") List<Long> ids);
UserMapper.xml
xml中collection的值可以为@Param中的key
<select id="selectByIdsWithListAndParam" resultType="user">
select * from user where id in
<foreach collection="ids" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
测试类
@Test
public void selectByIdsWithListAndParam(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
List<Long> ids = Arrays.asList(2L,3L,4L);
List<User> userList = userMapper.selectByIdsWithListAndParam(ids);
userList.forEach(System.out::println);
}
3.2 批量新增
UserMapper.java
//批量插入
void insertBatchWithList(List<User> userList);
UserMapper.xml
<!--
insert into user (name,age,sex,address,birth) values
(#{name},#{age},#{sex},#{address},#{birth}),
(#{name},#{age},#{sex},#{address},#{birth}),
(#{name},#{age},#{sex},#{address},#{birth})
-->
<insert id="insertBatchWithList">
insert into user (name,age,sex,address,birth) values
<foreach collection="list" separator="," item="user">
(#{user.name},#{user.age},#{user.sex},#{user.address},#{user.birth})
</foreach>
</insert>
测试类
@Test
public void insertBatchWithList() {
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
List<User> userList = Arrays.asList(new User(null,"股市",2,1,"广州",new Date()),new User(null,"顾问",2,1,"成都",new Date()));
userMapper.insertBatchWithList(userList);
sqlSession.close();
}
4 choose标签
choose
多条件分支标签:choose.
在choose标签，自上而下执行when中表达式，如果表达式为true,则将相应的字符串拼接在sql后面，且终止判断。如果所有的表达式都为false,则将otherwise中字符串，拼接在sql后面
UserMapper.java
/*choose标签
* 需求：根据年龄查询不同区间的用户信息
* */
List<User> selectByAge(int age);
UserMapper.xml
<!--
choose 标签 配有两种子标签 一种是 <when> 一种是<otherwise>
特点：<when>可以写多个 至少要提供一个
test属性 它是boolean，如果表达式成立，则拼接when中的条件
如果不成立，则拼接otherwise里面的条件
-->
<select id="selectByAge" resultType="user">
select * from user where
<choose>
<when test="age>80">
age > 80
</when>
<when test="age>60 and age <80">
age > 60 and age <80
</when>
<when test="age> 20and age < 60">
age > 20 and age <60
</when>
<otherwise>
age < 20
</otherwise>
</choose>
</select>
测试类
@Test
public void selectByAgeWithChoose() {
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
List<User> userList = userMapper.selectByAge(10);
userList.forEach(System.out::println);
sqlSession.close();
}
5.set标签
set标签是取代sql语句中的set关键字。set表示后面数据的更新。各个字段，需要使用逗号分隔。
set标签可以去掉最后一个逗号。
UserMapper.java
/*set标签
* 需求：根据提供的数据进行相应的修改
*/
void updateByCondition(User user);
UserMapper.xml
<!-- set标签可以去掉默认的逗号 -->
<update id="updateByCondition">
update user
<set>
<if test="name!=null and name!=''">
name = #{name},
</if>
<if test="age>0">
age = #{age},
</if>
<if test="sex < 2 and sex > -1">
sex = #{sex},
</if>
<if test="address!=null and address!=''">
address = #{address},
</if>
<if test="birth!=null">
birth = #{birth}
</if>
</set>
where id = #{id}
</update>
测试类
@Test
public void updateByCondition() {
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
User user = new User();
user.setName("嫦娥一号");
// user.setAge(2);
// user.setBirth(new Date());
// user.setAddress("北京");
// user.setSex(1);
user.setId(3L);
userMapper.updateByCondition(user);
十三、缓存
mybatis为了提高查询效率、性能。提供了缓存，缓存分为2类:
一级缓存
二级缓存
一级缓存：是指SqlSession级别的缓存，在同一个SqlSession，同样的SQL语句只会执行一次，不是第一次执行的SQL会从缓存中获取数据。
二级缓存: 是指SqlSessionFactory级别的缓存，在同一个SqlSessionFactory中，同样的SQL语句，只会执行一次，不是第一次执行的SQL会从缓存中获取数据。
注意：
不论一级缓存还是二级缓存，否是JVM中缓存。当服务器有多台时，缓存容易发生无效，数据发生了更新。缓存没有更新。在实际使用，如果数据可能发生比较频繁的更新不建议使用mybatis。
一级缓存
@Test
public void selectAllCache(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
List<User> userList = userMapper.selectAll();
for (User user : userList) {
System.out.println("user = " + user);
}
System.out.println("---------------------------------------------------");
List<User> userList1 = userMapper.selectAll();
for (User user : userList1) {
System.out.println("user = " + user);
}
sqlSession.close();
}
发现只执行了一次SQL
多个sqlSession，就执行多次sql
@Test
public void selectAllCacheTwoSqlSession(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
List<User> userList = userMapper.selectAll();
for (User user : userList) {
System.out.println("user = " + user);
}
sqlSession.close();
System.out.println("---------------------------------------------------");
SqlSession sqlSession1 = MyBatisUtil.openSession();
UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
List<User> userList1 = userMapper1.selectAll();
for (User user : userList1) {
System.out.println("user = " + user);
}
sqlSession.close();
}
多个sqlSession 执行多次sql
// 默认一级缓存是开启，那么意味者 在sqlSession 对象没有关闭之前
执行多次一样的查询，是不会多次发送sql语句的。
}
如果执行完查询之后，把sqlSession 对象关闭了，则意味者一级缓存释放了
则，再执行同样的查询，也需要发送sql 语句
二级缓存
二级缓存需要在映射的xml配置文件中，开启缓存：
并且被缓存的module数据需要支持，序列化
在UserMapper.java中开启二级缓存标签
module对象实现Serializable接口
再次执行测试类
@Test
public void selectAllCacheTwoSqlSession(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
List<User> userList = userMapper.selectAll();
for (User user : userList) {
System.out.println("user = " + user);
}
sqlSession.close();
System.out.println("---------------------------------------------------");
SqlSession sqlSession1 = MyBatisUtil.openSession();
UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
List<User> userList1 = userMapper1.selectAll();
for (User user : userList1) {
System.out.println("user = " + user);
}
sqlSession.close();
}
有50%的几率，只执行了一次Sql
十四、Mybatis注解
@Select
@Insert
@Delete
@Update
UserMapper.java
@Select("select * from user")
List<User> selectAllByAnno();
测试类
@Test
public void selectAllByAnno(){
SqlSession sqlSession = MyBatisUtil.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println(userMapper);
List<User> userList = userMapper.selectAllByAnno();
for (User user : userList) {
System.out.println("user = " + user);
}
}