比上篇文章更好一点的查询代码。
一、代码实现
(1)写死查询范围,非常简单的查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
public List<Goddness> query() throws SQLException // 就是这里出错了 { List<Goddness>result = new ArrayList<Goddness>(); Connection conn = dbutil.getConnection(); StringBuilder sb = new StringBuilder(); sb.append("select id,user_name,age from myfriend"); PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sb.toString()); ResultSet rs = ptmt.executeQuery(); Goddness g =null; while(rs.next()) { g = new Goddness(); g.setId(rs.getInt("id")); g.setUser_name(rs.getString("user_name")); g.setAge(rs.getInt("age")); result.add(g); } return result; } |
(2)稍微加强一点
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
public List<Goddness> query(String name,String mobile,String email) throws SQLException { List<Goddness>result = new ArrayList<Goddness>(); Connection conn = dbutil.getConnection(); StringBuilder sb = new StringBuilder(); sb.append("select * from myfriend"); sb.append(" where user_name like ? and mobile like ? and email like ?"); PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sb.toString()); ptmt.setString(1, "%"+name+"%"); ptmt.setString(2, "%"+mobile+"%"); ptmt.setString(3, "%"+email+"%"); System.out.println(sb.toString()); ResultSet rs = ptmt.executeQuery(); Goddness g =null; while(rs.next()) { g = new Goddness(); g.setId(rs.getInt("id")); g.setUser_name(rs.getString("user_name")); g.setAge(rs.getInt("age")); g.setSex(rs.getInt("sex")); 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")); result.add(g); } return result; } |
单独查询了name,mobie和email。
(3)再复杂一点的查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
public List<Goddness> query(List<Map<String, Object>>params) throws SQLException { List<Goddness>result = new ArrayList<Goddness>(); Connection conn = dbutil.getConnection(); StringBuilder sb = new StringBuilder(); sb.append("select * from myfriend where 1=1"); //sb.append("select * from myfriend where 1=1 and "); 重要技巧,经常用到 if(params!=null && params.size()>0) { for(int i = 0;i < params.size();i++) { Map<String, Object>map = params.get(i); sb.append(" and "+map.get("name")+" "+map.get("rela")+" "+map.get("value")); } } PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sb.toString()); System.out.println(sb.toString()); ResultSet rs = ptmt.executeQuery(); Goddness g =null; while(rs.next()) { g = new Goddness(); g.setId(rs.getInt("id")); g.setUser_name(rs.getString("user_name")); g.setAge(rs.getInt("age")); g.setSex(rs.getInt("sex")); 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")); result.add(g); } return result; } |
sql语句中的and 1 = 1,构造得很巧妙。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
List<Map<String, Object>> params = new ArrayList<Map<String,Object>>(); Map<String, Object> param = new HashMap<String,Object>(); param.put("name", "user_name"); param.put("rela", "like"); param.put("value", "'%xie4ever%'"); params.add(param); param = new HashMap<String,Object>(); param.put("name", "mobile"); param.put("rela", "like"); param.put("value", "'%5038%'"); params.add(param); List<Goddness> result = g.query(params); for(int i = 0;i < result.size(); i++) { System.out.println(result.get(i).toString()); } |
关键字可以通过maps的param来内嵌进去,非常方便。
(4)用id做单次查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
public Goddness get(Integer id) throws SQLException { Goddness g = null; Connection conn = dbutil.getConnection();//获得链接 String sql = ""+ " select * from myfriend "+ " where id = ? ";//编写sql语句 PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);//预编译 ptmt.setInt(1, id); ResultSet rs = ptmt.executeQuery();//查询这里不再用execute操作,execute针对数据的修改,增删改 //查询的话用executeQuery()查询操作 while(rs.next()) //遍历结果集 { g = new Goddness(); g.setId(rs.getInt("id")); g.setUser_name(rs.getString("user_name")); g.setAge(rs.getInt("age")); g.setSex(rs.getInt("sex")); 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; } |
简单直接。
二、总结
记录一下。