c3po mysql_C3PO数据库连接池

1 importjava.beans.BeanInfo;2 importjava.beans.Introspector;3 importjava.beans.PropertyDescriptor;4 importjava.sql.Connection;5 importjava.sql.PreparedStatement;6 importjava.sql.ResultSet;7 importjava.sql.ResultSetMetaData;8 importjava.sql.SQLException;9 importjava.sql.Statement;10 importjava.util.ArrayList;11 importjava.util.List;12

13 importjavax.sql.DataSource;14

15 importcom.mchange.v2.c3p0.ComboPooledDataSource;16

17 public classJDBCUtils {18

19 //使用C3P0数据库连接池管理数据库连接

20 private static DataSource dataSource = newComboPooledDataSource();21

22 //获取连接

23 public static Connection getConnection() throwsSQLException {24 returndataSource.getConnection();25 }26

27 //关闭连接

28 public static voidclose(Connection conn) {29 if (conn != null) {30 try{31 conn.close();32 } catch(SQLException e) {33 }34 }35 }36

37 //关闭statement

38 public static voidclose(Statement stmt) {39 if (stmt != null) {40 try{41 stmt.close();42 } catch(SQLException e) {43 }44 }45 }46

47 //关闭结果集

48 public static voidclose(ResultSet rs) {49 if (rs != null) {50 try{51 rs.close();52 } catch(SQLException e) {53 }54 }55 }56

57 //关闭结果集、statement、连接

58 public static voidcloseAll(ResultSet rs) {59 if (rs == null) {60 return;61 }62 try{63 close(rs);64 //如果先关掉statement再关conn，会抛出“You cannot operate on a closed65 //Statement!”的异常，导致conn没有关闭

66 close(rs.getStatement().getConnection());67 close(rs.getStatement());68

69 } catch(SQLException e) {70 e.printStackTrace();71 }72 }73

74 //关闭结果集、statement

75 public static voidcloseResultSetAndStatement(ResultSet rs) {76 if (rs == null) {77 return;78 }79 try{80 close(rs);81 close(rs.getStatement());82 } catch(SQLException e) {83

84 }85 }86

87 //执行insert、update、delete等sql语句

88 public static int executeUpdate(String sql, Object... parameters) throwsSQLException {89 Connection conn = null;90 try{91 conn =getConnection();92 returnexecuteUpdate(conn, sql, parameters);93 } finally{94 close(conn);95 }96 }97

98 //执行insert、update、delete等sql语句

99 public static int executeUpdate(Connection conn, String sql, Object... parameters) throws

100 SQLException {101 PreparedStatement ps = null;102 try{103 ps =conn.prepareStatement(sql);104 for (int i = 0; i < parameters.length; i++) {105 ps.setObject(i + 1, parameters[i]);106 }107 returnps.executeUpdate();108 } finally{109 close(ps);110 }111 }112

113 //执行查询

114 public static ResultSet executeQuery(String sql, Object... parameters) throwsSQLException {115 Connection conn = null;116 try{117 conn =getConnection();118 returnexecuteQuery(conn, sql, parameters);119 } catch(SQLException ex) {120 close(conn);121 throwex;122 }123 }124

125 //执行查询

126 public static ResultSet executeQuery(Connection conn, String sql, Object... parameters) throws

127 SQLException {128 PreparedStatement ps = null;129 try{130 ResultSet rs = null;131 ps =conn.prepareStatement(sql);132 for (int i = 0; i < parameters.length; i++) {133 ps.setObject(i + 1, parameters[i]);134 }135 rs =ps.executeQuery();136 returnrs;137 } catch(SQLException ex) {138 close(ps);139 throwex;140 }141 }142

143 //回滚

144 public static voidrollback(Connection conn) {145 try{146 conn.rollback();147 } catch(SQLException e) {148

149 }150 }151 }