动态SQL:
1.if
根据标签中test属性对应的表达式决定标签中的内容是否需要拼接到SQL中
<!-- List<Emp> getEmpByCondition();-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp where
<if test="empName != null and empName != ''">
emp_name=#{empName}
</if>
<if test="age != null and age != ''">
and age=#{age}
</if>
<if test="sex != null and sex != ''">
and sex=#{sex}
</if>
<if test="email != null and email != ''">
and email=#{email}
</if>
</select>
2.where
当where标签中有内容时,会自动生成where关键字,并且将内容前多余的and或or去掉
当where标签中没有内容时,此时where没有任何效果
注意:where标签不能将其中内容后面多余的and或or删除
<!-- List<Emp> getEmpByCondition();-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<where>
<if test="empName != null and empName != ''">
emp_name=#{empName}
</if>
<if test="age != null and age != ''">
and age=#{age}
</if>
<if test="sex != null and sex != ''">
and sex=#{sex}
</if>
<if test="email != null and email != ''">
and email=#{email}
</if>
</where>
</select>
3.trim
若标签中有内容:
prefix/suffix:将trim标签中内容前面或后面添加指定内容
suffixOverrides/prefixOverrides:将trim标签中内容前面或后面去掉指定内容
若标签中没有内容:trim标签中也没有任何效果
<!-- List<Emp> getEmpByCondition();-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<trim prefix="where" suffixOverrides="and|or">
<if test="empName != null and empName != ''">
emp_name=#{empName} and
</if>
<if test="age != null and age != ''">
age=#{age} and
</if>
<if test="sex != null and sex != ''">
sex=#{sex} and
</if>
<if test="email != null and email != ''">
email=#{email}
</if>
</trim>
</select>
4.choose、when、otherwise(相当于if...else if...else)
<!-- List<Emp> getEmpByChoose(Emp emp);-->
<select id="getEmpByChoose" 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="sex != null and sex != ''">
sex = #{sex}
</when>
<when test="email != null and email != ''">
email = #{email}
</when>
<otherwise>
did = 1
</otherwise>
</choose>
</where>
</select>
when至少要有一个,otherwise最多只能有一个
5.foreach(举例:批量添加和删除)
写接口
/*
* 通过数组实现批量删除
*
* */
int deleteMoreByArray(@Param("eids") Integer[] eids);
/*
* 通过list集合实现批量添加
*
* */
int insertMoreByList(@Param("emps") List<Emp> emps);
然后xml映射文件
<!-- int insertMoreByList(@Param("emps") List<Emp> emps);-->
<insert id="insertMoreByList">
insert into t_emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
</foreach>
</insert>
<!-- int deleteMoreByArray(Integer[] eids);-->
<delete id="deleteMoreByArray">
delete from t_emp where
<foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
</delete>
collection:设置需要循环的数组或集合
item:表示数组或集合中的每一个数据
separator:循环体之间的分割符
测试
@Test
public void testInsertMoreByList() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp1 = new Emp(null,"a1",23,"男","1234@qq.com");
Emp emp2 = new Emp(null,"a2",23,"男","1234@qq.com");
Emp emp3 = new Emp(null,"a3",23,"男","1234@qq.com");
List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
System.out.println(mapper.insertMoreByList(emps));
}
@Test
public void testDeleteMoreByArray() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
int result = mapper.deleteMoreByArray(new Integer[]{6, 7, 8});
System.out.println(result);
}
MyBatis缓存(面试题)
一级缓存:
一级缓存是SqlSession级别的,通过同一个SqlSession查询的数据会被缓存,下次查询相同的数据,就会从缓存中直接获取,不会从数据库重新访问。
使一级缓存失效的四种情况:
1)不同的SqlSession对应不同的一级缓存
2)同一个SqlSession但是查询条件不同
3)同一个SqlSession两次查询期间执行了任何一次增删改操作
4)同一个SqlSession两次查询期间手动清空了缓存
二级缓存:
二级缓存的开启条件
1.在核心配置文件中设置 cacheEnabled=true
2.在映射文件中设置cache标签
3.二级缓存必需在sqlSession关闭或者提交后才有效
4.查询数据必需实现序列化接口
失效场景:两次查询之间执行了任意的增删改,会使得一二级同时失效
MyBatis逆向工程
generatorConfig.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--
targetRuntime:执行生成的逆向工程的版本
MyBatis3Simple:生成基本的CRUD(清新简洁版)
MyBatis3:生成带条件的CRUD(奢华尊享版)
-->
<context id="DB2Tables" targetRuntime="MyBatis3">
<!--数据库连接的信息:驱动类、连接地址、用户名、密码 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"
userId="root"
password="">
</jdbcConnection>
<!--javaBen生成策略--> //targetPackage表示需要生成的包 targetProject表示生成的路径
<javaModelGenerator targetPackage="com.gec.mybatis.pojo" targetProject=".\src\main\java">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!--Sql映射文件生成策略-->
<sqlMapGenerator targetPackage="com.gec.mybatis.mapper" targetProject=".\src\main\resources">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!--Mapper接口生成策略-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.gec.mybatis.mapper" targetProject=".\src\main\java">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!--逆向分析的表-->
<!--tableName设置为*号,可以对应所有表,此时不用写domainObjectName-->
<!--domainObjectName属性指定生成出来的实体类的类名-->
<table tableName="t_emp" domainObjectName="Emp"></table>
<table tableName="t_dept" domainObjectName="Dept"></table>
</context>
</generatorConfiguration>
pom.xml中导入逆向工程的依赖
<!-- 控制Maven在构建过程中相关配置 -->
<build>
<!-- 构建过程中用到的插件 -->
<plugins>
<!-- 具体插件,逆向工程的操作是以构建过程中插件形式出现 -->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.0</version>
<!-- 插件的依赖 -->
<dependencies>
<!-- 逆向工程的核心依赖 -->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
<!-- 数据库连接池 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.2</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.8</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
双击就可以自动生成代码
生成之后就能调用各种方法测试 带example的表示根据条件操作
public class MBGTest {
@Test
public void testMBG(){
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
//查询所有数据
// List<Emp> list = mapper.selectByExample(null);
// list.forEach(emp -> System.out.println(emp));
//根据条件查询
// EmpExample example = new EmpExample();
// example.createCriteria().andEmpNameEqualTo("张三").andAgeGreaterThanOrEqualTo(30);
// example.or().andDidIsNotNull();
// List<Emp> list = mapper.selectByExample(example);
// list.forEach(emp -> System.out.println(emp));
mapper.updateByPrimaryKeySelective(new Emp(1,"admin",18,null,"123@qq.com",3));
} catch (IOException e) {
e.printStackTrace();
}
}
}
MyBatis分页插件
在pom.xml导入插件依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
limit index,pageSize
index:当前页的起始索引
pageSize:每页显示的条数
pageNum:当前页的页码
index=(pageNum-1)*pageSize
@Test
public void testPageHelper(){
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Page<Object> page = PageHelper.startPage(2, 4);
List<Emp> list = mapper.selectByExample(null);
list.forEach(emp -> System.out.println(emp));
System.out.println(page);
} catch (IOException e) {
e.printStackTrace();
}
}