JDBC串連SQLServer資料庫（簡易代碼）

標籤：style   blog   color   java   os   io   for   資料   ar   

  1 package pmsdao;  2   3 import java.sql.Connection;  4 import java.sql.DriverManager;  5 import java.sql.PreparedStatement;  6 import java.sql.ResultSet;  7 import java.sql.SQLException;  8   9 public class BaseDAO { 10     // 驅動類全名（包名.類名） 11     private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; 12     // 串連的URL 13     private static final String URL = "jdbc:sqlserver://localhost\\sqlexpress:1433;DatabaseName=terminalEquipment"; 14     // 登入SQLserver使用者名稱和密碼 15     private static final String USERNAME = "sa"; 16     private static final String PWD = "ghp418512"; 17  18     // 資料庫物件聲明 19     private static PreparedStatement pst = null; 20     private static ResultSet rs = null; 21     private static Connection con = null; 22  23     /** 24      * 載入驅動 25      */ 26     static { 27         try { 28             Class.forName(DRIVER); 29         } catch (ClassNotFoundException e) { 30             // TODO Auto-generated catch block 31             e.printStackTrace(); 32         } 33     } 34  35     /** 36      * 建立串連 37      */ 38     public static Connection getCon() { 39         try { 40             con = DriverManager.getConnection(URL, USERNAME, PWD); 41             return con; 42         } catch (SQLException e) { 43             // TODO Auto-generated catch block 44             e.printStackTrace(); 45         } 46         return null; 47     } 48  49     /** 50      * 執行查詢 51      *  52      * @param sql 53      *            執行的參數化SQL語句 54      * @param params 55      *            object數組，封裝所有SQL語句參數 56      * @return ResultSet 返回執行後的結果集 57      */ 58     public static ResultSet execQuery(String sql, Object[] params) { 59         try { 60             getCon(); 61             pst = con.prepareStatement(sql); 62  63             setPrepareStatementParams(params); 64             rs = pst.executeQuery(); 65         } catch (SQLException e) { 66             // TODO Auto-generated catch block 67             e.printStackTrace(); 68         } 69         return rs; 70  71     } 72  73     /** 74      * 執行增刪改SQL操作方法 75      *  76      * @param sql 77      *            執行的參數化SQL語句 78      * @param params 79      *            object數組，封裝所有SQL語句參數 80      * @return 受影響的行數，-1表示出現異常 81      */ 82     public int execUpdate(String sql, Object[] params) { 83  84         getCon(); 85         try { 86             pst = con.prepareStatement(sql); 87  88             setPrepareStatementParams(params); 89  90             int affectRows = pst.executeUpdate(); 91             return affectRows; 92         } catch (SQLException e) { 93             // TODO Auto-generated catch block 94             e.printStackTrace(); 95         } finally { 96             free(rs, pst, con); 97         } 98         return -1; 99 100     }101 102     /**103      * 為PrepareStatement設定參數104      * 105      * @param params106      *            參數數組107      * @throws SQLException108      */109     private static void setPrepareStatementParams(Object[] params)110             throws SQLException {111         if (params != null) {112             for (int i = 0; i < params.length; i++) {113                 pst.setObject(i + 1, params[i]);114             }115         }116     }117 118     /**119      * 關閉Connection,PrepareStatement,Result120      * 121      * @param rs122      * @param pst123      * @param con124      */125     public static void free(ResultSet rs, PreparedStatement pst, Connection con) {126         try {127             if (rs != null) {128                 rs.close();129             }130             if (pst != null) {131                 pst.close();132             }133             if (con != null) {134                 con.close();135             }136         } catch (SQLException e) {137             e.printStackTrace();138         }139 140     }141 }

 

JDBC串連SQLServer資料庫（簡易代碼）