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.     }