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;
	}

	@Override
	public 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());
	@Override
	public 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();
	@Override
	public 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));
		
		

	}

}

结果