动态sql
在整合代码中加UserMapper接口和配置文件
public interface UserMapper {
List<User> queryUserByCondition(User user);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xinzhi.dao.UserMapper">
<select id="queryUserByCondition" resultType="com.xinzhi.model.User" parameterType="com.xinzhi.model.User">
</select>
</mapper>
if
格式
<if test="条件">
sql 语句
</if>
当条件 成立时,会执行sql语句
if(条件){
sql语句
}
举例
<select id="queryUserByCondition" resultType="com.xinzhi.model.User" parameterType="com.xinzhi.model.User">
select id,username,password,age,phone from user where 1=1
<if test="age!=null and age!=''">
and age=#{age}
</if>
<if test="phone!=null and phone!=''">
and phone=#{phone}
</if>
</select>
测试
@Test
public void test02(){
User user = new User();
user.setPhone("120");
user.setAge(11);
List<User> users = userMapper.queryUserByCondition(user);
System.out.println(users);
}
choose…when…otherwise…
格式
<choose>
<when test="条件1">
sql语句1
</when>
<when test="条件2">
sql语句2
</when>
<otherwise>
sql语句3
</otherwise>
</chosse>
和我们java的if...else if ...else格式一样
当条件1成立,那么就不会执行后面的代码
案例
<select id="queryUserByCondition" resultType="com.xinzhi.model.User" parameterType="com.xinzhi.model.User">
select id,username,password,age,phone from user where 1=1
<choose>
<when test="age!=null and age!=''">
and age=#{age}
</when>
<when test="phone!=null and phone!=''">
and phone=#{phone}
</when>
<otherwise>
and password='112233'
</otherwise>
</choose>
</select>
where
- 标签里边的if 至少有一个成立,就会动态添加一个where,如果都不成立,不添加where
- 第一个if条件成立的,会自动去除连接符and 或者 or
<select id="queryUserByCondition" resultType="com.xinzhi.model.User" parameterType="com.xinzhi.model.User">
select id,username,password,age,phone from user
<where>
<if test="age!=null and age!=''">
and age=#{age}
</if>
<if test="phone!=null and phone!=''">
and phone=#{phone}
</if>
</where>
</select>
set
说明: 动态添加了set字段,也会动态的去掉最后一个逗号
<update id="updateUser" parameterType="com.xinzhi.model.User">
update user
<set>
<if test="username!=null and username!=''">username=#{username},</if>
<if test="password!=null and password!=''">password=#{password},</if>
</set>
where id=#{id}
</update>
@Test
public void test03(){
User user = new User();
user.setId(1);
user.setUsername("韩哥哥123");
user.setPassword("332211");
String message = userMapper.updateUser(user)>0?"成功":"失败";
System.out.println(message);
}
foreach
说明:适用于 id,in(xxx)
格式
循环遍历标签,适用于多个参数或者的关系。
<foreach
collection="" open=""close=""item="" separator="">
获取参数
</foreach>
<select id="queryUsersByIds" resultType="com.xinzhi.model.User"
parameterType="java.lang.Integer">
select id,username,password,age,phone from user
<where>
<foreach collection="list" item="id" open="id in (" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
@Test
public void test04(){
ArrayList<Integer> ids = new ArrayList<Integer>();
Collections.addAll(ids, 1, 2, 3, 4);
List<User> users = userMapper.queryUsersByIds(ids);
System.out.println(users);
}
trim
格式 pre- presay
格式 <trim prefix=前缀'' prefixoverrides=''
suffix=后缀'' suffixoverrides=''>
替换where
<select id="queryUserByCondition" resultType="com.xinzhi.model.User" parameterType="com.xinzhi.model.User">
select id,username,password,age,phone from user
<trim prefix="where" prefixOverrides="and">
<if test="age!=null and age!=''">
and age=#{age}
</if>
<if test="phone!=null and phone!=''">
and phone=#{phone}
</if>
</trim>
</select>
替换set
<update id="updateUser" parameterType="com.xinzhi.model.User">
update user
<trim prefix="set" suffixOverrides=",">
<if test="password!=null and password!=''">
password=#{password},
</if>
<if test="age!=null and age!=''">
age=#{age},
</if>
</trim>
where id=#{id}
</update>
sql片段
<sql id="别名">
查询的所有字段
</sql>
使用的时候 <include refid="别名"/>
分页
1、导入maven依赖
<!-- pageHelper依赖-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.6</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.6</version>
</dependency>
2 mybatis配置文件中指定方言
<plugins>
<!-- 注意:分页助手的插件 配置在通用mapper之前 -->
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 指定方言 -->
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
测试
@Test
public void test02(){
User user = new User();
PageHelper.startPage(1, 3);
List<User> users = userMapper.queryUsersByCondition(user);
PageInfo<User> pageInfo = new PageInfo<User>(users);
System.out.println("总条数:"+pageInfo.getTotal());
System.out.println("总页数:"+pageInfo.getPages());
System.out.println("当前页:"+pageInfo.getPageNum());
System.out.println("每页显示长度:"+pageInfo.getPageSize());
System.out.println("是否第一页:"+pageInfo.isIsFirstPage());
System.out.println("是否最后一页:"+pageInfo.isIsLastPage());
List<User> list = pageInfo.getList();
for (User user1 : list) {
System.out.println(user1);
}
}
mybatis多表查询
1、一对一
步骤
1、建表 user和card
2、创建实体类
3、配置文件
4、测试
创建实体类
public class Card {
private int id;
private String num;
private User user;
}
配置文件
<resultMap id="cards" type="com.xinzhi.model.Card">
<id column="id" property="id"></id>
<result column="num" property="num"></result>
<association property="user" javaType="com.xinzhi.model.User">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="age" property="age"></result>
<result column="phone" property="phone"></result>
</association>
</resultMap>
<select id="findAllCard" resultMap="cards">
select c.id id,num,uid,username,password,age,phone from card c,user u where c.uid=u.id
</select>
标签介绍
<resultMap>:配置数据库库字段和Java对象属性的映射关系标签。
id 属性:唯一标识
type 属性:实体对象类型
<id>:配置主键映射关系标签。
<result>:配置非主键映射关系标签。
column 属性:表中字段名称
property 属性: 实体对象变量名称
<association>:配置被包含对象的映射关系标签。
property 属性:被包含对象的变量名
javaType 属性:被包含对象的数据类型
测试
@Test
public void test05(){
List<Card> allCard = cardMapper.findAllCard();
System.out.println(allCard);
}
2、一对多
实体类
package com.xinzhi.model;
public class Student {
private int id;
private String name;
private int age;
}
public class Classes {
private int id;
private String name;
private List<Student> students;
}
配置文件
<resultMap id="class" type="com.xinzhi.model.Classes">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<collection property="students" ofType="com.xinzhi.model.Student">
<id column="sid" property="id"></id>
<result column="sage" property="age"></result>
<result column="sname" property="name"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="class">
select c.id id,c.name name,s.id sid,s.name sname,s.age sage from classes c,student s where s.cid=c.id
</select>
3、多对多
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Student {
private int id;
private String name;
private int age;
private List<Teacher> teachers;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Teacher {
private int id;
private String name;
private String sex;
private List<Student> students;
}
编写mapper
public interface StudentMapper {
List<Student> findAllStudent();
}
public interface StudentMapper {
List<Student> findAllStudent();
}
mapper配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xinzhi.dao.StudentMapper">
<resultMap id="student" type="com.xinzhi.model.Student">
<id property="id" column="sid" />
<result property="name" column="sname"/>
<result property="age" column="sage"/>
<collection property="teachers" ofType="com.xinzhi.model.Teacher">
<id property="id" column="tid" />
<result property="name" column="tname"/>
<result property="sex" column="tsex"/>
</collection>
</resultMap>
<select id="findAllStudent" resultMap="student">
select
s.id sid,s.name sname,s.age sage,
t.id tid,t.name tname,t.sex tsex
from
teacher t,student s,stu_teach st
where
t.id=st.tid and s.id=st.sid
</select>
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xinzhi.dao.TeachertMapper">
<resultMap id="teacher" type="com.xinzhi.model.Teacher">
<id property="id" column="tid" />
<result property="name" column="tname"/>
<result property="sex" column="tsex"/>
<collection property="students" ofType="com.xinzhi.model.Student">
<id property="id" column="sid" />
<result property="name" column="sname"/>
<result property="age" column="sage"/>
</collection>
</resultMap>
<select id="findAllTeacher" resultMap="teacher">
select
t.id tid,t.name tname,t.sex tsex,
s.id sid,s.name sname,s.age sage
from
teacher t,student s,stu_teach st
where
t.id=st.tid and s.id=st.sid
</select>
</mapper>
测试
@Autowired
private StudentMapper studentMapper;
@Autowired
private TeachertMapper teachertMapper;
@Test
public void test06(){
List<Student> allStudent = studentMapper.findAllStudent();
for (Student student : allStudent) {
String name = student.getName();
System.out.println(name);
for (Teacher teacher : student.getTeachers()) {
System.out.print("\t"+teacher.getName()+"-"+teacher.getSex()+"\t");
}
System.out.println();
}
}