DBUtils+连接池实现根据年龄区间 部门名称等动态模糊查询

表1：employee

表2：department

结构：

实例类

package com.m.domain;public class Condition {
    private Integer id;private String name;private String sex;private Integer age;private String dep_id;private String dname;public Condition() {
    }public Integer getId() {
    return id;}public void setId(Integer id) {
    this.id = id;}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 Integer getAge() {
    return age;}public void setAge(Integer age) {
    this.age = age;}public String getDep_id() {
    return dep_id;}public void setDep_id(String dep_id) {
    this.dep_id = dep_id;}public String getDname() {
    return dname;}public void setDname(String dname) {
    this.dname = dname;}@Overridepublic String toString() {
    return "Condition [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + ", dep_id=" + dep_id+ "]";}}

连接池

package com.m.Utils;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3P0Utils {
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();public static DataSource getDataSource() {
    return dataSource;}}

com.m.conditionDAO
接口

package com.m.conditionDAO;import java.util.List;import com.m.domain.Condition;public interface conditionDAO {
    
// 动态查询public List<Condition> queryLike(Condition con) throws Exception;}

实现类：

package com.m.conditionDAO;import java.util.ArrayList;
import java.util.List;import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;import com.m.Utils.C3P0Utils;
import com.m.domain.Condition;public class conditionDAOIpml implements conditionDAO {
    
//使用连接池+dbutils连接数据库QueryRunner qr=new QueryRunner(C3P0Utils.getDataSource());@Overridepublic List<Condition> queryLike(Condition con) throws Exception {
    
// 查询语句String sql="select e.* from employee e join department d on e.`dep_id`=d.`id` where 1=1 ";//注意这个地方有空格
// where 1=1 是想直接加上后面的条件
 获取con中的条件String sex=con.getSex();Integer age=con.getAge();String dname=con.getDname();
 使用List集合加入条件List<Object> list=new ArrayList <Object>();if(sex!=null && !sex.isEmpty()) {
    sql+="and sex like ? ";list.add("%"+sex+"%");}if(age!=null) {
    sql+="and age = ? ";list.add(age);}if(dname!=null&&!dname.isEmpty()) {
    sql+="and dname like ? ";list.add("%"+dname+"%");}
// 使用qr查询List<Condition> list2 = qr.query(sql, new BeanListHandler<Condition>(Condition.class), list.toArray());return list2;}}

com.m.demoService
接口

package com.m.demoService;import java.util.List;import com.m.domain.Condition;public interface demoService {
    
// 实现用户模糊查询操作public List<Condition> checkLike(Condition con) throws Exception;}

实现类

package com.m.demoService;import java.util.List;import com.m.conditionDAO.conditionDAO;
import com.m.conditionDAO.conditionDAOIpml;
import com.m.domain.Condition;public class demoServiceImpl implements demoService {
    conditionDAO cd=new conditionDAOIpml();@Overridepublic List<Condition> checkLike(Condition con) throws Exception {
    return cd.queryLike(con);}}

测试类

package com.m.test;import java.util.List;import com.m.demoService.demoService;
import com.m.demoService.demoServiceImpl;
import com.m.domain.Condition;public class test {
    public static void main(String[] args) throws Exception {
    Condition cd=new Condition();demoService ds=new demoServiceImpl();
// 按性别查询
// cd.setSex("f");
// List<Condition> list = ds.checkLike(cd);
// list.stream().forEach(a->System.out.println(a));
 性别和部门
// cd.setDname("技");
 cd.setSex("f");
// List<Condition> list = ds.checkLike(cd);
// list.stream().forEach(a->System.out.println(a));
// 按年龄查cd.setAge(18);List<Condition> list = ds.checkLike(cd);list.stream().forEach(a->System.out.println(a));}}

结果