Simple and highly reusable jdbcutils tool encapsulation implementation class and simple connection pooling implementation

Because now found to do a small project is to import n more dependent packages, very cumbersome, just want to quickly develop a small demand for a project, this time really do not want to use the framework, can only write a jdbcutils, although there are a lot of Apache on the Internet, Ali, but the feeling is not easy after use, And then I spent a little time writing a new one, not like a squirt.

1. We're going to write a callback interface that resultset the collection to the bean, and this one who used spring JDBC knows this stuff.

Package Org.framework.mvc.jdbc.bean;import Java.sql.resultset;import Java.sql.sqlexception;public interface rowmapper<t> {public abstract T Maprow (ResultSet rs) throws SQLException;}

2. First, a basic JDBC interface, so good specification point

Package Org.framework.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.framework.mvc.jdbc.bean.rowmapper;public Interface jdbcoperation {/** * Update or delete function * * @param SQL * @param params * @return Change record number * @throws SQLException */public abstr Act 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 number * @throws SQLException */public abstract int executebatch (String sql, List<object[]&gt ; params) throws sqlexception;/** * Batch update or delete function * * @param SQL * @param params * @return Change record number * @throws Sqlexceptio n */public Abstract int executebatch (String sql) throws sqlexception;/** * Select functioncan * * @param SQL * @param params * @return Native ResultSet data collection * @throws SQLException */public abstract ResultSet queryforres  Ultset (String sql, object[] params) throws sqlexception;/** * Select function * * @param SQL * @return Native resultset data collection * @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 sq L, object[] params, rowmapper<?> mapper) throws sqlexception;/** * Select function * * @param SQL * @param params * @retur n list<?> Data set * @throws SQLException */public abstract list<?> queryforbean (String sql, rowmapper<?> ma Pper) throws sqlexception;/** * Select function * * @param SQL * @param params * @return list<map<string, object>> data Collection * @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 */pu  Blic abstract list<map<string, object>> queryForMap (String sql) throws sqlexception;/** * Select function * * @param SQL * @return Statistics single-column records * @throws SQLException */public abstract int queryforint (String sql, object[] params) throws Sqlexc eption;/** * Select function * * @param SQL * @return Statistics column records * @throws SQLException */public abstract int queryforint (String sq  L) throws sqlexception;/** * Release Connection Resources * * @param x */public abstract void Free (Connection x);/** * Release Statement resources * * @param x */public abstract void Free (Statement x);/** * Release PreparedStatement resource * * @param x */public abstract void Free (PreparedStatement x);/** * Release ResultSet resource * * @param x */public abstract void Free (ResultSet x);/** * Set Data source * * @param D Atasource */public abstract void Setdatasource (DataSource DataSource);/** * Get Database link * * @return Connection */public abstr Act Connection Getconnection ();/** * Get Database link * * @param autocommit * @return Connection */public Connection getconnection (Boolean autocommit);} 

3. Implement the methods inside our interface

Package Org.framework.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.framework.mvc.jdbc.jdbcoperation;import org.framework.mvc.jdbc.bean.rowmapper;/* * * Simple JDBC Implementation class * * @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); Stmt.execute (); 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 = Getconnec tion (); 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 = Stmt.executequery (); while (Rs.next ()) {RET Urn 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 = Stmt.executequery (); 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 = Stmt.executequery (); 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<s Tring, 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&Lt;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% 100 0 = = 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 ()}} Private 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;}}

4. Then implement one of our connection pool bar, do not like dbcp,c3p0 words, those to rely on the package caused the project weight up

Package Org.framework.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;/** * Simple connection Pool Implementation class * * @author Shadow * */public class Simpledatasource implements DataSource {private int poolsize = 5;//default to 5 PR Ivate linkedlist<connection> pool = new linkedlist<connection> ();p ublic 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); . poolsize = poolsize;if (poolsize <= 0) {throw new RuntimeException ("Failed to initialize Pool size:" + poolsize);} for (int i = 0; i < poolsize; i++) {Connection con = drivermanager.gEtconnection (URL, name, pwd); con = Connectionproxy.getproxy (con, pool);//Gets the Proxied object Pool.add (con);//Add the Proxied object}} catch ( Exception e) {throw new RuntimeException (E.getmessage (), E);}} /** Gets the 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."); /** does not support timeout operations */public void setlogintimeout (int seconds) throws SQLException {throw new RuntimeException ("Unsupport Operati On. "); 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);} /** takes a connection object from the pool, uses 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 (" Do not support receiving user name and password operations "); /** implementation of connection dynamic Proxy */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);p ool.notify (); return null;} else {return Method.invoke (obj, args);}}} 

5. Start the test we just wrote the thing, Simplejdbc mainly have datasource to provide him on it, the other all ignore, test inside the user object I do not provide, inside is two attributes, ID and username, and then generate set and get method can be , of course, this is a demo resultset to the bean, of course, you can directly use queryForMap directly query the map collection, I think the interface provides a method is enough to use the

public static void Main (string[] args) throws SQLException {Simpledatasource DataSource = new Simpledatasource ("Org.sqlit E.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 US ER = null, @Overridepublic user Maprow (ResultSet rs) throws SQLException {user = new user (); User.setid (Rs.getint ("id")); us Er.setusername (rs.getstring ("username")); return user;}}); for (User user:list) {System.out.println (User.getid () + "---" + user.getusername ());}}