JDBC连接SQLServer数据库（简易代码）

  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 params
106      *            参数数组
107      * @throws SQLException
108      */
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,Result
120      * 
121      * @param rs
122      * @param pst
123      * @param con
124      */
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 }