表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));
}
}
结果