关系查询
一对一
public class Student {
private Integer sId;
private String sName;
// 通过对象表示一对一(对象不能自动映射)
private Teacher tId;
}
例题
查询每个学生的老师
关联查询
<!-- id:对应方法名 -->
<!-- resultMap:调用resultMap -->
<select id="selStu" resultMap="stuAndTea">
SELECT * FROM student s
left join
teacher t
on s.t_id = t.t_id
</select>
<!-- id:自定义resultMap -->
<!-- type:指定实体类对象 -->
<resultMap id="stuAndTea" type="Student">
<!--id:指定主键映射规则-->
<!--column:指定(数据库)字段列-->
<!--property:指定(实体类)属性对象-->
<id column="s_id" property="sId"/>
<!--result:指定非主键映射规则-->
<!--column:指定(数据库)字段列-->
<!--property:指定(实体类)属性对象-->
<result column="s_name" property="sName"/>
<!-- teacher是student的对象 -->
<!-- association:给对象赋值 -->
<!-- property:属性名 -->
<!-- javaType:指定(实体类)对象 -->
<association property="tId" javaType="teacher">
<id column="t_id" property="tId"/>
<result column="t_name" property="tName"/>
</association>
</resultMap>
分步查询
<!-- 思路分析:查出所有的学生,根据每个学生的老师id去查询对应的老师信息 -->
<select id="selStu" resultMap="stuAndTea">
SELECT * FROM student;
</select>
<resultMap type="student" id="stuAndTea">
<id column="s_id" property="sId"/>
<result column="s_name" property="sName"/>
<!-- column:提取字段参数交给调用的查询方法 -->
<!-- select:调用查询方法 -->
<association property="tId" javaType="teacher" column="t_id" select="getTeacherById"></association>
</resultMap>
<select id="getTeacherById" resultType="teacher">
select * from teacher where t_id = #{tid}
</select>
一对多
一个对象关联多个对象
数据层面:通过外键
public class Teacher {
private Integer tId;
private String tName;
// 通过集合表示一对多(集合不能自动映射)
List<Student> stus;
}
例题
查询老师的所有学生
关联查询
<select id="selTea" resultMap="teaAndAllStu">
SELECT * FROM teacher t
LEFT JOIN
student s
ON t.t_id = s.t_id
</select>
<resultMap type="teacher" id="teaAndAllStu" autoMapping="true">
<id column="t_id" property="tId"/>
<!-- collection:集合赋值 -->
<!-- ofType:集合所属类型 -->
<collection property="stus" ofType="student" autoMapping="true">
<id column="s_id" property="sId"/>
<result column="s_name" property="sName"/>
</collection>
</resultMap>
分步查询
<select id="selTea" resultMap="teaAndStu">
SELECT * FROM teacher;
</select>
<resultMap type="teacher" id="teaAndStu" autoMapping="true">
<id column="t_id" property="tId"/>
<collection property="stus" ofType="student" column="t_id" select="getStudentByTid"></collection>
</resultMap>
<select id="getStudentByTid" resultType="student">
SELECT * FROM student WHERE t_id = #{t_id}
</select>
多对多
数据库层面:必须引入中间表(用于记录表之间的关系)
实体类层面:本质是一对多
@Data
public class Student {
private int sid;
private String sname;
//多对多--最终处理时转为1对多
List<Course> allCou;
}
关联查询
<select id="getStudentAndCouese" resultMap="stuAndCou">
SELECT * FROM student s
LEFT JOIN
stu_cou sc
ON s.s_id = sc.s_id
LEFT JOIN
course c
ON sc.c_id = c.c_id
</select>
<resultMap type="student" id="stuAndCou" autoMapping="true">
<id column="s_id" property="sId"/>
<result column="s_name" property="sName"/>
<collection property="courses" ofType="course" autoMapping="true">
<id column="cid" property="cid"/>
<result column="c_name" property="cName"/>
</collection>
</resultMap>
分布查询
<select id="getStudentAndCouese" resultMap="stuAndCou">
SELECT * FROM student;
</select>
<resultMap type="student" id="stuAndCou" autoMapping="true">
<id column="s_id" property="sId"/>
<result column="s_name" property="sName"/>
<collection property="courses" ofType="course" column="s_id" select="getCourseBySid"></collection>
</resultMap>
<select id="getCourseBySid" resultType="course">
SELECT * FROM stu_cou sc
JOIN
course c
ON sc.c_id = c.c_id AND sc.s_id = #{s_id}
</select>
延时加载(按需加载)
作用
-
提升数据查询效率
-
减少计算机压力
解决方案
-
使用关联查询,不使用分步查询
-
开启延迟加载(按需加载)
全局开启
<settings>
<!-- 全局打开延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
局部开启
局部配置会覆盖全局配置
<!-- 对象或集合的标签上添加fetchType属性 -->
<!-- fetchType:lazy = 开启、eager = 关闭 -->
<collection fetchType="lazy"></collection>
缓存
加快运行速度
查询时,第一次将查询出的数据在缓存中放一份。以后遇到同样的操作,直接从缓存读取
开启缓存
一级缓存
sqlSession级别缓存:在同一个sqlSession中做同样的数据查询,第二次查询会走缓存
开启方式:默认打开的
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
StudentMapper mapper1 = sqlSession.getMapper(StudentMapper.class);
List<Student> students1 = mapper1.getStudentAndCouese();
// 走缓存
StudentMapper mapper2 = sqlSession.getMapper(StudentMapper.class);
List<Student> students2 = mapper2.getStudentAndCouese();
sqlSession.close();
二级缓存
mapper级别缓存 :同个mapper同个接口,第二次调用相同接口时走缓存
开启方式
全局配置文件中打开缓存开关
<settings>
<!-- 开启二级缓存 -->
<setting name="cacheEnabled" value="true"/>
</settings>
开启缓存接口对应mapper中声明一个cache标签
<!-- 当前接口开启二级缓存 -->
<cache></cache>
二级缓存底层实现基于对象序列化与反序列化实现
public class Student implements Serializable
参数传递
@Param
-
接口(删除指定用户)
// @Param:对参数取别名 Integer deleteUser(@Param("id")Integer id);
-
接口对应的xml配置
<delete id="deleteUser"> <!-- XML配置通过#{"别名"}在SQL语句上取值 --> DELETE FROM user WHERE uid = #{id} </delete>
传递Map
-
XML中声明参数类型 —— map
<!-- parameterType:传递参数类型,使用Map的键取值 --> <select id="getStuToName" resultType="student" parameterType="map"> SELECT * FROM student where s_name = #{name}; </select>
-
Dao层内传递Map类型值
Products getProByMap(Map map);
-
test层使用Map值传递参数
SqlSession sqlSession = SqlSessionUtil.getSqlSession(); TestDao Test = sqlSession.getMapper(TestDao.class); // 创建HashMap Map map = new HashMap(); // 写入键值对 map.put("name","a"); // 传递map作为参数 List<Student> couese = Test.getStuToName(map); for (Student student : couese) { System.out.println(student); } sqlSession.close();
SQL片段
-
sql片段,记录一段公共sql片段,用include标签引入
-
声明sql片段:
<sql>
标签
<sql id="information">eid,emp_name,age,sex,email</sql>
-
引用sql片段:
<include>
标签
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getUser" resultType="User">
select <include refid="information"></include> from user
</select>
模糊查询
模糊查询的模糊条件通过参数形式传递
<select id="getLikeStu" resultType="products">
SELECT * FROM products WHERE name LIKE "%"#{name}"%";
</select>
使用时在service层根据不同的业务场景生成不同的模糊条件
List<Products> proByLike = mapper.getProByLike("name");
分页查询
方式一
limit关键字分页
SELECT * FROM 表名 LIMIT 从哪查,查几条
前段传参数:pagenum:页数、pagesize:条数
转换:SELECT * FROM 表名 LIMIT (pagenum-1)*pagesize,pagesize
注意:service层完成转换工作
dao层接口:
//分页查询
List<Products> getProPage(@Param("startIndex")int startIndex,@Param("pageSize")int pageSize);
xml文件:
<select id="getPage" resultType="student">
SELECT * FROM student LIMIT #{startIndex},#{pageSize};
</select>
service层做转换
int pageNum = 2;
int pageSize = 2;
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
TestDao Test = sqlSession.getMapper(TestDao.class);
List<Student> page = Test.getPage((pageNum - 1) * pageSize, pageSize);
for (Student student : page) {
System.out.println(student);
}
sqlSession.close();
方式二
PageHelper
-
导入依赖:pageHelper核心包、jsql转换包
-
在全局配置中配置插件
<plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin> </plugins>
-
使用PageHelper
// (0++)
int pageNum = 0;
int pageSize = 2;
// 配置分页插件
PageHelper.startPage(pageNum, pageSize);
// 调用DAO层查询数据(所有信息)
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
TestDao mapper = sqlSession.getMapper(TestDao.class);
// 将Dao数据放入PageHelper进行分页
PageInfo<Student> pageInfo = new PageInfo<>(mapper.getPage());
// 获取分页后数据
List<Student> list = pageInfo.getList();
for (Student student : list) {
System.out.println(student);
}
// 获取总页数
System.out.println(pageInfo.getPages());
sqlSession.close();
动态SQL
根据不同的条件拼接不同的SQL语句
if
test(条件语句)值为真,拼接SQL,否则不进行拼接
<select id="selStu" resultType="student">
<!-- 添加1=1恒成立语句,防止条件不成立引发sql语句异常 -->
SELECT * FROM student where 1=1
<if test="stu.sName != null and stu.sName != ''">
and s_name = #{stu.sName}
</if>
</select>
where
常与<if>
标签结合使用,<if>
语句发生拼接,自动加where关键字 ,删除第一个拼接语句连接词(and、or)
<select id="selStu" resultType="student">
SELECT * FROM student
<where>
<if test="stu.sName != null and stu.sName != ''">
and s_name = #{stu.sName}
</if>
<if test="stu.sAge != null and stu.sAge != ''">
or s_age = #{stu.sAge}
</if>
</where>
</select>
set
常与<if>
标签结合使用,<if>
语句发生拼接,之前添加一个set关键字,在最后一次拼接的地方去除逗号
<update id="selStu">
update student
<set>
<if test="stu.sName != null and stu.sName != ''">
s_name = #{stu.sName},
</if>
<if test="stu.sAge != null and stu.sAge != ''">
s_age = #{stu.sAge},
</if>
</set>
where s_id = 1
</update>
trim
<select id="getProByCondtion" resultType="products">
SELECT * FROM products
<!-- prefix:拼接最前面添加XX -->
<!-- prefixOverrides:拼接在前面去除XX -->
<!-- suffix:拼接最后面添加XX -->
<!-- suffixOverrides:拼接在后面去除XX -->
<trim prefix="WHERE" prefixOverrides="OR">
<if test="name!=null and name !='' ">
OR name = #{name}
</if>
<if test="location!=null and location !='' ">
OR location = #{location}
</if>
</trim>
</select>
choose、when、otherwise
-
相当于
if..else
-
when至少有一个,otherwise至多有一个
-
when只执行一个
<select id="selStu" resultType="student">
select * from student
<where>
<choose>
<!-- 只执行一个,查询无果直接结束 -->
<when test="stu.sName != null and stu.sName != ''">
s_name = #{stu.sName}
</when>
<when test="stu.sAge != null and stu.sAge != ''">
s_age = #{stu.sAge}
</when>
<!-- 所有when都不成立时执行 -->
<otherwise>
s_id = 1
</otherwise>
</choose>
</where>
</select>
foreach
-
属性:
-
collection:循环的数组或集合
-
item:遍历元素(i)
-
separator:分隔符
-
open:foreach标签开始符
-
close:foreach标签结束符
-
-
批量删除
<!-- Integer selStu(@Param("id") Integer[] id); --> <delete id="selStu"> delete from student where s_id in <foreach collection="id" item="i" separator="," open="(" close=")"> #{i} </foreach> </delete>
SqlSession sqlSession = SqlSessionUtil.getSqlSession(); TestDao mapper = sqlSession.getMapper(TestDao.class); int result = mapper.selStu(new Integer[]{10, 11, 12, 9}); System.out.println(result); sqlSession.commit(); sqlSession.close();
-
批量添加
<!-- Integer selStu(@Param("stu") List<Student> stu); -->
<insert id="selStu">
insert into student values
<foreach collection="stu" item="i" separator="," >
<!-- 使用时注意使用遍历元素点属性 -->
(default ,#{i.sName},#{i.sAge},#{i.sGrade})
</foreach>
</insert>
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
TestDao mapper = sqlSession.getMapper(TestDao.class);
Student stu1 = new Student(null,"a",18,12.2);
Student stu2 = new Student(null,"b",28,35.0);
Student stu3 = new Student(null,"c",38,22.5);
Integer integer = mapper.selStu(Arrays.asList(stu1, stu2, stu3));
System.out.println(integer);
sqlSession.commit();
sqlSession.close();