使用bind元素创建变量

在使用模糊查询sql时，如果使用${}进行字符拼接，无法防止sql诸如问题，如果使用concat函数则只对mysql有效果，用Oracle则需要用连接符||，这样在数据库变的时候需要修改，不利于移植。mybatis提供元素<bind>很好能解决这个问题。

项目结构：

废话不说，使用maven管理jar，如下：

<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>com.example</groupId>
  <artifactId>maven</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>maven</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
   <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.2</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.2</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.40</version>
      </dependency>
  </dependencies>
</project>

mybatis配置如下：

<?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="resoures/db.properties" />
    <!--1.配置环境 ，默认的环境id为mysql -->
    <environments default="mysql">
        <!--1.2.配置id为mysql的数据库环境 -->
        <environment id="mysql">
            <!-- 使用JDBC的事务管理 -->
            <transactionManager type="JDBC" />
            <!--数据库连接池 -->
            <dataSource type="POOLED">
                <!-- 数据库驱动 -->
                <property name="driver" value="${jdbc.driver}" />
                <!-- 连接数据库的url -->
                <property name="url" value="${jdbc.url}" />
                <!-- 连接数据库的用户名 -->
                <property name="username" value="${jdbc.username}" />
                <!-- 连接数据库的密码 -->
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>
    </environments>
    <!--2.配置Mapper的位置 -->
    <mappers>
        <mapper resource="com/example/test/mybatis/mapper/UserMapper.xml" />
    </mappers>
</configuration>

数据库配置：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=root

PS：注意配置文件的位置!

三种方法实现：

数据库表格结构

javabean

package com.example.test.mybatis;

public class User {
    
    private String name;
    private String sex;
    private int age;
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "User [name=" + name + ", sex=" + sex + ", age=" + age + "]";
    }
    
}

session工具类：

package com.example.test.mybatis;

import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybatisUtils {
    
    private static SqlSessionFactory sqlSessionFactory = null;
    // 初始化SqlSessionFactory对象
    static {
        try {
            // 使用MyBatis提供的Resources类加载mybatis的配置文件
            Reader reader = 
                    Resources.getResourceAsReader("resoures/mybatis-config.xml");
            // 构建sqlSession的工厂
            sqlSessionFactory = 
                    new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取SqlSession对象的静态方法
    public static SqlSession getSession() {
        return sqlSessionFactory.openSession();
    }

}

mapper文件

方法一：_parameter.getName()传参，注意参数类型为javabean User

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

   <select id="findUserByName" parameterType="com.example.test.mybatis.User"
        resultType="com.example.test.mybatis.User">
        <!--_parameter.getUsername()也可直接写成传入的字段属性名，即username -->
        <bind name="pattern" value="'%'+_parameter.getName()+'%'" />
        select * from user
        where
        name like #{pattern}
    </select>

</mapper>

测试方式：

package com.example.test.mybatis;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

public class MybatisTest {
    public static void main(String[] args) {
        SqlSession session = MybatisUtils.getSession();
        User user = new User();
        user.setName("s");
        
        List<User> list = session.selectList("com.example.test.mybatis.mapper.UserMapper.findUserByName",user);
        for (User u : list) {
            System.out.println(u);
        }
        session.close();
    }

}

测试结果：User [name=as, sex=男, age=1]

 

方法二：直接使用_parameter传参，注意传入的参数类型是String name

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

    <select id="findUserByStrName" parameterType="String"
        resultType="com.example.test.mybatis.User">
        <!--_parameter.getUsername()也可直接写成传入的字段属性名，即username -->
        <bind name="username" value="'%'+_parameter+'%'" />
        select * from user
        where
        name like #{username}
    </select>
   
</mapper>

对应测试方法：

package com.example.test.mybatis;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

public class MybatisTest {
    public static void main(String[] args) {
        SqlSession session = MybatisUtils.getSession();  
        String name = "aa";  
        
        List<User> list = session.selectList("com.example.test.mybatis.mapper.UserMapper.findUserByStrName",name);
        for (User u : list) {
            System.out.println(u);
        }
        session.close();
    }

}
测试结果：

User [name=aa, sex=男, age=10]
User [name=aaad, sex=男, age=11]

方法三：直接用javabean的属性传参，注意参数类型是javabean User

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

   <select id="findUserByParameterName" parameterType="com.example.test.mybatis.User"
        resultType="com.example.test.mybatis.User">
        <!--_parameter.getUsername()也可直接写成传入的字段属性名，即name -->
        <bind name="username" value="'%'+name+'%'" />
        select * from user
        where
        name like #{username}
    </select>
    
</mapper>

package com.example.test.mybatis;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

public class MybatisTest {
    public static void main(String[] args) {
        SqlSession session = MybatisUtils.getSession();
        User user = new User();
        user.setName("aa");
        List<User> list = session.selectList("com.example.test.mybatis.mapper.UserMapper.findUserByParameterName",user);
        for (User u : list) {
            System.out.println(u);
        }
        session.close();
    }
    

}

测试结果：

User [name=aa, sex=男, age=10]
User [name=aaad, sex=男, age=11]