簡易高重用的jdbcutils工具封裝實作類別以及簡易串連池實現

     由於現在發現做個小項目都是匯入n多的依賴包,很煩瑣,只想快點開發完一個個的小需求項目,這個時候真心不想用架構,只能自己寫個jdbcutils,雖然網上有很多有apache的,阿里的,但是感覺用過後都不怎麼順手,然後自己花了點時間寫個新的,不喜勿噴

1.我們要寫個resultset集合轉成bean的回調介面,這個用過spring jdbc的人都知道這玩意

package org.simple.mvc.jdbc.bean;import java.sql.ResultSet;import java.sql.SQLException;public interface RowMapper<T> {public abstract T mapRow(ResultSet rs) throws SQLException;}

2.先來個基本的jdbc操作介面吧,這樣好規範點

package org.simple.mvc.jdbc;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.simple.mvc.jdbc.bean.RowMapper;public interface JdbcOperation {/** * update或delete功能 *  * @param sql * @param params * @return 變更記錄數 * @throws SQLException */public abstract int execute(String sql, Object[] params) throws SQLException;/** * update或delete功能 *  * @param sql * @return 變更記錄數 * @throws SQLException */public abstract int execute(String sql) throws SQLException;/** * 批處理update或delete功能 *  * @param sql * @param params * @return 變更記錄數 * @throws SQLException */public abstract int executeBatch(String sql, List<Object[]> params) throws SQLException;/** * 批處理update或delete功能 *  * @param sql * @param params * @return 變更記錄數 * @throws SQLException */public abstract int executeBatch(String sql) throws SQLException;/** * select功能 *  * @param sql * @param params * @return 原生ResultSet資料集合 * @throws SQLException */public abstract ResultSet queryForResultSet(String sql, Object[] params) throws SQLException;/** * select功能 *  * @param sql * @return 原生ResultSet資料集合 * @throws SQLException */public abstract ResultSet queryForResultSet(String sql) throws SQLException;/** * select功能 *  * @param sql * @param params * @return List<?>資料集合 * @throws SQLException */public abstract List<?> queryForBean(String sql, Object[] params, RowMapper<?> mapper) throws SQLException;/** * select功能 *  * @param sql * @param params * @return List<?>資料集合 * @throws SQLException */public abstract List<?> queryForBean(String sql, RowMapper<?> mapper) throws SQLException;/** * select功能 *  * @param sql * @param params * @return List<Map<String, Object>>資料集合 * @throws SQLException */public abstract List<Map<String, Object>> queryForMap(String sql, Object[] params) throws SQLException;/** * select功能 *  * @param sql * @param params * @return List<Map<String, Object>>資料集合 * @throws SQLException */public abstract List<Map<String, Object>> queryForMap(String sql) throws SQLException;/** * select功能 *  * @param sql * @return 統計單列記錄數 * @throws SQLException */public abstract int queryForInt(String sql, Object[] params) throws SQLException;/** * select功能 *  * @param sql * @return 統計單列記錄數 * @throws SQLException */public abstract int queryForInt(String sql) throws SQLException;/** * 釋放Connection資源 *  * @param x */public abstract void free(Connection x);/** * 釋放Statement資源 *  * @param x */public abstract void free(Statement x);/** * 釋放PreparedStatement資源 *  * @param x */public abstract void free(PreparedStatement x);/** * 釋放ResultSet資源 *  * @param x */public abstract void free(ResultSet x);/** * 設定資料來源 *  * @param dataSource */public abstract void setDataSource(DataSource dataSource);/** * 擷取資料庫連結 *  * @return Connection */public abstract Connection getConnection();/** * 擷取資料庫連結 *  * @param autoCommit * @return Connection */public Connection getConnection(boolean autoCommit);}

3.實現我們介面裡面的方法

package org.simple.mvc.jdbc.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.simple.mvc.jdbc.JdbcOperation;import org.simple.mvc.jdbc.bean.RowMapper;/** * 簡易JDBC實作類別 *  * @author shadow *  */public class SimpleJdbc implements JdbcOperation {private static final boolean AUTO_COMMIT = true;private DataSource dataSource;public SimpleJdbc() {}public SimpleJdbc(DataSource dataSource) {this.dataSource = dataSource;}public Connection getConnection() {return getConnection(AUTO_COMMIT);}public Connection getConnection(boolean autoCommit) {try {Connection conn = dataSource.getConnection();if (!autoCommit)conn.setAutoCommit(autoCommit);return conn;} catch (SQLException e) {e.printStackTrace();}return null;}@Overridepublic int execute(String sql, Object[] params) throws SQLException {Connection conn = getConnection(false);PreparedStatement stmt = null;int result = -1;try {stmt = createPreparedStatement(conn, sql, params);result = stmt.executeUpdate();conn.commit();} catch (Exception e) {conn.rollback();e.printStackTrace();} finally {free(stmt);free(conn);}return result;}@Overridepublic int execute(String sql) throws SQLException {return execute(sql, new Object[] {});}@Overridepublic ResultSet queryForResultSet(String sql, Object[] params) throws SQLException {Connection conn = getConnection();PreparedStatement stmt = null;try {stmt = createPreparedStatement(conn, sql, params);return stmt.executeQuery();} catch (Exception e) {e.printStackTrace();} finally {free(stmt);free(conn);}return null;}@Overridepublic ResultSet queryForResultSet(String sql) throws SQLException {return queryForResultSet(sql, new Object[] {});}@Overridepublic int queryForInt(String sql, Object[] params) throws SQLException {Connection conn = getConnection();PreparedStatement stmt = null;ResultSet rs = null;try {stmt = createPreparedStatement(conn, sql, params);rs = createResultSet(stmt);while (rs.next()) {return rs.getInt(1);}} catch (Exception e) {e.printStackTrace();} finally {free(rs);free(stmt);free(conn);}return 0;}@Overridepublic int queryForInt(String sql) throws SQLException {return queryForInt(sql, new Object[] {});}@Overridepublic List<?> queryForBean(String sql, Object[] params, RowMapper<?> mapper) throws SQLException {Connection conn = getConnection();PreparedStatement stmt = null;ResultSet rs = null;List<Object> list = null;try {stmt = createPreparedStatement(conn, sql, params);rs = createResultSet(stmt);list = new ArrayList<Object>();while (rs.next()) {list.add(mapper.mapRow(rs));}} catch (Exception e) {e.printStackTrace();} finally {free(rs);free(stmt);free(conn);}return list;}@Overridepublic List<?> queryForBean(String sql, RowMapper<?> mapper) throws SQLException {return queryForBean(sql, new Object[] {}, mapper);}@Overridepublic List<Map<String, Object>> queryForMap(String sql, Object[] params) throws SQLException {Connection conn = getConnection();PreparedStatement stmt = null;ResultSet rs = null;try {stmt = createPreparedStatement(conn, sql, params);rs = createResultSet(stmt);List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();Map<String, Object> map = null;ResultSetMetaData rsd = rs.getMetaData();int columnCount = rsd.getColumnCount();while (rs.next()) {map = new HashMap<String, Object>(columnCount);for (int i = 1; i < columnCount; i++) {map.put(rsd.getColumnName(i), rs.getObject(i));}list.add(map);}return list;} catch (Exception e) {e.printStackTrace();} finally {free(rs);free(stmt);free(conn);}return null;}@Overridepublic List<Map<String, Object>> queryForMap(String sql) throws SQLException {return queryForMap(sql, new Object[] {});}@Overridepublic int executeBatch(String sql, List<Object[]> params) throws SQLException {int result = 0;Connection conn = getConnection(false);PreparedStatement stmt = null;try {stmt = conn.prepareStatement(sql);for (int i = 0; i < params.size(); i++) {Object[] param = params.get(i);for (int j = 0; j < param.length; j++)stmt.setObject(j + 1, param[j]);stmt.addBatch();if (i % 1000 == 0) {stmt.executeBatch();stmt.clearBatch();}}stmt.executeBatch();conn.commit();result = params.size();} catch (Exception e) {conn.rollback();e.printStackTrace();} finally {free(stmt);free(conn);}return result;}@Overridepublic int executeBatch(String sql) throws SQLException {return executeBatch(sql, new ArrayList<Object[]>());}public DataSource getDataSource() {return dataSource;}public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;}@Overridepublic void free(Connection x) {if (x != null)try {x.close();} catch (SQLException e) {e.printStackTrace();}}@Overridepublic void free(Statement x) {if (x != null)try {x.close();} catch (SQLException e) {e.printStackTrace();}}@Overridepublic void free(PreparedStatement x) {if (x != null)try {x.close();} catch (SQLException e) {e.printStackTrace();}}@Overridepublic void free(ResultSet x) {if (x != null)try {x.close();} catch (SQLException e) {e.printStackTrace();}}public PreparedStatement createPreparedStatement(Connection conn, String sql, Object[] params) throws SQLException {PreparedStatement stmt = conn.prepareStatement(sql);for (int i = 0; i < params.length; i++)stmt.setObject(i + 1, params[i]);return stmt;}public ResultSet createResultSet(PreparedStatement stmt) throws SQLException {return stmt.executeQuery();}}

4.然後實現我們一個串連池吧,不喜歡dbcp,c3p0的話,那些要依賴包的導致項目重量上去了

package org.simple.mvc.jdbc.source;import java.io.PrintWriter;import java.lang.reflect.InvocationHandler;import java.lang.reflect.Method;import java.lang.reflect.Proxy;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.LinkedList;import javax.sql.DataSource;/** * 簡易串連池實作類別 *  * @author shadow *  */public class SimpleDataSource implements DataSource {private int poolSize = 5;private LinkedList<Connection> pool = new LinkedList<Connection>();public SimpleDataSource(String driver, String url, String name, String pwd) {this(driver, url, name, pwd, 5);}public SimpleDataSource(String driver, String url) {this(driver, url, "", "", 5);}public SimpleDataSource(String driver, String url, String name, String pwd, int poolSize) {try {Class.forName(driver);this.poolSize = poolSize;if (poolSize <= 0) {throw new RuntimeException("初始化池大小失敗: " + poolSize);}for (int i = 0; i < poolSize; i++) {Connection con = DriverManager.getConnection(url, name, pwd);con = ConnectionProxy.getProxy(con, pool);// 擷取被代理的對象pool.add(con);// 添加被代理的對象}} catch (Exception e) {throw new RuntimeException(e.getMessage(), e);}}/** 擷取池大小 */public int getPoolSize() {return poolSize;}/** 不支援日誌操作 */public PrintWriter getLogWriter() throws SQLException {throw new RuntimeException("Unsupport Operation.");}public void setLogWriter(PrintWriter out) throws SQLException {throw new RuntimeException("Unsupport operation.");}/** 不支援逾時操作 */public void setLoginTimeout(int seconds) throws SQLException {throw new RuntimeException("Unsupport operation.");}public int getLoginTimeout() throws SQLException {return 0;}@SuppressWarnings("unchecked")public <T> T unwrap(Class<T> iface) throws SQLException {return (T) this;}public boolean isWrapperFor(Class<?> iface) throws SQLException {return DataSource.class.equals(iface);}/** 從池中取一個連線物件,使用了同步和線程調度 */public Connection getConnection() throws SQLException {synchronized (pool) {if (pool.size() == 0) {try {pool.wait();} catch (InterruptedException e) {throw new RuntimeException(e.getMessage(), e);}return getConnection();} else {return pool.removeFirst();}}}public Connection getConnection(String username, String password) throws SQLException {throw new RuntimeException("不支援接收使用者名稱和密碼的操作");}/** 實現對Connection的動態代理 */static class ConnectionProxy implements InvocationHandler {private Object obj;private LinkedList<Connection> pool;private ConnectionProxy(Object obj, LinkedList<Connection> pool) {this.obj = obj;this.pool = pool;}public static Connection getProxy(Object o, LinkedList<Connection> pool) {Object proxed = Proxy.newProxyInstance(o.getClass().getClassLoader(), new Class[] { Connection.class },new ConnectionProxy(o, pool));return (Connection) proxed;}public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {if (method.getName().equals("close")) {synchronized (pool) {pool.add((Connection) proxy);pool.notify();}return null;} else {return method.invoke(obj, args);}}}}

5.開始測試下我們剛剛寫的玩意,simplejdbc主要是有datasource提供給他就可以了,其他的一律無視,測試裡面的user對象我就不提供,裡面就是兩個屬性,id和username,然後產生set和get方法即可,當然這個是示範resultset轉成bean的,當然也可以直接使用queryForMap直接查詢出map集合,我想介面提供的方法已經足夠使用了

public static void main(String[] args) throws SQLException {SimpleDataSource dataSource = new SimpleDataSource("org.sqlite.JDBC", "jdbc:sqlite:/E:p1010.db");SimpleJdbc jdbc = new SimpleJdbc(dataSource);List<User> list = (List<User>) jdbc.queryForBean("select * from t_user", new RowMapper<User>() {User user = null;@Overridepublic User mapRow(ResultSet rs) throws SQLException {user = new User();user.setId(rs.getInt("id"));user.setUsername(rs.getString("username"));return user;}});for (User user : list) {System.out.println(user.getId() + "---" + user.getUsername());}}