动态SQL与模糊查询

    xiaoxiao2021-03-26  12

    动态SQL与模糊查询

    创建数据

    create table d_user( id int primary key auto_increment, name varchar(10), age int(3) ); insert into d_user(name,age) values('Tom',12); insert into d_user(name,age) values('Bob',13); insert into d_user(name,age) values('Jack',18); insert into d_user(name,age) values('Jhon',16); insert into d_user(name,age) values('Bart',15); insert into d_user(name,age) values('Lisa',17);

    问题分析

    查询名字中带“o”的,并且年龄在13–18岁之间的user

    普通sql查询

    select * from d_user where name like ' %o%' and age between 13 and 18; +----+------+-----+ | id | name | age | +----+------+-----+ | 2 | Bob | 13 | | 4 | Jhon | 16 | +----+------+-----+ 2 rows in set

    使用mybatis查询

    封装查询条件的bean

    package com.bart.mybatis.beans; /** * 查询条件的封装类 * @author hp * */ public class ConditionUser { @Override public String toString() { return "ConditionUser [name=" + name + ", minAge=" + minAge + ", maxAge=" + maxAge + "]"; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getMinAge() { return minAge; } public void setMinAge(int minAge) { this.minAge = minAge; } public int getMaxAge() { return maxAge; } public void setMaxAge(int maxAge) { this.maxAge = maxAge; } private String name; private int minAge; private int maxAge; public ConditionUser(String name, int minAge, int maxAge) { super(); this.name = name; this.minAge = minAge; this.maxAge = maxAge; } public ConditionUser() { super(); } }

    配置mapper

    <?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.bart.mybatis.mapper.conditionUserMapper"> <!-- 查询名字带'o'的并且在 minAge到maxAge之间 --> <select id="getUser" parameterType="_ConditionUser" resultType="_User"> select * from d_user where <if test='name!="%null%" '> name like #{name} and </if> age between #{minAge} and #{maxAge} </select> </mapper>

    配置conf.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> <!-- development : 开发模式 work : 工作模式 --> <!-- 加载db.properties --> <properties resource="db.properties"> </properties> <!-- 配置别名,可以再mapper中使用,方便操作 --> <typeAliases> <typeAlias type="com.bart.mybatis.beans.User" alias="_User"/> <typeAlias type="com.bart.mybatis.beans.ConditionUser" alias="_ConditionUser"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${driver}" /> <property name="url" value="${url}" /> <property name="username" value="${name}" /> <property name="password" value="${password}" /> </dataSource> </environment> </environments> <!-- 映射mapper配置文件 --> <mappers> resource="com/bart/mybatis/mapper/conditionUserMapper.xml"/> </mappers> </configuration>

    创建测试

    @Test public void Test(){ SqlSessionFactory factory = DBUtil.getSessionFactory(); SqlSession session = factory.openSession(); String statement="com.bart.mybatis.mapper.conditionUserMapper.getUser"; //String name = "null"; String name = "o"; ConditionUser cu = new ConditionUser("%"+name+"%",13,18); List<User> list = session.selectList(statement,cu); System.out.println(list); session.close(); }

    结果

    当String name = "null"; 相当于只查询年龄13–18 的user [ User [id=2, name=Bob, age=13], User [id=3, name=Jack, age=18], User [id=4, name=Jhon, age=16], User [id=5, name=Bart, age=15], User [id=6, name=Lisa, age=17], User [id=7, name=Lisa, age=17] ] 当String name = "o";相当于查询名字中带“o”的年龄在13–18 之间的user [User [id=2, name=Bob, age=13], User [id=4, name=Jhon, age=16]]

    总结

    在mapper中配置select查询的时候用到了类似于JSTL的判断语句,当满足该条件时,包含的条件作为查询条件,否则不作为查询条件,实现了SQL的动态查询

    转载请注明原文地址: https://ju.6miu.com/read-600347.html

    最新回复(0)