多条件可选查询解决方法

多条件可选查询
在前台有几个查询条件的填写。填写了姓名后按照姓名模糊查找，填写了年龄后可以按年龄范围查找，填写了成绩后可以按成绩查找，这三个条件任写其中的两个或多个都可以按其对应的条件查找出来！不分先后！

------解决方案--------------------
StringBuffer，然后用append
------解决方案--------------------
把条件理清楚了，很easy的！不相信，自己试试呗

------解决方案--------------------

探讨
String sql="select pid,pname,model,price,manufact,stockNum,inDate,state from product where state=0 ";
if(ep.getPname()!=""){
System.out.println("==name"+ep.getPname());
sql=sql+" and pname like '%"……

------解决方案--------------------

Java code


package com.bt.employee.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bt.employee.dao.EmployeeDAO_Query;
import com.bt.employee.entity.Employee;
import com.bt.employee.util.DBUtil;

public class EmployeeDAOImpl_Query1 implements EmployeeDAO_Query{

    public List<Employee> specificInquiresr(String ename, 
            String age, String grade) throws SQLException {
        Connection conn  = DBUtil.getConnection();
        List<Employee> employees =  new ArrayList<Employee>();
        StringBuffer sql = new StringBuffer();
        sql.append("select * from 表 where 1=1");
        //String ename1 = ename.trim();
        if(ename.trim()!=""){
            sql.append(" and ename like '%" + ename +"%'");
        }
                
        if(age.equals("0")){
            sql.append(" " );
        }else if(age.equals("1")){
            sql.append(" and age<60" );
        }else if(age.equals("2")){
            sql.append(" and salary between 60 and 80" );
        }else if(age.equals("3")){
            sql.append(" and salary between 80 and 100" );
        }
                if(grade!=""){
            sql.append(" and grade= " + grade);
        }
        
        String sql1 = sql.toString();
        PreparedStatement ps = conn.prepareStatement(sql1);
        System.out.println(sql1);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            Employee e = new Employee();
            e.setId(rs.getLong("id"));
            e.setEname(rs.getString("ename"));
            e.setAge(rs.getInt("age"));
            e.setSalary(rs.getDouble("grade"));
            employees.add(e);
        }
        return employees;
    }

}

------解决方案--------------------
ibatis的SQLmapping里面有isNotEmpty判断

多条件可选查询解决方法

相关推荐