jdbc中查找条件动态化

一般一个select语句的查找条件具有不确定性，如果单纯的只是把查找条件本身当做参数放入查找函数中，就不具有灵活性，如下：

public Goddess queryOne(Integer id) throws SQLException {
        Goddess g=null;
        Connection conn=DBUtil.getConn();
        String sql=""+
                    " select * from imooc_goddess"+
                    " where id=?";
        PreparedStatement ptmt=conn.prepareStatement(sql);
        ptmt.setInt(1, id);
        ResultSet rs=ptmt.executeQuery();
        while(rs.next())
        {
            g=new Goddess();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
            g.setBirthday(rs.getDate("birthday"));
            g.setEmail(rs.getString("email"));
            g.setMobile(rs.getString("mobile"));
            g.setCreate_date(rs.getDate("create_date"));
            g.setCreate_user(rs.getString("create_user"));
            g.setUpdate_date(rs.getDate("update_date"));
            g.setUpdate_user(rs.getString("update_user"));
            g.setIsdel(rs.getInt("isdel")); 
        }
        return g;
    }

这里只是把一个条件id当做查找条件作为形参，但是事实情况可能包含很多查找条件，这时考虑可以用List

public List<Goddess> query(List<Map<String,Object>> params) throws SQLException{
        Goddess g=null;
        List<Goddess> gs = new ArrayList<Goddess>();
        Connection conn=DBUtil.getConn();
        StringBuilder sb=new StringBuilder();
        sb.append("select * from imooc_goddess where 1=1");
        if(params!=null && params.size()>0)
        {
            for(int i=0;i<params.size();i++)
            {
                Map<String,Object> tmp=params.get(i);
                sb.append(" and "+tmp.get("name")+" "+tmp.get("rea")+" "+tmp.get("value"));
            }
        }
        PreparedStatement ptmt=conn.prepareStatement(sb.toString());
        ResultSet rs=ptmt.executeQuery();
        while(rs.next())
        {
            g=new Goddess();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
            g.setBirthday(rs.getDate("birthday"));
            g.setEmail(rs.getString("email"));
            g.setMobile(rs.getString("mobile"));
            g.setCreate_date(rs.getDate("create_date"));
            g.setCreate_user(rs.getString("create_user"));
            g.setUpdate_date(rs.getDate("update_date"));
            g.setUpdate_user(rs.getString("update_user"));
            g.setIsdel(rs.getInt("isdel")); 
            gs.add(g);
        }
        return gs;      
    }

如果具有以下数据表imooc_goddess:

具体测试的时候比如 select * from imooc_goddess where user_name like “’%程%’” and sex=1，代码如下：

public static void main(String[] args) throws Exception {
        GoddessDao dao=new GoddessDao();
        List<Goddess> gs=new ArrayList<Goddess>();
        Goddess g=null;
        List<Map<String,Object>> params=new ArrayList<Map<String,Object>>();
        Map<String,Object> param=new HashMap<String,Object>();
        param.put("name", "sex");
        param.put("rea","=");
        param.put("value", 1);
        params.add(param);
        param=new HashMap<String,Object>();
        param.put("name", "user_name");
        param.put("rea", "like");
        param.put("value", "'%程%'");
        params.add(param);
        gs=dao.query(params);
        for(Goddess gg:gs)
        {
            System.out.println(gg.toString());
        }
    }

输出结果如下：
Goddess [id=12, user_name=程秋平, sex=1, age=20, birthday=2017-02-21, email=Viviencheng@outlook.com, mobile=15527128967, create_user=Admin, update_user=Admin, create_date=2017-02-21, update_date=2017-02-21, isdel=1]

另外一个技巧：
如果查询条件中间不用连接词and而是用or，那么就应该把sb的初始字符串的“1=1”换成”1=0”，并且在后面的连接中用”or”:

sb.append("select * from imooc_goddess where 1=0");
        if(params!=null && params.size()>0)
        {
            for(int i=0;i<params.size();i++)
            {
                Map<String,Object> tmp=params.get(i);
                sb.append(" or "+tmp.get("name")+" "+tmp.get("rea")+" "+tmp.get("value"));
            }
        }