Javaweb-MyBatis查询&动态sql

最新推荐文章于 2024-06-30 04:33:22 发布

吃土的柚

最新推荐文章于 2024-06-30 04:33:22 发布

阅读量1.2k

点赞数

文章标签： mybatis sql 数据库

本文链接：https://blog.csdn.net/qq_44157505/article/details/130351491

版权

MyBatis查询&动态sql

1.Mybatis查询

1.1 环境准备

1.1.1 数据库表

-- 员工管理
create table emp (
  id int unsigned primary key auto_increment comment 'ID',
  username varchar(20) not null unique comment '用户名',
  password varchar(32) default '123456' comment '密码',
  name varchar(10) not null comment '姓名',
  gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
  image varchar(300) comment '图像',
  job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
  entrydate date comment '入职时间',
  dept_id int unsigned comment '部门ID',
  create_time datetime not null comment '创建时间',
  update_time datetime not null comment '修改时间'
) comment '员工表';

INSERT INTO emp
	(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
	(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
	(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
	(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
	(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
	(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
	(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
	(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
	(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
	(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
	(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
	(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()),
	(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()),
	(13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()),
	(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
	(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
	(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()),
	(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

1.1.2 实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    private Integer id;
    private String username;
    private String password;
    private String name;
    private Short gender;
    private String image;
    private Short job;

    //注意:这四个属性跟数据表中的字段不一致
    private LocalDate ed;

    private Integer deptId;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

1.1.3 mapper接口

public interface EmpMapper {

}

1.1.4 工具类

//mybatis工具类
public class MyBatisUtil {

    private static SqlSessionFactory sqlSessionFactory;

    //保证SqlSessionFactory是单例的
    static {
        try {
            //读取主配置文件,读成数据流
            InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");

            //创建SqlSessionFactory
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //获取Sqlsession
    public static SqlSession getSqlSession(){
        //获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }

    //提交事务和关闭Session
    public static void close(SqlSession sqlSession){

        if(sqlSession != null){
            //提交事务
            sqlSession.commit();

            //释放资源
            sqlSession.close();
        }
    }
}

1.2 结果封装

执行查询SQL是会有结果集返回来的，mybatis会将结果封装到我们指定的实体中

实体类属性名和数据库查询结果集中名称一致的字段，mybatis会自动封装
实体类属性名和数据库查询结果集中名称不一致的字段，不能自动封装，需要我们手动处理
① 添加查询所有的方法

    //查询
    @Select("select * from emp")
    List<Emp> findAll();

② 测试

    //查询所有
    @Test
    public void testFindAll() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        List<Emp> empList = empMapper.findAll();
        empList.forEach(e -> System.out.println(e));//lambda方式打印

        MybatisUtil.close(sqlSession);
    }

1.2.1 驼峰映射

当表中字段和类中属性的关系是_和驼峰写法时，可以开启mybatis的自动驼峰映射

<setting name="mapUnderscoreToCamelCase" value="true"/>

① 在SqlMapConfig.xml开启驼峰映射支持

② 再次执行查询方法测试

1.2.2 起别名

当表中字段和类中属性完全没有关系时，可以使用别名来修改sql返回集中字段的名称

① 给不对应的字段起别名

    //查询
    @Select("select id, username, password, name, gender, image, job, entrydate as ed, dept_id, create_time, update_time from emp")
    List<Emp> findAll2();

② 测试

    //查询所有
    @Test
    public void testFindAll2() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        List<Emp> empList = empMapper.findAll2();
        empList.forEach(e -> System.out.println(e));//lambda方式打印

        MybatisUtil.close(sqlSession);
    }

请添加图片描述

1.2.3 自定义映射

当表中字段和类中属性完全没有关系时，可以通过@Results进行手动结果映射

① 自定义结果映射

    //查询
    @Results({//@Results: 自定义结果映射
            @Result(column = "entrydate", property = "ed"),//@Result一条映射规则 (column = "数据表中字段名", property = "实体类属性名")
            @Result(column = "dept_id", property = "deptId")
    })
    @Select("select * from emp")
    List<Emp> findAll3();

② 测试

    //查询所有
    @Test
    public void testFindAll3() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        List<Emp> empList = empMapper.findAll3();
        empList.forEach(e -> System.out.println(e));//lambda方式打印

        MybatisUtil.close(sqlSession);
    }

请添加图片描述

1.3 条件查询

当查询方法出现多个查询条件时，需要使用@Param注解设置对应关系

① 添加条件查询的方法

	//#{} 里面的内容
    //1. 如果参数是一个对象类型, #{}写的是对象中的属性名
    //2. 如果参数是一个简单类型(8基本 + 8包装 +String) ,#{}可以随便写, 但是推荐写方法形参名
    //3. 如果参数是多个简单类型的参数, #{}中写的是@Param注解中的值
    //条件查询
    @Select("select * from emp where name = #{name} and gender = #{gender} and entrydate between #{begin} and #{end}")
    List<Emp> findList(@Param("name") String name, @Param("gender") Short gender, @Param("begin") LocalDate begin, @Param("end") LocalDate end);

② 测试

    //条件查询
    @Test
    public void testFindList() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        List<Emp> empList = empMapper.findList("张三丰", (short) 1, LocalDate.of(2002, 01, 01), LocalDate.of(2023, 12, 31));
        empList.forEach(e -> System.out.println(e));//lambda方式打印

        MybatisUtil.close(sqlSession);
    }

请添加图片描述

1.4 模糊查询

当查询sql中出现模糊查询语法时，需要使用concat函数连接条件，比如根据用户名进行模糊查询

where name like '%张%'`----------------->`where name  like concat('%',#{username},'%')

① 添加条件查询的方法

    //模糊查询
    @Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and entrydate between #{begin} and #{end}")
    List<Emp> findListLike(@Param("name") String name, @Param("gender") Short gender, @Param("begin") LocalDate begin, @Param("end") LocalDate end);

② 测试

    //模糊查询
    @Test
    public void testFindListLike() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        List<Emp> empList = empMapper.findListLike("张", (short) 1, LocalDate.of(2002, 01, 01), LocalDate.of(2023, 12, 31));
        empList.forEach(e -> System.out.println(e));//lambda方式打印

        MybatisUtil.close(sqlSession);
    }

请添加图片描述

2.XML写SQL

目前的SQL都是写在了注解中，对于一些简单的SQL这么做，确实很简答，但是对于比较复杂的SQL，写在注解中就比较麻烦了
所以mybatis还提供了一种使用xml文件编写sql的做法，至于到底是用注解还是用xml，其实只要根据自己的方便程度选择就可以了
下面使用xml的方式完成上面的模糊查询功能

2.1 创建XML

使用xml编写sql是需要遵循一些规范的

 1. XML映射文件的名称与Mapper接口名称一致，并且将XML映射文件和Mapper接口放置在相同包下（同包同名）。
 2. XML映射文件的namespace属性为Mapper接口全限定名一致。
 3. XML映射文件中sql语句的id与Mapper接口中的方法名一致，并保持返回类型一致。

请添加图片描述

2.1.1接口

在接口中添加一个根据id查询的方法

//注意: 对于同一个方法,使用xml写sql的话,就不能在使用注解写sql了
//@Select("select * from emp where id = #{id}")
Emp findById(Integer id);

2.1.2 xml

在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="com.itheima.mapper.EmpMapper">
    
    <select id="findById" resultType="com.itheima.domain.Emp">
        select * from emp where id = #{id}
    </select>

</mapper>

2.1.3 测试

测试文件中添加测试方法

//根据id查询
@Test
public void testFindById() {
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

    Emp emp = empMapper.findById(1);
    System.out.println(emp);

    MybatisUtil.close(sqlSession);
}

2.2 常见错误

2.2.1 错误①

对于同一个方法来讲，注解和XML只能选择一种来用，否则会出现下面的错误

2.2.2 错误②

检查下约定好的对应关系，一定有一个关系写错了

2.3 插件安装

MybatisX 是一款基于 IDEA 的快速开发Mybatis的插件，为效率而生。

3. 动态SQL

先来看一个需求：根据用户输入的内容查询员工列表（注意：每个条件都不是必填的），页面如下图

那么此时再用原来学习的知识，编写出如下的sql，还行不行呢？

select * from user where name= #{name} and gender = #{gender} and entrydate between #{begin} and #{end}

显然是有问题的，因为按照上面的sql来执行，如果用户只输入了姓名张三，得到的sql就变成了

select * from user where name= '张三' and gender = null and entrydate between null and null

很显然不符合我们的预期，我们预期是仅仅用户输入的查询条件出现在sql中，没输入的就不出现，即下面这样

select * from user where name= '张三'

像上面这样，需要根据不同的条件来执行不同的sql语句的情况，就需要用到动态sql。

在mybatis中，动态SQL是通过下面几个标签来实现的：

if 用于条件判断
where，set 用于格式控制
foreach 用于循环遍历

3.1 if和where

任务：根据用户输入的内容查询员工列表（名字要采用模糊查询）

3.1.1 接口

//条件查询
List<Emp> findByCondition(@Param("name") String name, @Param("gender") Short gender,
                          @Param("begin") LocalDate begin, @Param("end") LocalDate end);

3.1.2 映射

if：使用test进行条件判断，只有条件成立，条件中的sql才会生效

 select * from emp where
 <if test="name != null">
  	name = #{name}
 </if>

where：只会在标签内部有内容的情况下才插入where子句，而且会自动去除子句的开头的AND或OR。

<!--
    字符串需要跟null和空串比较
    其他类型只要跟null比较

    if：使用test进行条件判断，只有条件成立，条件中的sql才会生效
    where：只会在<where>标签内部有内容的情况下才插入where子句，而且会自动去除子句的开头的AND或OR。
-->
<select id="findByCondition" resultType="com.itheima.domain.Emp">
    select * from emp
    <where>
        <if test="name != null and name != ''">
            name like concat('%',#{name},'%')
        </if>
        <if test="gender != null">
            and gender = #{gender}
        </if>
        <if test="begin != null and end != null">
            and entrydate between #{begin} and #{end}
        </if>
    </where>
</select>

3.1.3 测试

     //条件查询
    @Test
    public void testFindByCondition() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        //List<Emp> empList = empMapper.findByCondition("张", (short) 1, LocalDate.of(2002, 01, 01), LocalDate.of(2023, 12, 31));
        //List<Emp> empList = empMapper.findByCondition("张", (short) 1, null, null);
        List<Emp> empList = empMapper.findByCondition("", (short) 1, null, null);
        empList.forEach(e -> System.out.println(e));//lambda方式打印

        MybatisUtil.close(sqlSession);
    }

3.2 if和set

任务：根据主键修改1号员工的信息（注意只修改用户提交的字段）

请添加图片描述

3.2.1 接口

//根据id更新不为空的字段
void update(Emp emp);

3.2.2 映射

set：动态地在set代码块之前加入SET关键字，并删掉set代码块中最后一个多余的逗号（用在update语句中）

<update id="update">
    update emp
    <set>
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        <if test="password != null and password != ''">
            password = #{password},
        </if>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="gender != null">
            gender = #{gender},
        </if>
        <if test="image != null and image != ''">
            image = #{image},
        </if>
        <if test="job != null">
            job = #{job},
        </if>
        <if test="ed != null">
            entrydate = #{ed},
        </if>
        <if test="deptId != null">
            dept_id = #{deptId},
        </if>
        <if test="createTime != null">
            create_time = #{createTime},
        </if>
        <if test="updateTime != null">
            update_time = #{updateTime},
        </if>
    </set>
    where id = #{id}
</update>

3.2.3 测试

//更新
@Test
public void testUpdate() {
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

    Emp emp = new Emp();
    emp.setId(1);
    emp.setUsername("haha");

    empMapper.update(emp);

    MybatisUtil.close(sqlSession);
}

3.3 foreach

任务：根据id的集合批量删除员工

3.3.1 接口

//删除
void deleteByIds(@Param("ids") List<Integer> ids);

3.3.2 映射

foreach 用于循环遍历

 * collection：集合名称
* item：集合遍历出来的元素
 * separator：每一次遍历使用的分隔符
 * open： 遍历开始前拼接的片段
 * close：遍历结束后拼接的片段

<delete id="deleteByIds">
    delete from emp where id in
    <foreach collection="ids" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</delete>

3.3.3 测试

//批量删除
@Test
public void deleteByIds() {
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

    empMapper.deleteByIds(Arrays.asList(13, 14, 15));

    MybatisUtil.close(sqlSession);
}

3.4 sql片段

sql片段的作用是将代码中重复的部分，提取出来达到复用的目的

* 定义SQL片段:  <sql id="selectUser"></sql> 
* 引用SQL片段:  <include refid="selectUser"></include>

请添加图片描述

3.5 总结

标签	作用
if	用于条件判断，只有条件成立，条件中的sql才会生效
where	用在where代码部分，主要作用是去除子句的开头的and或or
set	用于update语句的set部分，主要作用是删掉set代码块中最后一个多余的逗号
foreach	用于对输入的集合参数进行遍历取值，一般用于批量操作中
sql	用于抽取sql片段

4.配置文件

sqlMapConfig.xml 这是mybatis的主配置文件，文件中的标签有严格的顺序要求, 可以省略一部分，但是不能乱序

 * settings：控制一些全局配置项的开闭
 * mappers：用于指定Mapper接口的位置
 * environments: 配置数据库连接信息和事务管理器

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

    <!--全局配置项的开关-->
    <settings>
        <!--在控制台输出发送的sql日志-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--开启驼峰映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--环境-->
    <environments default="development">
        <environment id="development">
            <!--事务管理器-->
            <transactionManager type="JDBC"/>

            <!--数据源(数据库连接池)-->
            <dataSource type="POOLED">
                <!--数据库连接驱动-->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <!--数据库连接地址-->
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <!--用户名-->
                <property name="username" value="root"/>
                <!--密码-->
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <!--接口所在包-->
    <mappers>
        <package name="com.itheima.mapper"/>
    </mappers>
</configuration>

数据库连接池

* Java程序跟数据库进行数据交互是需要建立连接的，而连接的创建和销毁比较耗时，为了防止频繁的创建和销毁连接，就出现了连接池技术
* 连接池类似于前面学过的线程池，就是在项目启动的时候，就会初始化一批连接，放到池中，用的时候直接取，用完了再归还到池中
* 业界目前比较出名的有下面两款：
	Druid(德鲁伊):  阿里巴巴提供的数据库连接池技术，国内使用率很高，提供了完善的监控机制
	HikariCP:  日本人开发的连接池技术，号称性能之王，速度最快，SpringBoot2.0默认使用此连接池