当前位置： 首页 > news >正文

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

news 2025/8/6 14:47:42

一、需求

根据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。
http://www.lryc.cn/news/312764.html

相关文章：

  • LabVIEW管道缺陷智能检测系统
  • java在cmd中乱码的问题解决
  • OpenHarmony教程指南—ArkUI中组件、通用、动画、全局方法的集合
  • 第二证券|金价逼近历史高点 黄金股价值有望重估
  • 关于51单片机晶振定时问题
  • NoSQL--2.MongoDB配置（Windows版）
  • HTML静态网页成品作业(HTML+CSS)——安徽宣笔设计制作(5个页面)
  • MySQL CTEs通用表表达式：进阶学习-递归查询
  • [Java安全入门]二.序列化与反序列化
  • Dutree：Linux 文件系统磁盘使用追踪工具
  • http和https的区别是什么？
  • 学习Android的第十九天
  • C#上位机调试经验
  • BUUCTF---[极客大挑战 2019]BabySQL1
  • 0基础跨考计算机｜408保姆级全年计划
  • C# 操作LiteDB
  • LeetCode 2917.找出数组中的 K-or 值：基础位运算
  • MySQL窗口函数：从理论到实践
  • Vue+SpringBoot打造考研专业课程管理系统
  • python基础第二天
  • YOLOV9论文解读
  • 【Spring】21 通过@Primary注解优化注解驱动的自动装配
  • 【HTML】HTML基础7.3（自定义列表）
  • java设计模式课后作业（待批改）
  • qt 语音引擎 QTextToSpeech Microsoft SAPI
  • react hook： useimperativeHandle
  • 30天自制操作系统（第28天）
  • Nginx启动服务
  • coqui-ai/TTS 案例model文件
  • 如何利用API接口进行高效的商品变体管理？