Java訪問sql server資料庫工具

標籤：

  1 package com.dao;  2   3 import java.sql.*;  4 import java.util.ArrayList;  5 import java.util.List;  6   7 import com.sun.jmx.snmp.Timestamp;  8   9 public abstract class BaseDao { 10  11     String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; 12     String url = "jdbc:sqlserver://localhost:1433;database=NewsManagerSystem"; 13     String user = "sa"; 14     String pwd = "sa"; 15  16     private Connection conn; 17     private PreparedStatement stmt; 18     private ResultSet rs; 19      20     public abstract Object createModel(ResultSet rs) throws SQLException; 21  22     private Connection getConnection() throws SQLException { 23          24         try { 25             Class.forName(driver); 26         } catch (ClassNotFoundException e) { 27             e.printStackTrace(); 28         } 29          30         return DriverManager.getConnection(url, user, pwd); 31     } 32      33     private void closeAll(Connection conn, Statement stmt,ResultSet rs) { 34          35         if(rs != null){ 36             try{ 37                 rs.close(); 38             }catch(SQLException e) { 39                 e.printStackTrace(); 40             } 41         } 42          43         if (stmt != null) { 44             try { 45                 stmt.close(); 46             } catch (SQLException e) { 47                 e.printStackTrace(); 48             } 49         } 50  51         if (conn != null) { 52             try { 53                 conn.close(); 54             } catch (SQLException e) { 55                 e.printStackTrace(); 56             } 57         } 58     } 59      60     private void preExecute(String sql,Object...args)throws SQLException{ 61         conn = getConnection(); 62         stmt = conn.prepareStatement(sql); 63         for (int i = 0; i < args.length; i++) { 64             Object p = args[i]; 65             if(p instanceof java.util.Date){ 66                 if(!p.getClass().getName().startsWith("java.sql.")){ 67                     java.util.Date date = (java.util.Date)p; 68                     p = new Timestamp(date.getTime()); 69                 } 70             } 71             stmt.setObject(i+1, p); 72         } 73     } 74      75     private void postExecute() { 76         closeAll(conn, stmt, rs); 77         conn = null; 78         stmt = null; 79         rs =  null; 80     } 81      82     protected int executeUpdate(String sql,Object...args){ 83         int res = -1; 84          85         try { 86             preExecute(sql,args); 87             res = stmt.executeUpdate(); 88         } catch (SQLException e) { 89             e.printStackTrace(); 90         } finally{ 91             postExecute(); 92         } 93          94         return res; 95     } 96      97     protected List executeQuery(String sql,Object...args){ 98         List list = new ArrayList(); 99         100         try {101             preExecute(sql,args);102             rs = stmt.executeQuery();103             104             while (rs.next()) {105                 Object obj = createModel(rs);106                 list.add(obj);107             }108         } catch (SQLException e) {109             e.printStackTrace();110         }finally{111             postExecute();112         }113         114         return list;115     }116     117     protected Object executeQueryFirst(String sql,Object...args){118         Object obj = null;119         120         try {121             preExecute(sql, args);122             rs = stmt.executeQuery();123             124             if(rs.next()){125                 obj = createModel(rs);126             }127         } catch (SQLException e) {128             e.printStackTrace();129         }finally{130             postExecute();131         }132         133         return obj;134     }135     136     protected Object executeQueryScalar(String sql,Object...args){137         Object obj = null;138         139         try {140             preExecute(sql, args);141             rs = stmt.executeQuery();142             143             if(rs.next()){144                 obj = rs.getObject(1);145             }146         } catch (SQLException e) {147             e.printStackTrace();148         }finally{149             postExecute();150         }151         152         return obj;153     }154     155 }

 

Java訪問sql server資料庫工具