Simple and high-reuse jdbcutils tool encapsulation implementation class and simple connection pool implementation

It is very cumbersome to import more than n dependent packages to a small project. I just want to quickly develop small demand projects. At this time, I really don't want to use the framework. I can only write a jdbcutils file by myself, although there are a lot of apache and Alibaba on the internet, I don't feel very comfortable after using it. I spent some time writing a new one.

1. We need to write a callback interface for converting the resultset set to bean. Anyone who has used spring jdbc knows this.

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. Let's take a look at the basic jdbc operation interface.

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 or delete function ** @ param SQL * @ param params * @ return number of change records * @ throws SQLExce Ption */public abstract int execute (String SQL, Object [] params) throws SQLException; /*** update or delete function ** @ param SQL * @ return change record count * @ throws SQLException */public abstract int execute (String SQL) throws SQLException; /*** batch update or delete function ** @ param SQL * @ param params * @ return change record count * @ throws SQLException */public abstract int executeBatch (String SQL, list <Object []> params) throws SQLEx Ception; /*** batch update or delete function ** @ param SQL * @ param params * @ return change record count * @ throws SQLException */public abstract int executeBatch (String SQL) throws SQLException;/*** select function ** @ param SQL * @ param params * @ return native ResultSet Data Set * @ throws SQLException */public abstract ResultSet queryForResultSet (String SQL, object [] params) throws SQLException;/***** select function ** @ param SQL * @ return Native ResultSet Data Set * @ throws SQLException */public abstract ResultSet queryForResultSet (String SQL) throws SQLException; /*** select function ** @ param SQL * @ param params * @ return List <?> Data Set * @ throws SQLException */public abstract List <?> QueryForBean (String SQL, Object [] params, RowMapper <?> Mapper) throws SQLException;/*** select function ** @ param SQL * @ param params * @ return List <?> Data Set * @ throws SQLException */public abstract List <?> QueryForBean (String SQL, RowMapper <?> Mapper) throws SQLException;/*** select function ** @ param SQL * @ param params * @ return List <Map <String, object> Data Set * @ throws SQLException */public abstract List <Map <String, Object> queryForMap (String SQL, Object [] params) throws SQLException; /*** select function ** @ param SQL * @ param params * @ return List <Map <String, object> Data Set * @ throws SQLException */public abstract List <Map <String, Object> queryForMap (String SQL) throws SQLException; /*** select function ** @ param SQL * @ return counts the number of records in a single column * @ throws SQLException */public abstract int queryForInt (String SQL, Object [] params) throws SQLException; /*** select function ** @ param SQL * @ return counts the number of records in a single column * @ throws SQLException */public abstract int queryForInt (String SQL) throws SQLException; /*** release the Connection resource ** @ param x */public abstract void free (Connection x ); /*** release the Statement resource ** @ param x */public abstract void free (Statement x ); /*** release the PreparedStatement resource ** @ param x */public abstract void free (PreparedStatement x ); /*** release the ResultSet resource ** @ param x */public abstract void free (ResultSet x ); /*** set the data source ** @ param dataSource */public abstract void setDataSource (DataSource dataSource ); /*** obtain the database link ** @ return Connection */public abstract Connection getConnection (); /*** obtain the database link ** @ param autoCommit * @ return Connection */public Connection getConnection (boolean autoCommit );}

3. Implement the methods in our interface

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. B Ean. rowMapper;/*** simple JDBC implementation class ** @ author shadow **/public class SimpleJdbc implements JdbcOperation {private static final boolean AUTO_COMMIT = true; private DataSource; public SimpleJdbc () {} public SimpleJdbc (DataSource dataSource) {this. dataSource = dataSource;} public Connection getConnection () {return getConnection (AUTO_COMMIT);} public Connection getConnection (boolean autoCommit) {tr Y {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.exe cuteUpdate (); conn. commit ();} catch (Exception e) {co Nn. 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); retur N stmt.exe cuteQuery ();} 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 = nu Ll; 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 ;}@ O Verridepublic 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);} ret Urn 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) Then stmt.executebatch(~~stmt.clearbatch(~~~~stmt.exe cuteBatch (); 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.exe cuteQuery ();}}

4. Then implement a connection pool. If you do not like dbcp or c3p0, the items that need to be dependent on the package will become heavier.

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. export list; import javax. SQL. dataSource;/*** simple connection pool implementation class ** @ author shadow **/public class SimpleDataSource implements DataSource {Private int poolSize = 5; private shortlist <Connection> pool = new shortlist <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 ("initialization pool size failed:" + poolSize) ;}for (int I = 0; I <poolSize; I ++) {Connection con = DriverManager. getConnection (url, name, pwd); con = ConnectionProxy. getProxy (con, pool); // gets the pool of the proxy object. add (con); // add the object to be proxy} catch (Exception e) {throw new RuntimeException (e. getMessage (), e) ;}}/** get pool size */public int getPoolSize () {return poolSize ;}/** Does not support log operations */public PrintWriter getLogWriter () throws SQLException {throw new RuntimeException ("Unsupport Operation. ");} public void setLogWriter (PrintWriter out) throws SQLException {throw new RuntimeException (" Unsupport operation. ");}/** timeout operation not supported */public void setLoginTimeout (int seconds) throws SQLException {throw new RuntimeException (" Unsupport operation. ");} public int getLoginTimeout () throw S 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);}/** get a Connection object from the pool and use synchronization and thread scheduling */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 ("the user name and password cannot be received ");} /** implement dynamic proxy for 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, writable list <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. optional Y ();} return null;} else {return method. invoke (obj, args );}}}}

5. we started to test what we just wrote. simplejdbc is mainly provided to him by datasource. Others are ignored. I will not provide the user object in the test, which contains two attributes, id and username, and then generate the set and get methods. Of course, this demonstrates converting resultset into bean. Of course, you can also directly query the map set using queryForMap, I think the method provided by the interface is enough

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());}}