工厂模式的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 Exception111 */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 Exception131 */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 }