JAVA连接、操作数据库的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 }
赞 (0)