Mybatis编写SQL

文章目录

一、用注解编写
- 1.1 增
- - 普通增加
  - 获取自增ID
- 1.2 删和改
- 1.3 查
- - 单表查询
  - 多表查询
二、用xml编写
- 2.1 使用xml的流程
- 2.2 增
- - 普通增加
  - 获取自增ID
- 2.3 删和改
- 2.4 查
三、#{} 和 ${}
- 3.1 #{} 、${}
- 3.1 预编译 SQL 、即时编译SQL

两种写法是可以同时存在的

一、用注解编写

1.1 增

普通增加

拼接字符串：如果注解里面的字符串太长了，可以用 +即【拼接字符串】的方式。直接按回车即可。
返回值：增删改时，返回值可以为Integer，也可以为void。但我们一般还是会设置为Integer的，因为【有时候我们的程序是否执行成功，是没有什么提示的，此时，我们就可以用“受影响的行数”来判断程序有无执行成功】
- 当为Integer时，返回的是“受影响的行数”
- 为void就什么都不返回
参数：也可以一个一个传。因为要传的参数比较多，此处是用对象来传参

@Mapper
public interface UserInfoMapper {
    @Insert("insert into userinfo (username, password, age, gender, phone)" +
            "values(#{username}, #{password}, #{age}, #{gender}, #{phone})")
    Integer insert(UserInfo userInfo);
}

获取自增ID

使用场景：有很多服务都是需要拿到id后，才能进行下一步操作。如订单团队，需要获取你的订单ID，后续才能进行支付对账操作。
代码：使用@Options注解
- useGeneratedKeys：是否自动生成key，默认为false，这里要设置为 true
- keyProperty：要把生成的key(主键)的值赋值给谁。下面示例中是赋值给了userinfo对象的id属性。

@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into userinfo (username, password, age, gender, phone)" +
        "values(#{username}, #{password}, #{age}, #{gender}, #{phone})")
Integer insert(UserInfo userInfo);

1.2 删和改

删除：

 @Delete("delete from userinfo where id = #{id}")
 Integer delete(Integer id);

修改：

@Mapper
public interface UserInfoMapper {

    @Update("update userinfo set age = #{age} where id = #{id}")
    Integer update(UserInfo userInfo);
}

@Slf4j
@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;

    @Test
    void update() {
        UserInfo userInfo = new UserInfo();
        userInfo.setAge(22);
        userInfo.setId(11);
        Integer res = userInfoMapper.update(userInfo);
        //使用返回值判断【程序是否正确运行】
        if (res > 0){
            log.info("数据更新成功");
        }
    }
}

1.3 查

单表查询

不带有参数

@Mapper
public interface UserInfoMapper {
    @Select("select * from userInfo")
    List<UserInfo> selectAll();
}

带参数：
- 如果只有一个参数，名称是可以不匹配的，但通常情况下，为了方便去阅读，我们还是会让他们保持一致
- 如果有多个参数，是需要我们匹配的

参数匹配

@Mapper
public interface UserInfoMapper {
    @Select("select * from userInfo where id = #{id}")
    UserInfo selectUser(Integer id);
}

参数不匹配

@Mapper
public interface UserInfoMapper {
    @Select("select * from userinfo where id = #{id}")
    UserInfo selectUser(Integer id123);
}

多表查询

避免使用多表查询：我们要尽量避免使用多表查询，尤其是性能要求很高的项目
- 原因：
  - 慢：如果查询两个表分别要10s，把他们放在一起查询肯定会大于10s，因为多表查询还有【整理】的操作
  - 程序员不可控：Java方面可以使用多线程的方式优化多表查询，但如果直接使用多表查询的SQL语句，相当于直接把优化的操作交给了Mysql，程序员层面无法再优化了
  - 会影响其他项目：通过情况下，数据库是集群使用的，即很多项目都会用到一个数据库。此时，当出现慢查询时，会影响整个集群，即会影响到所有使用该集群的项目。
  - 扩容效率低：当要进行扩容操作时，Java服务器扩容十分方便，但是数据库集群扩容就需要专门的人员来处理，十分麻烦
- 多表查询的优势：操作简单。如果使用多线程优化，我们还需要进行逻辑上的处理，所以一些对性能要求不是很高的项目，还是可以使用多表查询的
如何使用多表查询：直接用多表查询的SQL + Java对象有对应的属性
- 原理：Mybatis不在乎是多表SQL还是单表SQL
  - Mybatis的工作是把这个方法的SQL语句发送给Mysql，在Mysql给我们返回一个结果(可以通过打印的Mybatis日志查看)后，Mybatis进行结果映射，并按照方法定义返回对应的值
  - 所以，Mybatis根本不关注SQL是单表还是多表，它所关注的只是如何进行结果映射，我们只要确保对象里有对应的属性就行。

在这里插入图片描述

二、用xml编写

2.1 使用xml的流程

配置数据库资源：和注解时配置的一样

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

配置路径：
对xml进行声明：
- namespace：表示要实现哪个接口，要求写接口的全限定类名(如果按住ctrl能来到想要的接口，就说明没有写错路径)

<?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.example.demo.mapper.UserInfoXMLMapper">

</mapper>

编写xml：
- 换行规则：xml的SQL区域，换行直接敲回车即可，整块会被当成一个字符串
- 返回类型的指定：只有查询时，需要去说明返回的类型，其他的增删改则不需要指定
  - 指定返回类型的规则：
    - 使用全限定类名
    - 不需要指定返回的是List，Mybatis能识别出我们返回的是List 还是其他的数据类型

@Mapper
public interface UserInfoXMLMapper {
    List<UserInfo> selectAll();
}

<?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.example.demo.mapper.UserInfoXMLMapper">
    <select id="selectAll" resultType="com.example.demo.model.UserInfo">
       select * from userinfo	<!-->直接在这里写SQL语句即可<-->
    </select>
</mapper>

2.2 增

普通增加

未重命名：

@Mapper
public interface UserInfoXMLMapper {
    Integer insert(UserInfo userInfo);
}

<?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.example.demo.mapper.UserInfoXMLMapper">
    <insert id="insert">
        insert into userinfo (username, password, age, gender, phone)
        values(#{username}, #{password}, #{age},
        #{gender}, #{phone})
    </insert>
</mapper>

重命名：重命名方法和【注解方式】一致

@Mapper
public interface UserInfoXMLMapper {
   
    Integer insert(@Param("user") UserInfo userInfo);
}

<?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.example.demo.mapper.UserInfoXMLMapper">
    <insert id="insert">
        insert into userinfo (username, password, age, gender, phone)
        values(#{user.username}, #{user.password}, #{user.age},
        #{user.gender}, #{user.phone})
    </insert>
</mapper>

获取自增ID

<?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.example.demo.mapper.UserInfoXMLMapper">
    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        insert into userinfo (username, password, age, gender, phone)
        values(#{username}, #{password}, #{age},
        #{gender}, #{phone})
    </insert>
</mapper>

2.3 删和改

删除：

<delete id="delete">
    delete from userinfo where id = #{id}
</delete>

修改：

<update id="update">
    update userinfo set age = #{age} where id = #{id}
</update>

2.4 查

关于resultType的指定：
- 范围：只有查询操作需要指定
- 使用方法：使用全限定类名。不需要指定返回的是List，Mybatis能识别出我们返回的是List 还是其他的数据类型

<?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.example.demo.mapper.UserInfoXMLMapper">
    <select id="selectAll" resultType="com.example.demo.model.UserInfo">
       select * from userinfo	<!-->直接在这里写SQL语句即可<-->
    </select>
</mapper>

三、#{} 和 ${}

3.1 #{} 、${}

使用方面：优先使用#，不能直接使用#，就搭配内置函数或写多个接口等，最后考虑用$
#{}、${}区别
- 共同点：都是用来获取变量的值
- 不同点：
  - #{}是预编译SQL，${}是即时编译SQL
  - 特殊情况下不能直接使用#
无法使用直接#的场景：当参数为String，自动加的引号多此一举时。如排序、模糊查询、表名字段名等作为了参数。下面的解决方法其实也是#{}解决SQL注入的方法。
- 升序降序：如果升序降序是参数，即使我们加了引号也无法运行
- 模糊查询：

3.1 预编译 SQL 、即时编译SQL

什么是预编译SQL 和即时编译SQL
- 预编译SQL：编译后缓存，后续从缓存拿，直接执行
  - 编译一次后会将编译后的SQL语句缓存起来，后续再执行这条语句时，不会再次编译(只是输入的参数不同)，省去了前面的过程，直接执行，以此来提高效率
  - 相当于是一个框架，给你提前预留好一个位置，不管你是什么，都会把你作为id的参数塞进去
- 即时编译SQL：直接去拼接，不管你这个参数里面是什么样，直接去拼上，也就会有【SQL注入】的问题了。

在这里插入图片描述

预编译SQL VS 即时编译SQL区别
- 性能对比：预编译SQL占优
  - 预编译SQL：因为有缓存，可以直接去拿，性能高
  - 即时编译SQL：当出现【select * from bookinfo where id = ‘1’】的情况时，虽然 Mysql 会自动转化类型，代码依旧可以正常运行。但是当类型不一样时，有可能会出现一些性能问题
- SQL注入问题：预编译SQL不存在SQL注入的问题，$则有
  - 什么是SQL注入问题：当参数中有SQL语句，可能会把这个语句当做SQL执行。
  - 示例：如【select * from bookinfo bookName = ‘’ or 1 = ‘1’]】，实际需求是要找bookName为【'or 1 = '1】的数据，但由于前后引号闭合了，or直接作为SQL语句执行，最终出现了bug