MyBatis查询数据库

MyBatis是一款优秀的持久层框架，它支持自定义SQL、存储过程以及高级映射。MyBatis去除了几乎所有的JDBC代码以及设置参数和获取结果集的工作。MyBatis可以通过简单的XML_或注解来配置和映射原始类型、接口和Java POJO (Plain Old Java Objects，普通老式Java对象）为数据库中的记录。

简单来说MyBatis是更简单完成程序和数据库交互的工具，也就是更简单的操作和读取数据库工具。
Mybatis官网：https://mybatis.org/mybatis-3/zh/index.html

MyBatis也是一个ORM框架，ORM (Object Relational Mapping) ，即对象关系映射。在面向对象编程语言中，将关系型数据库中的数据与对象建立起映射关系，进而自动的完成数据与对象的互相转换:

1. 将输入数据（即传入对象)+SQL映射成原生SQL
2. 将结果集映射为返回对象，即输出对象

ORM把数据库映射为对象︰

• 数据库表(table) -->类(class)
• 记录(record，行数据)–>对象 (object)
• 字段(field) -->对象的属性(attribute)

一般的ORM框架，会将数据库模型的每张表都映射为一个Java类。
也就是说使用MyBatis可以像操作对象一样来操作数据库中的表，可以实现对象和数据库表之的转换，接下来我们来看MyBatis 的使用吧。

接下来我们要实现的功能是∶使用MyBatis 的方式来读取用户表中的所有用户，我们使用个人博客的数据库和数据包．具体SQL如下。

-- 创建数据库
drop database if exists mycnblog;
create database mycnblog DEFAULT CHARACTER SET utf8;
-- 使⽤数据数据
use mycnblog;
-- 创建表[⽤户表]
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 datetime default now(),
    updatetime datetime default now(),
    `stateìnt default 1
);
-- 创建⽂章表
drop table if exists  articleinfo;
create table articleinfo(
    id int primary key auto_increment,
    title varchar(100) not null,
    content text not null,
    createtime datetime default now(),
    updatetime datetime default now(),
    uid int not null,
    rcount int not null default 1,
    `stateìnt default 1
);
-- 创建视频表
drop table if exists videoinfo;
create table videoinfo(
    vid int primary key,
    `title` varchar(250),
    ùrl` varchar(1000),
    createtime datetime default now(),
    updatetime datetime default now(),
    uid int
);
-- 添加⼀个⽤户信息
INSERT INTO `mycnblog`.ùserinfo` (ìd`, ùsername`, `password`, `photo`, 
`createtime`, ùpdatetime`, `state`) VALUES 
(1, 'admin', 'admin', '', '2021-12-06 17:10:48', '2021-12-06 17:10:48', 
1);
-- ⽂章添加测试数据
insert into articleinfo(title,content,uid)
    values('Java','Java正⽂',1);
    
-- 添加视频
insert into videoinfo(vid,title,url,uid) values(1,'java 
title','http://www.baidu.com',1);

此步骤需要进行两项设置，数据库连接字符串设置和MyBatis的XML文件配置。

配置连接字符串
如果是 application.yml 添加如下内容：

# 数据库连接配置
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mycnblog?
characterEncoding=utf8&useSSL=false
    username: root
    password: 940194
    driver-class-name: com.mysql.cj.jdbc.Driver

注意事项：

如果使用 MySQL 是 5.x 之前的使用的是“com.mysql.jdbc.Driver”，如果是⼤于 5.x 使用的是“com.mysql.cj.jdbc.Driver”。

配置 MyBatis 中的 XML 路径
MyBatis 的 XML 中保存是查询数据库的具体操作 SQL，配置如下：

# 配置 mybatis xml 的⽂件路径，在 resources/mapper 创建所有表的 xml ⽂件
mybatis:
  mapper-locations: classpath:mapper/**Mapper.xml

下面按照后端开发的工程思路，也就是下面的流程来实现MyBatis查询所有用户的功能:

先添加用户的实体类：

import lombok.Data;
import java.util.Date;

@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private String photo;
    private Date createTime;
    private Date updateTime;
}

数据持久层的接口定义：

import org.apache.ibatis.annotations.Mapper;
import java.util.List;

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

数据持久成的实现，mybatis 的固定 xml 格式：

UserMapper.xml 查询所有用户的具体实现 SQL：

    
        select * from userinfo
    

 
以下是对以上标签的说明:
 
标签︰需要指定namespace属性，表示命名空间，值为mapper接口的全限定名，包括全包名.类名。
查询标签:是用来执行数据库的查询操作的: 
  
id:是和Interface (接口）中定义的方法名称一样的，表示对接口的具体实现方法。
resultType:是返回的数据类型，也就是开头我们定义的实体类。
 
 
1.3.4 添加服务层 
服务层实现代码如下：
 
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;

@Service
public class UserService {
    @Resource
    private UserMapper userMapper;
    public List getAll() {
        return userMapper.getAll();
    }
}
 
1.4.5 添加控制层 
控制器层的实现代码如下：
 
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;

@RestController
@RequestMapping("/u")
public class UserController {
    @Resource
    private UserService userService;
    @RequestMapping("/getall")
    public List getAll(){
        return userService.getAll();
    }
}
 
以上代码写完，整个 MyBatis 的查询功能就实现完了，接下来使用 postman 来测试⼀下。
 
1.4.6 使用 postman 测试 
 
2. 增、删、改操作 
接下来，我们来实现一下用户的增加、删除和修改的操作，对应使用MyBatis 的标签如下:
 
insert标签:插入语句
update标签:修改语句
delete标签︰删除语句
 
注：开启Mybatis 执行日志
 
#开启Mybaits sql打印
mybaits.configuration.log-impl=org.apache.ibatis.logging.stdout.std0utImpl
 
2.1 增加用户操作 
controller 实现代码︰
 
@RequestMapping(value = "/add",method = RequestMethod.POST)
public Integer add(@RequestBody User user){
    return userService.getAdd(user);
}
 
mapper interface：
 
Integer add(User user);
 
mapper.xml
 

  insert into userinfo(username,password,photo,state)
  values(#{username},#{password},#{photo},1)

 
Postman 添加访问：
 
 
默认情况下返回的是受影响的行数，如上图所示，用到的json 数据如下:
 
{"username":"mysql","password":"mysql","photo":"img.png"}
 
特殊的添加:返回自增id
 默认情况下返回的是受影响的行号，如果想要返回自增id，具体实现如下。
 controller 实现代码:
 
@RequestMapping(value = "/add2", method = RequestMethod.POST)
public Integer add2(@RequestBody User user) {
    userService.getAdd2(user);
    return user.getId();
}
 
mapper 接口：
 
@Mapper
public interface UserMapper {
    // 添加，返回⾃增id
    void add2(User user);
}
 
mapper.xml 实现如下：
 


    insert into userinfo(username,password,photo,state)
    values(#{username},#{password},#{photo},1)

 
useGeneratedKeys:这会令MyBatis使用JDBC的getGeneratedKeys方法来取出由数据库内部生成的主键（比如︰像MySQL和SQLServer这样的关系型数据库管理系统的自动递增字段)，默认值: false。
keyColumn:设置生成键值在表中的列名，在某些数据库（像PostgreSQL)中，当主键列不是表中的第一列的时候，是必须设置的。如果生成列不止一个，可以用逗号分隔多个属性名称。
keyProperty:指定能够唯一识别对象的属性，MyBatis 会使用getGeneratedKeys的返回值或insert语句的selectKey子元素设置它的值，默认值∶未设置(unset)。如果生成列不止一个，可以用逗号分隔多个属性名称。
 
postman返回结果︰
 
 
2.2 修改用户操作 
controller：
 
@RequestMapping("/update")
    public Integer update(Integer id, String name) {
        return userService.update(id, name);
    }
 
mapper.xml 实现代码：
 

    update userinfo set username=#{name} where id=#{id}

 
2.3 删除用户操作 

    delete from userinfo where id=#{id}

 
3. 查询操作 
3.1 表单查询 
下面我们来实现⼀下根据用户 id 查询用户信息的功能。
 Controller 实现代码如下：
 
@RequestMapping("/getuser")
public User getUserById(Integer id) {
    return userService.getUserById(id);
}
 
Mapper.xml 实现代码如下：
 

    select * from userinfo where id=#{id}

• #{}: 预编译处理。
• ${}: 字符直接替换。

预编译处理: 在处理#{}时，会将SQL中的#{}替换为?号（例如： “?”），替换后会加上引号，使用PreparedStatement的set方法来赋值。
直接替换∶在处理 ${} 时，就是把 ${}直接替换成变量的值。

     select * from userinfo order by id ${sort}

 
使用${sort}可以实现排序查询，而使用#{sort}就不能实现排序查询了，因为当使用#{sort}查询时如果传递的值为 String 则会加单引号，就会导致sql错误。
 
3.1.3 SQL 注入问题 

    select * from userinfo where username='${name}' and password='${pwd}'

sql 注⼊代码：“’ or 1='1”

结论:
用于查询的字段，尽量使用#{}预查询的方式。#{}可以防止SQL注入，${}不能防止SQL注入。

like使用#{}报错

         select * from userinfo where username like '%#{username}%';
    
 
相当于: select * from userinfo where username like ‘%‘username’%’;
 这个是不能直接使用${}，可以考虑使用mysql的内置函数concat()来处理，实现代码如下︰
 

         select * from userinfo where username like concat('%',#
{username},'%');

 
3.2 多表查询 
如果是增、删、改返回搜影响的行数，那么在mapper.xml 中是可以不设置返回的类型的，如下图所示∶
 
 然而即使是最简单查询用户的名称也要设置返回的类型，否则会出现如下错误。
 查询不设置返回类型的错误示例演示
 controller代码:
 
@RequestMapping("/getname")
public String getNameById(Integer id) {
    return userService.getNameById(id);
}
 
mapper.xml 实现代码：
 

    select username from userinfo where id=#{id}

访问接口执行结果如下：

显示运行了一个查询但没有找到结果映射，也就是说对于select查询标签来说至少需要两个属性.

• id属性: 用于标识实现接口中的那个方法;
• 结果映射属性: 结果映射有两种实现标签: resultMap和resultType。

绝大数查询场景可以使用resultType进行返回，如下代码所示:

    select username from userinfo where id=#{id}

 

    select * from userinfo
    
        
            username=#{username}
        
        
            and password=#{password}
        
    

它的优点是使用方便，直接定义到某个实体类即可。

resultMap 使用场景:

• 字段名称和程序中的属性名不同的情况，可使用resultMap 配置映射;
• 一对一和一对多关系可以使用resultMap映射并查询数据。

字段名和属性名不同的情况

程序中的属性如下：

mapper.xml 代码如下：

  select * from userinfo where id=#{id}

 
查询的结果如下
 
这个时候就可以使用 resultMap 了，resultMap 的使用如下：
 
 mapper.xml
 

    
    
    


    select * from userinfo where id=#{id}

查询的结果就有值了，如下图所示：

在多表查询时，如果使用resultType标签，在一个类中包含了另一个对象是查询不出来被包含的对象的，比如以下实体类：

@Data
public class ArticleInfo {
    private Integer id;
    private String title;
    private String content;
    private LocalDateTime createtime;
    private LocalDateTime updatetime;
    private Integer rcount;
    // 包含了 userinfo 对象
    private UserInfo user;
}

    
        select a.*,u.* from articleinfo a left join userinfo u on a.uid=u.id
    
 
程序的执行结果如下图所示：
 
 此时我们就需要使用特殊手段来实现联表查询了。
 
3.2.3.1 一对一: 一篇文章对应一个作者 
一对一映射要使用association标签，具体实现如下(一篇文章只对应一个作者)︰
 

   
   
   
   
   
   
   
   
   
   


  select a.*,u.username u_username from articleinfo a
  left join userinfo u on a.uid=u.id

以上使用association标签，表示一对一的结果映射:

• property 属性:指定Article中对应的属性，即用户。
• resultMap属性∶指定关联的结果集映射，将基于该映射配置来组织用户数据。
• columnPrefix属性:绑定一对一对象时，是通过
  columnPrefix+association.resultMap.column来映射结果集字段。
  association.resultMap.column是指标签中resultMap属性，对应的结果集映射中，column字段。

注意事项: columnPrefix 不能省略
columnPrefix 如果省略，并且恰好两个表中如果有相同的字段，那么就会导致查询出错，示例如下：

ArticleInfo.java

@Data
public class ArticleInfo {
    private Integer id;
    private String title;
    private String content;
    private LocalDateTime createtime;
    private LocalDateTime updatetime;
    private Integer rcount;
    // 包含了 userinfo 对象
    private UserInfo user;
}

UserInfo.java

@Data
public class UserInfo {
    private int id;
    private String name; // todo:和数据库自定不一致，username
    private String password;
    private String photo;
    private LocalDateTime createtime;
    private LocalDateTime updatetime;
    private int state;
    private List artList;
}

ArticleInfoMapper.xml

    
        
        
        
        
        
        
        
        
        
        
        
    

    
        select a.*,u.* from articleinfo a left join userinfo u on a.uid=u.id
    


    
        select a.*,u.id u_id,u.username u_username,u.password u_password from articleinfo a
        left join userinfo u on
        a.uid=u.id
    

    

UserMapper.xml

    
    
        
        
        
        
        
        
        
        
        
        
        
    

    
        select u.*,a.id a_id,a.title a_title from userinfo u left join articleinfo a on u.id=a.uid
    


 
 
3.2.3.2 一对多: 一个作者对应多篇文章 
一对多需要使用collection标签，用法和association相同，如下所示:
 

  
  
  
  
  
  


  select u.*,a.title a_title from userinfo u
  left join articleinfo a on u.id=a.uid where u.id=#{id}

动态sql是Mybatis的强大特性之一，能够完成不同条件下不同的sql拼接。
可以参考官方文档:Mybatis动态sql

  insert into user(
          username,
          password,
          nickname,
          
            sex,
          
          birthday,
          head
      ) values (
          #{username},
          #{password},
          #{nickname},
          
            #{sex},
          
          #{birthday},
          #{head}
      )

  insert into user
    
        
          username,
        
        
          password,
        
        
          nickname,
        
        
          sex,
        
        
          birthday,
        
        
          head,
        
        
          create_time,
        
    
    
        
          #{username},
        
        
          #{password},
        
        
          #{nickname},
        
        
          #{sex},
        
        
          #{birthday},
        
        
          #{head},
        
        
          #{createTime},
        
    

int deleteByIds(List ids);

    delete from article
    where id in
    
        #{item}
    

int updateById(User user);

    update user
        
            
                username=#{username},
            
            
                password=#{password},
            
            
                nickname=#{nickname},
            
            
                sex=#{sex},
            
            
                birthday=#{birthday},
            
            
                head=#{head},
            
            
                create_time=#{createTime},
            
        
    where id=#{id}

List selectByCondition(User user);

    select id, username, password, nickname, sex, birthday, head, 
create_time
        from user
    
        
            and username=#{username}
        
        
            and password=#{password}
        
        
            and nickname=#{nickname}
        
        
            and sex=#{sex}
        
        
            and birthday=#{birthday}
        
        
            and head=#{head}
        
        
            and create_time=#{createTime}