「Mybatis深入二」：高级查询-多条件查询

大家好，我是讯享网，很高兴认识大家。

一、需求

根据id 和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....  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','男','上海');

讯享网

在这里插入图片描述
讯享网

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 configuration PUBLIC "-//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; @Override public 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 mapper PUBLIC "-//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="findByIdAndUsername1" resultMap="userResultMap"> <!--两种方法--> <!--select * from user where id = #{param1} and username = #{param2}--> select * from user where id = #{arg0} and username = #{arg1} </select> </mapper>

UserMapper.java

讯享网package mapper; import domain.User; import java.util.List; public interface UserMapper { 
      public User findByIdAndUsername1(int id, 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 { 
      @Test public void test1() throws Exception { 
      InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); //获取Mapper代理对象 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername1(1, "tom"); System.out.println(user); sqlSession.close(); } }

测试结果

2、方式2-注解方式

注解方式主要关注传参和sql占位符的映射关系，如下图：

在这里插入图片描述

注意：resultMap是建立结果返回类型和表字段类型的关系映射，也就是你从数据库查到数据，映射到返回结果类型对象，而@Param是传参的映射，也就是你传入的参数和sql中#{}占位符位置内容的映射关系。

User

可以修改User实体类中的属性username为usernameabc，此时 实体类的属性和表中字段名不一致，需要重新定义resultMap的映射关系。

讯享网package domain; import java.util.Date; public class User { 
      private Integer id; private String usernameabc; private Date birthday; private String sex; private String address; public Integer getId() { 
      return id; } public void setId(Integer id) { 
      this.id = id; } 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; } public String getUsernameabc() { 
      return usernameabc; } public void setUsernameabc(String usernameabc) { 
      this.usernameabc = usernameabc; } @Override public String toString() { 
      return "User{" + "id=" + id + ", usernameabc='" + usernameabc + '\'' + ", birthday=" + birthday + ", sex='" + sex + '\'' + ", address='" + address + '\'' + '}'; } }

UserMapper.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="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="findByIdAndUsername1" resultMap="userResultMap"> <!--两种方法--> <!--select * from user where id = #{param1} and username = #{param2}--> select * from user where id = #{arg0} and username = #{arg1} </select> <!--多条件查询：方式2--> <select id="findByIdAndUsername2" resultMap="userResultMap"> select * from user where username = #{usernameabc} and id = #{id} </select> </mapper>

UserMapper.java

讯享网package mapper; import domain.User; import org.apache.ibatis.annotations.Param; import java.util.List; public interface UserMapper { 
      public User findByIdAndUsername1(int id, String username); public User findByIdAndUsername2(@Param("id") Integer id, @Param("usernameabc") 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 @Test public void test1() throws Exception { 
      InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); //获取Mapper代理对象 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername1(1, "tom"); System.out.println(user); sqlSession.close(); } //多条件查询：方式2 @Test public void test2() throws Exception{ 
      InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername2(1, "tom"); System.out.println(user); sqlSession.close(); } }

测试结果

3、方式3-传入实体类 (推荐使用)

只要传入 实体类的成员属性名 和 sql占位符的传入名称 一致即可。

在这里插入图片描述

UserMapper.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="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="findByIdAndUsername1" resultMap="userResultMap"> <!--两种方法--> <!--select * from user where id = #{param1} and username = #{param2}--> select * from user where id = #{arg0} and username = #{arg1} </select> <!--多条件查询：方式2--> <select id="findByIdAndUsername2" resultMap="userResultMap"> select * from user where username = #{usernameabc} and id = #{id} </select> <!--多条件查询：方式3--> <select id="findByIdAndUsername3" parameterType="user" resultMap="userResultMap"> select * from user where id = #{id} and username = #{usernameabc} </select> </mapper>

注意：前两种方式都不需要编写parameterType，而这种方式需要。

UserMapper.java

package mapper; import domain.User; import org.apache.ibatis.annotations.Param; import java.util.List; public interface UserMapper { 
      public User findByIdAndUsername1(int id, String username); public User findByIdAndUsername2(@Param("id") Integer id, @Param("usernameabc") String username); public User findByIdAndUsername3(User user); }

测试类

讯享网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 @Test public void test1() throws Exception { 
      InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); //获取Mapper代理对象 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername1(1, "tom"); System.out.println(user); sqlSession.close(); } //多条件查询：方式2 @Test public void test2() throws Exception{ 
      InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername2(1, "tom"); System.out.println(user); sqlSession.close(); } //多条件查询：方式3 @Test public void test3() throws Exception{ 
      InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setId(1); user.setUsernameabc("tom"); User user2 = mapper.findByIdAndUsername3(user); System.out.println(user2); sqlSession.close(); } }

测试结果

「Mybatis深入二」：高级查询-多条件查询

一、需求

二、代码演示

1、方式1

2、方式2-注解方式

3、方式3-传入实体类 (推荐使用)

相关推荐