MyBatis框架学习笔记(二)
- 五、简单映射
-
- 5.1 单条记录结果集处理Map
-
- (1)`resultType`属性,ResultSet对象转换为Map对象
- (2)`resultType`属性,ResultSet对象转换为Map对象
- (3)自己建立映射关系,查询语句和Map中的KEY的赋值,由我们决定
- 5.2 单条记录结果处理Student
-
-
- (1)默认情况下,映射为自动映射,查询虚拟表中字段和Java类中的属性一致,可以完成自动映射(赋值过程)
- (2)``标签,完成映射,可以做到复用性
-
- 5.3 单个值单条记录处理方式
- 5.4 多条记录结果集处理
- 5.5 封装工具类
- 六. 动态SQL语句
-
- 6.1动态查询
-
- (1) IF标签和恒等式
- (2) WHERE标签
- 6.2动态更新
-
- (1)SET标签
- 6.3 动态添加:TRIM标签(还可以实现前两者)
-
- ``标签:最经典的应用之一,动态添加语句,需要保证字段和数据顺序出现的一致性
- 6.5 `FOREACH`标签(批量删除和批量添加)
- 6.6 CHOOSE标签
- 6.7`sql`标签和`include`标签:提取公共sql语句
- 6.8 附录代码:
五、简单映射
针对于单表的查询结果集(SELECT语句->->JDBC对查询记录的封装对象),在MyBatis框架对ResultSet对象进行再次处理,在学习JavaSE阶段时候,我们通过
ResultSet
的规则使用反射技术,完成数据库中记录(虚拟表)和Java对象的映射。
约定优于配置
MyBatis简单映射是针对于
查询的结果集ResultSet
的再次处理,无外乎2种情况:1.单条记录 2.多条记录
所以我们对核心文件进行简单的小小的升级,
注意:核心配置出现的标签
:
有顺序的限制
打开
mybatis-config.xml
文件中的
mybatis-3-config.dtd
,我们会看到如下顺序:
其中
configuration 代表根标签表达式:在正则表达式中
?
?
代表出现 0~1次
说明其设置标签出现顺序:必须按照此代码顺序出现,所以mapper是在最后出现
核心配置文件
<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>
<property name="jdbc.driverName" value="com.mysql.jdbc.Driver"/>
<property name="jdbc.url" value="jdbc:mysql://127.0.0.1:3306/yue_mybatis"/>
<property name="jdbc.username" value="root"/>
<property name="jdbc.password" value=""/>
</properties>
<settings>
<!--启动日志管理-->
<setting name="logImpl" value="LOG4J"/>
</settings>
<!--自定义类型别名-->
<typeAliases >
<typeAlias type="com.yue.model.Student" alias="Student"/>
</typeAliases>
<!-- 配置开发环境:数据源和事务管理器 -->
<environments default="mysql">
<environment id="mysql">
<!-- 事务管理器:管理事务 -->
<!-- 学生JDBC的时候,默认情况下,我们的事务都是自动提交,当我们执行INSERT/UPDATE/DELETE直接改变数据库中的记录 -->
<!-- 涉及批量操作的时候,我们需要将事务改成手动提交:1.connection.setAutoCommit(false) 2.connecton.commit() -->
<!-- MyBatis框架默认情况下执行变更数据库记录的操作都是:手动提交 -->
<transactionManager type="JDBC"/>
<!-- 数据库连接池:预先将一些Connection保存到容器当中,使用时候直接重容器中获取,不再需要每次创建 -->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- 加载映射文件:classpath下加载映射文件resources目录下 -->
<mappers>
<mapper resource="mapper/StudentMapper.xml"/>
</mappers>
</configuration>
5.1 单条记录结果集处理Map
单条记录: 虚拟表
中含有多个字段
接口文件:
package com.yue.mapper;
import java.util.Map;
public interface StudentMapper {
/*1.通过主键获取一条记录多个字段,ResultSet->Map对象*/
Map<String,Object> queryMap01(Integer id);
Map<String,Object> queryMap02(Integer id);
Map<String,Object> queryMap03(Integer id);
}
(1) resultType
属性,ResultSet对象转换为Map对象
resultType
默认情况下,Map中的KEY为虚拟表中的字段
<!-- 1.单条记录:ResultSet->Map对象-->
<!--resultType:告知将结果集处理成哪种java类型的数据(select中专有标签)-->
<select id="queryMap01" parameterType="java.lang.Integer"
resultType="java.util.Map">
SELECT * FROM student WHERE student_id=#{id};
</select>
(2) resultType
属性,ResultSet对象转换为Map对象
resultType
通过改变字段别名,设置Map中的KEY
<!-- parameterType/resultType:都写Java类中全路径,浪费 -->
<!-- 类型映射,MyBatis框架对Java类的全路径设置别名 -->
<select id="queryMap02" parameterType="integer" resultType="map">
SELECT student_id id,student_name name,student_sex sex
FROM student WHERE student_id=#{id};
</select>
(3)自己建立映射关系,查询语句和Map中的KEY的赋值,由我们决定
注意:
标签中的resultMap属性,找映射关系的
<select>
标签
<resultMap>
<!-- 手动建立映射关系 : resultMap属性一定找resultMap标签-->
<!-- type:ResultSet转成成什么Java类型 -->
<!-- autoMapping="true" 自动映射,虚拟表字段映射-->
<resultMap id="MapResultMapper" type="map" autoMapping="true">
<result column="student_id" property="ID"/>
<result column="student_NAME" property="NAME"/>
</resultMap>
<select id="queryMap03" parameterType="_int" resultMap="MapResultMapper">
SELECT * FROM student WHERE student_id=#{id};
</select>
注意
result标签中的autoMapping属性:
默认为true===>未指明的虚拟表字段都会自动映射
设为false===>只做指明字段的映射
5.2 单条记录结果处理Student
建立持久化类:
package com.yue.model;
import java.util.Date;
/*
* POJO
* JavaSE阶段:存储数据库中记录VO(Value Object)
* JavaEE阶段:存储数据库中记录实体Bean
* 使用对象存储数据,但是数据库中没有该条记录的时候,该对象称为DTO(数据传输对象)
* 持久层框架,存储数据库中记录,持久化对象
* */
public class Student {
private Integer studentId;
private String studentName;
private String studentSex;
private Integer age;
private Date birthday;
}
(1)默认情况下,映射为自动映射,查询虚拟表中字段和Java类中的属性一致,可以完成自动映射(赋值过程)
<!-- 1.单条记录:ResultSet->Student自定义对象-->
<select id="queryStudent01" parameterType="int" resultType="com.yue.model.Student">
SELECT * FROM student WHERE student_id=#{id};
</select>
结果: 因为id,name,sex属性,数据表中字段名和持久化类属性名不一致,所以结果为空,所以手动设置:
<!-- 1.单条记录:ResultSet->Student自定义对象-->
<select id="queryStudent01" parameterType="int" resultType="com.yue.model.Student">
SELECT
student_id studentId,
student_name studentName,
student_sex studentSex,
age,birthday
FROM student WHERE student_id=#{id};
</select>
结果:
(2) <resultMap>
标签,完成映射,可以做到复用性
<resultMap>
核心配置文件配置自定义类型的别名:
<!--自定义类型别名-->
<typeAliases >
<typeAlias type="com.yue.model.Student" alias="Student"/>
</typeAliases>
<!-- 2.单条记录:ResultSet->Student自定义对象-->
<!--手动映射-->
<resultMap id="StudentResultMapper" type="Student" autoMapping="false">
<!-- <id>标签:做主键映射,MyBatis框架通过ID标签区分是否是相同数据 -->
<!-- jdbcType/javaType可以省略不写 -->
<id column="student_id" jdbcType="INTEGER" property="studentId" javaType="java.lang.Integer"/>
<result column="student_name" jdbcType="VARCHAR" property="studentName" javaType="java.lang.String"/>
<result column="student_sex" jdbcType="VARCHAR" property="studentSex" javaType="java.lang.String"/>
<result column="age" jdbcType="INTEGER" property="age" javaType="java.lang.Integer"/>
<result column="birthday" jdbcType="DATE" property="birthday" javaType="java.util.Date"/>
</resultMap>
<select id="queryStudent02" parameterType="int" resultMap="StudentResultMapper">
SELECT * FROM student WHERE student_id=#{id};
</select>
5.3 单个值单条记录处理方式
一行一列,单个值(基本数据类型、String类型、日期类型等)
<!-- 3.单个值:resultType -->
<select id="queryValue01" parameterType="int" resultType="string">
SELECT student_name FROM student WHERE student_id=#{id}
</select>
<select id="queryValue02" parameterType="int" resultType="date">
SELECT birthday FROM student WHERE student_id=#{id}
</select>
<select id="queryValue03" resultType="_int">
SELECT COUNT(*) FROM student
</select>
<select id="queryValue04" resultType="_int">
SELECT SUM(age) FROM student
</select>
5.4 多条记录结果集处理
本质上也是处理每条记录的映射(ResultSet需要遍历,每条记录,处理成Java类型,添加到集合当中),重复过程。
<!-- ResultMap标签复用性 -->
<!-- 4.1 多条记录:ResultSet->List<Map> -->
<select id="listMap" parameterType="string" resultMap="MapResultMapper">
SELECT * FROM student WHERE student_sex=#{sex}
</select>
<!-- 4.2 多条记录:ResultSet->List<Student> -->
<!-- 模糊查询使用,MySQL函数拼接% ,推荐-->
<select id="listStudent" parameterType="string" resultMap="StudentResultMapper">
SELECT * FROM student WHERE student_name LIKE CONCAT('%',#{name},'%')
</select>
<!-- 4.3 多条记录:ResultSet->List<String> -->
<!-- 模糊查询使用,Java拼接% ,推荐-->
<select id="listStudentName" parameterType="string" resultType="string">
SELECT student_name FROM student WHERE student_name LIKE #{name}
</select>
<!-- 4.4 多条记录:ResultSet->List<Date> -->
<!-- 模糊查询使用,$手动处理%,拼接SQL语句,不推荐 -->
<select id="listBirthday" parameterType="string" resultType="date">
SELECT birthday FROM student WHERE student_name LIKE '%${name}%'
</select>
<!-- 4.5 分组查询+聚合函数 -->
<select id="list" resultType="map">
SELECT student_sex,COUNT(*) num FROM student GROUP BY student_sex
</select>
5.5 封装工具类
package com.yue.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;
/**
* final关键字:
* final修饰类不能被继承,java.lang.String
* final修饰方法不能重写
* final修饰变量不能改变值(地址值)
*/
public final class MyBatisUtil {
private MyBatisUtil(){
//私有化的构造方法,不允许外部进行实例化
//但是可以通过反射进行实例化
}
private final static String PATH = "config/mybatis-config.xml";
//static修饰的对象,共享数据,单例设计模式
private static SqlSessionFactory factory ;
static {//随着类的加载而加载,执行一次
try {
InputStream in = Resources.getResourceAsStream(PATH);
factory = new SqlSessionFactoryBuilder().build(in);
} catch (IOException e) {
e.printStackTrace();//给程序员使用错误隐藏,应记录日志当中
throw new RuntimeException("加载核心配置文件错误");
}
}
public static SqlSession getSqlSession(){//类方法:通过类名调用
return factory.openSession();
}
public static void closeSqlSession(SqlSession sqlSession){//静态方法:通过类名调用
if(sqlSession!=null){
sqlSession.close();
}
}
}
所用全部测试文件:
package com.yue.test;
import com.yue.mapper.StudentMapper;
import com.yue.model.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;
public class 简单映射及国际处理 {
private SqlSession sqlSession;
private StudentMapper studentMapper;
@Before
public void init()throws Exception{
String path = "config/mybatis-config.xml";
InputStream in = Resources.getResourceAsStream(path);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession();
studentMapper = sqlSession.getMapper(StudentMapper.class);
}
@After
public void close(){
if(sqlSession!=null){
sqlSession.close();
}
}
/*默认情况下,Map中的KEY为虚拟表中的字段*/
@Test
public void testQueryMap01(){
System.out.println("接口方式:底层就是调用selectOne方法");
Map<String,Object> tempMap = studentMapper.queryMap01(14);
System.out.println("tempMap = " + tempMap);
System.out.println("原生方式:");
tempMap = sqlSession.selectOne("com.yue.mapper.StudentMapper.queryMap01",15);
System.out.println("tempMap = " + tempMap);
}
/*默认情况下,Map中的KEY为虚拟表中的字段*/
/*通过改变字段别名,设置Map中的KEY*/
@Test
public void testQueryMap02(){
System.out.println("接口方式:底层就是调用selectOne方法");
Map<String,Object> tempMap = studentMapper.queryMap02(14);
System.out.println("tempMap = " + tempMap);
System.out.println("原生方式:");
tempMap = sqlSession.selectOne("com.yue.mapper.StudentMapper.queryMap02",15);
System.out.println("tempMap = " + tempMap);
}
/*手动建立映射关系*/
@Test
public void testQueryMap03(){
System.out.println("接口方式:底层就是调用selectOne方法");
Map<String,Object> tempMap = studentMapper.queryMap03(14);
System.out.println("tempMap = " + tempMap);
}
/*2.单条记录:ResultSet->Student自定义对象*/
@Test
public void testqueryStudent01(){
System.out.println("接口方式:底层就是调用selectOne方法");
Student student = studentMapper.queryStudent01(17);
System.out.println("student = " + student);
}
/*2.单条记录:ResultSet->Student自定义对象*/
/*手动映射*/
@Test
public void testqueryStudent02(){
System.out.println("接口方式:底层就是调用selectOne方法");
Student student = studentMapper.queryStudent02(17);
System.out.println("student = " + student);
}
/*3.单个值:resultType*/
@Test
public void testqueryValue(){
System.out.println("接口方式:底层就是调用selectOne方法");
String studentName = studentMapper.queryValue01(28);
System.out.println("studentName = " + studentName);
Date birthday = studentMapper.queryValue02(28);
System.out.println("birthday = " + birthday);
int count = studentMapper.queryValue03(28);
System.out.println("count = " + count);
int sum = studentMapper.queryValue04(28);
System.out.println("sum = " + sum);
}
/*4.多条记录 模糊查询*/
@Test
public void testList(){
System.out.println("接口方式:底层就是调用selectList方法");
List<Map<String,Object>> list01 = studentMapper.listMap("男");
System.out.println("list01 = " + list01);
List<Student> list02 = studentMapper.listStudent("北");
System.out.println("list02 = " + list02);
String name = "紫";
List<String> list03 = studentMapper.listStudentName("%"+name+"%");
System.out.println("list03 = " + list03);
List<Date> list04 = studentMapper.listBirthday("星");
System.out.println("list04 = " + list04);
List<Map<String,String>> list05 = studentMapper.list();
System.out.println("list05 = " + list05);
}
}
六. 动态SQL语句
Java Web阶段,我们出现如下的界面,我会通过代码来控制SQL语句产生(动态拼接) 上面的界面分析,我查询的情况有四种组合方式:1.查询所有信息 2.按照姓名查询,所有性别 3.按照性别查询,所有名称 4.按照姓名和性别查询。Java Web阶段的时候,我们的解决方案是通过 恒等式
动态拼接SQL语句,模拟代码如下:
//获取客户端的数据
String studentName = request.getParameter("name");
String studentSex = request.getParameter("sex");
//为了拼接动态的SQL语句,需要使用恒等式拼接字符串
String sql = "SELECT * FROM student WHERE 1=1 ";//恒等式每一条记录都需要判断恒等式是否成立,记录很多影响一丢丢的效率
if(studentName != null && studentName.trim().length()>0){
//sql += " AND student_name LIKE '%"+studentName+"%'";
sql += " AND student_name LIKE CONCAT('%','"+studentName+"','%')";
}
if(studentSex != null && studentSex.trim().length()>0){
sql += " AND student_sex='"+studentSex+"'";
}
简单说一下动态sql语句为什么使用恒等式:
因为要判断恒等式后面的条件是否成立.如成立,正常返回条件;
若不成立,且没有恒等式,那么代码会变成如
SELECT * FROM table WHERE
,
显然,这语句是错误的,加上恒等式可以让它不管何种情况都可以运行
6.1动态查询
(1) IF标签和恒等式
<if>
标签:返回是boolean类型,如果true显示标签内内容
<?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和接口全路径相同,方法名称和ID值一致 -->
<!--&&和and一致-->
<mapper namespace="com.yue.mapper.StudentMapper">
<!--手动映射-->
<resultMap id="StudentResultMapper" type="Student" autoMapping="false">
<!-- <id>标签:做主键映射,MyBatis框架通过ID标签区分是否是相同数据 -->
<!-- jdbcType/javaType可以省略不写 -->
<id column="student_id" property="studentId"/>
<result column="student_name" property="studentName"/>
<result column="student_sex" property="studentSex"/>
<result column="age" property="age" />
<result column="birthday" property="birthday"/>
</resultMap>
<!--1.通过java程序拼接-->
<select id="if01" resultMap="StudentResultMapper">
SELECT * FROM student WHERE 1=1
<if test="studentName != null and studentName.trim() != ''">
AND student_name LIKE #{studentName}
</if>
<if test="studentSex != null && studentSex.trim() != ''">
AND student_sex = #{studentSex}
</if>
</select>
<!--通过MySql函数拼接-->
<select id="if02" resultMap="StudentResultMapper">
SELECT * FROM student WHERE 1=1
<if test="studentName != null and studentName.trim() != ''">
AND student_name LIKE CONCAT('%',#{studentName},'%')
</if>
<if test="studentSex != null and studentSex.trim() != ''">
AND student_sex = #{studentSex}
</if>
</select>
</mapper>
@Test
public void if标签(){
System.out.println("通过姓名模糊查询");
String studentName ="孙";
/*1.自己拼`%`*/
List<Student> list01 = studentMapper.if01("%"+studentName+"%", null);
System.out.println("list01 = " + list01);
List<Student> list02 = studentMapper.if01("", "女");
System.out.println("list02 = " + list02);
/*2.不用自己拼`%`*/
List<Student> list03 = studentMapper.if02(studentName, null);
System.out.println("list03 = " + list03);
List<Student> list04 = studentMapper.if02("", "女");
System.out.println("list04 = " + list04);
}
(2) WHERE标签
因为上面的恒等,每条记录都需要判断恒等式是否成立,记录多的话会影响效率,那么,可以使用 <where>
解决:
-
标签之间有显示内容,在内容的最前面加入where关键字<where>
-
标签之间有显示内容,检查内容的最前端是含有<where>
或者AND
(不区分大小写),如果含有将其抹掉OR
<!--WHERE标签和IF标签动态生成SQL语句-->
<select id="where01" resultMap="StudentResultMapper">
SELECT * FROM student
<where>
<if test="studentName != null and studentName.trim() != ''">
AND student_name LIKE CONCAT('%',#{studentName},'%')
</if>
<if test="studentSex != null and studentSex.trim() != ''">
AND student_sex = #{studentSex}
</if>
</where>
</select>
检查内容的最前端是含有或者
AND
(不区分大小写),如果含有将其抹掉:
OR
6.2动态更新
(1)SET标签
我们开发中会涉及到对单表的不同字段进行修改,那么就会产生不同的更新语句,可以使用
<set>
标签:
-
标签之间有显示内容,在内容的最前面加入set关键字<set>
-
标签之间有显示内容,检查内容的最后端是含有<set>
,如果含有将其抹掉,
<!--SET标签更新数据-->
<update id="updata01" parameterType="student">
UPDATE student
<set>
<if test="studentName != null and studentName!= '' ">
student_name=#{studentName},
</if>
<if test="studentSex != null and studentSex!= '' ">
student_sex=#{studentSex},
</if>
<if test="age != null ">
age=#{age},
</if>
<if test="birthday != null ">
birthday=#{birthday},
</if>
</set>
WHERE student_id=#{studentId}
</update>
注意更新数据需要有提交:
sqlSession.commit();
@Test
public void set标签动态更新语句(){
Student s = new Student();
s.setStudentId(31);
s.setAge(18);
s.setStudentName("起舞弄清影");
studentMapper.updata01(s);
s = new Student();
s.setStudentId(32);
s.setAge(18);
s.setStudentName("何似在人间");
s.setStudentSex("诗");
studentMapper.updata01(s);
s = new Student();
s.setStudentId(33);
s.setAge(18);
s.setStudentName("明空");
s.setStudentSex("女");
s.setBirthday(new Date());
studentMapper.updata01(s);
sqlSession.commit();
}
6.3 动态添加:TRIM标签(还可以实现前两者)
标签中的属性是可以自由组合,实现
<trim>
或者
<where>
等其他负责功能
<set>
-
:标签内有显示内容的时候,在内容的prefix
加入的内容最前端
-
:标签内有显示内容的时候,在内容的suffix
加入的内容最后端
-
:标签内有显示内容的时候,检查跟最前的内容匹配,如果匹配成功将其除掉prefixOverrides
-
:标签内有显示内容的时候,检查跟最后的内容匹配,如果匹配成功将其除掉suffixOverrides
实现前文
where
和
set
功能:
<select id="where02" parameterType="student" resultMap="studentMapper">
SELECT * FROM student
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<!-- 类中属性 -->
<if test="studentName != null and studentName.trim()!=''">
AND student_name LIKE CONCAT('%',#{studentName},'%')
</if>
<!-- 类中属性 -->
<if test="studentSex != null and studentSex.trim()!=''">
AND student_sex=#{studentSex}
</if>
</trim>
</select>
<update id="update02" parameterType="student">
UPDATE student
<trim prefix="SET" suffixOverrides=",">
<if test="studentName != null and studentName!= '' ">
student_name=#{studentName},
</if>
<if test="studentSex != null and studentSex!= '' ">
student_sex=#{studentSex},
</if>
<if test="age != null ">
age=#{age},
</if>
<if test="birthday != null ">
birthday=#{birthday},
</if>
</trim>
WHERE student_id=#{studentId}
</update>
<trim>
标签:最经典的应用之一,动态添加语句,需要保证字段和数据顺序出现的一致性
<trim>
动态添加:
<insert id="add" parameterType="student" useGeneratedKeys="true" keyProperty="studentId">
INSERT INTO student
<!-- 动态添加的字段和数据顺序需要保持一致 -->
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="studentName != null and studentName != ''">
student_name,
</if>
<if test="studentSex != null and studentSex != ''">
student_sex,
</if>
<if test="age != null">
age,
</if>
<if test="birthday != null">
birthday,
</if>
</trim>
<trim prefix="VALUES (" suffix=")" suffixOverrides=",">
<if test="studentName != null and studentName != ''">
#{studentName},
</if>
<if test="studentSex != null and studentSex != ''">
#{studentSex},
</if>
<if test="age != null">
#{age},
</if>
<if test="birthday != null">
#{birthday},
</if>
</trim>
</insert>
@Test
public void trim标签动态添加语句(){
Student s = new Student();
s.setAge(18);
s.setStudentName("起舞弄清影");
studentMapper.add(s);
s = new Student();
s.setAge(18);
s.setStudentName("何似在人间");
s.setStudentSex("诗");
studentMapper.add(s);
s = new Student();
s.setAge(18);
s.setStudentName("明空");
s.setStudentSex("女");
s.setBirthday(new Date());
studentMapper.add(s);
sqlSession.commit();
}
6.5 FOREACH
标签(批量删除和批量添加)
FOREACH
<foreach>
:遍历数据或者集合使用
<foreach collection="" item="" open="" close="" separator="" index=""
-
:必填项,遍历集合的名称,重要:collection
-
:遍历集合中每个元素赋值给该变量item
-
:遍历整个集合结果内容的最前面加入的内容(open
)不是每次遍历加入的内容
-
:遍历整个集合结果内容的的最后面加入的内容(close
)不是每次遍历加入的内容
-
:每次遍历的结果之间使用什么进行分隔separator
-
:索引定义的名称index
注意:
如果传递的是数组,不要设置parameterType属性,让MyBatis自动识别
-
数组没有指定KEY,MyBatis默认传递一个形参数组的默认KEY:array
即:
<foreach collection="array"></foreach>
-
集合没有指定KEY,MyBatis默认传递一个形参集合的默认KEY:list
即:
<foreach collection="list"></foreach>
- map类型的数据设置为自身的key值
- 自定义对象设置为自身属性,如
则设置为void delete04(MyModel myModel);
<foreach collection="datas"></foreach>
- 当多于一个参数的时候,用@parm,如:
则设置为void delete05(@Param("model") MyModel myModel);
<foreach collection="model.datas"></foreach>
- 批量添加:必须要相同数量的字段和数据
<!-- 1.foreach标签:数组 -->
<!-- 传递的是数组,不要设置parameterType属性,让MyBatis自动识别 -->
<delete id="delete01">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 数组没有指定KEY,MyBatis默认传递一个形参数组的默认KEY:array -->
<foreach collection="array" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 2.foreach标签:List -->
<!--传递集合:`java.util.List` -->
<delete id="delete02" parameterType="list">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 集合没有指定KEY,MyBatis默认传递一个形参集合的默认KEY:list -->
<foreach collection="list" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 3.foreach标签:Map -->
<delete id="delete03" parameterType="map">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 设置你指定的KEY -->
<foreach collection="myArray" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 4.foreach标签:MyModel -->
<delete id="delete04" parameterType="com.yue.model.MyModel">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 设置你类中的集合属性 -->
<foreach collection="datas" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 5.foreach标签:MyModel -->
<delete id="delete05" parameterType="com.yue.model.MyModel">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 设置你类中的集合属性 -->
<foreach collection="model.datas" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 批量添加:相同数量的字段和数据 -->
<insert id="addBatch" parameterType="list">
INSERT INTO student (student_name,student_sex,age) VALUES
<foreach collection="list" item="st" separator=",">
(#{st.studentName},#{st.studentSex},#{st.age})
</foreach>
</insert>
void delete01(Integer[] arr);
void delete02(List<Integer> arr);
void delete03(Map<String,Object> tempMap);
void delete04(MyModel myModel);
void delete05(@Param("model") MyModel myModel);
@Test
public void foreach标签批量删除语句(){
studentMapper.delete01(new Integer[]{12,14,15});
List<Integer> tempList = new ArrayList<>();
tempList.add(14);
tempList.add(15);
tempList.add(12);
studentMapper.delete02(tempList);
Map<String,Object> tempMap = new HashMap<>();
tempMap.put("myArray",new Integer[]{12,14,15});
studentMapper.delete03(tempMap);
MyModel mm = new MyModel();
mm.setDatas(new String[]{"12","14"});
studentMapper.delete04(mm);
mm.setDatas(new String[]{"12","14","15"});
studentMapper.delete05(mm);
sqlSession.commit();
}
@Test
public void foreach语句_批量添加(){
List<Student> students = new ArrayList<>();
Student s = new Student();
s.setStudentName("小明");
s.setAge(10);
s.setStudentSex("男");
students.add(s);
s = new Student();
s.setStudentName("小红");
s.setAge(10);
s.setStudentSex("女");
students.add(s);
s = new Student();
s.setStudentName("小白");
s.setAge(10);
s.setStudentSex("女");
students.add(s);
studentMapper.addBatch(students);
sqlSession.commit();
}
6.6 CHOOSE标签
注意;选择标签:涉及知识点,小于号和中文比较
不支持,用
<
<
代替
如:
student_id < 10
:小于10的数据
或者:
在CDATA中不会进行转义,并且不支持标签
<![CDATA[ student_id<15 ]]>
<!-- 选择标签:涉及知识点,小于号和中文比较 -->
<select id="query01" parameterType="string" resultMap="studentMapper">
SELECT * FROM student
<where>
<choose>
<when test='sex == "男"'>
student_id > 16
</when>
<when test='sex == "女"'>
student_id < 10
</when>
<otherwise>
<!-- CDATA中不会进行转义,并且不支持标签 -->
<![CDATA[
student_id<15
]]>
</otherwise>
</choose>
</where>
</select>
注意:
<when test='sex == "男"'>
写条件时,只能外面
''
,里面
""
.否则不支持
6.7 sql
标签和 include
标签:提取公共sql语句
sql
include
<sql id="student_columns"">
公共的sql语句
</sql>
<include refid="student_columns"/>
- 如何使用:
令refid和sql中id属性一致
<select id="loadStudentByPK" parameterType="int" resultMap="StudentSelectResultMapper">
SELECT
<include refid="student_columns"/>
FROM student
WHERE student_id=#{id}
</select>
在 mybatis 的定义中,节点是用来定义可重用的 SQL 代码段。它可以被包含在其他语句里面, 使用
<sql>
节点来包含。
<include>
注意:
它里面是可以使用 ${}
占位符参数化的(注意,此处的参数不是调用时传进来的), 不同的属性值通过包含的实例而变化。
如:
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>
因此, 我们在连接查询时, 就不用手写那么多的别名了
<select id="selectUsers" resultType="map">
select
<include refid="userColumns">
<property name="alias" value="t1"/>
</include>,
<include refid="userColumns">
<property name="alias" value="t2"/>
</include>
from some_table t1
cross join some_table t2
</select>
-来自此处
6.8 附录代码:
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和接口全路径相同,方法名称和ID值一致 -->
<!--&&和and一致-->
<mapper namespace="com.yue.mapper.StudentMapper">
<!--手动映射-->
<resultMap id="StudentResultMapper" type="Student" autoMapping="false">
<!-- <id>标签:做主键映射,MyBatis框架通过ID标签区分是否是相同数据 -->
<!-- jdbcType/javaType可以省略不写 -->
<id column="student_id" property="studentId"/>
<result column="student_name" property="studentName"/>
<result column="student_sex" property="studentSex"/>
<result column="age" property="age" />
<result column="birthday" property="birthday"/>
</resultMap>
<!--1.通过java程序拼接-->
<select id="if01" resultMap="StudentResultMapper">
SELECT * FROM student WHERE 1=1
<if test="studentName != null and studentName.trim() != ''">
AND student_name LIKE #{studentName}
</if>
<if test="studentSex != null && studentSex.trim() != ''">
AND student_sex = #{studentSex}
</if>
</select>
<!--通过MySql函数拼接-->
<select id="if02" resultMap="StudentResultMapper">
SELECT * FROM student WHERE 1=1
<if test="studentName != null and studentName.trim() != ''">
AND student_name LIKE CONCAT('%',#{studentName},'%')
</if>
<if test="studentSex != null and studentSex.trim() != ''">
AND student_sex = #{studentSex}
</if>
</select>
<!--WHERE标签和IF标签动态生成SQL语句-->
<select id="where01" resultMap="StudentResultMapper">
SELECT * FROM student
<where>
<if test="studentName != null and studentName.trim() != ''">
AND student_name LIKE CONCAT('%',#{studentName},'%')
</if>
<if test="studentSex != null and studentSex.trim() != ''">
AND student_sex = #{studentSex}
</if>
</where>
</select>
<!--SET标签更新数据-->
<update id="updata01" parameterType="student">
UPDATE student
<set>
<if test="studentName != null and studentName!= '' ">
student_name=#{studentName},
</if>
<if test="studentSex != null and studentSex!= '' ">
student_sex=#{studentSex},
</if>
<if test="age != null ">
age=#{age},
</if>
<if test="birthday != null ">
birthday=#{birthday},
</if>
</set>
WHERE student_id=#{studentId}
</update>
<insert id="add" parameterType="student" useGeneratedKeys="true" keyProperty="studentId">
INSERT INTO student
<!-- 动态添加的字段和数据顺序需要保持一致 -->
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="studentName != null and studentName != ''">
student_name,
</if>
<if test="studentSex != null and studentSex != ''">
student_sex,
</if>
<if test="age != null">
age,
</if>
<if test="birthday != null">
birthday,
</if>
</trim>
<trim prefix="VALUES (" suffix=")" suffixOverrides=",">
<if test="studentName != null and studentName != ''">
#{studentName},
</if>
<if test="studentSex != null and studentSex != ''">
#{studentSex},
</if>
<if test="age != null">
#{age},
</if>
<if test="birthday != null">
#{birthday},
</if>
</trim>
</insert>
<!-- 1.foreach标签:数组 -->
<!-- 传递的是数组,不要设置parameterType属性,让MyBatis自动识别 -->
<delete id="delete01">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 数组没有指定KEY,MyBatis默认传递一个形参数组的默认KEY:array -->
<foreach collection="array" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 2.foreach标签:List -->
<!--传递集合:`java.util.List` -->
<delete id="delete02" parameterType="list">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 集合没有指定KEY,MyBatis默认传递一个形参集合的默认KEY:list -->
<foreach collection="list" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 3.foreach标签:Map -->
<delete id="delete03" parameterType="map">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 设置你指定的KEY -->
<foreach collection="myArray" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 4.foreach标签:MyModel -->
<delete id="delete04" parameterType="com.yue.model.MyModel">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 设置你类中的集合属性 -->
<foreach collection="datas" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 5.foreach标签:MyModel -->
<delete id="delete05" parameterType="com.yue.model.MyModel">
DELETE FROM student WHERE student_id <!-- IN(12,14,15) -->
<!-- 设置你类中的集合属性 -->
<foreach collection="model.datas" item="val" open="IN (" close=")" separator=",">
#{val}
</foreach>
</delete>
<!-- 批量添加:相同数量的字段和数据 -->
<insert id="addBatch" parameterType="list">
INSERT INTO student (student_name,student_sex,age) VALUES
<foreach collection="list" item="st" separator=",">
(#{st.studentName},#{st.studentSex},#{st.age})
</foreach>
</insert>
<!-- 选择标签:涉及知识点,小于号和中文比较 -->
<select id="query01" parameterType="string" resultMap="studentMapper">
SELECT * FROM student
<where>
<choose>
<when test='sex == "男"'>
student_id > 16
</when>
<when test='sex == "女"'>
student_id < 10
</when>
<otherwise>
<!-- CDATA中不会进行转义,并且不支持标签 -->
<![CDATA[
student_id<15
]]>
</otherwise>
</choose>
</where>
</select>
</mapper>
接口文件:
package com.yue.mapper;
import com.yue.model.MyModel;
import com.yue.model.Student;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface StudentMapper {
List<Student> if01(
@Param("studentName") String studentName,
@Param("studentSex") String studentSex);
List<Student> if02(
@Param("studentName") String studentName,
@Param("studentSex") String studentSex);
List<Student> where01(Student student);
void updata01(Student student);
void add(Student student);
void delete01(Integer[] arr);
void delete02(List<Integer> arr);
void delete03(Map<String,Object> tempMap);
void delete04(MyModel myModel);
void delete05(@Param("model") MyModel myModel);
void addBatch(List<Student> studentList);
List<Student> query01(String studentSex);
}
测试文件:
package com.yue.test;
import com.yue.mapper.StudentMapper;
import com.yue.model.MyModel;
import com.yue.model.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.*;
public class 动态SQL语句 {
private SqlSession sqlSession;
private StudentMapper studentMapper;
@Before
public void init()throws Exception{
String path = "config/mybatis-config.xml";
InputStream in = Resources.getResourceAsStream(path);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession();
studentMapper = sqlSession.getMapper(StudentMapper.class);
}
@After
public void close(){
if(sqlSession!=null){
sqlSession.close();
}
}
@Test
public void if标签(){
System.out.println("通过姓名模糊查询");
String studentName ="孙";
/*1.自己拼`%`*/
List<Student> list01 = studentMapper.if01("%"+studentName+"%", null);
System.out.println("list01 = " + list01);
List<Student> list02 = studentMapper.if01("", "女");
System.out.println("list02 = " + list02);
/*2.不用自己拼`%`*/
List<Student> list03 = studentMapper.if02(studentName, null);
System.out.println("list03 = " + list03);
List<Student> list04 = studentMapper.if02("", "女");
System.out.println("list04 = " + list04);
}
@Test
public void where标签(){
List<Student> list = studentMapper.where01(null);
System.out.println("list = " + list);
Student student = new Student();
student.setStudentName("北");
list = studentMapper.where01(student);
System.out.println("list = " + list);
student = new Student();
student.setStudentSex("男");
list = studentMapper.where01(student);
System.out.println("list = " + list);
student = new Student();
student.setStudentName("北");
student.setStudentSex("男");
list = studentMapper.where01(student);
System.out.println("list = " + list);
}
@Test
public void set标签动态更新语句(){
Student s = new Student();
s.setStudentId(31);
s.setAge(18);
s.setStudentName("起舞弄清影");
studentMapper.updata01(s);
s = new Student();
s.setStudentId(32);
s.setAge(18);
s.setStudentName("何似在人间");
s.setStudentSex("诗");
studentMapper.updata01(s);
s = new Student();
s.setStudentId(33);
s.setAge(18);
s.setStudentName("明空");
s.setStudentSex("女");
s.setBirthday(new Date());
studentMapper.updata01(s);
sqlSession.commit();
}
@Test
public void trim标签动态添加语句(){
Student s = new Student();
s.setAge(18);
s.setStudentName("起舞弄清影");
studentMapper.add(s);
s = new Student();
s.setAge(18);
s.setStudentName("何似在人间");
s.setStudentSex("诗");
studentMapper.add(s);
s = new Student();
s.setAge(18);
s.setStudentName("明空");
s.setStudentSex("女");
s.setBirthday(new Date());
studentMapper.add(s);
sqlSession.commit();
}
@Test
public void foreach标签批量删除语句(){
studentMapper.delete01(new Integer[]{12,14,15});
List<Integer> tempList = new ArrayList<>();
tempList.add(14);
tempList.add(15);
tempList.add(12);
studentMapper.delete02(tempList);
Map<String,Object> tempMap = new HashMap<>();
tempMap.put("myArray",new Integer[]{12,14,15});
studentMapper.delete03(tempMap);
MyModel mm = new MyModel();
mm.setDatas(new String[]{"12","14"});
studentMapper.delete04(mm);
mm.setDatas(new String[]{"12","14","15"});
studentMapper.delete05(mm);
sqlSession.commit();
}
@Test
public void foreach语句_批量添加(){
List<Student> students = new ArrayList<>();
Student s = new Student();
s.setStudentName("小明");
s.setAge(10);
s.setStudentSex("男");
students.add(s);
s = new Student();
s.setStudentName("小红");
s.setAge(10);
s.setStudentSex("女");
students.add(s);
s = new Student();
s.setStudentName("小白");
s.setAge(10);
s.setStudentSex("女");
students.add(s);
studentMapper.addBatch(students);
sqlSession.commit();
}
@Test
public void choose标签(){
studentMapper.query01("男");
studentMapper.query01("女");
studentMapper.query01(null);
}
}