「Mybatis深入三」：高级查询-模糊查询

一、需求

根据username 模糊查询user 表

二、代码演示

1、方式1

• 数据库环境

  CREATE DATABASE `mybatis_db`;
  USE `mybatis_db`;
  CREATE TABLE `user` (`id` INT(11) NOT NULL AUTO_INCREMENT,`username` VARCHAR(32) NOT NULL COMMENT '用户名称',`birthday` DATETIME DEFAULT NULL COMMENT '生日',`sex` CHAR(1) DEFAULT NULL COMMENT '性别',`address` VARCHAR(256) DEFAULT NULL COMMENT '地址',PRIMARY KEY (`id`)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO USER(id,username,birthday,sex,address) VALUES (1,'tom','2024-01-01 00:00:00','男','北京'),(2,'jerry','2023-01-01 00:00:00','男','上海'),(3,'tom1','2024-02-29 00:00:00','男','北京');

• pom依赖

  <?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>org.example</groupId><artifactId>mybatis-dao</artifactId><version>1.0-SNAPSHOT</version><!--指定编码及版本--><properties><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><maven.compiler.encoding>UTF-8</maven.compiler.encoding><java.version>1.11</java.version><maven.compiler.source>1.11</maven.compiler.source><maven.compiler.target>1.11</maven.compiler.target></properties><!--引入相关依赖--><dependencies><!--引入mybatis依赖--><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.3</version></dependency><!--引入mysql驱动--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.47</version></dependency><!--引入junit--><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version></dependency></dependencies></project>
  

• SqlMapConfig.xml

  <?xml version="1.0" encoding="UTF-8" ?>
  <!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><properties resource="jdbc.properties"></properties><typeAliases><!--方式一：给单个实体起别名-->
  <!--        <typeAlias type="domain.User" alias="user"></typeAlias>--><!--方式二：批量起别名 别名就是类名，且不区分大小写--><package name="domain"/></typeAliases><!--环境配置--><environments default="mysql"><!--使用mysql环境--><environment id="mysql"><!--使用jdbc事务管理亲--><transactionManager type="JDBC"></transactionManager><!-- 使用连接池--><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><!--加载映射配置--><mappers><mapper resource="mapper/UserMapper.xml"></mapper></mappers></configuration>
  

• User实体类

  package domain;import java.util.Date;public class User {private Integer id;private String username;private Date birthday;private String sex;private String address;@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", birthday=" + birthday +", sex='" + sex + '\'' +", address='" + address + '\'' +'}';}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}
  }

• UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.UserMapper"><!--id : 标签的唯一标识type: 封装后实体类型--><resultMap id="userResultMap" type="domain.User"><!--手动配置映射关系--><!--id: 用来配置主键--><id property="id" column="id"></id><!-- result: 表中普通字段的封装--><result property="username" column="username"></result><result property="birthday" column="birthday"></result><result property="sex" column="sex"></result><result property="address" column="address"></result></resultMap><!--模糊查询：方式1--><select id="findByUsername1" parameterType="string" resultMap="userResultMap">select * from user where username like #{username}</select></mapper>

• UserMapper.java

  package mapper;import domain.User;import java.util.List;public interface UserMapper {public List<User> findByUsername1(String username);}

• 测试类

  package test;import domain.User;
  import mapper.UserMapper;
  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.Test;import java.io.InputStream;
  import java.util.List;public class MybatisTest {//模糊查询：方式1@Testpublic void testFindByUsername1() throws Exception{InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);List<User> users = mapper.findByUsername1("%tom%");for (User user : users) {System.out.println(user);}sqlSession.close();}}

• 测试结果

  

2、方式2

• UserMapper.java

  package mapper;import domain.User;
  import org.apache.ibatis.annotations.Param;import java.util.List;public interface UserMapper {public List<User> findByUsername1(String username);public List<User> findByUsername2(String username);
  }

• UserMapper.xml

  <?xml version="1.0" encoding="UTF-8" ?>
  <!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="mapper.UserMapper"><!--id : 标签的唯一标识type: 封装后实体类型--><resultMap id="userResultMap" type="domain.User"><!--手动配置映射关系--><!--id: 用来配置主键--><id property="id" column="id"></id><!-- result: 表中普通字段的封装--><result property="usernameabc" column="username"></result><result property="birthday" column="birthday"></result><result property="sex" column="sex"></result><result property="address" column="address"></result></resultMap><!--模糊查询：方式1--><select id="findByUsername1" parameterType="string" resultMap="userResultMap">select * from user where username like #{username}</select><!--模糊查询：方式2--><select id="findByUsername2" parameterType="string" resultMap="userResultMap">select * from user where username like '%${value}%'</select></mapper>
  

  注意：

  1. ${}中只能填写value
  2. 不推荐使用，因为会出现sql 注入问题

• 测试类

  package test;import domain.User;
  import mapper.UserMapper;
  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.Test;import java.io.InputStream;
  import java.util.List;public class MybatisTest {//模糊查询：方式1@Testpublic void testFindByUsername1() throws Exception{InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);List<User> users = mapper.findByUsername1("%tom%");for (User user : users) {System.out.println(user);}sqlSession.close();}//模糊查询：方式2@Testpublic void testFindByUsername2() throws Exception{InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);List<User> users = mapper.findByUsername2("tom");for (User user : users) {System.out.println(user);}sqlSession.close();}}

• 测试结果

  

3、方式3 (推荐使用)

• UserMapper.java

  package mapper;import domain.User;
  import org.apache.ibatis.annotations.Param;import java.util.List;public interface UserMapper {public List<User> findByUsername1(String username);public List<User> findByUsername2(String username);public List<User> findByUsername3(String username);
  }

• UserMapper.xml

  <?xml version="1.0" encoding="UTF-8" ?>
  <!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="mapper.UserMapper"><!--id : 标签的唯一标识type: 封装后实体类型--><resultMap id="userResultMap" type="domain.User"><!--手动配置映射关系--><!--id: 用来配置主键--><id property="id" column="id"></id><!-- result: 表中普通字段的封装--><result property="usernameabc" column="username"></result><result property="birthday" column="birthday"></result><result property="sex" column="sex"></result><result property="address" column="address"></result></resultMap><!--模糊查询：方式1--><select id="findByUsername1" parameterType="string" resultMap="userResultMap">select * from user where username like #{username}</select><!--模糊查询：方式2--><select id="findByUsername2" parameterType="string" resultMap="userResultMap">select * from user where username like '%${value}%'</select><!--模糊查询：方式3--><select id="findByUsername3" parameterType="string" resultMap="userResultMap">select * from user where username like concat(concat('%', #{username}), '%')</select></mapper>
  

  注意：

  1. ${}中只能填写value
  2. 不推荐使用，因为会出现sql 注入问题

• 测试类

  package test;import domain.User;
  import mapper.UserMapper;
  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.Test;import java.io.InputStream;
  import java.util.List;public class MybatisTest {//模糊查询：方式1@Testpublic void testFindByUsername1() throws Exception{InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);List<User> users = mapper.findByUsername1("%tom%");for (User user : users) {System.out.println(user);}sqlSession.close();}//模糊查询：方式2@Testpublic void testFindByUsername2() throws Exception{InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);List<User> users = mapper.findByUsername2("tom");for (User user : users) {System.out.println(user);}sqlSession.close();}//模糊查询：方式3@Testpublic void testFindByUsername3() throws Exception{InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);List<User> users = mapper.findByUsername3("tom");for (User user : users) {System.out.println(user);}sqlSession.close();}}

• 测试结果

  

三、${} 与 #{} 区别

1. #{} :表示一个占位符号
   • 通过 #{} 可以实现 preparedStatement 向占位符中设置值，自动进行 java 类型和 jdbc 类型转换，#{}可以有效防止sql 注入。
   • #{} 可以接收简单类型值或 pojo 属性值。
   • 如果 parameterType 传输单个简单类型值， #{} 括号中可以是value 或其它名称。
2. ${} :表示拼接sql 串
   • 通过 ${} 可以将 parameterType 传入的内容拼接在 sql 中且不进行jdbc 类型转换，会出现 sql 注入问题。
   • ${} 可以接收简单类型值或 pojo 属性值。
   • 如果 parameterType 传输单个简单类型值， ${} 括号中只能是value。