Mybatis高级

小韩程序员

已于 2023-09-15 22:39:38 修改

阅读量31

点赞数

文章标签： mybatis 数据库 sql

于 2023-09-07 20:11:16 首次发布

本文链接：https://blog.csdn.net/aaoouuo/article/details/132745130

版权

3一动态sql

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>

1 if

<if test="条件">
sql 语句
</if>
当条件成立的时候，会执行sql语句
if (条件){
sql 语句
}

2 choose...when...otherwise...

<choose>
<when test="条件1">
   sql语句1
</when>

<when test="条件2">
   sql语句2
</when>

<otherwise>
sql语句3
</otherwise>
</choose>

和我们java的if...else if ...else格式一样
当条件1成立，那么就不会执行后面的代码

3 where

<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>

4 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>

5 foreach

适用于 id in（x,x,x）

循环遍历标签。适用于多个参数或者的关系。
<foreach collection=“”open=“”close=“”item=“”separator=“”>
获取参数
</foreach>

6 trim

格式 <trim prefix=前缀'' prefixoverrides=''
suffix=后缀'' suffixoverrides=''>

1、替换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>

2、替换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>

7 Sql片段

<sql id="别名">
查询的所有字段
</sql>

使用的时候 <include refid="别名"/>

二分页

1 分页的使用步骤

1 导入maven依赖

<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>

<plugin interceptor="com.github.pagehelper.PageHelper">

<property name="dialect" value="mysql"/>
</plugin>
</plugins>

3 java代码测试

@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、创建实体类

public class Card {
    private int id;
    private String num;
    private User user;
}

2、配置文件

<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>

3、标签介绍

<resultMap>：配置数据库库字段和Java对象属性的映射关系标签。
    id 属性：唯一标识
    type 属性：实体对象类型
<id>：配置主键映射关系标签。
<result>：配置非主键映射关系标签。
    column 属性：表中字段名称
    property 属性： 实体对象变量名称
<association>：配置被包含对象的映射关系标签。
    property 属性：被包含对象的变量名
    javaType 属性：被包含对象的数据类型

4、测试

@Test
public void test05(){
    List<Card> allCard = cardMapper.findAllCard();
    System.out.println(allCard);
}

2 一对多

1、实体类和表

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;
}

2、配置文件

<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、标签介绍

<resultMap>：配置字段和对象属性的映射关系标签。
    id 属性：唯一标识
    type 属性：实体对象类型
<id>：配置主键映射关系标签。
<result>：配置非主键映射关系标签。
    column 属性：表中字段名称
    property 属性： 实体对象变量名称
<collection>：配置被包含集合对象的映射关系标签。
    property 属性：被包含集合对象的变量名
    ofType 属性：集合中保存的对象数据类型

3 多对多

1 实体类

@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;
}

2 编写mapper

public interface StudentMapper {

    List<Student> findAllStudent();
}

public interface StudentMapper {

    List<Student> findAllStudent();
}

3 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>

4 测试

@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();
    }
}