java jdbc查询的解读 2015年9月23日
学习
0 条评论

比上篇文章更好一点的查询代码。

一、代码实现

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

简单直接。

二、总结

记录一下。

Post Views: 70