简述
ORMapping: Object Relationship Mapping 对象关系映射
- 对象:面向对象
- 关系:关系型数据库
- 映射:java到Mysql的映射,开发者可以以面向对象的思想来管理数据库
使用
- 创建maven工程,添加pom依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
- 新建数据库表
create table t_account(
id int primary key auto_increment,
username varchar(11),
password varchar(11),
age int
);
- 新建对应实体类
@Data
public class Account {
private long id;
private String name;
private String password;
private int age;
}
- 创建myBatis配置文件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>
<!--配置mybatis的运行环境-->
<environments default="dev">
<environment id="dev">
<!--配置jdbc事务管理-->
<transactionManager type="JDBC"></transactionManager>
<!--pooled配置jdbc数据源连接池-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/sql??useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
</configuration>
使用原生接口
- mybatis框架需要开发者自定义sql语句,卸载mapper.xml文件中,实际开发中,会为每个实体类创建对应的mapper.xm,定义管理该对象数据的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.youzm.mapper.AccountMapper">
<insert id="save" parameterType="com.youzm.entity.Account">
insert into t_account(username,password,age)
values (#{username},#{password},#{age})
</insert>
</mapper>
- namespace 通常设置为文件所在包+文件名形式
- insert标签表示执行添加操作
- select标签表示执行查询操作
- update标签表示执行更新操作
- delete标签表示执行删除操作
- id是实际调用mybatis方法时需要用到的参数
- parameterType是调用对应方法是参数的数据类型
- 在全局配置文件中注册accountMapper.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>
<!--配置mybatis的运行环境-->
<environments default="dev">
<environment id="dev">
<!--配置jdbc事务管理-->
<transactionManager type="JDBC"></transactionManager>
<!--pooled配置jdbc数据源连接池-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/sql??useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--注册AccountMapper.xml-->
<mappers>
<mapper resource="com/youzm/mapper/AccountMapper.xml"></mapper>
</mappers>
</configuration>
- 调用mybatis的原生接口执行添加操作
public class Test {
public static void main(String[] args) {
//加载配置文件
InputStream inputStream=Test.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession=sqlSessionFactory.openSession();
String statement="com.youzm.mapper.AccountMapper.save";
Account account=new Account(1L,"youzm","123",21);
sqlSession.insert(statement,account);
sqlSession.commit();
}
}
由于无法读取位于java文件夹下的xml文件,可在pom.xml文件中添加以下代码
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<!--<resource>
<directory>src/main/resources</directory>
<includes>
<include>*.xml</include>
<include>*.properties</include>
</includes>
</resource>-->
</resources>
</build>
通过Mapper代理实现自定义接口
- 自定义接口,定义相关业务方法
public interface AccountRepository {
public int save(Account account);
public int update(Account account);
public int deleteById(long id);
public List<Account> findAll();
public Account findById(long id);
}
- 编写与方法相对应的Mapper.xml,定义接口方法对应的sql语句
statement标签可更具sql执行的业务选择insert,delete,update,select
myBatis框架会根据规则自动创建接口实现类的代理对象
规则:
- Mapper.xml中namespace为接口的全类名
- Mapper.xml中statement的id为接口中对应的方法名
- Mapper.xml中的statement的parameterTyper和接口中对应方法的参数类型一致
- Mapper.xml中的statement的reultType和接口中的对应的方法的返回值类型一致
<?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.youzm.repository.AccountRepository">
<insert id="save" parameterType="com.youzm.entity.Account">
insert into t_account(username,password,age) values (#{username},#{password},#{age})
</insert>
<update id="update" parameterType="com.youzm.entity.Account">
update t_account set username=#{username} ,password=#{password} where id =#{id}
</update>
<delete id="deleteById" parameterType="java.lang.Long">
delete from t_account where id=#{id}
</delete>
<select id="findAll" resultType="com.youzm.entity.Account">
select * from t_account
</select>
<select id="findById" parameterType="long" resultType="com.youzm.entity.Account">
select * from t_account where id=#{id}
</select>
</mapper>
- 在mybatis.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>
<!--配置mybatis的运行环境-->
<environments default="dev">
<environment id="dev">
<!--配置jdbc事务管理-->
<transactionManager type="JDBC"></transactionManager>
<!--pooled配置jdbc数据源连接池-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/sql?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--注册AccountMapper.xml-->
<mappers>
<mapper resource="com/youzm/mapper/AccountMapper.xml"></mapper>
<mapper resource="com/youzm/repository/AccountRepository.xml"></mapper>
</mappers>
</configuration>
- 调用接口的代理对象完成相关的业务操作
public static void main(String[] args) {
InputStream inputStream=Test.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession=sqlSessionFactory.openSession();
//获取实现接口的代理对象
AccountRepository accountRepository=sqlSession.getMapper(AccountRepository.class);
//查询
/* List<Account> list=accountRepository.findAll();
for(Account account:list){
System.out.println(account);
}*/
//添加对象
Account account=new Account(2L,"zhou","123",23);
accountRepository.save(account);
sqlSession.commit();
sqlSession.close();
}
Mapper.xml
- statement标签:select ,update,delete,insert分别对应查询,修改,删除,添加操作
- parameterType:参数数据类型
- 基本数据类型,通过id查询Account
<select id="findById" parameterType="long" resultType="com.youzm.entity.Account">
select * from t_account where id=#{id}
</select>
- String类型,通过name查询Account
<select id="findByName" parameterType="java.lang.String" resultType="com.youzm.entity.Account">
select * from t_account where username=#{username}
</select>
- 包装类,通过Id查询Account
<select id="findById" parameterType="java.lang.Long" resultType="com.youzm.entity.Account">
select * from t_account where id=#{id}
</select>
- 多个参数,通过username和age查询Account,固定写arg0,或者param0
<select id="findByNameAndAge" resultType="com.youzm.entity.Account">
select * from t_account where username=#{arg0} and age=#{arg1}
</select>
- java Bean
<update id="update" parameterType="com.youzm.entity.Account">
update t_account set username=#{username} ,password=#{password} where id =#{id}
</update>
- resultType:结果类型
- 基本数据类型,统计Account总数
<select id="count" resultType="int">
select count (*) from t_account
</select>
- 包装类,统计Account总数
<select id="count2" resultType="java.lang.Integer">
select count (*) from t_account
</select>
- String 类型
<select id="findNameById" resultType="java.lang.String">
select username from t_account where id=#{id}
</select>
4.java Bean
<select id="findById" parameterType="long" resultType="com.youzm.entity.Account">
select * from t_account where id=#{id}
</select>
级联查询
- 一对多
java Bean实体类
Student
@Data
public class Student {
private long id;
private String name;
private Classes classes;
}
Classes
@Data
public class Classes {
private long id;
private String name;
List<Student> studentList;
}
StudentRepository
public interface StudentRepository {
public Student findById(long id);
}
ClassesRepository
public interface ClassesRepository {
public Classes findById(long id);
}
StudentRepository.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.youzm.repository.StudentRepository">
<resultMap id="studentMap" type="com.youzm.entity.Student">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<association property="classes" javaType="com.youzm.entity.Classes">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</association>
</resultMap>
<select id="findById" resultMap="studentMap">
select s.id,s.name,c.id as cid,c.name cname from student s,classes c where s.id=#{id} and s.cid=c.id
</select>
</mapper>
ClassesRepository
<?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.youzm.repository.ClassesRepository">
<resultMap id="classesMap" type="com.youzm.entity.Classes">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<collection property="studentList" ofType="com.youzm.entity.Student">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
</collection>
</resultMap>
<select id="findById" resultMap="classesMap">
select s.id,s.name,c.id as cid,c.name cname from student s,classes c where c.id=#{id} and s.cid=c.id
</select>
</mapper>
- 多对多
实体类
@Data
public class Custom {
private long id;
private String name;
List<Goods> goodsList;
}
@Data
public class Goods {
private long id;
private String name;
private List<Custom> customList;
}
CustomRepository.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.youzm.repository.CustomRepository">
<resultMap id="classesMap" type="com.youzm.entity.Custom">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<collection property="goodsList" ofType="com.youzm.entity.Goods">
<id column="gid" property="id"></id>
<result column="gname" property="name"></result>
</collection>
</resultMap>
<select id="findById" resultMap="classesMap">
select c.id cid,c.name cname,g.id gid,g.name gname from customer c ,goods g ,custom_goods cg where c.id=#{id} and c.id=cg.cid and g.id=cg.gid
</select>
</mapper>
逆向工程
mybatis框架需要:实体类,自定义mapper接口,mapper.xml
传统的开发中上述的三个组件需要开发者手动创建,逆向工程可以帮助开发者来自动创建三个组件,减轻开发者的工作量。
如何使用
MyBatis Generator,简称MBG,是专门为Mybatis框架开发者定制的代码生成器,可以自动生成Mybatis框架所需要的实体类,mapper接口,Mapper.xml,支持基本的CRUD操作,复杂的还是需要开发者完成。
1.新建maven工程,引入依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.7</version>
</dependency>
- 创建MBG配置文件
- jdbcConnection配置数据库连接信息
- javaModelGenerator配置javaBean生成策略
- sqlMapGenerator配置sql映射文件生成策略
- javaClientGenerator配置Mapper接口的生成策略
- table配置目标数据库(tableName:表名,domainObjectName:javaBean类名)
<?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>
<context id="testTable" targetRuntime="MyBatis3">
<jdbcConnection
driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/sql?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC"
userId="root"
password="root"></jdbcConnection>
<javaModelGenerator targetPackage="com.youzm.entity" targetProject="./src/main/java"></javaModelGenerator>
<sqlMapGenerator targetPackage="com.youzm.repository" targetProject="./src/main/java"></sqlMapGenerator>
<javaClientGenerator type="XMLMAPPER" targetPackage="com.youzm.repository" targetProject="./src/main/java"></javaClientGenerator>
<table tableName="user" domainObjectName="User"></table>
</context>
</generatorConfiguration>
- 创建执行类
public static void main(String[] args) {
List<String> warings=new ArrayList<String>();
boolean overwrite=true;
String genCig="/generatorConfig.xml";
File configFile=new File(Test.class.getResource(genCig).getFile());
ConfigurationParser configurationParser=new ConfigurationParser(warings);
Configuration configuration=null;
try {
configuration=configurationParser.parseConfiguration(configFile);
} catch (IOException e) {
e.printStackTrace();
} catch (XMLParserException e) {
e.printStackTrace();
}
DefaultShellCallback defaultShellCallback=new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator=null;
try {
myBatisGenerator=new MyBatisGenerator(configuration,defaultShellCallback,warings);
} catch (InvalidConfigurationException e) {
e.printStackTrace();
}
try {
myBatisGenerator.generate(null);
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
MyBatis延迟加载
- 什么是延迟加载
延迟加载也叫懒加载,惰性加载蛮实用延迟加载可以提高程序的运行效率,针对于数据库持久层的操作,在某些特定的情况下回去访问特定的数据库,在其他情况可以不访问某些表,从一定程度上减少java应用与数据库的交互次数。
查询学生和班级时,学生和班级是两张不同的表,若此时当前需求只需要获取学生的信息,那么查询学生单表即可,如果需要通过学生获取班级,则需查询两张表
不同的业务需求,需要查询不同的表,更据具体的业务需求来动态减少数据表查询的工作就是延迟加载
- 在config.xml中开启延迟加载
<configuration>
<settings>
<!--打印sql-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--开启延迟加载-->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
- 将多表关联查询拆分成多个单表查询
StudentRepository.java
public Student findByIdLazy(long id);
StudentRepository.xml
<resultMap id="studentMapLazy" type="com.youzm.entity.Student">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<association property="classes" javaType="com.youzm.entity.Classes"
select="com.youzm.repository.ClassesRepository.findByIdLazy" column="cid">
</association>
</resultMap>
<select id="findByIdLazy" resultMap="studentMapLazy">
select * from student s where s.id=#{id}
</select>
ClassesRepository.java
public Classes findByIdLazy(long id);
ClassesRepository.xml
<select id="findByIdLazy" resultType="com.youzm.entity.Classes">
select * from classes c where c.id=#{id}
</select>
-执行结果
public class Test5 {
public static void main(String[] args) {
InputStream inputStream=Test.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession=sqlSessionFactory.openSession();
StudentRepository studentRepository=sqlSession.getMapper(StudentRepository.class);
Student student=studentRepository.findByIdLazy(1);
System.out.println(student.getName());
System.out.println("======");
System.out.println(student.getClasses());
sqlSession.close();
}
}
MyBatis缓存
-
什么是MyBatis缓存
使用缓存可以减少java应用与数据库的交互次数没从而提升程序的运行效率。比如查询出id=1的对象,第一次查询出后会自动就爱那个该对象保存到缓存中,当下一次查询时,直接从缓存中读取出对象即可,无需再次访问数据库。 -
MyBatis缓存分类
-
一级缓存:SqlSession级别,且不能关闭
操作数据库时需要创建SqlSession,在对象中一个Hashmap用于存储缓存数据,不同的SqlSession之间缓存数据区域是互不影响。
一级缓存的作用域是SqlSession范围,当在同一个SqlSession中执行两次相同的sql语句时,第一次执行完会将结果保存在缓存中,第二次查询会直接从缓存中获取。
需要注意的是,如果SqlSession执行了DML操作(insert,update,delete),Mybati必须清空缓存以保证数据的准确性。 -
二级缓存:Mapper级别,默认关闭,可以开启
使用二级缓存时,多个SqlSession使用同一个Mapper的sql语句操作数据库,得到的数据会存在二级缓存区,同样使用HashMap进行数据存储,相较于一级缓存,二级缓存的范围更大,多个SqlSession可以共用二级缓存,二级缓存是跨SqlSession的
二级缓存是多个SqlSession共享的,其作用域是mapper的同个namespace,不同的sqlsession两次执行相同namespace下的sql,参数也相等,则第一次执行成功会将数据保存到二级缓存中,第二次可以直接从二级缓存中取出数据
代码
- 一级缓存
初始代码:
public static void main(String[] args) {
InputStream inputStream=Test.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession=sqlSessionFactory.openSession();
AccountRepository accountRepository=sqlSession.getMapper(AccountRepository.class);
Account account=accountRepository.findById(1);
System.out.println(account);
Account account1=accountRepository.findById(1);
System.out.println(account1);
sqlSession.close();
}
此时处于同一个sqlSession,结果如下:
结果显示,只查询一次数据库。
修改代码:
public static void main(String[] args) {
InputStream inputStream=Test.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession=sqlSessionFactory.openSession();
AccountRepository accountRepository=sqlSession.getMapper(AccountRepository.class);
Account account=accountRepository.findById(1);
System.out.println(account);
sqlSession.close();
sqlSession=sqlSessionFactory.openSession();
accountRepository=sqlSession.getMapper(AccountRepository.class);
Account account1=accountRepository.findById(1);
System.out.println(account1);
sqlSession.close();
}
重新打开一个新的sqlSession,则查询两次
- 二级缓存
- mybatis自带的二级缓存
- config.xml配置开启二级缓存
<settings>
<!--打印sql-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--开启延迟加载-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--开启二级缓存-->
<setting name="cacheEnabled" value="true"/>
</settings>
- Mapper.xml中配置二级缓存
<mapper namespace="com.youzm.repository.AccountRepository">
<cache></cache>
- 对应实体类实现序列化接口
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Account implements Serializable {
private long id;
private String username;
private String password;
private int age;
}
- 输入结果
只执行一次
- ehcache 第三方二级缓存
- 引入依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-ehcache</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>net.sf.ehcache</groupId>
<artifactId>ehcache-core</artifactId>
<version>2.0.0</version>
</dependency>
- 配置ehcache.xml
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="../config/ehcache.xsd">
<diskStore/>
<defaultCache
maxElementsInMemory="1000"
maxElementsOnDisk="10000000"
eternal="false"
overflowToDisk="false"
timeToIdleSeconds="120"
timeToLiveSeconds="120"
diskExpiryThreadIntervalSeconds="120"
memoryStoreEvictionPolicy="LRU">
</defaultCache>
</ehcache>
- Mapper.xml配置二级缓存
<cache type="org.mybatis.caches.ehcache.EhcacheCache">
<!--创建缓存之后,最后一次访问缓存的时间至缓存失效的时间间隔-->
<property name="timeToIdleSeconds" value="3600"/>
<!--缓存自创建时间起至失效的时间间隔-->
<property name="timeToLiveSeconds" value="3600"/>
<!--缓存回收策略,LRU表示移除近期使用最少的对象-->
<property name="memoryStoreEvictionPolicy" value="LRU"/>
</cache>
- 实体类不需要实现序列化接口。
动态SQL
使用动态sql可以简化代码的开发,减少开发者的工作量,程序可以自动的根据业务参数来决定sql的组成
- if标签
<select id="findByAccount" parameterType="com.youzm.entity.Account" resultType="com.youzm.entity.Account">
select * from t_account where
<if test="id!=0">
id=#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
<if test="password!=null">
and password=#{password}
</if>
<if test="age!=0">
and age=#{age}
</if>
</select>
if标签可以自动根据表达式的结果来决定是否将对应的语句添加到sql中,如果条件不成立则不添加,如果条件成立则添加。
- where 标签
<select id="findByAccount" parameterType="com.youzm.entity.Account" resultType="com.youzm.entity.Account">
select * from t_account
<where>
<if test="id!=0">
id=#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
<if test="password!=null">
and password=#{password}
</if>
<if test="age!=0">
and age=#{age}
</if>
</where>
</select>
where 标签可以自动判断是否需要删除语句块中的 and 关键字,如果检测到 where 直接跟 and 拼接,则
自动删除 and,通常情况下 if 和 where 结合起来使用。
- choose,when 标签
<select id="findByAccount" parameterType="com.youzm.entity.Account" resultType="com.youzm.entity.Account">
select * from t_account
<where>
<choose>
<when test="id!=0">
id=#{id}
</when>
<when test="username=null">
and username=#{username}
</when>
<when test="age!=0">
and age=#{age}
</when>
</choose>
</where>
</select>
- trim 标签
trim标签中的prefix和suffix属性会被用于生成实际的sql语句描绘和标签内部的语句进行拼接,如果语句前后出现prefixOverrides或者suffixOverrides属性中指定的值,Mybatis框架会自动将其删除。
<select id="findByAccount" parameterType="com.youzm.entity.Account" resultType="com.youzm.entity.Account">
select * from t_account
<trim prefix="where" prefixOverrides="and">
<if test="id!=0">
id=#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
<if test="password!=null">
and password=#{password}
</if>
<if test="age!=0">
and age=#{age}
</if>
</trim>
</select>
- set标签
set 标签用于update操作,会自动根据参数选择生成 SQL 语句
<update id="update" parameterType="com.youzm.entity.Account">
update t_account
<set>
<if test="username!=null">
username=#{username},
</if>
<if test="password!=nul">
password=#{password},
</if>
<if test="age!=0">
age=#{age}
</if>
</set>
where id =#{id}
</update>
- foreach
<select id="findByIds" resultType="com.youzm.entity.Account">
select * from t_account
<where>
<foreach collection="ids" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
参考视频:b站楠哥-----mybatis极简入门