文章目录
MyBatis
MyBatis重要核心步骤:省去了建表,写对应的实体类(可以用lombok组件)
//对应实体类
public class Account {
private long id;
private String username;
private String password;
private int age;
}
1.创建 MyBatis 的配置⽂文件 config.xml(一般是在resources下)
<?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="account">
<!-- 配置MyBatis运⾏行行环境 -->
<environment id="account">
<!-- 配置MyBatis运⾏行行环境 -->
<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/mybatis01? useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="111"/>
<property name="password" value="111"/>
</dataSource>
</environment>
</environments>
</configuration>
2.通过原生接口实现
2.1Mapper.xml的编写
- MyBatis 框架需要开发者⾃自定义 SQL 语句句,写在 Mapper.xml ⽂文件中,实际开发中,会为每个实体 类创建对应的 Mapper.xml ,定义管理理该对象数据的 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="Account.Account">
<insert id="save" parameterType="Account.Account">
<!--这个就是表示插入语句的操作-->
insert into account (username ,password,age) values (#{username},#{password},#{age});
<!--对于相关的键值用#{}的形式写起来,就有点像preparedStatement的那种预留空间-->
</insert>
</mapper>
- namespace 通常设置为⽂文件所在包+⽂文件名的形式。
- insert 标签表示执⾏行行添加操作。
- select 标签表示执⾏行行查询操作。
- update 标签表示执⾏行行更更新操作。
- delete 标签表示执⾏行行删除操作。
- id 是实际调⽤用 MyBatis 方法时需要⽤用到的参数。
- parameterType 是调⽤用对应方法时参数的数据类型
2.2注册这个mapper.xml
在全局配置⽂文件 config.xml 中注册 mapper.xml
<mappers>
<mapper resource="AccountConfig/Mapper.xml"></mapper>
</mappers>
2.3通过官方的接口实现类(调api)
public class Test01 {
public static void main(String[] args) {
InputStream inputStream =Test01.class.getResourceAsStream("/config.xml");
//通过类加载器获取资源
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//创建builder
SqlSessionFactory build = sqlSessionFactoryBuilder.build(inputStream);
//导入资源,变成Factory类
SqlSession sqlSession = build.openSession();
//open资源
Account account = new Account(1L,"张三","root",24);
String statement ="Account.Account.save";
sqlSession.insert(statement,account);
//插入对应插入方法
sqlSession.commit();
//记得提交
sqlSession.close();
}
}
- statement 标签可根据 SQL 执⾏行行的业务选择 insert、delete、update、select。
- 是通过mapper.xml中的namespace和id组合而成
3.自定义接口实现(推荐)
3.1自己编写接口
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);
}
3.2创建接口对应的 xml,就像上面的mapper.xml
MyBatis 框架会根据规则⾃自动创建接口实现类的代理理对象。
规则:
- Mapper.xml 中 namespace 为接口的全类名。
- Mapper.xml 中 statement 的 id 为接口中对应的方法名。
- Mapper.xml 中 statement 的 parameterType 和接口中对应方法的参数类型一致
- Mapper.xml 中 statement 的 resultType 和接口中对应方法的返回值类型一致。
<?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="AccountRepository.AccountRepository">
<insert id="save" parameterType="Account.Account">
insert into account (username, password, age)
values (#{username}, #{password}, #{age});
</insert>
<!--插入标签-->
<update id="update" parameterType="Account.Account">
update account set username =#{username},password =#{password},age =#{age} where id = #{id};
</update>
<!--更改标签-->
<delete id="deleteById" parameterType="long">
delete
from account
where id = #{id};
</delete>
<!--删除标签-->
<select id="findAll" resultType="Account.Account">
select *
from account;
</select>
<select id="findById" resultType="Account.Account">
select *
from account
where id = #{id};
</select>
<!--查询标签-->
</mapper>
3.3、在 config.xml 中注册
<mappers>
<mapper resource="AccountConfig/Mapper.xml"></mapper>
<mapper resource="AccountRepository/AccountRepository.xml"></mapper>
</mappers>
3.4 调用官方api
public static void main(String[] args) {
InputStream resourceAsStream = Test02.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory build = sqlSessionFactoryBuilder.build(resourceAsStream);
SqlSession sqlSession = build.openSession();
AccountRepository mapper = sqlSession.getMapper(AccountRepository.class);
//这里有点与上面不一样,通过sqlSession获取接口里面的资源转换对象,调用这里面的对象的方法
// Account account = new Account(2L, "李四", "123", 22);
// mapper.update(account);
// mapper.deleteById(3);
for (Account account : mapper.findAll()) {
System.out.println(account);
}
sqlSession.commit();
//提交
sqlSession.close();
//关资源
}
4.级联查询
- 样例:classes类和student类,表示classes里可以有多个学生,典型的一对多;
/*classes表*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for classes
-- ----------------------------
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
`id` int NOT NULL,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
/*student表*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int NOT NULL,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`cid` int NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `cid`(`cid`) USING BTREE,
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `classes` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
/*classes表对应实体*/
public class Classes {
private long id;
private String name;
private List<Student> students;
}
/*Student表对应实体*/
public class Student {
private long id;
private String name;
private Classes classes;
}
4.1 一对多 ->通过学生查班级和学生
-
编写自定义接口
-
public interface StudentConfig { public Student findById (long id); }
-
-
配置对应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.StudentConfig.StudentConfig"> <!--namespace还是写对应的接口的全路径--> <resultMap id="StudentMap" type="com.Student.Student"> <!--这里联合查询与一般的不一样,需要用到resultMap--> <id column="id" property="id"></id> <result column="name" property="name"></result> <association property="classes" javaType="com.Student.Classes"> <id column="cid" property="id"></id> <result column="cname" property="name"></result> </association> </resultMap> <select id="findById" parameterType="long" resultMap="StudentMap"> select s.id ,s.name,c.id cid,c.name cname from student s,classes c where s.id = #{id} and c.id =cid; -- sql语句,通过cid联合查询 </select> </mapper>
-
关于这里的
resultMap
下面的属性- 首先是 resultMap自身的id属性,可自定义,需要与下面的sql查询语句对应,Type就是想要最后得到的类型,因为可以从StudentConfig这个配置接口里看到所以写实体类的全路径
id
就相当于是主键需要的id,然后Colum
就是sql语句中的对应元素,property
是原生类中的字段名称- 对于不是单一的字段就需要通过
association
来联合,因为结果是一个实体类,而不是多个(多个需要用collection
字段)然后就是javaType
就是写实体类的对应全路径 - 在他的下面写上sql语句中对应的主键字段和原实体类中对应的字段,与之前的一样
-
-
全局config里面注册mapper
4.2 多对一 ->通过班级类查询学生
-
编写自定义接口
-
public interface ClassesConfig { public Classes findById(long id); }
-
-
配置对应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.StudentConfig.ClassesConfig"> <resultMap id="ClassesMap" type="com.Student.Classes"> <id column="id" property="id"></id> <result column="name" property="name"></result> <collection property="students" ofType="com.Student.Student"> <id column="sid" property="id"></id> <result column="sname" property="name"></result> </collection> </resultMap> <select id="findById" parameterType="long" resultMap="ClassesMap"> select s.id sid,s.name sname,c.id,c.name from student s, classes c where c.id =#{id} and cid =c.id; </select> </mapper>
-
这里只将上面的
association
字段换成了collection
字段,其他的其实都差不多
-
-
全局config里面注册mapper
4.3多对多
样例:商品与顾客
/*customer表*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`id` int NOT NULL,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
/*goods表*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ------------------------- ---
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
`id` int NOT NULL,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
/*customer与goods关联表*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for customer_goods
-- ----------------------------
DROP TABLE IF EXISTS `customer_goods`;
CREATE TABLE `customer_goods` (
`id` int NOT NULL,
`cid` int NOT NULL,
`gid` int NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `cid`(`cid`) USING BTREE,
INDEX `gid`(`gid`) USING BTREE,
CONSTRAINT `customer_goods_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `customer` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `customer_goods_ibfk_2` FOREIGN KEY (`gid`) REFERENCES `goods` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
//Goods实体类
public class Goods {
private long id;
private String name;
private List<Customer> customers;
}
//Customer实体类
public class Customer {
private long id;
private String name;
private List<Goods> goods;
}
-
编写自定义接口
-
public interface CustomerConfig { public Customer findById(long id); }
-
-
配置对应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.GoodsConfig.CustomerConfig"> <resultMap id="CustomerMap" type="com.Goods.Customer"> <id column="cid" property="id"></id> <result column="cname" property="name"></result> <collection property="goods" ofType="com.Goods.Goods"> <id column="gid" property="id"></id> <result column="gname" property="name"></result> </collection> </resultMap> <select id="findById" parameterType="long" resultMap="CustomerMap"> select c.id cid, c.name cname, g.id gid, g.name gname from customer c,goods g,customer_goods cg where c.id = #{id} and cg.cid =c.id and cg.gid = g.id; </select> </mapper>
-
注:这里就注意哪个是结果集对应的类,要根据接口的定义来配置
-
-
全局config里面注册mapper
5.逆向工程
-
约束头文件
-
<?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">
-
-
导入依赖
-
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.29</version> </dependency> <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.7</version> </dependency> <dependency> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-core</artifactId> <version>1.4.0</version> </dependency> </dependencies>
-
创建 MBG 配置⽂文件 config.xml
- jdbcConnection 配置数据库连接信息。
- javaModelGenerator 配置 JavaBean 的⽣生成策略。
- sqlMapGenerator 配置 SQL 映射⽂文件⽣生成策略。
- javaClientGenerator 配置 Mapper 接⼝口的⽣生成策略。
- table 配置目标数据表。
<?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="testTables" targetRuntime="MyBatis3">
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mybatis01?useUnicode=true&characterEncoding=UTF-8"
userId="root"
password="root"
></jdbcConnection>
<javaModelGenerator targetPackage="com.Lei.pojo" targetProject="./src/main/java"></javaModelGenerator>
<sqlMapGenerator targetPackage="com.Lei.mapper" targetProject="./src/main/java"></sqlMapGenerator>
<javaClientGenerator type="XMLMAPPER" targetPackage="com.Lei.mapper"
targetProject="./src/main/java"></javaClientGenerator>
<table tableName="student" domainObjectName="Student"></table>
</context>
</generatorConfiguration>
context
标签 id自定义,targetRuntime
表示生成版本,这里创建原始版本,参数为MyBatis3targetPackage
生成的在哪个包下,targetProject
从哪里开始生成->这里就写从根目录下./src/main/java
tableName
:表名,domainObjectName
:JavaBean 类名
编写对对应的主程序来实现创建逆向工程
public static void main(String[] args) throws XMLParserException, IOException, InvalidConfigurationException, SQLException, InterruptedException {
List<String> warnings = new ArrayList<>();
boolean overwrite = true;
String file = Test01.class.getResource("/config.xml").getFile();
//写自己刚刚对应的配置文件
File configFile = new File(file);
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
myBatisGenerator.generate(null);
}
写好自己刚刚对应的配置文件的位置,其余的都是可以直接黏贴;
6.延迟加载
-
延迟加载也叫懒加载,针对于数据持久层的操作, 在某些特定的情况下去访问特定的数据库,从一定程度上减少了了 Java 应⽤用与数据库的交互次数。
-
这里拿student表做样例
-
在自定义接口中编写新的方法
-
//studentconfig接口中新增 public Student findByIdLazy(long id);
-
//ClassesConfig接口中新增 public Classes findByIdLazy(long id);
-
-
将一开始的级联查询拆分成两个表的分别查询
-
<!--ClassesConfig.xml中的新增select字段 原本的sql语句都变成了直接查询单表 --> <select id="findByIdLazy" parameterType="long" resultType="com.Student.Classes"> select *from classes where id =#{id}; </select>
-
<!--studentconfig.xml中的新增select字段 由于返回是student类所以一开始还是用resultMap存储 但是不同点就是关联另外一个表的时候,变成了select找到关联类接口中的方法, 并且通过colum元素指代将想要合并的元素与代入新查找的元素; 原本的sql语句都变成了直接查询单表 --> <resultMap id="StudentMapLazy" type="com.Student.Student"> <id column="id" property="id"></id> <result column="name" property="name"></result> <!-- <association property="classes" javaType="com.Student.Classes" select="com."--> <association property="classes" javaType="com.Student.Classes" select="com.StudentConfig.ClassesConfig.findByIdLazy" column="cid"> </association> </resultMap> <select id="findByIdLazy" parameterType="long" resultMap="StudentMapLazy"> select *from student where id =#{id}; </select>
-
-
测试类
-
public static void main(String[] args) { InputStream resourceAsStream = Test02.class.getClassLoader().getResourceAsStream("config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory build = sqlSessionFactoryBuilder.build(resourceAsStream); SqlSession sqlSession = build.openSession(); StudentConfig mapper = sqlSession.getMapper(StudentConfig.class); Student byId = mapper.findByIdLazy(1); //这里先获取的是StudentConfig的类 ClassesConfig mapper1 = sqlSession.getMapper(ClassesConfig.class); Classes byIdLazy = mapper1.findByIdLazy(byId.getClasses().getId()); //通过student的实体类生成的方法,获取getClasses(class元素是原来的字段里的)元素的id System.out.println(byIdLazy); sqlSession.close(); }
-
-
记得在全局配置里打开开关
-
<settings> <!-- 打印SQL执行的语句--> <setting name="logImpl" value="STDOUT_LOGGING"/> <!-- 开启延迟加载--> <setting name="lazyLoadingEnabled" value="true"/> </settings>
-
-
7.MyBatis缓存
- 第一次查询出之后会自动将该对象保存到缓存中,当下一次查询时,直接从缓存中取出对象即可, 无需再次访问数据库,减少访问次数
一级缓存:SqlSession 级别,默认开启,并且不不能关闭。
二级缓存:Mapper 级别,默认关闭,可以开启。二级缓存是多个
SqlSession
共享的,范围比一级存的大
-
MyBatis ⾃自带的二级缓存
-
config.xml 配置开启二级缓存
-
<settings> <!-- 开启⼆二级缓存 --> <setting name="cacheEnabled" value="true"/> </settings>
-
-
Mapper.xml 中配置二级缓存
-
<cache></cache>
-
-
自定义接口实现序列化
implements Serializable
-
-
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.4.3</version> </dependency>
-
-
在全局config的同一目录添加 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>
-
-
config.xml 配置开启二级缓存
-
<settings> <!-- 开启⼆二级缓存 --> <setting name="cacheEnabled" value="true"/> </settings>
-
-
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>
-
-
实体类不需要实现序列列化接口。
-
8.动态SQL
简单来说就是标签的使用,if,where,choose,when,trim,set,foreach
标签名 | 作用 |
---|---|
if | if 标签可以自动根据表达式的结果来决定是否将对应的语句添加到 SQL 中,如果条件不成立则不添加, 如果条件成⽴立则添加 |
where | where 标签可以自动判断是否要删除语句句块中的 and 关键字,如果检测到 where 直接跟 and 拼接,则自动删除 and,通常情况下 if 和 where 结合起来使用 |
choose when | 类似于if和where的连用 |
trim | trim 标签中的 prefix 和 suffix 属性会被⽤用于生成实际的 SQL 语句句会和标签内部的语句进行接,如 果语句前后出现了了 prefixOverrides 或者 suffixOverrides 属性中指定的值,MyBatis 框架会⾃自动将其删除。 |
set | set 标签⽤用于 update 操作,会⾃自动根据参数选择⽣生成 SQL 语句句 |
foreach | foreach 标签可以迭代⽣生成⼀一系列列值,这个标签主要⽤用于 SQL 的 in 语句句 |
-
if、where正常都一起用
-
接口里自定义方法
-
public Account findByAccount(Account account);
-
-
xml里编写相应的sql语句
-
<select id="findByAccount" resultType="com.Account.Account" parameterType="com.Account.Account"> select *from account <where> <if test="id!=0"> and id= #{id}</if> <if test="username!=null"> and username = #{username} </if> <if test="password"> and password = #{password} </if> <if test="age!=0"> and age = #{age} </if> </where> </select>
-
就是如果查找的时候少参数的话,依旧可以凭借提供的参数查找,而不需要写四条sql语句一一对应,很像java中的方法重载
-
-
-
when、choose其实和ifwhere一样
-
与上面同一个方法
-
xml中sql语句
-
<select id="findByAccount" resultType="com.Account.Account" parameterType="com.Account.Account"> select *from account <where> <choose> <when test="id!=0"> and id= #{id} </when> <when test="username!=null"> and username = #{username} </when> <when test="password!=null"> and password =#{password} </when> <when test="age!=0"> and age =#{age} </when> </choose> </where> </select>
-
所以选择if,where连用就好啦
-
-
-
trim
-
与上面同方法
-
xml中sql
-
<select id="findByAccount" resultType="com.Account.Account" parameterType="com.Account.Account"> select *from account <trim prefix="where" prefixOverrides="and"> <if test="id!=0"> and 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>
-
就是如果有where直接与and拼接,那么框架就会自动将元素删除
-
-
-
set
-
set用于update标签所以需要接口里编写update方法
-
public int update (Account account);
-
-
xml中sql
-
<update id="update" parameterType="com.Account.Account"> update account <set> <if test="id!=0"> and 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> </set> </update>
-
能够将真正需要改动的字段进行修改,其余字段不会变,原来的直接update是所有的字段重新覆盖所以效率低
-
-
-
foreach
-
在原始类中添加字段
-
private List<Long> ids;
-
-
接口中自定义方法
-
public List<Account> findByIds (Account account);
-
-
xml中对应的sql
-
<select id="findByIds" parameterType="com.Account.Account" resultType="com.Account.Account"> -- select *from account where id in(1,4,5)这个是sql原型 select *from account <where> <foreach collection="ids" open="id in(" close=")" item="id" separator=",">#{id}</foreach> </where> </select>
-
-