jdbc通用访问方法

1. //通过SQL反回对应的数据集
2. //比如:sql select nameSc,age from users
3. public List getResultDataList(String sql, Connection con) {
4. List<Map> dataList = new ArrayList<Map>();
5. Map<String, String> map = new HashMap<String, String>();//存放对应字段数据
6. PreparedStatement ps = null;
7. ResultSet ds = null;
8. ResultSetMetaData rsd;
9. try {
10. ps = con.prepareStatement(sql);
11. ds = ps.executeQuery();
12. while (ds.next()) {
13. map = new HashMap<String, String>();//通过键值对存放一条记录的数据
14. rsd = ds.getMetaData();
15. for (int i = 1; i <= rsd.getColumnCount(); i++) {
16. switch (rsd.getColumnType(i)) {//通过判断数据类型转换数据
17. case 12:// varchar(12)
18. map.put(rsd.getColumnName(i), ds.getString(i));
19. break;
20. case 1:// char(1)
21. map.put(rsd.getColumnName(i), ds.getString(i));
22. break;
23. case -7:// bit(-7)
24. map.put(rsd.getColumnName(i), Boolean.toString(ds
25. .getBoolean(i)));
26. break;
27. case 4:// int(4)
28. map.put(rsd.getColumnName(i), Integer.toString(ds
29. .getInt(i)));
30. break;
31. case 5:// smallint(5)
32. map.put(rsd.getColumnName(i), Integer.toString(ds
33. .getInt(i)));
34. break;
35. case 93:// datetime(93)
36. if (ds.getString(i) == null)
37. map.put(rsd.getColumnName(i), "");
38. else {
39. if (ds.getString(i).length() >= 19) {
40. map.put(rsd.getColumnName(i), ds.getString(i)
41. .substring(0, 19));
42. } else {
43. map.put(rsd.getColumnName(i), ds.getString(i)
44. .substring(0, 10));
45. }
46. }
47. break;
48. case 3:// decimal(3)
49. map.put(rsd.getColumnName(i), Double.toString(ds
50. .getDouble(i)));
51. break;
52. default:
53. map.put(rsd.getColumnName(i), ds.getString(i));
54. break;
55. }
56. }
57. dataList.add( map);//存放所有行数据
58. }
59. } catch (SQLException e) {
60. e.printStackTrace();
61. throw new RuntimeException(e);
62. } finally {
63. <span style="color: red;">connection是通过HibernateDaoSupport获取得到的，似乎能够自动回收（反回到连接池），是否真的不用关闭连接呢？</span>
64. /*
65. * try{ if(ds!=null){ds.close();ds=null;}
66. * if(ps!=null){ps.close();ps=null;}
67. * if(con!=null){con.close();con=null;} }catch(SQLException e){}
68. */
69. }
70. return dataList;
71. }


1. //通过传递SQL,Sql的参数更新数据
2. //比如 sql insert into users(nameCs,age)valuse(?,?)
3. //params就是存放?代表的值
4. public int execUpdateByParams(String sql, Connection con, List params) {
5. PreparedStatement ps = null;
6. int message = 0;
7. try {
8. String paramsStr="";
9. ps = con.prepareStatement(sql);
10. if (params != null) {
11. for (int i = 0; i < params.size(); i++) {
12. String p = params.get(i)==null?null:"" + params.get(i);//设置对应的参数
13. ps.setString(i + 1, p);
14. }
15. }
16. ps.executeUpdate();
17. } catch (SQLException e) {
18. e.printStackTrace();
19. message = -1;
20. throw new RuntimeException(e);
21. } finally {
22. try {
23. if (ps != null) {
24. <span style="color: red;">ps.close();</span>//怎么在这他有把这个关闭了呢？这会有问题吧？
25. ps = null;
26. }
27. // if(con!=null){con.close();con=null;}
28. } catch (SQLException e) {
29. }
30. }
31.
32. return message;
33. }