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, just to write a jdbcutils, despite the online there are very many Apache, 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.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. Start with a major JDBC interface, which is a good specification point

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 Change record number * @throws SQLException */public abstract int execute (String sql, object[)  params) throws sqlexception;/** * Update or delete function * * @param SQL * @return Change record number * @throws SQLException */public Abstract int execute (String sql) throws sqlexception;/** * Batch update or delete function * * @param SQL * @param params * @return Change record number * @t Hrows SQLException */public Abstract int executebatch (String sql, list<object[]> params) throws sqlexception;/** * Batch Handle update or DELETE function * * @param SQL * @param params * @return Change record number * @throws SQLException */public abstract int EXECUTEBATC H (String sql) throws sqlexception;/** * Select function * * @param SQL * @param params * @return Native ResultSet data collection * @throws SQLException */public abstract ResultSet Queryforresultset (String sql, object[] params) throws sqlexception;/** * Select function * * @param SQL * @return Native resultset data collection * @throws Sqlex Ception */public abstract ResultSet queryforresultset (String sql) throws sqlexception;/** * Select function * * @param SQL * @pa RAM params * @return list<?
> Data collection * @throws SQLException */public abstract list<?
> Queryforbean (String sql, object[] params, rowmapper<?> mapper) throws sqlexception;/** * Select function * * @param s QL * @param params * @return list<?
> Data collection * @throws SQLException */public abstract list<?> queryforbean (String sql, rowmapper<?> Mapper) throw s sqlexception;/** * Select function * * @param SQL * @param params * @return list<map<string, object>> data set * @throw s SQLException */public abstract list<map<string, object>> queryformap (String sql, object[] params) throws SQ lexception;/** * Select function * * @param SQL * @param params * @return list<map<string, object>> data set * @throws SQ Lexception */public 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[] p Arams) throws sqlexception;/** * Select function * * @param SQL * @return count single-column records * @throws SQLException */public abstract int Q Ueryforint (String sql) throws sqlexception;/** * Release Connection resource * * @param x */public abstract void Free (Connection x);/* * * FREE Statement Resources * * @param x */public abstract void Free (Statement x);/** * Release PreparedStatement resource * * @param x */public abstract void free (PREPAREDST Atement x);/** * Release ResultSet resource * * @param x */public abstract void Free (ResultSet x);/** * Set Data source * * @param dataSource * /public abstract void Setdatasource (DataSource DataSource);/** * Get Database link * * @return Connection */public abstract Connect Ion Getconnection ();/** * Get Database links * * @param autocommit * @return Connection */public Connection getconnection (boolean au Tocommit);}

3. Implement the methods inside 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.bean.rowmapper;/** * Simple JDBC Implementation class * * @author Shadow * */public class Simplejdbc implements Jdbcoperation {private static final Boolean auto_com MIT = 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.execute Update (); 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 = Createresultset (stmt); while (Rs.next ()) {R Eturn 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 = NE W 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[] {}, MA Pper);} @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<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<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 ()}} 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. 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.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;/** * Simple Connection Pool Implementation class * * @author Shadow * */public class Simpledatasource implements DataSource {private int poolsize = 5;private linkedlist<connection> poo L = new linkedlist<connection> ();p ublic simpledatasource (string driver, string URL, string name, string pwd) {This (d River, 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);//Get Proxied object Pool.add (con);//Add Proxied Object}} catch (Exception e) {T Hrow 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 () {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 username 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 to be able to, the other all ignore, test inside the user object I do not provide, inside is two attributes, ID and username, Then generate the set and get methods can, of course, this is to demonstrate resultset to bean, of course, can also 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 ());}}

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