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 }