MyBatis
1. 入门
编写一个入门级别的mybatis案例:
创建一个maven工程
导入依赖,在该工程的pom中,并且日志文件加入其中(log4j):
<?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"> <parent> <artifactId>SSM</artifactId> <groupId>com.fuli</groupId> <version>1.0-SNAPSHOT</version> </parent> <modelVersion>4.0.0</modelVersion> <artifactId>mybatis__helloworld</artifactId> <!--java工程使用jar包,Web工程才打war包--> <packaging>jar</packaging> <dependencies> <!--mybatis的依赖--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.7</version> </dependency> <!--junit的依赖--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <!--mysql的依赖--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.16</version> </dependency> <!--log4j日志--> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> </dependencies> </project>
log4j.xml:
log4j的配置文件名为log4j.xml(必须为这个),存放的位置是
src/main/resources
目录下<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd"> <!--下面一句会爆红,不过没关系--> <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/"> <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender"> <param name="Encoding" value="UTF-8"/> <layout class="org.apache.log4j.PatternLayout"> <param name="ConversionPattern" value="%-5p%d{MM-ddHH:mm:ss,SSS}%m(%F:%L)\n"/> </layout> </appender> <logger name="java.sql"> <level value="debug"/> </logger> <logger name="org.apache.ibatis"> <level value="info"/> </logger> <root> <level value="debug"/> <appender-ref ref="STDOUT"/> </root> </log4j:configuration>
创建一个数据库(ssm)和表(t_user)
列名 | 数据类型 | 长度 | 主键 | 非空 | 自增 |
---|---|---|---|---|---|
id | int | √ | √ | √ | |
username | varchar | 20 | |||
password | varchar | 20 | |||
age | int | ||||
gender | char | ||||
varchar | 50 |
创建User类(必须提供无参构造)
package com.fuli.mybatis.pojo; public class User { private Integer id; private String username; private String password; private Integer age; private String gender; private String email; public User() { } public User(Integer id, String username, String password, Integer age, String gender, String email) { this.id = id; this.username = username; this.password = password; this.age = age; this.gender = gender; this.email = email; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", age=" + age + ", gender='" + gender + '\'' + ", email='" + email + '\'' + '}'; } }
创建mybatis-config.xml核心配置文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!--设置连接数据库的环境--> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///ssm?serverTimezone=UTC"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <!--引入映射文件--> <mappers> <!--我是在当前一层有创了个mapper文件夹,然后UserMapper.xml放在里面的--> <mapper resource="mapper/UserMapper.xml"/> </mappers> </configuration>
创建mapper接口——一般如果是User类的mapper就写
UserMapper
package com.fuli.mybatis.mapper; public interface UserMapper { int insertUser(); }
根据创建的Usermapper接口来写
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接口的全类名--> <mapper namespace="com.fuli.mybatis.mapper.UserMapper"> <!--这个id要写UserMapper接口中的方法名--> <insert id="insertUser"> insert into t_user values(null,'刘乙洁','123',22,'男','123@qq.com') </insert> </mapper>
编写测试类(在测试包下写)
package com.fuli.mybatis.test; import com.fuli.mybatis.mapper.UserMapper; 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; public class MyTest { @Test public void insertTest() throws IOException { //1. 读取mybatis的核心配置文件(注意这里的Resources类是ibatis的) InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); //2. 创建SqlSessionFactoryBuilder对象 SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); //3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象 SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream); //4. 创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务;如下: //SqlSession sqlSession = sqlSessionFactory.openSession(); //sqlSession.commit();//手动提交;如果不手动提交,默认会回滚事务 //采用下面的方法就会自动提交 SqlSession sqlSession = sqlSessionFactory.openSession(true); //5. 通过代理模式来创建UserMapper对象,因为我们自己无法直接创建UserMapper对象,因为他是接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); int res = userMapper.insertUser(); System.out.println("影响的行数:"+res); //6. 关闭session sqlSession.close(); } }
2. 增删改查
在上一节的基础上来完成此功能
由于要写多个测试方法,所以上面的获取sqlsession对象要重复很多次,所以我们把它抽取出来设计为一个工具类(util)
package com.fuli.mybatis.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 SqlSessionUtil { //一般工具类的方法都设置为静态方法 public static SqlSession getSqlSession(){ SqlSession session = null; try { //1. 加载mybatis的核心配置文件 InputStream is = Resources.getResourceAsStream("mybatis-config.xml"); //2. 获取SqlSessionFactoryBuilder对象 SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); //3. 获取SqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is); //4. 获取sqlsession对象 session = sqlSessionFactory.openSession(true); } catch (IOException e) { e.printStackTrace(); } return session; } }
UserMapper接口类
package com.fuli.mybatis.mapper; import com.fuli.mybatis.pojo.User; import java.util.List; public interface UserMapper { /** * 插入数据 * @return */ int insertUser(); /** * 更新用户信息 */ void updateUser(); /** * 删除用户信息 */ void deleteUser(); /** * 根据id信息查询用户信息 * @return */ User selectById(); /** * 查询所有用户信息 * @return */ List<User> selectAllUser(); }
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接口的全类名--> <mapper namespace="com.fuli.mybatis.mapper.UserMapper"> <!--这个id要写UserMapper接口中的方法名--> <insert id="insertUser"> insert into t_user values(null,'刘乙洁','123',22,'男','123@qq.com') </insert> <update id="updateUser"> update t_user set password = '888' where id = 2; </update> <delete id="deleteUser"> delete from t_user where id = 2 </delete> <!-- resultType:设置结果类型,即查询的数据要转换为的java类型 resultMap:自定义类型,处理多对一或一对多的映射关系 --> <select id="selectById" resultType="com.fuli.mybatis.pojo.User"> select * from t_user where id = 1; </select> <select id="selectAllUser" resultType="com.fuli.mybatis.pojo.User"> select * from t_user; </select> </mapper>
测试类
package com.fuli.mybatis.test; import com.fuli.mybatis.mapper.UserMapper; import com.fuli.mybatis.pojo.User; import com.fuli.mybatis.util.SqlSessionUtil; import com.mysql.cj.Session; 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.List; public class MyTest { @Test public void testUpdateUser(){ //使用自己的工具类来获取sqlsession对象 SqlSession sqlSession = SqlSessionUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); mapper.updateUser(); sqlSession.close(); } @Test public void testDeleteUser(){ //使用自己的工具类来获取sqlsession对象 SqlSession sqlSession = SqlSessionUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); mapper.deleteUser(); sqlSession.close(); } @Test public void testSelectById(){ //使用自己的工具类来获取sqlsession对象 SqlSession sqlSession = SqlSessionUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.selectById(); System.out.println(user); sqlSession.close(); } @Test public void testSelectAllUser(){ //使用自己的工具类来获取sqlsession对象 SqlSession sqlSession = SqlSessionUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> users = mapper.selectAllUser(); System.out.println(users); sqlSession.close(); } }
3. MyBatis核心配置文件
①environments(只看environments部分)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--设置连接数据库的环境-->
<!--
environments:配置多个连接数据库的环境
属性:
default:设置默认使用的环境id
-->
<environments default="development">
<!--
environment:配置某个具体的环境
属性:
id:表示连接数据库环境的唯一标识,不能重复
-->
<environment id="development">
<!--
transactionManager:设置事务管理的方式
属性:
type="JDBC/MANAGED"
JDBC:表示当前环境中,执行SQL时,使用的是JDBC中原生的事务管理方式,例如事务的提交或回滚需要手动处理
MANAGED:被管理,例如Spring
-->
<transactionManager type="JDBC"/>
<!--
dataSource:配置数据源
属性:
type:设置数据源类型
type="POOLED/UNPOOLED/JNDI"
POOLED:表示使用数据库连接池缓存数据库连接
UNPOOLED:表示不使用数据库连接池
JNDI:表示使用上下文中的数据源
-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///ssm?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--引入隐射文件-->
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
②properties(只看properties部分)
先写properties文件:
#为了防止以后多个properties文件中的键出现重名的现象,我们一般加个前缀;当前文件和mapper-config.xml在一个目录下
#即在src/main/resources下
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql:///ssm?serverTimezone=UTC
jdbc.username=root
jdbc.password=root
在mapper-config.xml核心配置文件中引入properties文件
<?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文件,此后就可以在当前文件中使用${key}的方式访问value;见下面的DataSource部分-->
<properties resource="jdbc.properties"/>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--引入隐射文件-->
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
③typeAliases(只看typeAliases部分)
<?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>
<!--注意:里面的这些标签是有先后顺序的,顺序不对的话configuration会报错的-->
<properties resource="jdbc.properties"/>
<!--设置别名之后,映射文件,如UserMapper.xml文件中的resultType或resultMap均可以写别名,不用写全类名了-->
<typeAliases>
<!--
typeAlias:设置某个类型的别名
属性:
type:设置需要设置别名的类型
alias:设置某个类型的别名,若不设置该属性,那么该类型拥有默认的别名,即类名,且不区分大小写
-->
<!--<typeAlias type="com.fuli.mybatis.pojo.User" alias="abc"/>-->
<!--<typeAlias type="com.fuli.mybatis.pojo.User"/>-->
<!-- 以包为单位, 将包下所有的类型设置默认的类型别名, 即类名且不区分大小写 -->
<package name="com.fuli.mybatis.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--引入隐射文件-->
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
④Mappers(只看Mappers部分)
<?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="jdbc.properties"/>
<typeAliases>
<package name="com.fuli.mybatis.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--引入映射文件-->
<mappers>
<!--<mapper resource="mapper/UserMapper.xml"/>-->
<!--
以包的方式引入映射文件,但是必须满足两个条件
1.mapper接口和映射文件所在的包必须一致
2.mapper接口的名字和映射文件的名字必须一致
-->
<package name="com.fuli.mybatis.mapper"/>
</mappers>
</configuration>
注意:要想用package的方式来引入某个包下的所有映射文件,必须要在resources目录下创建与mapper接口相同的包
操作如下:
点击resources,右键New—>Directory–>写名字;因为我的==mapper接口==所在的包为com.fuli.mybatis.mapper;
所以我在写名字的时候就要写com/fuli/mybatis/mapper
,然后把mapper.xml
放入该文件夹中
4. MyBatis获取参数的两种方式
${}
的本质就是字符串拼接,#{}
的本质就是占位符赋值
${}
使用字符串拼接的方式拼接sql,若为字符串类型或日期类型的字段进行赋值时,需要手动加单引号;但是#{}
使用占位符赋值的方式拼接sql,此时为字符串类型或日期类型的字段进行赋值时,可以自动添加单引号
①单个字面量类型的参数
若mapper接口中的方法参数为单个的字面量类型,此时可以使用${}
和#{}
以任意的名称 获取参数值,注意${}
需要手动添加单引号,其中{}
中的内容在单个字面量类型的参数的时候可以随便写。
UserMapper接口:
/**
* 通过用户名查询用户信息(单个字面量类型的参数)
* @param username
* @return
*/
User selectUserByUsername(String username);
使用#{}
时UserMapper.xml
<select id="selectUserByUsername" resultType="User">
<!--使用#{内容}的时候,不需要加单引号,并且其中的内容可以随便写(前提是单个参数)-->
select * from t_user where username = #{username}
</select>
测试类:
@Test
public void testSelectUserByUsername(){
//使用自己的工具类来获取sqlsession对象
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.selectUserByUsername("刘乙洁");
System.out.println(user);
}
使用#{}
时的日志文件:
//这里是问号,也就是说使用#{}的时候本质是用占位符赋值的
DEBUG01-1013:24:16,970==> Preparing: select * from t_user where username = ?(BaseJdbcLogger.java:137)
DEBUG01-1013:24:16,994==> Parameters: 刘乙洁(String)(BaseJdbcLogger.java:137)
DEBUG01-1013:24:17,012<== Total: 1(BaseJdbcLogge r.java:137)
User{id=1, username='刘乙洁', password='123', age=22, gender='男', email='123@qq.com'}
使用${}
时UserMapper.xml
<select id="selectUserByUsername" resultType="User">
<!--使用${内容}的时候,必须加单引号,并且其中的内容可以随便写(前提是单个参数)-->
select * from t_user where username = '${username}'
</select>
使用${}
时的日志文件:
//这里是字符串拼接,也就是说使用${}的时候本质是用字符串拼接的
DEBUG01-1013:28:19,109==> Preparing: select * from t_user where username = '刘乙洁'(BaseJdbcLogger.java:137)
DEBUG01-1013:28:19,129==> Parameters: (BaseJdbcLogger.java:137)
DEBUG01-1013:28:19,146<== Total: 1(BaseJdbcLogger.java:137)
User{id=1, username='刘乙洁', password='123', age=22, gender='男', email='123@qq.com'}
②多个字面量类型的参数
若mapper接口中的方法参数为多个时,此时MyBatis会自动将这些参数放在一个map集合中,以arg0,arg1…为键,以参数为值;以param1,param2…为键,以参数为值;因此只需要通过${}
和#{}
访问map集合的键就可以获取相对应的值,注意${}
需要手动加单引号。
UserMapper接口:
/**
* 根据用户名和密码验证登录(多个个字面量类型的参数)
* @param username
* @param password
*/
void checkLogin(String username,String password);
UserMapper.xml:
此时#{}和${}中的内容就不能随便写了,如果按照下面的来写,会报错:
<select id="checkLogin" resultType="User">
select * from t_user where username = #{username} and password = #{password};
</select>
测试类:
@Test
public void testCheckLogin(){
//使用自己的工具类来获取sqlsession对象
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.checkLogin("刘乙洁","123");
}
此时日志文件报错:
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.apache.ibatis.binding.BindingException: Parameter 'username' not found. Available parameters are [arg1, arg0, param1, param2]
### Cause: org.apache.ibatis.binding.BindingException: Parameter 'username' not found. Available parameters are [arg1, arg0, param1, param2]
显示的错误是绑定错误
,可用的参数是[arg1, arg0, param1, param2]
,也就是说我们${}
和#{}
中的内容不能随便写了,要写arg0
或者param1
等
修改过后的UserMapper.xml
<select id="checkLogin" resultType="User">
<!--select * from t_user where username = #{arg0} and password = #{arg1};-->
<!--或者-->
select * from t_user where username = #{param1} and password = #{param2};
</select>
日志:
DEBUG01-1013:43:43,028==> Preparing: select * from t_user where username = ? and password = ?;(BaseJdbcLogger.java:137)
DEBUG01-1013:43:43,049==> Parameters: 刘乙洁(String), 123(String)(BaseJdbcLogger.java:137)
DEBUG01-1013:43:43,066<== Total: 1(BaseJdbcLogger.java:137)
③实体类型的参数
若mapper接口中的方法参数为实体类对象时,此时可以使用${}
和#{}
,通过访问实体类对象中的属性名获取属性值,注意${}
需要手动加单引号
UserMapper接口
/**
* 添加用户信息(实体类参数)
* @param user
*/
void insertUser(User user);
UserMapper.xml:这里的#{}
中内容要为属性值,即是在实体类中,get和set方法中去掉get和set之后的字段小写就是属性;例如getId();属性就是id;setUsername();属性就是username
<insert id="insertUser">
insert into t_user values(#{id},#{username},#{password},#{age},#{gender},#{email})
</insert>
测试类:
@Test
public void testInsertUser(){
//使用自己的工具类来获取sqlsession对象
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User(null, "张三", "222", 18,"男", "234@263.com");
mapper.insertUser(user);
}
日志:
DEBUG01-1013:52:41,381==> Preparing: insert into t_user values(?,?,?,?,?,?)(BaseJdbcLogger.java:137)
DEBUG01-1013:52:41,403==> Parameters: null, 张三(String), 222(String), 18(Integer), 男(String), 234@263.com(String)(BaseJdbcLogger.java:137)
DEBUG01-1013:52:41,408<== Updates: 1(BaseJdbcLogger.java:137)
④使用@param注解
可以通过@Param
注解标识mapper接口中的方法参数,此时,会将这些参数放在map集合中,以@Param
注解的value属性值为键,以参数为值;以param1,param2…为键,以参数为值;只需要通过${}
和#{}
访问map集合的键就可以获取相对应的值,注意${}
需要手动加单引号
简单的来说也就是使用@param
注解之后,里面的值就是#{}
中的内容
UserMapper接口
/**
* 根据用户名和密码来查找用户信息
* @param username
* @param password
* @return
*/
User selectUserByNameAndPWD(@Param("name") String username, @Param("pwd") String password);
UserMapper.xml
<select id="selectUserByNameAndPWD" resultType="User">
select * from t_user where username = #{name} and password = #{pwd}
</select>
测试类:
@Test
public void testSelectUserByNameAndPWD(){
//使用自己的工具类来获取sqlsession对象
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.selectUserByNameAndPWD("张三", "222");
System.out.println(user);
}
日志:
DEBUG01-1014:03:42,757==> Preparing: select * from t_user where username = ? and password = ?(BaseJdbcLogger.java:137)
DEBUG01-1014:03:42,778==> Parameters: 张三(String), 222(String)(BaseJdbcLogger.java:137)
DEBUG01-1014:03:42,795<== Total: 1(BaseJdbcLogger.java:137)
User{id=2, username='张三', password='222', age=18, gender='男', email='234@263.com'}
⑤小结
${}
和#{}
的用法完全一样,只是${}
需要加上单引号,而#{}
不需要- 获取参数总的来说,只需要记住实体类型的参数和**@param**注解即可,这两个用的特别多,遇到单字面量类型的参数的时候,也就建议使用
@param
注解
5. MyBatis处理特殊SQL
①处理模糊查询
基本语句:select * from t_user where username like '%你的内容%'
SelectLikeMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface SelectLikeMapper {
/**
* 通过模糊查询来查找用户信息
* @param like
* @return
*/
List<User> getUserByLike(@Param("like") String like);
}
SelectLikeMapper.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.fuli.mybatis.mapper.SelectLikeMapper">
<!--User getUserByLike(@Param("like") String like);-->
<select id="getUserByLike" resultType="User">
<!--推荐写这个-->
select * from t_user where username like "%"#{like}"%"
<!--还可以这样写-->
<!--select * from t_user where username like "%${like}%"-->
<!--也可以这样写-->
<!--select * from t_user where username like concat("%",#{like},"%")-->
</select>
</mapper>
测试类:
package com.fuli.mybatis.test;
import com.fuli.mybatis.mapper.SelectLikeMapper;
import com.fuli.mybatis.pojo.User;
import com.fuli.mybatis.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class SelectLikeTest {
@Test
public void testgetUserByLike(){
SqlSession sqlSesssion = SqlSessionUtil.getSqlSesssion();
SelectLikeMapper mapper = sqlSesssion.getMapper(SelectLikeMapper.class);
List<User> list = mapper.getUserByLike("张");
System.out.println(list);
sqlSesssion.close();
}
}
②处理批量删除
BatchDeleteUserMapper接口:
package com.fuli.mybatis.mapper;
import org.apache.ibatis.annotations.Param;
public interface BatchDeleteUserMapper {
/**
* 根据id来实现批量删除用户信息
* @param id
*/
void batchDeleteUserById(@Param("ids") String id);
}
BatchDeleteUserMapper.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.fuli.mybatis.mapper.BatchDeleteUserMapper">
<delete id="batchDeleteUserById">
<!--这里只能使用${}不能用#{},因为如果你用#{}SQL语句会是'你传递的id',会有SQL语法错误-->
delete from t_user where id in(${ids})
</delete>
</mapper>
测试类
package com.fuli.mybatis.test;
import com.fuli.mybatis.mapper.BatchDeleteUserMapper;
import com.fuli.mybatis.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class BatchDeleteUserTest {
@Test
public void testBatchDeleteUserById(){
SqlSession sqlSesssion = SqlSessionUtil.getSqlSesssion();
BatchDeleteUserMapper mapper = sqlSesssion.getMapper(BatchDeleteUserMapper.class);
mapper.batchDeleteUserById("4,5,6,7");
}
}
③动态设置表名
主要还是看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.fuli.mybatis.mapper.对应的Mapper接口">
<select id="对应的Mapper接口中的方法名">
<!--这里只能使用${}不能用#{},问题还是出在单引号上,所以使用${}就没有单引号问题-->
select * from ${tableName}
</select>
</mapper>
④获取自增的主键
AutoPrimaryKeyMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.User;
public interface AutoPrimaryKeyMapper {
/**
* 通过添加用户,然后获取自增的主键(即新增的这个用户的主键)
* @param user
*/
void insertUser(User user);
}
AutoPrimaryKeyMapper.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.fuli.mybatis.mapper.AutoPrimaryKeyMapper">
<!--
useGeneratedKeys:表示当前添加功能使用自增的主键
keyProperty:表示将添加的数据的自增的主键存入实体类中的id属性中
-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user values(null,#{username},#{password},#{age},#{gender},#{email})
</insert>
</mapper>
测试类
package com.fuli.mybatis.test;
import com.fuli.mybatis.mapper.AutoPrimaryKeyMapper;
import com.fuli.mybatis.pojo.User;
import com.fuli.mybatis.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class AutoPrimaryKeyTest {
@Test
public void testAutoPrimaryKey(){
SqlSession sqlSesssion = SqlSessionUtil.getSqlSesssion();
AutoPrimaryKeyMapper mapper = sqlSesssion.getMapper(AutoPrimaryKeyMapper.class);
User user = new User(null, "打哈哈", "444", 56, "男", "999@qq.com");
mapper.insertUser(user);
System.out.println(user);
}
}
6. 自定义映射ResultMap
准备工作,创建两个表:员工表t_emp
和部门表t_dept
员工表t_emp
:
列名 | 数据类型 | 长度 | 主键 | 非空 | 自增 |
---|---|---|---|---|---|
emp_id | int | √ | √ | √ | |
emp_name | varchar | 20 | |||
age | int | ||||
gender | char | ||||
dept_id | int |
部门表t_dept
:
列名 | 数据类型 | 长度 | 主键 | 非空 | 自增 |
---|---|---|---|---|---|
dept_id | int | √ | √ | √ | |
dept_name | varchar | 20 |
①使用全局配置来解决字段名和属性名不一致的情况
这个问题主要就是数据库无法使用驼峰命名,而Java中的属性一般使用驼峰命名的,所以在访问数据库的时候,字段与属性就无法匹配,可能导致数据出错
两个实体类:
package com.fuli.mybatis.pojo;
public class Dept {
private Integer deptId;
private String deptName;
public Dept(Integer deptId, String deptName) {
this.deptId = deptId;
this.deptName = deptName;
}
public Dept() {
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
@Override
public String toString() {
return "Dept{" +
"deptId=" + deptId +
", deptName='" + deptName + '\'' +
'}';
}
}
package com.fuli.mybatis.pojo;
public class Emp {
private Integer empId;
private String empName;
private Integer age;
private String gender;
private Integer deptId;
public Emp(Integer empId, String empName, Integer age, String gender, Integer deptId) {
this.empId = empId;
this.empName = empName;
this.age = age;
this.gender = gender;
this.deptId = deptId;
}
public Emp() {
}
public Integer getEmpId() {
return empId;
}
public void setEmpId(Integer empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
@Override
public String toString() {
return "Emp{" +
"empId=" + empId +
", empName='" + empName + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
", deptId=" + deptId +
'}';
}
}
EmpMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface EmpMapper {
/**
* 根据id来获取员工信息
* @param id
* @return
*/
Emp getEmpById(@Param("id") Integer id);
}
EmpMapper.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.fuli.mybatis.mapper.EmpMapper">
<!--Emp getEmpById(@Param("id") Integer id);-->
<select id="getEmpById" resultType="Emp">
select * from t_emp where emp_id = #{id}
</select>
</mapper>
测试类:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.Emp;
import com.fuli.mybatis.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class ResultMapTest {
@Test
public void testGetEmpById(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpById(1);
System.out.println(emp);
}
}
结果日志:
DEBUG01-1017:55:19,218==> Preparing: select * from t_emp where emp_id = ?(BaseJdbcLogger.java:137)
DEBUG01-1017:55:19,239==> Parameters: 1(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1017:55:19,257<== Total: 1(BaseJdbcLogger.java:137)
Emp{empId=null, empName='null', age=20, gender='男', deptId=null}
可以看见empId和empName
的值为null,出现这种情况的原因是因为数据库中的字段是用下划线命名的,而Java中的实体类的属性是驼峰命名的,例如数据库中:emp_id
;Java实体类中:empId
;
因此要解决上面的问题就得在Mybatis的核心配置文件中配置一下setting标签,如下所示:
<?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="jdbc.properties"/>
<settings>
<!--这个配置的目的就是将下划线转换为驼峰,默认是false-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<package name="com.fuli.mybatis.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--引入映射文件-->
<mappers>
<package name="com.fuli.mybatis.mapper"/>
</mappers>
</configuration>
此时再次运行结果日志:
DEBUG01-1018:03:25,452==> Preparing: select * from t_emp where emp_id = ?(BaseJdbcLogger.java:137)
DEBUG01-1018:03:25,474==> Parameters: 1(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1018:03:25,491<== Total: 1(BaseJdbcLogger.java:137)
Emp{empId=1, empName='张三', age=20, gender='男', deptId=1}
或者使用另外一种常用的方法,即用ResultMap来解决上面的问题:
EmpMapper.xml:核心配置文件中的setting已经注销
<?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.fuli.mybatis.mapper.EmpMapper">
<!--使用ResultMap来解决字段名与属性名不一致的问题-->
<!--
resultMap:设置自定义的映射关系
id:唯一标识,这个id用于select、update标签中resultMap的位置,如下面的select标签
type:处理映射关系的实体类型
id标签:处理主键和实体类中属性的映射关系
result标签:处理普通字段和实体类中属性的映射关系
column:设置映射关系的字段名,即你数据库的字段
property:设置映射关系的属性名,即你实体类中的属性
-->
<resultMap id="empResultMap" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<result column="dep_id" property="depId"></result>
</resultMap>
<select id="getEmpById" resultMap="empResultMap">
select * from t_emp where emp_id = #{id}
</select>
</mapper>
结果日志也和上面一样
最后在也可以在写SQL语句的时候使用别名来解决:
<?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.fuli.mybatis.mapper.EmpMapper">
<select id="getEmpById" resultType="Emp">
select emp_id empId,emp_name empName,dep_id depId from t_emp where emp_id = #{id}
</select>
</mapper>
②ResultMap处理多对一和一对一的映射关系(三种方式)
方式一:级联查询:
package com.fuli.mybatis.pojo;
public class Emp {
private Integer empId;
private String empName;
private Integer age;
private String gender;
private Dept dept;//部门类,多个员工对应一个部门(也可以这样理解:一个员工对应一个部门)
public Emp(Integer empId, String empName, Integer age, String gender, Dept dept) {
this.empId = empId;
this.empName = empName;
this.age = age;
this.gender = gender;
this.dept = dept;
}
public Emp() {
}
public Integer getEmpId() {
return empId;
}
public void setEmpId(Integer empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Emp{" +
"empId=" + empId +
", empName='" + empName + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
", dept=" + dept +
'}';
}
}
/**
* 通过员工id来获取员工的信息以及对应的部门
* @param empId
* @return
*/
Emp getEmpAndDeptByEmpId(@Param("empId") Integer empId);
<!--Emp getEmpAndDeptByEmpId(@Param("empId") Integer empId);-->
<!--方式一:级联查询-->
<resultMap id="empAndDeptResultMap" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<result column="dept_id" property="dept.deptId"></result>
<result column="dept_name" property="dept.deptName"></result>
</resultMap>
<select id="getEmpAndDeptByEmpId" resultMap="empAndDeptResultMap">
select * from t_emp,t_dept where emp_id = #{empId} and t_emp.dept_id = t_dept.dept_id
</select>
@Test
public void testGetEmpAndDeptByEmpId(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpAndDeptByEmpId(1);
System.out.println(emp);
}
日志结果:
DEBUG01-1020:38:44,038==> Preparing: select * from t_emp,t_dept where emp_id = ? and t_emp.dept_id = t_dept.dept_id(BaseJdbcLogger.java:137)
DEBUG01-1020:38:44,058==> Parameters: 1(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1020:38:44,076<== Total: 1(BaseJdbcLogger.java:137)
Emp{empId=1, empName='张三', age=20, gender='男', dept=Dept{deptId=1, deptName='A'}}
方式二,通过association标签实现:
<resultMap id="empAndDeptResultMap" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<!--
association 就是用来处理多对一和一对一的映射关系的
property:是实体类中对应的实体类型的属性
javaType:类型别名
-->
<association property="dept" javaType="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
</association>
</resultMap>
<select id="getEmpAndDeptByEmpId" resultMap="empAndDeptResultMap">
select * from t_emp,t_dept where emp_id = #{empId} and t_emp.dept_id = t_dept.dept_id
</select>
方式三,分步查询(重要)
EmpMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface EmpMapper {
/**
* 分步查询第一步,先获取员工的信息
* @param empId
* @return
*/
Emp getEmpAndDeptByEmpIdOneStep(@Param("empId") Integer empId);
}
DeptMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.Dept;
import org.apache.ibatis.annotations.Param;
public interface DeptMapper {
/**
* 分步查询的第二步,根据第一步查询出来的dept_id来查询部门信息
* @param deptId
* @return
*/
Dept getEmpAndDeptByEmpIdTwoStep(@Param("deptId") Integer deptId);
}
EmpMapper.xml:
<resultMap id="empAndDeptResultMapStepOne" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<!--
property:写实体类类型的属性
select:下一步查询的唯一标识,也就是下一步查询的全类名.方法名
column:下一步查询所需要的参数(名字要和数据库中的字段一样)
-->
<association property="dept"
select="com.fuli.mybatis.mapper.DeptMapper.getEmpAndDeptByEmpIdTwoStep"
column="dept_id"></association>
</resultMap>
<select id="getEmpAndDeptByEmpIdOneStep" resultMap="empAndDeptResultMapStepOne">
<!--分步查询,先查员工信息-->
select * from t_emp where emp_id = #{empId}
</select>
DeptMapper.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.fuli.mybatis.mapper.DeptMapper">
<resultMap id="empAndDeptResultMapStepTwo" type="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
</resultMap>
<select id="getEmpAndDeptByEmpIdTwoStep" resultMap="empAndDeptResultMapStepTwo">
<!--分步查询,再查部门信息-->
select * from t_dept where dept_id = #{deptId}
</select>
</mapper>
测试类:
@Test
public void testGetEmpAndDeptByStep(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpAndDeptByEmpIdOneStep(2);
System.out.println(emp);
}
日志结果:
//第一步查询
DEBUG01-1021:06:14,533==> Preparing: select * from t_emp where emp_id = ?(BaseJdbcLogger.java:137)
DEBUG01-1021:06:14,556==> Parameters: 2(Integer)(BaseJdbcLogger.java:137)
//第二步查询
DEBUG01-1021:06:14,575====> Preparing: select * from t_dept where dept_id = ?(BaseJdbcLogger.java:137)
DEBUG01-1021:06:14,575====> Parameters: 1(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1021:06:14,577<==== Total: 1(BaseJdbcLogger.java:137)
DEBUG01-1021:06:14,578<== Total: 1(BaseJdbcLogger.java:137)
Emp{empId=2, empName='李四', age=30, gender='男', dept=Dept{deptId=1, deptName='A'}}
③延迟加载
分步查询的优点:可以实现延迟加载
实现延迟加载必须在核心配置文件中进行配置:lazyLoadingEnabled
和aggressiveLazyLoading
最好同时都要设置
<settings>
<!--
lazyLoadingEnabled:
延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。
特定关联关系中可通过设置 fetchType 属性来覆盖该项的开关状态(即是在非核心mapper.xml文件中的association标签中开启)
aggressiveLazyLoading:
开启时,任一方法的调用都会加载该对象的所有延迟加载属性。
否则,每个延迟加载属性会按需加载(参考 lazyLoadTriggerMethods)。
在 3.4.1 及之前的版本中默认为 true
-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
测试:
当我们只需要查询用户的姓名的时候,就不用全部执行,只需要指向一部分SQL语句即可
@Test
public void testGetEmpAndDeptByStep(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpAndDeptByEmpIdOneStep(2);
System.out.println(emp.getEmpName());//只查询员工的姓名
}
日志结果:
DEBUG01-1021:17:10,936==> Preparing: select * from t_emp where emp_id = ?(BaseJdbcLogger.java:137)
DEBUG01-1021:17:10,961==> Parameters: 2(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1021:17:11,008<== Total: 1(BaseJdbcLogger.java:137)
李四
可以看见我们只执行了一部分的SQL,但是如果你没有开启延迟加载,那么就会执行你的所有分步查询
注意:
- 因为这个延迟加载是配置在核心配置文件中的,会导致所有的
mapper.xml
文件中的分步查询都生效,所以如果你想要一部分==不==延迟加载,你可以再对于的mapper.xml
文件中的association
标签中添加fetchType
属性,设置为eager
即可
④ResultMap处理一对多的映射关系(两种方式)
方式一,使用collection标签
package com.fuli.mybatis.pojo;
import java.util.List;
public class Dept {
private Integer deptId;
private String deptName;
private List<Emp> emps;//多个员工对应一个部门,那么反过来,一个部门里就有多个员工
public Dept(Integer deptId, String deptName, List<Emp> emps) {
this.deptId = deptId;
this.deptName = deptName;
this.emps = emps;
}
public Dept() {
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public List<Emp> getEmps() {
return emps;
}
public void setEmps(List<Emp> emps) {
this.emps = emps;
}
@Override
public String toString() {
return "Dept{" +
"deptId=" + deptId +
", deptName='" + deptName + '\'' +
", emps=" + emps +
'}';
}
}
DeptMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.Dept;
import org.apache.ibatis.annotations.Param;
public interface DeptMapper {
/**
* 查询一个部门中所有员工的信息
* @param deptId
* @return
*/
Dept getDeptAndEmpByDeptId(@Param("deptId") Integer deptId);//注意返回值
}
DeptMapper.xml
<resultMap id="deptAndEmpResultMap" type="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
<!--
collection:处理一对多的映射关系
ofType:设置collection标签所处理的集合属性中存储数据的类型
-->
<collection property="emps" ofType="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
</collection>
</resultMap>
<!--Dept getDeptAndEmpByDeptId(@Param("deptId") Integer deptId);-->
<select id="getDeptAndEmpByDeptId" resultMap="deptAndEmpResultMap">
select * from t_emp,t_dept where t_dept.dept_id = #{deptId} and t_dept.dept_id = t_emp.dept_id
</select>
测试类:
@Test
public void testGetDeptAndEmpByDeptId(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = mapper.getDeptAndEmpByDeptId(1);
System.out.println(dept);
}
日志结果:
DEBUG01-1021:44:36,947==> Preparing: select * from t_emp,t_dept where t_dept.dept_id = ? and t_dept.dept_id = t_emp.dept_id(BaseJdbcLogger.java:137)
DEBUG01-1021:44:36,969==> Parameters: 1(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1021:44:36,987<== Total: 2(BaseJdbcLogger.java:137)
Dept{deptId=1, deptName='A', emps=[Emp{empId=1, empName='张三', age=20, gender='男', dept=null}, Emp{empId=2, empName='李四', age=30, gender='男', dept=null}]}
方式二:分步查询
DeptMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.Dept;
import org.apache.ibatis.annotations.Param;
public interface DeptMapper {
/**
* 使用分步查询,查询部门对应的多个员工——第一步
* @param deptId
* @return
*/
Dept getDeptAndEmpByDeptIdStepOne(@Param("deptId") Integer deptId);
}
DeptMapper.xml:
<resultMap id="deptAndEmpByDeptIdStepOne" type="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
<collection property="emps"
select="com.fuli.mybatis.mapper.EmpMapper.getDeptAndEmpByDeptIdStepTow"
column="dept_id"></collection>
</resultMap>
<!--Dept getDeptAndEmpByDeptIdStepOne(@Param("deptId") Integer deptId);-->
<select id="getDeptAndEmpByDeptIdStepOne" resultMap="deptAndEmpByDeptIdStepOne">
select * from t_dept where dept_id = #{deptId}
</select>
EmpMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface EmpMapper {
/**
* 使用分步查询,查询部门对应的多个员工——第二步
* @param deptId
* @return
*/
List<Emp> getDeptAndEmpByDeptIdStepTow(@Param("deptId") Integer deptId);
}
EmpMapper.xml:
<resultMap id="deptAndEmpByDeptIdStepTow" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
</resultMap>
<!--List<Emp> getDeptAndEmpByDeptIdStepTow(@Param("deptId") Integer deptId);-->
<select id="getDeptAndEmpByDeptIdStepTow" resultMap="deptAndEmpByDeptIdStepTow">
select * from t_emp where dept_id = #{deptId}
</select>
测试类:
@Test
public void testGetDeptAndEmpByDeptIdByStep(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = mapper.getDeptAndEmpByDeptId(1);
System.out.println(dept);
}
日志结果:
DEBUG01-1416:52:32,196==> Preparing: select * from t_emp,t_dept where t_dept.dept_id = ? and t_dept.dept_id = t_emp.dept_id(BaseJdbcLogger.java:137)
DEBUG01-1416:52:32,218==> Parameters: 1(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1416:52:32,243<== Total: 2(BaseJdbcLogger.java:137)
Dept{deptId=1, deptName='A', emps=[Emp{empId=1, empName='张三', age=20, gender='男', dept=null}, Emp{empId=2, empName='李四', age=30, gender='男', dept=null}]}
7. 动态SQL
Mybatis
框架的动态SQL
技术是一种根据特定条件动态拼装SQL
语句的功能,它存在的意义是为了解决拼接SQL
语句字符串时的痛点问题。
①if标签
if
标签可通过test
属性的表达式进行判断,若表达式的结果为true
,则标签中的内容会执行;反之标签中的内容不会执行
Emp实体类:
package com.fuli.mybatis.pojo;
public class Emp {
private Integer empId;
private String empName;
private Integer age;
private String gender;
public Emp(Integer empId, String empName, Integer age, String gender) {
this.empId = empId;
this.empName = empName;
this.age = age;
this.gender = gender;
}
public Emp() {
}
public Integer getEmpId() {
return empId;
}
public void setEmpId(Integer empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Emp{" +
"empId=" + empId +
", empName='" + empName + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
'}';
}
}
DynamicSQLMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.Emp;
import java.util.List;
public interface DynamicSQLMapper {
/**
* 通过条件查询来获取员工信息
* @param emp
* @return
*/
List<Emp> getEmpByCondition(Emp emp);
}
DynamicSQLMapper.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.fuli.mybatis.mapper.DynamicSQLMapper">
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp WHERE
<!--if,通过test属性中的表达式来判断标签中的内容是否有效(即是否会拼接到SQL中)-->
<if test="empName != null and empName != ''"> <!--if标签中的test的属性直接写Emp实体类的属性即可-->
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age} <!--注意不要忘了and,这是SQL的语法-->
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
</mapper>
测试类:
package com.fuli.mybatis.test;
import com.fuli.mybatis.mapper.DynamicSQLMapper;
import com.fuli.mybatis.pojo.Emp;
import com.fuli.mybatis.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class DynamicSQLTest {
@Test
public void getEmpByCondition(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp by = new Emp(null, "宝玉", 12, "女");
List<Emp> emps = mapper.getEmpByCondition(by);
for (Emp emp : emps) {
System.out.println(emp);
}
}
}
日志结果:
DEBUG01-1417:34:36,016==> Preparing: select * from t_emp WHERE emp_name = ? and age = ? and gender = ?(BaseJdbcLogger.java:137)
DEBUG01-1417:34:36,038==> Parameters: 宝玉(String), 12(Integer), 女(String)(BaseJdbcLogger.java:137)
DEBUG01-1417:34:36,055<== Total: 1(BaseJdbcLogger.java:137)
Emp{empId=null, empName='null', age=12, gender='女'}//出现null了,原因是数据库的字段名和实体类的属性名不一致
解决办法就是:①使用核心配置文件中的setting来解决,②是使用隐射文件的resultMap标签来解决;这个问题前面已经解决过了
DEBUG01-1417:40:02,775==> Preparing: select * from t_emp WHERE emp_name = ? and age = ? and gender = ?(BaseJdbcLogger.java:137)
DEBUG01-1417:40:02,796==> Parameters: 宝玉(String), 12(Integer), 女(String)(BaseJdbcLogger.java:137)
DEBUG01-1417:40:02,813<== Total: 1(BaseJdbcLogger.java:137)
Emp{empId=5, empName='宝玉', age=12, gender='女'}
但是这样写SQL会导致一些问题,如下所示:
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp WHERE
<!--以下SQL语句是有问题的,请看下面的测试-->
<if test="empName != null and empName != ''">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
当我把测试类简单修改一下,请看结果:
@Test
public void getEmpByCondition(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
//修改了这一句,这样就会导致empName = '',然后xml中的SQL就不会执行第一个if标签
Emp by = new Emp(null, "", 12, "女");
List<Emp> emps = mapper.getEmpByCondition(by);
for (Emp emp : emps) {
System.out.println(emp);
}
}
请看日志:
//第一句的SQL语句出错'where and'
DEBUG01-1417:49:00,406==> Preparing: select * from t_emp WHERE and age = ? and gender = ?(BaseJdbcLogger.java:137)
DEBUG01-1417:49:00,438==> Parameters: 12(Integer), 女(String)(BaseJdbcLogger.java:137)
如果我把测试类中的实体类属性全部设置为null
,又会导致如下结果:
//发现只有一个where,SQL语法出错
DEBUG01-1417:52:00,164==> Preparing: select * from t_emp WHERE(BaseJdbcLogger.java:137)
DEBUG01-1417:52:00,185==> Parameters: (BaseJdbcLogger.java:137)
①将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.fuli.mybatis.mapper.DynamicSQLMapper">
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp WHERE 1 = 1 <!--1. 增加1=1恒成立的语句-->
<if test="empName != null and empName != ''">
and emp_name = #{empName} <!--2. 在第一个条件语句前面拼接上 and-->
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
</mapper>
日志信息:
DEBUG01-1417:53:07,870==> Preparing: select * from t_emp WHERE 1 = 1(BaseJdbcLogger.java:137)
DEBUG01-1417:53:07,894==> Parameters: (BaseJdbcLogger.java:137)
DEBUG01-1417:53:07,914<== Total: 5(BaseJdbcLogger.java:137)
Emp{empId=1, empName='张三', age=20, gender='男'}
Emp{empId=2, empName='李四', age=30, gender='男'}
Emp{empId=3, empName='王五', age=18, gender='男'}
Emp{empId=4, empName='郭靖', age=34, gender='男'}
Emp{empId=5, empName='宝玉', age=12, gender='女'}
②使用where
标签,看下面
②where标签
where
和if
一般结合使用:
- 若
where
标签中的if条件都不满足,则where
标签没有任何功能,即不会添加where
关键字 - 若
where
标签中的if
条件满足,则where
标签会自动添加where
关键字,并将条件最前方多余的and
去掉
注意:where
标签不能去掉条件最后多余的and
,如出现下面情况:
<?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.fuli.mybatis.mapper.DynamicSQLMapper">
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<where>
<if test="empName != null and empName != ''">
emp_name = #{empName} and <!--修改将and写在后面-->
</if>
<if test="age != null and age != ''">
age = #{age} and
</if>
<if test="gender != null and gender != ''">
gender = #{gender}
</if>
</where>
</select>
</mapper>
当我们的gender是null或者空串其他非空的时候,日志信息:
//会发现保存,最后一个有and
DEBUG01-1418:04:37,606==> Preparing: select * from t_emp WHERE emp_name = ? and age = ? and(BaseJdbcLogger.java:137)
DEBUG01-1418:04:37,639==> Parameters: 宝玉(String), 12(Integer)(BaseJdbcLogger.java:137)
修改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.fuli.mybatis.mapper.DynamicSQLMapper">
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<where>
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</where>
</select>
</mapper>
使用where
标签,并且and
写在每一句的前面也可以完美的解决if
中出现的上面提到的问题
③trim标签
trim
用于去掉或添加标签中的内容
常用属性:
prefix
:在trim
标签中的内容的前面添加某些内容prefixOverrides
:在trim
标签中的内容的前面去掉某些内容suffix
:在trim
标签中的内容的后面添加某些内容suffixOverrides
:在trim
标签中的内容的后面去掉某些内容
<?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.fuli.mybatis.mapper.DynamicSQLMapper">
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<trim prefix="where" suffixOverrides="and">
<if test="empName != null and empName != ''">
emp_name = #{empName} and
</if>
<if test="age != null and age != ''">
age = #{age} and
</if>
<if test="gender != null and gender != ''">
gender = #{gender}
</if>
</trim>
</select>
</mapper>
④choose、when、otherwise标签
choose、when、otherwise相当于if…else if..else
<!--
choose when otherwise
相当于java中的if...else if ...else
when至少设置一个,当有多个when的时候,其中一个满足条件之后,其他的都不执行了
otherwise最多设置一个
-->
<select id="getEmpByConditionTow" resultType="Emp">
SELECT * FROM t_emp
<where>
<choose>
<when test="empName != null and empName != ''">
emp_name = #{empName}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
<when test="gender != null and gender != ''">
gender = #{gender}
</when>
</choose>
</where>
</select>
⑤foreach标签
foreach
标签是用来完成批量操作的一个标签
①批量添加:
DynamicSQLMapper接口:
/**
* 批量添加用户信息
* @param emps
*/
void insertMoreEmp(@Param("emps")List<Emp> emps);
DynamicSQLMapper.xml:
<!--void insertMoreEmp(@Param("emps")List<Emp> emps);-->
<insert id="insertMoreEmp">
insert into t_emp VALUES
<!--
foreach属性:
collection:如果你写了@Param,那么就填@Param中的值,如果没有写,那么如果你的参数是一个list集合那么就写list
item:表示循环体中的具体对象,可以自己随便起一个名字
separator:表示分割符,就好比批量添加:
insert into 表名 vlaues(),(),()
可以看见批量添加操作中间使用逗号分割,所以这里写逗号
-->
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.gender},null)
</foreach>
</insert>
测试类:
@Test
public void testInsetMoreEmp(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp1 = new Emp(null, "zs1", 12, "女");
Emp emp2 = new Emp(null, "zs2", 12, "女");
Emp emp3 = new Emp(null, "zs3", 12, "女");
ArrayList<Emp> emps = new ArrayList<Emp>();
emps.add(emp1);
emps.add(emp2);
emps.add(emp3);
mapper.insertMoreEmp(emps);
sqlSession.close();
}
日志:
DEBUG01-1419:04:19,660==> Preparing: insert into t_emp VALUES (null,?,?,?,null) , (null,?,?,?,null) , (null,?,?,?,null)(BaseJdbcLogger.java:137)
DEBUG01-1419:04:19,682==> Parameters: zs1(String), 12(Integer), 女(String), zs2(String), 12(Integer), 女(String), zs3(String), 12(Integer), 女(String)(BaseJdbcLogger.java:137)
DEBUG01-1419:04:19,692<== Updates: 3(BaseJdbcLogger.java:137)
②批量删除
DynamicSQLMapper接口:
/**
* 根据用户id批量删除用户
* @param empId
*/
void deleteMoreEmp(@Param("empIds") Integer[] empId);
DynamicSQLMapper.xml:
<!--void deleteMoreEmp(@Param("empIds") Integer[] empId);-->
<!--批量删除方式一-->
<delete id="deleteMoreEmp">
delete from t_emp where emp_id in
<foreach collection="empIds" item="empId" open="(" close=")" separator=",">
#{empId}
</foreach>
</delete>
<!--批量删除方式二-->
<delete id="deleteMoreEmp">
delete from t_emp where
<foreach collection="empIds" separator="or" item="empId">
emp_id = #{empId}
</foreach>
</delete>
测试类:
@Test
public void testDeleteMoreEmp(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Integer[] ids = {9,10,11};
mapper.deleteMoreEmp(ids);
sqlSession.close();
}
日志:
//方式一的日志
DEBUG01-1419:26:58,845==> Preparing: delete from t_emp where emp_id in ( ? , ? , ? )(BaseJdbcLogger.java:137)
DEBUG01-1419:26:58,867==> Parameters: 6(Integer), 7(Integer), 8(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1419:26:58,872<== Updates: 3(BaseJdbcLogger.java:137)
//方式二的日志这是
DEBUG01-1419:24:51,991==> Preparing: delete from t_emp where emp_id = ? or emp_id = ? or emp_id = ?(BaseJdbcLogger.java:137)
DEBUG01-1419:24:52,013==> Parameters: 9(Integer), 10(Integer), 11(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1419:24:52,015<== Updates: 0(BaseJdbcLogger.java:137)
⑥SQL标签
sql
片段,可以记录一段公共sql
片段,在使用的地方通过include
标签进行引入
<sql id="empColumns">
emp_id,emp_name,age,gender,dept_id
</sql>
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
<!--这里使用include标签来引入SQL片段-->
select <include refid="empColumns" />from t_emp
<trim prefix="where" suffixOverrides="and">
<if test="empName != null and empName != ''">
emp_name = #{empName} and
</if>
<if test="age != null and age != ''">
age = #{age} and
</if>
<if test="gender != null and gender != ''">
gender = #{gender}
</if>
</trim>
</select>
日志:
DEBUG01-1615:33:18,738==> Preparing: select emp_id,emp_name,age,gender,dept_id from t_emp where emp_name = ? and age = ?(BaseJdbcLogger.java:137)
DEBUG01-1615:33:18,763==> Parameters: 宝玉(String), 12(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1615:33:18,808<== Total: 1(BaseJdbcLogger.java:137)
Emp{empId=5, empName='宝玉', age=12, gender='女'}
8. Mybatis的缓存
①Mybatis的一级缓存
一级缓存是SqlSession
级别的,通过同一个SqlSession
查询的数据会被缓存,下次查询相同的数据,就会从缓存中直接获取,不会从数据库重新访问,mybatis
的一级缓存是默认开启的
CacheMapper接口:
package com.fuli.mybatis.mapper;
import com.fuli.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;
public interface CacheMapper {
/**
* 根据员工id来获取员工信息
* @param empId
* @return
*/
Emp getEmpById(@Param("empId") Integer empId);
}
CacheMapper.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.fuli.mybatis.mapper.CacheMapper">
<resultMap id="EmpById" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
</resultMap>
<!--Emp getEmpById(@Param("empId") Integer empId);-->
<select id="getEmpById" resultMap="EmpById">
select * from t_emp where emp_id = #{empId};
</select>
</mapper>
测试类:
import com.fuli.mybatis.mapper.CacheMapper;
import com.fuli.mybatis.pojo.Emp;
import com.fuli.mybatis.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class CacheMapperTest {
@Test
public void testCacheGetEmpById(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
CacheMapper mapper = sqlSession.getMapper(CacheMapper.class);
Emp emp1 = mapper.getEmpById(1);
Emp emp2 = mapper.getEmpById(1);
System.out.println(emp1);
//因为是通过同一个sqlsession来访问的,所以查找第二对象的时候并没有从数据库中查找,而是
//直接从mybatis的缓存中来的,可以通过观察日志的输出,只有一个SQL语句执行,说明第二个是使用缓存
//但是必须要保证是使用的同一个sqlsession对象以及查询的数据要相同
System.out.println(emp2);
}
}
日志:
DEBUG01-1615:53:12,311==> Preparing: select * from t_emp where emp_id = ?;(BaseJdbcLogger.java:137)
DEBUG01-1615:53:12,333==> Parameters: 1(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1615:53:12,351<== Total: 1(BaseJdbcLogger.java:137)
Emp{empId=1, empName='张三', age=20, gender='男'}
Emp{empId=1, empName='张三', age=20, gender='男'}
使一级缓存失效的四种情况:
- 不同的SqlSession对应不同的一级缓存
- 同一个SqlSession但是查询条件不同
- 同一个SqlSession两次查询期间执行了任何一次增删改操作
- 同一个SqlSession两次查询期间手动清空了缓存
sqlSession.clearCache();
②Mybatis的二级缓存
二级缓存是SqlSessionFactory
级别,通过同一个SqlSessionFactory
创建的SqlSession
查询的结果会被缓存;此后若再次执行相同的查询语句,结果就会从缓存中获取
二级缓存开启的条件:
在核心配置文件中,设置全局配置属性cacheEnabled=”true”,默认为true,不需要设置
在映射文件中设置标签
<cache/>
<?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.fuli.mybatis.mapper.CacheMapper"> <cache/> <!--在对应的mapper文件中写上--> <resultMap id="EmpById" type="Emp"> <id column="emp_id" property="empId"></id> <result column="emp_name" property="empName"></result> <result column="age" property="age"></result> <result column="gender" property="gender"></result> </resultMap> <!--Emp getEmpById(@Param("empId") Integer empId);--> <select id="getEmpById" resultMap="EmpById"> select * from t_emp where emp_id = #{empId}; </select> </mapper>
二级缓存必须在
SqlSession
关闭或提交之后有效@Test public void testMybatisCache2(){ //因为二级缓存是SqlsessionFactory级别的,所以要保证是同一个SqlsessionFactory对象 //因此此处不能用SqlSession工具类来获取SqlSession //因为使用SqlSession工具类来获取SqlSession会导致不同的SqlsessionFactory try { InputStream is = org.apache.ibatis.io.Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is); SqlSession sqlSession1 = sqlSessionFactory.openSession(true); CacheMapper mapper1 = sqlSession1.getMapper(CacheMapper.class); Emp emp1 = mapper1.getEmpById(1); sqlSession1.close();//必须关闭或提交sqlsession,才能使用二级缓存 System.out.println(emp1); SqlSession sqlSession2 = sqlSessionFactory.openSession(true); CacheMapper mapper2 = sqlSession2.getMapper(CacheMapper.class); Emp emp2 = mapper2.getEmpById(1); System.out.println(emp2); sqlSession2.close();//必须关闭或提交sqlsession,才能使用二级缓存 } catch (IOException e) { e.printStackTrace(); } } }
查询的数据所转换的实体类类型必须实现序列化的接口(
Serializable
)public class Emp implements Serializable
日志:
DEBUG01-1616:23:05,554Cache Hit Ratio [com.fuli.mybatis.mapper.CacheMapper]: 0.0(LoggingCache.java:60)
DEBUG01-1616:23:06,558==> Preparing: select * from t_emp where emp_id = ?;(BaseJdbcLogger.java:137)
DEBUG01-1616:23:06,578==> Parameters: 1(Integer)(BaseJdbcLogger.java:137)
DEBUG01-1616:23:06,596<== Total: 1(BaseJdbcLogger.java:137)
Emp{empId=1, empName='张三', age=20, gender='男'}
WARN 01-1616:23:06,603As you are using functionality that deserializes object streams, it is recommended to define the JEP-290 serial filter. Please refer to https://docs.oracle.com/pls/topic/lookup?ctx=javase15&id=GUID-8296D8E8-2B93-4B9A-856E-0A65AF9B8C66(SerialFilterChecker.java:46)
//只要Cache Hit Ratio 不为0,则表示二级缓存设置成功
DEBUG01-1616:23:06,607Cache Hit Ratio [com.fuli.mybatis.mapper.CacheMapper]: 0.5(LoggingCache.java:60)
Emp{empId=1, empName='张三', age=20, gender='男'}
使二级缓存失效的情况:两次查询之间执行了任意的增删改,会使一级和二级缓存同时失效
③二级缓存的相关配置(默认不修改,仅作了解)
在mapper
配置文件中添加的cache
标签可以设置一些属性:
eviction
属性:缓存回收策略,默认的是LRU
。LRU(Least Recently Used)
–最近最少使用的:移除最长时间不被使用的对象。FIFO(First in First out)
–先进先出:按对象进入缓存的顺序来移除它们。SOFT
–软引用:移除基于垃圾回收器状态和软引用规则的对象。WEAK
–弱引用:更积极地移除基于垃圾收集器状态和弱引用规则的对象。
flushInterval
属性:刷新间隔,单位毫秒默认情况是不设置,也就是没有刷新间隔,缓存仅仅调用语句时刷新
size
属性:引用数目,正整数代表缓存最多可以存储多少个对象,太大容易导致内存溢出
readOnly
属性:只读,true/false
true
:只读缓存;会给所有调用者返回缓存对象的相同实例。因此这些对象不能被修改。这提供了很重要的性能优势。false:读写缓存;会返回缓存对象的拷贝(通过序列化)。这会慢一些,但是安全,因此默认是false
。
④Mybatis缓存查询的顺序
先查询二级缓存,因为二级缓存中可能会有其他程序已经查出来的数据,可以拿来直接使用。
如果二级缓存没有命中,再查询一级缓存
如果一级缓存也没有命中,则查询数据库
SqlSession关闭之后,一级缓存中的数据会写入二级缓存