2

mybatis 07: sql标签中 "#{}" 和 "${}" 的作用和比较 - nefu_wa...

 1 year ago
source link: https://www.cnblogs.com/nefu-wangxun/p/16581197.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

"#{}"占位符

  • 传参大部分使用"#{}"，在数据库底层使用的是：PreparedStatement预编译处理对象
  • 数据库底层被解析为"?"，用来传值，是安全的数据库访问，可以防止sql注入
  • 通过在SqlMapConfig.xml添加日志输出配置，在后文测试输出的结果中可以验证
<?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>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

</configuration>
  • "#{}"里参数的写法，要参考parameterType的类型
  • 如果parameterType的类型是简单类型(8种基本类型(封装) + String类型)，则"#{}"里变量名称任意
    <!--
        //根据用户主键查取用户信息
        User getById();
    -->

    <select id="getById" resultType="user" parameterType="int">
        select
            id, username, birthday, sex, address
        from
            users
        where
            id=#{asYouLike}
    </select>
  • 如果parameterType的类型是实体类的类型，则"#{}"里只能是类中成员变量的名称，而且区分大小写
//User实体类中的属性

public class User {
    private Integer id;
    private String userName;
    private Date birthday;
    private String sex;
    private String address;
}

   <!--
        //向用户表中增加用户信息
        int insert(User user);
   -->

    <insert id="insert" parameterType="user">
        insert into
                users(username, birthday, sex, address)
        values(#{userName}, #{birthday}, #{sex}, #{address})
    </insert>

"${}"占位符

用于字符串的拼接和字符串的替换

字符串拼接

  • 一般用于模糊查询，建议少用，因为存在sql注入风险
  • "${}"中参数名称的写法，分两种情况，与"#{}"的两种情况相同，可参考之
  • 注意：对于parameterType的类型是简单类型时，"${}"里变量名称随便写，但是分版本
    • 如果是3.5.1及以下版本，只能写"value"

模糊查询示例

  • 未优化前，存在sql注入风险
    <!--
        //根据用户名模糊查询用户信息
        List<User> getByName(String name);
    -->

    <select id="getByName" parameterType="string" resultType="user">
        select
            id, username, birthday, sex, address
        from
            users
        where username like '%${name}%'
    </select>
  • 优化后，使用"#{}"接受传参，底层是preparedStatement预编译对象，可以防止sql注入
    <!--
        //优化后的模糊查询
        List<User> getByNameBetter(String name);
    -->

    <select id="getByNameBetter" parameterType="string" resultType="user">
        select
            id, username, birthday, sex, address
        from
            users
        where username like concat('%', #{name}, '%')
    </select>

字符串替换("${}"的主要作用)

需求：在users表中，根据地址或者用户名模糊查询用户信息

sql语句：

select * from users where username like '%模糊查询条件%'

select * from users where address like '%模糊查询条件%'

存在的问题：两条sql语句的结构在本质上是相同的，写两条语句十分冗余，可以采用替换列名的方式进行优化

UsersMapper.java接口

package com.example.mapper;

import com.example.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * 数据访问层的接口，定义对数据库完成的CRUD的操作
 */
public interface UsersMapper {
    
    //根据用户名或者地址模糊查询
    List<User> getByNameOrAddress(
            @Param("colName")
            String colName,
            @Param("userName")
            String userName
    );
}
  • 接口分析：当接口中的方法的参数有多个时，用注解标识参数，sql标签可通过注解中声明的参数名获取参数

UsersMapper.xml文件

<?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.mapper.UsersMapper">

    <!--
        //根据用户名或者地址模糊查询
        List<User> getByNameOrAddress(
                @Param("colName")
                String colName,
                @Param("userName")
                String userName);
    -->
    
    <select id="getByNameOrAddress" resultType="user">
        select
            id, username, birthday, sex, address
        from
            users
        where
            ${colName} like concat('%', #{userName}, '%')
    </select>

</mapper>
  • sql标签分析
    • 当标签对应的接口中的方法有多个参数时，标签中的入参类型，即：parameterType，取消不写，通过方法中注解的参数名称获取参数
    • 用于替换时只可以使用"${}"，应该放在like前。"#{}"用来传值，应该放在like后用来占位传值
package com.example.mapper;

import com.example.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class TestUsersMapper {

    //时间刷
    SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");

    //SqlSession对象
    SqlSession sqlSession;

    //mybatis动态代理对象
    UsersMapper usersMapper;

    //获取SqlSession
    @Before
    public void getSqlSession() throws IOException {
        //读取核心配置文件
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //获取SqlSession
        sqlSession = factory.openSession();
        //获取mybatis动态代理对象
        usersMapper = sqlSession.getMapper(UsersMapper.class);
    }

    //归还SqlSession
    @After
    public void closeSession(){
        sqlSession.close();
    }
    

    @Test
    public void testGetByNameOrAddress(){
        List<User> users = usersMapper.getByNameOrAddress("username", "小");
        //List<User> users = usersMapper.getByNameOrAddress("address", "市");
        users.forEach(System.out::println);
    }
}

根据用户名模糊查询

Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]
Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]
Opening JDBC Connection
Created connection 1293462056.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]
    
==>  Preparing: select id, username, birthday, sex, address from users where username like concat('%', ?, '%')
==> Parameters: 小(String)
    
<==    Columns: id, username, birthday, sex, address
<==        Row: 2, 小王, 2001-07-12, 1, 芜湖市
<==        Row: 3, 小张, 1999-02-22, 1, 长沙
<==        Row: 29, 小昕, 2001-03-14, 女, 忻州
<==      Total: 3
Users{id=2, userName='小王', birthday=Thu Jul 12 00:00:00 CST 2001, sex='1', address='芜湖市'}
Users{id=3, userName='小张', birthday=Mon Feb 22 00:00:00 CST 1999, sex='1', address='长沙'}
Users{id=29, userName='小昕', birthday=Wed Mar 14 00:00:00 CST 2001, sex='女', address='忻州'}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]
Returned connection 1293462056 to pool.

Process finished with exit code 0

根据地址模糊查询

Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]
Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]
Opening JDBC Connection
Created connection 1293462056.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]
    
==>  Preparing: select id, username, birthday, sex, address from users where address like concat('%', ?, '%')
==> Parameters: 市(String)
    
<==    Columns: id, username, birthday, sex, address
<==        Row: 2, 小王, 2001-07-12, 1, 芜湖市
<==        Row: 7, 学委, 2001-05-13, 2, 平顶山市
<==      Total: 2
Users{id=2, userName='小王', birthday=Thu Jul 12 00:00:00 CST 2001, sex='1', address='芜湖市'}
Users{id=7, userName='学委', birthday=Sun May 13 00:00:00 CST 2001, sex='2', address='平顶山市'}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]
Returned connection 1293462056 to pool.

Process finished with exit code 0

测试结果分析

  • sql标签在底层分别被解析为
==>  Preparing: select id, username, birthday, sex, address from users where username like concat('%', ?, '%')
==> Parameters: 小(String)

==>  Preparing: select id, username, birthday, sex, address from users where address like concat('%', ?, '%')
==> Parameters: 市(String)
  • 由上可知
    • 标签中的${colName}分别被替换成了传入的"username"和"address"，起到了替换作用
    • "#{}"被解析成"?"，#{userName}分别拿到值：小(String)和市(String)，起到占位传值作用

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK