Mybatis学习（1）——mybatis介绍 & 入门案例 & 全局配置文件详解 & 增删改查 + mybatis事务 & mapper.xml文件#{} 和 ${} & 动态SQL入门
原文链接：https://blog.csdn.net/Pireley/article/details/131520252
目录
引出
1.mybatis是啥？持久层框架，关系型数据库，ORM（Object Relation Mapping）对象关系映射；
2.怎么用，全局配置文件mybatis-config.xml文件，驼峰命名，日志，起别名，配置连接数据库；
3.增删改查，如何用mybatis实现，select，update，insert，delete，#{}，${}；
4.mapper.xml文件，parameterType和resultType，@param注解；
5.mybatis事务初步，打开自动提交，sqlSessionFactory.openSession(true);
6.动态SQL入门，if，foreach，set，代码片段，常见的转义符；
一、mybatis是啥
1.官网 & ORM（Object Relation Mapping）对象关系映射
https://mybatis.org/mybatis-3/zh/index.html
-
mybatis 是一个优秀的基于 java 的持久层框架，主要应用于关系型数据库（sql）,它内部封装了 jdbc，使开发者只需要关注 sql 语句本身，而不需要花费精力去处理加载驱动、创建连接、创建 statement ，封装数据等繁杂的过程。
-
mybatis 通过 xml 或注解的方式将要执行的各种 statement 配置起来，并通过 java 对象和 statement 中sql 的动态参数进行映射生成最终执行的 sql 语句，最后由 mybatis 框架执行 sql 并将结果映射为 java 对象并返回。
-
采用 ORM 思想解决了实体和数据库映射的问题，对 jdbc 进行了封装，屏蔽了 jdbc api 底层访问细节，使我们不用与 jdbc api 打交道，就可以完成对数据库的持久化操作。
ORM思想是什么？
ORM（Object Relation Mapping）对象关系映射，是一种思想，主要包含三种对应关系：
| 关系映射 | 对应关系 |
|---|---|
| 类和表对应 | 一个pojo类 ←→ 一张数据库表 |
| 字段和列名对应 | pojo类中的一个字段 ←→ 数据库表中的一列 |
| 类实例化对象和数据对应 | pojo类的一个对象 ←→ 数据库表中的一行数据 |
ORM思想是所有持久层框架的基本思想，也是目前所有数据传输的思想。就是把数据和对象一一对应起来。从本质上来说SpringMVC也是做的这样的事情，数据在页面时，传到后台就成了对象。
2.普通JDBC的操作方式
package com.tianju.jdbc;
import com.tianju.entity.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Demo {
public static void main(String[] args) throws SQLException {
// 注册驱动
String driverClassname = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
// String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";
String name = "root";
String password = "123";
// 1.类加载，加载驱动
try {
Class.forName(driverClassname);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
<span class="token comment">// 2.建立连接，Connection</span>
<span class="token class-name">Connection</span> connection <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
<span class="token class-name">PreparedStatement</span> preparedStatement <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
<span class="token class-name">ResultSet</span> resultSet <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
<span class="token keyword">try</span> <span class="token punctuation">{<!-- --></span>
<span class="token comment">// 2.建立连接，Connection</span>
connection <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span>url<span class="token punctuation">,</span> name<span class="token punctuation">,</span> password<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 3.准备sql语句</span>
preparedStatement <span class="token operator">=</span> connection<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span><span class="token string">"SELECT * FROM user"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 4.执行sql语句，进行查询，获取结果</span>
resultSet <span class="token operator">=</span> preparedStatement<span class="token punctuation">.</span><span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 5.对结果进行处理</span>
<span class="token class-name">List</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">User</span><span class="token punctuation">></span></span> userList <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">ArrayList</span><span class="token generics"><span class="token punctuation"><</span><span class="token punctuation">></span></span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">while</span> <span class="token punctuation">(</span>resultSet<span class="token punctuation">.</span><span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">{<!-- --></span>
<span class="token class-name">User</span> user <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">User</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
user<span class="token punctuation">.</span><span class="token function">setId</span><span class="token punctuation">(</span>resultSet<span class="token punctuation">.</span><span class="token function">getInt</span><span class="token punctuation">(</span><span class="token string">"id"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
user<span class="token punctuation">.</span><span class="token function">setUsername</span><span class="token punctuation">(</span>resultSet<span class="token punctuation">.</span><span class="token function">getString</span><span class="token punctuation">(</span><span class="token string">"username"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
userList<span class="token punctuation">.</span><span class="token function">add</span><span class="token punctuation">(</span>user<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>userList<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{<!-- --></span>
<span class="token keyword">assert</span> connection <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">;</span> <span class="token comment">// 判断connection是不是null</span>
connection<span class="token punctuation">.</span><span class="token function">rollback</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">// 如果出异常要能够回滚</span>
e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{<!-- --></span>
<span class="token comment">// 6.关闭连接</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span>resultSet<span class="token operator">!=</span><span class="token keyword">null</span><span class="token punctuation">)</span><span class="token punctuation">{<!-- --></span>
<span class="token keyword">try</span> <span class="token punctuation">{<!-- --></span>
resultSet<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{<!-- --></span>
e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span>preparedStatement<span class="token operator">!=</span><span class="token keyword">null</span><span class="token punctuation">)</span><span class="token punctuation">{<!-- --></span>
<span class="token keyword">try</span> <span class="token punctuation">{<!-- --></span>
preparedStatement<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{<!-- --></span>
e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span>connection<span class="token operator">!=</span><span class="token keyword">null</span><span class="token punctuation">)</span><span class="token punctuation">{<!-- --></span>
<span class="token keyword">try</span> <span class="token punctuation">{<!-- --></span>
connection<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{<!-- --></span>
e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span>
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
JDBC操作的问题
- 连接和关闭占用大量代码
- SQL语句直接写在Java代码中，不容易维护
- 修改Java代码，有可能要动SQL语句，修改SQL语句也有可能要动Java代码
3.mybatis入门案例
操作顺序1个数据库 3个xml文件，一个Java类
- pom导入包---------------一次性配好
- mybatis-config.xml维护连接相关信息-------------连接数据库、实体类的别名，帮我们找到写sql的文件
- mapper.xml文件存sql和映射关系----写sql的文件
- Java类：接口，接口的方法和mapper.xml的文件，一一对应起来
- Java代码获取数据
（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>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>groupId</span><span class="token punctuation">></span></span>com.tianju<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>groupId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>artifactId</span><span class="token punctuation">></span></span>mybatis0612<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>artifactId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>version</span><span class="token punctuation">></span></span>1.0-SNAPSHOT<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>version</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>properties</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>project.build.sourceEncoding</span><span class="token punctuation">></span></span>UTF-8<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>project.build.sourceEncoding</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>maven.compiler.source</span><span class="token punctuation">></span></span>1.8<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>maven.compiler.source</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>maven.compiler.target</span><span class="token punctuation">></span></span>1.8<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>maven.compiler.target</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>properties</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dependencies</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dependency</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>groupId</span><span class="token punctuation">></span></span>mysql<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>groupId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>artifactId</span><span class="token punctuation">></span></span>mysql-connector-java<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>artifactId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>version</span><span class="token punctuation">></span></span>8.0.22<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>version</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dependency</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dependency</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>groupId</span><span class="token punctuation">></span></span>junit<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>groupId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>artifactId</span><span class="token punctuation">></span></span>junit<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>artifactId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>version</span><span class="token punctuation">></span></span>4.12<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>version</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>scope</span><span class="token punctuation">></span></span>test<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>scope</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dependency</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dependency</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>groupId</span><span class="token punctuation">></span></span>log4j<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>groupId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>artifactId</span><span class="token punctuation">></span></span>log4j<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>artifactId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>version</span><span class="token punctuation">></span></span>1.2.17<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>version</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dependency</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dependency</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>groupId</span><span class="token punctuation">></span></span>org.projectlombok<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>groupId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>artifactId</span><span class="token punctuation">></span></span>lombok<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>artifactId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>version</span><span class="token punctuation">></span></span>1.18.26<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>version</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dependency</span><span class="token punctuation">></span></span>
<!-- mybatis 框架-->
<!-- 小版本会导致userMapper findByUsernameSQL-->
<!-- 3.5.9-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<span class="token comment"><!-- 分页相关的包--></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dependency</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>groupId</span><span class="token punctuation">></span></span>com.github.pagehelper<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>groupId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>artifactId</span><span class="token punctuation">></span></span>pagehelper<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>artifactId</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>version</span><span class="token punctuation">></span></span>3.7.5<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>version</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dependency</span><span class="token punctuation">></span></span>
<!-- json-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.76</version>
</dependency>
</dependencies>
</project>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
（2）mybatis-config.xml全局配置文件
要点：
- 1.开启驼峰命名；
- 2.让日志生效；
- 3.给所有实体类起别名，所有的别名都是首字母小写；
- 4.分页需要加拦截器；
- 5.连接数据库的驱动，路径，用户名和密码；其中url需要在&后面加 amp;
- 6.告诉配置文件，sql的xml文件在哪，java找；
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 数据库和实体类 驼峰的问题 ：开启驼峰命名-->
<settings>
<!-- 处理驼峰命名-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- 日志生效-->
<setting name="logImpl" value="LOG4J"/>
</settings>
<!-- 别名 给所有的实体类都起一个别名com.tianju.entity，所有别名都是类名的首字母小写-->
<typeAliases>
<package name="com.tianju.entity"/>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>typeAliases</span><span class="token punctuation">></span></span>
<!-- 分页需要加个拦截器-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>environments</span> <span class="token attr-name">default</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>development<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>environment</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>development<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>transactionManager</span> <span class="token attr-name">type</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>JDBC<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dataSource</span> <span class="token attr-name">type</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>POOLED<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>property</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>driver<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>com.mysql.cj.jdbc.Driver<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>property</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>url<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false<span class="token entity named-entity" title="&">&amp;</span>serverTimezone=Asia/Shanghai<span class="token entity named-entity" title="&">&amp;</span>allowPublicKeyRetrieval=true<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>property</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>username<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>root<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>property</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>password<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>123<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dataSource</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>environment</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>environments</span><span class="token punctuation">></span></span>
<!-- 告诉配置文件，sql的xml文件在哪，java找用. 普通文件用 /-->
<mappers>
<!-- 简化方式，不然得写很多mapper-->
<!-- <mapper resource="com/tianju/dao/UserMapper.xml"/>-->
<package name="com.tianju.dao"/>
</mappers>
</configuration>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
（3）log4j.properties日志配置，可以看到执行的SQL
# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE debug info warn error fatal
#log4j.rootCategory=debug, CONSOLE, LOGFILE
# ??LOGFILE?????????
log4j.rootCategory=debug, CONSOLE
Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE
CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=D:/axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
（4）userMapper.java接口文件
要点：
-
@Select(“select * from user”) // 不建议，用的字段都写出来，尽量不用*；
-
如果3.4.5版本，3.4开头的就是低版本，并且username要在动态sql中使用即，if，where，中使用，则一个参数也需要加@param，如果是3.5.9以上版本，则可以不加；
-
如果传多个参数，则必须加@Param(“username”)；
-
如果传入的参数是一个list，则建议加注解Integer deleteBatchParam(@Param(“ids”) List < Integer > ids)；
-
sql注入漏洞相关 #{} 和 ${}；#{}填充?，不会有注入漏洞； ${}直接拼字符串，有注入漏洞；
package com.tianju.dao;
import com.tianju.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
- 返回值，方法名，参数
*/
public interface UserMapper {
// @Select("select * from user") // 不建议，用的字段都写出来，尽量不用*
List<User> queryList();// select * from ...
<span class="token comment">// 根据id查询一个人</span>
<span class="token class-name">User</span> <span class="token function">findById</span><span class="token punctuation">(</span><span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"id"</span><span class="token punctuation">)</span> <span class="token class-name">Integer</span> id<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">User</span> <span class="token function">findByUsername</span><span class="token punctuation">(</span><span class="token class-name">String</span> username<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 如果3.4.5版本，3.4开头的就是低版本，并且username要在动态sql中使用</span>
<span class="token comment">// 即，if，where，中使用，则一个参数也需要加@param，如果是3.5.9以上版本，则可以不加</span>
<span class="token class-name">List</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">User</span><span class="token punctuation">></span></span> <span class="token function">findByUsernameSQL</span><span class="token punctuation">(</span><span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"username"</span><span class="token punctuation">)</span> <span class="token class-name">String</span> username<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 新增一个人</span>
<span class="token class-name">Integer</span> <span class="token function">add</span><span class="token punctuation">(</span><span class="token class-name">User</span> user<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">Integer</span> <span class="token function">addUser</span><span class="token punctuation">(</span><span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"user"</span><span class="token punctuation">)</span><span class="token class-name">User</span> user<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 各种查询</span>
<span class="token class-name">List</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">User</span><span class="token punctuation">></span></span> <span class="token function">findByUsernameAndAgeAndAddress</span><span class="token punctuation">(</span>
<span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"username"</span><span class="token punctuation">)</span> <span class="token class-name">String</span> username<span class="token punctuation">,</span>
<span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"age"</span><span class="token punctuation">)</span> <span class="token class-name">Integer</span> age<span class="token punctuation">,</span>
<span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"address"</span><span class="token punctuation">)</span> <span class="token class-name">String</span> address<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">List</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">User</span><span class="token punctuation">></span></span> <span class="token function">findByUsernameAndAgeAndAddressWhere</span><span class="token punctuation">(</span>
<span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"username"</span><span class="token punctuation">)</span> <span class="token class-name">String</span> username<span class="token punctuation">,</span>
<span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"age"</span><span class="token punctuation">)</span> <span class="token class-name">Integer</span> age<span class="token punctuation">,</span>
<span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"address"</span><span class="token punctuation">)</span> <span class="token class-name">String</span> address<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 批量删除</span>
<span class="token class-name">Integer</span> <span class="token function">deleteBatch</span><span class="token punctuation">(</span><span class="token class-name">List</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">Integer</span><span class="token punctuation">></span></span> ids<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 批量删除 加注解</span>
<span class="token class-name">Integer</span> <span class="token function">deleteBatchParam</span><span class="token punctuation">(</span><span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"ids"</span><span class="token punctuation">)</span> <span class="token class-name">List</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">Integer</span><span class="token punctuation">></span></span> ids<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 根据用户名密码查有几个，sql注入漏洞相关 #{} 和 ${}</span>
<span class="token class-name">Integer</span> <span class="token function">countByUsernameAndPassword</span><span class="token punctuation">(</span><span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"username"</span><span class="token punctuation">)</span> <span class="token class-name">String</span> username<span class="token punctuation">,</span><span class="token annotation punctuation">@Param</span><span class="token punctuation">(</span><span class="token string">"password"</span><span class="token punctuation">)</span> <span class="token class-name">String</span> password<span class="token punctuation">)</span><span class="token punctuation">;</span>
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
（4）对应的userMapper.xml文件，SQL语句
要点：
-
namespace=“com.tianju.dao.UserMapper” ：这个xml文件和 UserMapper这个接口对应；
-
integer要小写：id=“findById” parameterType=“integer” resultType=“user”；
-
如果在mapper里加了注解，parameterType 就不需要写了；
-
如果在mapper里加了注解，参数有实体类，则下面拿属性值时要用点 user.username；
-
如果多个条件，用if语句，string类型判断null和空，integer，date，只需要判断null；
-
批量操作in，用foreach语句，collection=“ids” open=“(” close=“)” item=“id” separator=“,” 表示：open 开始是 ( 结束为右括号 ) ,
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--这个xml文件和 UserMapper这个接口对应-->
<mapper namespace="com.tianju.dao.UserMapper">
<!-- 方法名，返回值，参数类型-->
<select id="queryList" resultType="com.tianju.entity.User">
SELECT * FROM user
</select>
<!-- integer要小写-->
<!-- <select id="findById" parameterType="integer" resultType="user">-->
<!-- 如果在mapper里加了注解，parameterType 就不需要写了-->
<select id="findById" resultType="user">
SELECT * FROM user WHERE id = #{id}
</select>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>select</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>findByUsername<span class="token punctuation">"</span></span> <span class="token attr-name">parameterType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>string<span class="token punctuation">"</span></span> <span class="token attr-name">resultType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>user<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
SELECT * FROM user WHERE username = #{username}
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>select</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>select</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>findByUsernameSQL<span class="token punctuation">"</span></span> <span class="token attr-name">parameterType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>string<span class="token punctuation">"</span></span> <span class="token attr-name">resultType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>user<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
SELECT * FROM user
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>where</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>if</span> <span class="token attr-name">test</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>username!=null and username!=<span class="token punctuation">'</span><span class="token punctuation">'</span><span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
username = #{username}
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>if</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>where</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>select</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>select</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>add<span class="token punctuation">"</span></span> <span class="token attr-name">parameterType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>user<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
INSERT INTO user(username,password,birthday,sex,address,age)
VALUES (#{username}, #{password}, #{birthday}, #{sex}, #{address},#{age})
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>select</span><span class="token punctuation">></span></span>
<!-- 如果参数有实体类，则下面拿属性值时要用点 user.username-->
<select id="addUser">
INSERT INTO user(username,password,birthday,sex,address,age)
VALUES (#{user.username}, #{user.password}, #{user.birthday}, #{user.sex}, #{user.address},#{user.age})
</select>
<!-- #{} 和 ${}-->
<select id="countByUsernameAndPassword" resultType="integer">
<!-- SELECT COUNT() FROM user WHERE username = #{username} AND password = #{password}-->
SELECT COUNT() FROM user WHERE username = '${username}' AND password = '${password}'
</select>
<!-- 如果多个条件怎么搞-->
<select id="findByUsernameAndAgeAndAddress" resultType="user">
SELECT * FROM user WHERE 1=1
<if test="username!=null and username !='' ">
AND username LIKE #{username}
</if>
<if test="age!=null">
AND age > #{age}
</if>
<if test="address!=null and address !='' ">
AND address = #{address}
</if>
</select>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>select</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>findByUsernameAndAgeAndAddressWhere<span class="token punctuation">"</span></span> <span class="token attr-name">resultType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>user<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
SELECT * FROM user
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>where</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>if</span> <span class="token attr-name">test</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>username!=null and username !=<span class="token punctuation">'</span><span class="token punctuation">'</span> <span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
username LIKE #{username}
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>if</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>if</span> <span class="token attr-name">test</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>age!=null<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
AND age > #{age}
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>if</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>if</span> <span class="token attr-name">test</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>address!=null and address !=<span class="token punctuation">'</span><span class="token punctuation">'</span> <span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
AND address = #{address}
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>if</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>where</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>select</span><span class="token punctuation">></span></span>
<!-- 批量删除的-->
<!-- <select id="deleteBatch" parameterType="list">-->
<delete id="deleteBatch" parameterType="list">
DELETE FROM user WHERE id IN
<!-- open 开始是 ( 结束为右括号 ) ,-->
<foreach collection="list" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</delete>
<!-- 建议-->
<delete id="deleteBatchParam">
DELETE FROM user WHERE id IN
<!-- open 开始是 ( 结束为右括号 ) ,-->
<foreach collection="ids" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</delete>
</mapper>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
（5）进行测试，执行SQL
package com.tianju.test;
import com.tianju.dao.UserMapper;
import com.tianju.entity.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 java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Main {
public static void main(String[] args) throws IOException {
// 1.读取mybatis的配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
// 2.生成一个工厂---这个工厂是通过建造者造出来的
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 3.建造者根据mybatis-config.xml 建造一个SqlSessionFactory 工厂
SqlSessionFactory sqlSessionFactory = builder.build(is);
// 4.通过sqlSessionFactory 生成一个 sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
// 5.mybatis会把所有的接口都用动态代理生成实现类，这些实现类都可以通过sqlSession获取
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.queryList();
userList.forEach(System.out::println);
<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">"==========================="</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">User</span> user <span class="token operator">=</span> userMapper<span class="token punctuation">.</span><span class="token function">findById</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>user<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">"============="</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>userMapper<span class="token punctuation">.</span><span class="token function">findByUsername</span><span class="token punctuation">(</span><span class="token string">""</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>userMapper<span class="token punctuation">.</span><span class="token function">findByUsername</span><span class="token punctuation">(</span><span class="token string">"张三"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
4.mybatis工作流程分析
读取配置文件—>建造者 —> 工厂SqlSessionFactory—>SqlSession—>获取接口都用动态代理生成的实现类
-
SqlSessionFactoryBuilder使用全局配置文件构建出SqlSessionFactory对象
-
使用SqlSessionFactory创建出SqlSession对象
-
使用SqlSession获得mapper接口的对象
-
调用mapper接口的某个方法，mybatis自动从对应的映射文件中找到对应的sql语句
下面两步是mybatis帮我们做的
-
自动使用JDBC执行此sql语句并得到ResultSet结果集
-
自动根据ORM思想把结果集处理成List对象并返回
通过这样的流程，通过对JDBC代码、处理结果集代码的封装，使得开发人员减少了90%以上的编码量，并且使得代码结构简单清晰，sql语句易于管理维护
不重要：基于注解实现
修改mybatis-config.xml 文件,删除通过文件配置，加上类配置
<!-- <mappers>-->
<!-- <mapper class="com.woniu.mapper.UserMapper"/>-->
<!-- </mappers>-->
- 1
- 2
- 3
在UserMapper类上加上
@Select("select * from user")
List<User> findAll();
- 1
- 2
最后删除UserMapper.xml文件
二、mybatis-config.xml全局配置文件
文件中所有属性如下：
-properties（属性）
--property
-settings（全局配置参数）
--setting
-typeAliases（类型别名）
--typeAliase
--package
-typeHandlers（类型处理器）
-objectFactory（对象工厂）
-plugins（插件）
-environments（环境集合属性对象）
--environment（环境子属性对象）
—transactionManager（事务管理）
—dataSource（数据源）
-mappers（映射器）
--mapper
--package
1.指定日志实现
加入pom文件
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
- 1
- 2
- 3
- 4
- 5
把mybatis日志信息打印出来有助于理解和学习mybatis，在资源目录下放置我们的log配置文件
log4j.properties
# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE debug info warn error fatal
log4j.rootCategory=debug, CONSOLE, LOGFILE
Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE
CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=/Users/edz/axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
如果mybatis没有自动调用，也可以在配置文件中加入,jdk11情况下，不需要配置
<configuration>
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
</configuration>
- 1
- 2
- 3
- 4
- 5
2. <environments>环境管理
即事务管理器和数据源管理
（1）事务管理
mybatis有两种事务管理方式：JDBC、Managed
JDBC方式即使用JDBC管理事务，由开发人员手动控制事务的commit、rollback
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@fe18270]
- 1
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@fe18270]
- 1
sqlSessionFactory.openSession()时可以传入true设置成自动提交，默认是非自动提交
MANAGED方式即把事务交给spring容器管理，比如交给spring
一般使用时都是mybatis和spring进行整合，使用MANAGED方式管理事务。单独学习时需要使用JDBC方式
（2）数据源管理
mybatis有三种数据源管理方式：UNPOOLED、POOLED、JNDI：
-
UNPOOLED就是不使用数据库连接池，每次需要数据库连接都重新创建，使用后再关闭；
-
POOLED就是使用数据库连接池
-
JNDI就是使用在JDNI上下文中维护的数据库连接池，不需要mybatis管理维护
无论是UNPOOLED还是POOLED方式，学习阶段都可以只简单的指定driver、url、username、password这四个基本的连接参数
一般使用时都是mybatis和spring进行整合，数据源在spring中进行管理维护。单独学习时才由mybatis管理维护
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@fe18270]
Returned connection 266437232 to pool.
- 1
- 2
3.<typeAliases>. aliases别名
给类起别名的目的是简化书写，在配置文件中使用短的别名代替长的全类名（别名不是必须的）
在全局配置文件中使用<typeAliases>管理用户自定义的别名，另外mybatis已经对经常使用的类起了别名
（1）mybatis提供的预定义别名
在类import org.apache.ibatis.type.TypeAliasRegistry;中定义了很多预定义的别名
| 别名 | 映射的类型 |
|---|---|
| int | Integer |
| integer | Integer |
| double | Double |
| float | Float |
| boolean | Boolean |
| date | Date |
| decimal | BigDecimal |
| bigdecimal | BigDecimal |
（2）自定义别名
把模式改为xml文件的模式
直接指定类的别名：
<typeAliases>
<typeAlias type="com.tianju.domain.User" alias="user"></typeAlias>
</typeAliases>
- 1
- 2
- 3
未来在mapper.xml文件中，就可以不写全类名了
<select id="findAll" resultType="user">
select * from user
</select>
- 1
- 2
- 3
如果实体类特别多，我们也可以给一个包下的所有类起别名
给某个包（及子包）下的所有普通类自动起别名：（不会给匿名类、内部类、接口起别名）
<typeAliases>
<package name="com.tianju.domain"/>
</typeAliases>
- 1
- 2
- 3
别名为类的简单名称的全部小写形式，比如com.tianju.pojo.User的别名是user，
别名在使用时忽略大小写，即使用User、uSer、usEr和使用user的效果是一样的
另外，配置文件使用的一些值如UNPOOLED、POOLED、JDBC等也是别名
注意：
- mapper的命名空间和别名是不同的概念，不能混用
- 放的时候，要放在xml的environments标签上面，否则会报错
<typeAliases>
<package name="domain.blog"/>
</typeAliases>
- 1
- 2
- 3
4.<mappers>映射配置文件
使用<mappers>管理映射配置文件
直接指定配置文件路径的方式：
<mappers>
<mapper resource="com/tianju/mapper/UserMapper.xml"/>
</mappers>
- 1
- 2
- 3
自动加载某个包（及子包）下所有配置文件的方式：
<mappers>
<package name="com.tianju.mapper"/>
</mappers>
- 1
- 2
- 3
注解方式时，没有xml文件，所以使用类加载，或者用上面的包也可以
<!-- <mappers>-->
<!-- <mapper class="com.woniu.mapper.UserMapper"/>-->
<!-- </mappers>-->
- 1
- 2
- 3
为啥能用点 . ？
5.<properties>引入properties配置文件
加入
<properties resource="jdbc.properties"></properties>
- 1
在配置文件目录下，加入jdbc.properties
jdbc.driverClass = com.mysql.cj.jdbc.Driver
jdbc.connectionURL = jdbc:mysql://127.0.0.1:3306/javaweb?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
#jdbc.connectionURL = "jdbc:mysql://"+ip+"?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true"
jdbc.username = root
jdbc.password = 123
- 1
- 2
- 3
- 4
- 5
【注意】易错点& 和 &；
注意在xml文件中，要用
&
- 1
而在其他文件中，要用&
在数据库连接中，使用时，可以用如下方法
<property name="username" value="${jdbc.username}"/>
- 1
6.开启驼峰命名
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
- 1
- 2
- 3
全局配置文件的案例
要点：
- 1.开启驼峰命名；
- 2.让日志生效；
- 3.给所有实体类起别名，所有的别名都是首字母小写；
- 4.分页需要加拦截器；
- 5.连接数据库的驱动，路径，用户名和密码；其中url需要在&后面加 amp;
- 6.告诉配置文件，sql的xml文件在哪，java找；
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 数据库和实体类 驼峰的问题 ：开启驼峰命名-->
<settings>
<!-- 处理驼峰命名-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- 日志生效-->
<setting name="logImpl" value="LOG4J"/>
</settings>
<!-- 别名 给所有的实体类都起一个别名com.tianju.entity，所有别名都是类名的首字母小写-->
<typeAliases>
<package name="com.tianju.entity"/>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>typeAliases</span><span class="token punctuation">></span></span>
<!-- 分页需要加个拦截器-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>environments</span> <span class="token attr-name">default</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>development<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>environment</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>development<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>transactionManager</span> <span class="token attr-name">type</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>JDBC<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dataSource</span> <span class="token attr-name">type</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>POOLED<span class="token punctuation">"</span></span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>property</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>driver<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>com.mysql.cj.jdbc.Driver<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>property</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>url<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false<span class="token entity named-entity" title="&">&amp;</span>serverTimezone=Asia/Shanghai<span class="token entity named-entity" title="&">&amp;</span>allowPublicKeyRetrieval=true<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>property</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>username<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>root<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>property</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>password<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>123<span class="token punctuation">"</span></span><span class="token punctuation">/></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dataSource</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>environment</span><span class="token punctuation">></span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>environments</span><span class="token punctuation">></span></span>
<!-- 告诉配置文件，sql的xml文件在哪，java找用. 普通文件用 /-->
<mappers>
<!-- 简化方式，不然得写很多mapper-->
<!-- <mapper resource="com/tianju/dao/UserMapper.xml"/>-->
<package name="com.tianju.dao"/>
</mappers>
</configuration>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
三、增删改查
增加：如何新增一条数据【自动提交】
（1）把sqlSession提出来，最后提交一下
要点：
-
SqlSession sqlSession = null;提出来，在最后提交：sqlSession.commit(true);
-
测试的注解import org.junit.Before; 所有测试代码执行之前都会执行@Before注解下的代码；
-
@After，在@Test下面的方法中的代码执行后执行；
DEBUG ansaction.jdbc.JdbcTransaction - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@30ee2816]
package com.tianju;
import com.tianju.dao.UserMapper;
import com.tianju.entity.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 java.io.IOException;
import java.io.InputStream;
import java.util.Date;
public class MybatisTestDemo {
UserMapper userMapper = null;
SqlSession sqlSession = null;
<span class="token comment">// 每次运行其他test之前，都会先运行before</span>
<span class="token annotation punctuation">@Before</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">before</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">IOException</span> <span class="token punctuation">{<!-- --></span>
<span class="token comment">// 1.读取mybatis的配置文件</span>
<span class="token class-name">InputStream</span> is <span class="token operator">=</span> <span class="token class-name">Resources</span><span class="token punctuation">.</span><span class="token function">getResourceAsStream</span><span class="token punctuation">(</span><span class="token string">"mybatis-config.xml"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 2.生成一个工厂---这个工厂是通过建造者造出来的</span>
<span class="token class-name">SqlSessionFactoryBuilder</span> builder <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">SqlSessionFactoryBuilder</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 3.建造者根据mybatis-config.xml 建造一个SqlSessionFactory 工厂</span>
<span class="token class-name">SqlSessionFactory</span> sqlSessionFactory <span class="token operator">=</span> builder<span class="token punctuation">.</span><span class="token function">build</span><span class="token punctuation">(</span>is<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 4.通过sqlSessionFactory 生成一个 sqlSession</span>
sqlSession <span class="token operator">=</span> sqlSessionFactory<span class="token punctuation">.</span><span class="token function">openSession</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">// 5.mybatis会把所有的接口都用动态代理生成实现类，这些实现类都可以通过sqlSession获取</span>
userMapper <span class="token operator">=</span> sqlSession<span class="token punctuation">.</span><span class="token function">getMapper</span><span class="token punctuation">(</span><span class="token class-name">UserMapper</span><span class="token punctuation">.</span><span class="token keyword">class</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">"初始化方法"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
<span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">addUser</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">{<!-- --></span>
userMapper<span class="token punctuation">.</span><span class="token function">addUser</span><span class="token punctuation">(</span>
<span class="token keyword">new</span> <span class="token class-name">User</span><span class="token punctuation">(</span><span class="token keyword">null</span><span class="token punctuation">,</span> <span class="token string">"shirley567"</span><span class="token punctuation">,</span> <span class="token string">"123"</span><span class="token punctuation">,</span>
<span class="token keyword">new</span> <span class="token class-name">Date</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">"女"</span><span class="token punctuation">,</span> <span class="token string">"无锡"</span><span class="token punctuation">,</span> <span class="token number">28</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
<span class="token annotation punctuation">@After</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">after</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">{<!-- --></span>
<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">"结束测试运行"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
sqlSession<span class="token punctuation">.</span><span class="token function">commit</span><span class="token punctuation">(</span><span class="token boolean">true</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">"is here?"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
（2）一开始就打开自动提交
sqlSession = sqlSessionFactory.openSession(true);
- 1
（3）封装SqlSessionUtils.java工具类
要点：
- 读取mybatis配置文件，告诉那里读取；
- 生成一个建造sqlSession工厂的建造者，SqlSessionFactoryBuilder；
- 建造者SqlSessionFactoryBuilder根据mybatis-config.xml建造一个SqlSession工厂，SqlSessionFactory；
- 在SqlSession工厂中，通过SqlSessionFactory生成一个sqlSession，且打开自动提交sqlSession = sqlSessionFactory.openSession(true);
- 需要什么类型的mapper就提供什么类型的mapper，public static <T> T getMapper(Class<T> mapperClass){return sqlSession.getMapper(mapperClass);}；
package com.tianju.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 SqlSessionUtils {
private static SqlSession sqlSession;
static {
//1.读取mybatis配置文件
InputStream is = null;
try {
is = Resources.getResourceAsStream("mybatis-config.xml");
} catch (IOException e) {
throw new RuntimeException(e);
}
<span class="token comment">//2.生成一个工厂--这个工程是通过建造者建造出来的</span>
<span class="token class-name">SqlSessionFactoryBuilder</span> builder <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">SqlSessionFactoryBuilder</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">//3.建造者根据mybatis-config.xml建造一个SqlSession工厂</span>
<span class="token class-name">SqlSessionFactory</span> sqlSessionFactory <span class="token operator">=</span> builder<span class="token punctuation">.</span><span class="token function">build</span><span class="token punctuation">(</span>is<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">//4.通过sqlSessionFactory生成一个sqlSession</span>
sqlSession <span class="token operator">=</span> sqlSessionFactory<span class="token punctuation">.</span><span class="token function">openSession</span><span class="token punctuation">(</span><span class="token boolean">true</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">//如果设置为true,每次自动提交</span>
<span class="token punctuation">}</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token generics"><span class="token punctuation"><</span><span class="token class-name">T</span><span class="token punctuation">></span></span> <span class="token class-name">T</span> <span class="token function">getMapper</span><span class="token punctuation">(</span><span class="token class-name">Class</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">T</span><span class="token punctuation">></span></span> mapperClass<span class="token punctuation">)</span><span class="token punctuation">{<!-- --></span>
<span class="token keyword">return</span> sqlSession<span class="token punctuation">.</span><span class="token function">getMapper</span><span class="token punctuation">(</span>mapperClass<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
（4）新增一条数据，且获得自增的id；useGeneratedKeys=“true” keyProperty=“id”
Integer add(@Param("user") User user);
- 1
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--这个xml文件和 UserMapper这个接口对应-->
<mapper namespace="com.tianju.dao.UserMapper">
<!-- TODO:这样是不是可以获得新增的数据的id-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
INSERT INTO user_tab(id,username,password,nickname,img_url,create_time,update_time)
VALUES (null,#{user.username},#{user.password},#{user.nickname},null,now(),now())
</insert>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
删除：比如根据id删除一条数据
/**
* 根据id删除一条图书信息
* @param id id
* @return 影响的代码行数
*/
Integer removeById(Integer id);
- 1
- 2
- 3
- 4
- 5
- 6
<delete id="removeById" parameterType="integer">
DELETE FROM t_opus WHERE id = #{id}
</delete>
- 1
- 2
- 3
修改：如何只修改某个字段？【if语句】
存在问题，只想修改username和address，结果把其他字段都弄成了null
后台信息：
修改后数据库的数据，其他字段都成了null
（1）先查后改，繁琐不建议
（2）mybatis的if语句
/**
* 修改一条图书信息
*/
Integer updateById(@Param("opus") Opus opus);
- 1
- 2
- 3
- 4
<!-- 如果@Param("opus") 和上面对比-->
<update id="updateById">
UPDATE t_opus SET
<if test="opus.name!=null and opus.name!=''">
name=#{opus.name},
</if>
<!-- 如果的Integer类型的id，加上了不为''的条件-->
<if test="opus.typeId!=null">
<!-- 如果这里没有加 逗号 -->
typeId=#{opus.typeId},
</if>
<if test="opus.intro!=null and opus.intro!=''">
intro=#{opus.intro},
</if>
updateTime=now() WHERE id=#{opus.id}
</update>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
查询：如何传多个查询条件？【Where，if语句】
多个参数必须加 注解@param，指向接收的参数名字
List<Opus> queryByAuthorIdAndLikeNameAndTypeId(
@Param("typeId") Integer typeId,
@Param("name") String name,
@Param("authorId") Integer authorId);
- 1
- 2
- 3
- 4
<!-- 多种条件查询 加上类型查询-->
<select id="queryByAuthorIdAndLikeNameAndTypeId" resultType="opus">
SELECT t_opus.*,tt.name AS typename,tu.username
FROM t_opus
LEFT JOIN t_types tt ON t_opus.typeId = tt.id
LEFT JOIN user_tab tu ON t_opus.authorId = tu.id
<where>
<if test="typeId!=null">
typeId = #{typeId}
</if>
<if test="authorId!=null">
AND authorId = #{authorId}
</if>
<if test="name!=null and name!=''">
AND t_opus.name LIKE #{name}
</if>
</where>
ORDER BY t_opus.id DESC
</select>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
查询总数，聚合查询
<select id="findAllCount" resultType="int">
select count(*) from user
</select>
- 1
- 2
- 3
@Test
public void findAllCount(){
System.out.println(userMapper.findAllCount());
}
- 1
- 2
- 3
- 4
//查询总人数
int findAllCount();
- 1
- 2
四、xml文件中的parameterType和 resultType
1.查询全部resultType="user
注意这里的resultType，表示结果类型，查询出来的结果类型就是User类的类型，因为我们定义过别名，所以可以直接使用。
<select id="findAll" resultType="user">
select * from user
</select>
- 1
- 2
- 3
2.查询一条记录
这里又学习了一个，叫参数类型Integer，表示传入的参数，是integer类型的
User findById(Integer userId);
- 1
<select id="findById" resultType="user" parameterType="Integer">
select * from user where id=#{id}
</select>
- 1
- 2
- 3
3.#{}是占位符表达式 和 ognl 表达式Object Graphic Navigation Language 对象图导航语言
#{}是占位符表达式，对应的就是parameter 参数
-
sql 语句中使用#{}字符；
-
代表占位符，相当于原来 jdbc 部分所学的?，都是用于执行语句时替换实际的数据；
-
具体的数据是由#{}里面的内容决定的；
#{}中内容的写法：
由于我们保存方法的参数是 一个 User 对象，此处要写 User 对象中的属性名称。
它用的是 ognl 表达式。
ognl 表达式：它是 apache 提供的一种表达式语言，全称是：Object Graphic Navigation Language 对象图导航语言
-
它是按照一定的语法格式来获取数据的，语法格式就是使用 #{对象.对象}的方式；
-
#{user.username}会先去找 user 对象，然后在 user 对象中找到 username 属性，并调用；
-
getUsername()方法把值取出来。但是我们在 parameterType 属性上指定了实体类名称，所以可以省略 user. 而直接写 username；
4.#{}和${}的区别【面试常问】
#{}表示一个占位符号 ?
-
通过#{}可以实现 preparedStatement 向占位符中设置值，自动进行 java 类型和 jdbc 类型转换，
-
#{}可以有效防止 sql 注入。 #{}可以接收简单类型值或 pojo 属性值。 如果 parameterType 传输单个简单类型值，#{}括号中可以是 value 或其它名称。
${}表示拼接sql 串
- 通过${}可以将 parameterType 传入的内容拼接在 sql 中且不进行 jdbc 类型转换， ${}可以接收简单类型值或 pojo 属性值；
- 如果 parameterType 传输单个简单类型值，${}括号中只能是 value。
<select id="findByName" parameterType="string" resultType="user">
select * from user where username like '%${value}%'
</select>
- 1
- 2
- 3
注入漏洞的案例
<select id="denglu" parameterType="user" resultType="user">
select * from user where username='${username}' and password='${password}' limit 1
</select>
- 1
- 2
- 3
@Test
public void test3(){
User user = new User();
user.setUsername("madinglude");
user.setPassword("11' or '1'='1");
// user.setUsername("admin' and 1=1#");
User denglu = userMapper.denglu(user);
System.out.println(denglu);
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
select * from user where username='madinglude' and password='11' or '1'='1'
- 1
五、mybatis事务，自动提交
JDBC的事务中，我们是setAutoCommit()，来控制事务。
mybatis中，我们的默认配置是设置setAutoCommit()为false，执行insert语句参考日志：
source.pooled.PooledDataSource - Created connection 1526298704.
2021-07-08 18:44:10,660 1353 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5af97850]
如果要自动提交事物，我们可以修改如下：
sqlSession = sqlSessionFactory.openSession(true);
- 1
六、动态SQL语句
前面学习的sql语句都是单表操作，比较简单，如果碰到复杂的sql语句，就要用到动态SQL了。我们将学习到if where foeach等控制语句
转义符号.
< <= > >= & ' "
< <= > >= & ' "
- 1
- 2
1.<if>标签
设想 一种搜索情况，可以根据商品名称，价格，产地等等情况进行搜索，但是搜索的人也许只填写一个条件，也许填写多个，这是我们不知道的，所以在sql中要判断，一个条件是否输入了，这时候就要用到if了。
List<Opus> queryByAuthorIdAndLikeNameAndTypeId(
@Param("typeId") Integer typeId,
@Param("name") String name,
@Param("authorId") Integer authorId);
- 1
- 2
- 3
- 4
<!-- 多种条件查询 加上类型查询-->
<select id="queryByAuthorIdAndLikeNameAndTypeId" resultType="opus">
SELECT t_opus.*,tt.name AS typename,tu.username
FROM t_opus
LEFT JOIN t_types tt ON t_opus.typeId = tt.id
LEFT JOIN user_tab tu ON t_opus.authorId = tu.id
<where>
<if test="typeId!=null">
typeId = #{typeId}
</if>
<if test="authorId!=null">
AND authorId = #{authorId}
</if>
<if test="name!=null and name!=''">
AND t_opus.name LIKE #{name}
</if>
</where>
ORDER BY t_opus.id DESC
</select>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
2.<foreach>标签
（1）批量删除
/**
* 批量删除图书信息
* @param ids 要删除的id
* @return
*/
Integer removeBatch(@Param("ids") List<Integer> ids);
- 1
- 2
- 3
- 4
- 5
- 6
<!-- 批量删除，java文件写上注解-->
<delete id="removeBatch">
DELETE FROM t_opus WHERE id IN
<!-- open 开始是 ( 结束为右括号 ) 用逗号进行分隔 -->
<foreach collection="ids" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</delete>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
（2）批量插入
insert into user (username,birthday,sex,address)
values
('zsd',‘2010-01-01’，‘nan’,'南京'),
('zsd',‘2010-01-01’，‘nan’,'南京')，
('zsd',‘2010-01-01’，‘nan’,'南京')，
('zsd',‘2010-01-01’，‘nan’,'南京')，
('zsd',‘2010-01-01’，‘nan’,'南京');
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
要点：中间要用逗号，分割，最后是一个分号，或者不写；
void insertBath(List<User> list);
- 1
mybatis会自动把最后一个逗号，去掉；
<insert id="insertBath" parameterType="list" >
insert into user (username,birthday,sex,address) values
<foreach collection="list" item="user" separator=",">
(#{user.username},#{user.birthday},#{user.sex},#{user.address})
</foreach>
</insert>
- 1
- 2
- 3
- 4
- 5
- 6
特别注意：
mysql默认接受sql的大小是1048576(1M)，即批量插入方式若数据量超过1M会报如下异常：
（可通过调整MySQL安装目录下的my.ini文件中[mysqld]段的＂max_allowed_packet = 1M＂）
nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576).
You can change this value on the server by setting the max_allowed_packet' variable.
- 1
- 2
- 3
3.<set>标签
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
3.代码片段–不建议
<!-- 抽取重复的语句代码片段 -->
<sql id="defaultSql">
select * from user
</sql>
<!-- 配置查询所有操作 -->
<select id="findAll" resultType="user">
<include refid="defaultSql"></include>
</select>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
转义符号.
< <= > >= & ' "
< <= > >= & ' "
- 1
- 2
总结
1.mybatis是啥？持久层框架，关系型数据库，ORM（Object Relation Mapping）对象关系映射；
2.怎么用，全局配置文件mybatis-config.xml文件，驼峰命名，日志，起别名，配置连接数据库；
3.增删改查，如何用mybatis实现，select，update，insert，delete，#{}，${}；
4.mapper.xml文件，parameterType和resultType，@param注解；
5.mybatis事务初步，打开自动提交，sqlSessionFactory.openSession(true);
6.动态SQL入门，if，foreach，set，代码片段，常见的转义符；