MyBatis篇】四. MyBatis高级第一章 #{}和${}符号的区别（共三章）

MyBatis高级第一章 #{}和${}符号的区别

github源码（day56-mybatis-senior）https://github.com/1196557363/ideaMavenProject

#{}和${}符号的区别

项目准备

1. 使用idea创建maven项目（不用web项目）
2. pom.xml导包

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>mybatis-senior</groupId>
    <artifactId>day56-mybatis-senior</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

        <!-- 添加Mybatis的依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>

        <!-- MySQL的驱动包  -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.3</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.25</version>
        </dependency>
    </dependencies>
</project>

3. 配置文件

3.1 MyBatis.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>

    <properties resource="jdbc.properties"/>

    <typeAliases>
        <package name="com.wpj.bean"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClass}"/>
                <property name="url" value="${jdbc.jdbcUrl}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
      </environment>

    </environments>

    <mappers>
        <mapper resource="mapper/IUserDao2.xml"/>
    </mappers>
</configuration>

3.2 jdbc.properties

	jdbc.jdbcUrl=jdbc:mysql://localhost:3306/day56-mybatis-senior
	jdbc.driverClass=com.mysql.jdbc.Driver
	jdbc.username=root
	jdbc.password=123456

3.3 log4j.properties

 ### #配置根Logger ###  
log4j.rootLogger = debug,stdout,E,MAIL
  
### 输出到控制台 ###  
log4j.appender.stdout=org.apache.log4j.ConsoleAppender  
log4j.appender.stdout.Target=System.out  
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout  
log4j.appender.stdout.Encoding=UTF-8
log4j.appender.stdout.layout.ConversionPattern= %d{yyy-MM-dd HH\:mm\:ss} %p %c{0}\:%L - %m%n

4. 创建数据库day56-mybatis-senior和表user

create database day56-mybatis-senior;
use day56-mybatis-senior
create table user(
	id int primary key,
	name varchar(20),
	pwd varchar(20),
	age int
)

1. #和$符号的区别

1.1 定义User

package com.wpj.bean;

/**
 * ClassName: User
 * Description:
 *			提供无参全参构造方法，getter和setter方法，toString方法
 * @author JieKaMi
 * @version 1.0
 * @date: 2020\1\7 0007 19:10
 * @since JDK 1.8
 */
public class User {
    private Integer id;
    private String name;
    private String pwd;
    private Integer age;
    public User() { }
    public User(Integer id, String name, String pwd, Integer age) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
        this.age = age;
    }
    public Integer getId() { return id; }
    public void setId(Integer id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getPwd() { return pwd; }
    public void setPwd(String pwd) { this.pwd = pwd; }
    public Integer getAge() { return age; }
    public void setAge(Integer age) { this.age = age; }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                ", age=" + age +
                '}';
    }
}

1.2 定义IUserDao2接口

因为我是一个project多个模块，IUserDao之前定义过了，所以定义IUserDao2。

package com.wpj.dao;

import com.wpj.bean.*;

/**
 * ClassName: IUserDao2
 * Description:
 *
 * @author JieKaMi
 * @version 1.0
 * @date: 2020\1\7 0007 19:02
 * @since JDK 1.8
 */
public interface IUserDao2 {
    /**
     * 根据id查询对象，但是对象要显示的属性由用户指定
     * @param id    id
     * @param column {id,name}
     * @return
     */
    User getUserById(@Param("id")Integer id, @Param("column")String column);
}

1.3 创建接口对应mapper #{column}

<?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.wpj.dao.IUserDao2">
    <select id="getUserById" resultType="user">
        select #{column} from user
    </select>
</mapper> 

1.3 创建接口对应mapper ${column}

<?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.wpj.dao.IUserDao2">
    <select id="getUserById" resultType="user">
        select ${column} from user
    </select>
</mapper> 

1.4 Test

import com.wpj.bean.*;
import com.wpj.dao.*;
import org.apache.ibatis.io.*;
import org.apache.ibatis.session.*;
import org.junit.*;

import java.io.*;

/**
 * ClassName: MyBatisTest
 * Description:
 *
 * @author JieKaMi
 * @version 1.0
 * @date: 2020\1\7 0007 19:19
 * @since JDK 1.8
 */
public class MyBatisTest {
    @Test
    public void testGetUserById(){
        String config = "MyBatis.xml";
        InputStream is = null;
        try {
            is = Resources.getResourceAsStream(config);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            IUserDao2 userDao = sqlSession.getMapper(IUserDao2.class);
            User user = userDao.getUserById(1,"id,name");
            System.out.println(user);
            sqlSession.close();

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(is!=null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

1.5 结果

1.5.1 使用#{}

Preparing: select ? from user where id = ?
Parameters: id,name(String), 1(Integer)
Total: 1
null

1.5.2 使用${}

Preparing: select id,name from user where id = ?
Parameters: 1(Integer)
Total: 1
User{id=1, name=‘wpj’, pwd=‘null’, age=null}

1.6 ${} 的应用场景

/**
 * 根据用户输入的sql查询
 * @param sql 用户输入的sql语句
 * @return 查询的结果集
 */
List<Map<Object,Object>> findBySql(@Param("sql") String sql);

<select id="findBySql" resultType="map">
   ${sql}
</select>

List<Map<Object,Object>> list =userDao.findBySql("select * from user"); 
for(Map<Object,Object> map : list){
	System.out.println(map);
}

1.7 update标签中可以写delete语句

public int deleteUser(Integer id);

<update id="deleteUser">
	delete from t_user where id = #{id}
</update>

userDaoImpl.deleteUser(5);

因为mybatis底层也是jdbc，而jdbc操作数据库只有select 和 update（update-delete-insert）