工厂模式的DBHelper
1 import java.sql.Connection;
2 import java.sql.DriverManager;
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.Statement;
6
7 /**
8 * 数据库工具类，负责完成打开、关闭数据库，执行查询或更新
9 * @author MKing
10 *
11 */
12 public class DbHelper {
13 /**
14 * 数据库URL
15 */
16 private static final String URL = "jdbc:mysql://localhost:3306/bookstore";
17 /**
18 * 登录用户名
19 */
20 private static final String USER = "root";
21 /**
22 * 登录密码
23 */
24 private static final String PASSWORD = "12345";
25
26 private static Connection connection = null;
27 private static Statement statement = null;
28
29 private static DbHelper helper = null;
30
31 static {
32 try {
33 Class.forName("com.mysql.jdbc.Driver");
34 } catch (ClassNotFoundException e) {
35 e.printStackTrace();
36 }
37 }
38
39 private DbHelper() throws Exception {
40 connection = DriverManager.getConnection(URL, USER, PASSWORD);
41 statement = connection.createStatement();
42 }
43
44 /**
45 * 返回单例模式的数据库辅助对象
46 *
47 * @return
48 * @throws Exception
49 */
50 public static DbHelper getDbHelper() throws Exception {
51 if (helper == null || connection == null || connection.isClosed())
52 helper = new DbHelper();
53 return helper;
54 }
55
56 /**
57 * 执行查询
58 * @param sql 要执行的SQL语句
59 * @return 查询的结果集对象
60 * @throws Exception
61 */
62 public ResultSet executeQuery(String sql) throws Exception {
63 if (statement != null) {
64 return statement.executeQuery(sql);
65 }
66
67 throw new Exception("数据库未正常连接");
68 }
69
70 /**
71 * 执行查询
72 * @param sql 要执行的带参数的SQL语句
73 * @param args SQL语句中的参数值
74 * @return 查询的结果集对象
75 * @throws Exception
76 */
77 public ResultSet executeQuery(String sql, Object...args) throws Exception {
78 if (connection == null || connection.isClosed()) {
79 DbHelper.close();
80 throw new Exception("数据库未正常连接");
81 }
82 PreparedStatement ps = connection.prepareStatement(sql);
83 int index = 1;
84 for (Object arg : args) {
85 ps.setObject(index, arg);
86 index++;
87 }
88
89 return ps.executeQuery();
90 }
91
92 /**
93 * 执行更新
94 * @param sql 要执行的SQL语句
95 * @return 受影响的记录条数
96 * @throws Exception
97 */
98 public int executeUpdate(String sql) throws Exception {
99 if (statement != null) {
100 return statement.executeUpdate(sql);
101 }
102 throw new Exception("数据库未正常连接");
103 }
104
105 /**
106 * 执行更新
107 * @param sql 要执行的SQL语句
108 * @param args SQL语句中的参数
109 * @return 受影响的记录条数
110 * @throws Exception
111 */
112 public int executeUpdate(String sql, Object...args) throws Exception {
113 if (connection == null || connection.isClosed()) {
114 DbHelper.close();
115 throw new Exception("数据库未正常连接");
116 }
117 PreparedStatement ps = connection.prepareStatement(sql);
118 int index = 1;
119 for (Object arg : args) {
120 ps.setObject(index, arg);
121 index++;
122 }
123 return ps.executeUpdate();
124 }
125
126 /**
127 * 获取预编译的语句对象
128 * @param sql 预编译的语句
129 * @return 预编译的语句对象
130 * @throws Exception
131 */
132 public PreparedStatement prepareStatement(String sql) throws Exception {
133 return connection.prepareStatement(sql);
134 }
135
136 /**
137 * 关闭对象，同时将关闭连接
138 */
139 public static void close() {
140 try {
141 if (statement != null)
142 statement.close();
143 if (connection != null)
144 connection.close();
145 } catch (Exception e) {
146 e.printStackTrace();
147 } finally {
148 helper = null;
149 }
150 }
151 }