mybatis的#与$的区别

mybatis的#与$的区别

#{} 底层使用PreparedStatement来执行SQL语句
${} 底层使用Statement来执行SQL语句

#号

1、会预编译，防止sql注入

select * from user where id =#{id}

会变编译为

select * from user where id = '1'

2、一般能用#的就别用$

$号

1、传入什么就是什么，不会对字符串进行处理

select * from user where id =#{id}

会变编译为

select * from user where id = 1

2、不能防止sql注入问题
3、一般传入的是表名，注意的是order by 后面的是传入${}

测试，#与$一起用

（1）导入依赖

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

（2）编写实体类

package com.ycz.mybatis.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

/**
 * @Description:
 * @Author: Alex
 * @Date 2022-07-03-20:02
 * @Version: V1.0
 **/
@Data
public class User {
    @TableId(type = IdType.AUTO)
    private Integer id;

    private String name;

    private String pwd;

    private String gender;

    private String qq;

    private Integer age;

    private String address;

    private String email;

    private Integer deleted;
}

配置

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 数据源名称
spring.datasource.name=defaultDataSource
# 数据库连接地址
spring.datasource.url=jdbc:mysql://localhost:3306/temp?serverTimezone=UTC
# 数据库用户名&密码：
spring.datasource.username=root
spring.datasource.password=123456

mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis-plus.mapper-locations=classpath*:com/ycz/**/**/xml/*Mapper.xml

Mapper

package com.ycz.mybatis.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ycz.mybatis.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @Description:
 * @Author: Alex
 * @Date 2022-07-03-20:05
 * @Version: V1.0
 **/
@Mapper
public interface UserMapper extends BaseMapper<User> {

   void insert2(@Param("tableName")String tableName , @Param("user") User user);

   List<User> selectUser(@Param("tableName")String tableName , @Param("orderByTemp")String orderByTemp);

}

<?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.ycz.mybatis.mapper.UserMapper">

    <insert id="insert2" parameterType="com.ycz.mybatis.entity.User">
        insert into ${tableName} (`name` , pwd,gender,qq,age,address,email,deleted)
        values(#{user.name},#{user.pwd} , #{user.gender} ,
               #{user.qq} ,#{user.age} , #{user.address} , #{user.email} , #{user.deleted} )
    </insert>

    <select id="selectUser" resultType="com.ycz.mybatis.entity.User">
        select * from  ${tableName} order by ${orderByTemp}
    </select>

</mapper>

测试：

  @Test
    void insert2(){
        User user = new User();
        user.setAddress("sz");
        user.setAge(18);
        user.setName("asdasd");
        user.setGender("男");
        user.setEmail("51561651@qq。com");
        user.setQq("asdasd");
        user.setDeleted(0);
        userMapper.insert2("user" , user);
    }

结果:

成功

   @Test
    void select(){
        List<User> users = userMapper.selectUser("user", "age");
        users.forEach(System.out::println);
    }

结果：

成功