JDBC Connection DB Instance

Package javacommon.base;import java.sql.connection;import java.sql.preparedstatement;import  java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import  Java.util.arraylist;import java.util.hashmap;import java.util.list;import java.util.map;public  class jdbctemplate {private connection conn = null;private connection  getconnection ()  {         if (conn == null)  {         conn = dbmanager.getconn ();          }         return  Conn;} Public jdbctemplate (Connection conn)  {this.conn = conn;} Public jdbctemplate ()  {conn = getconnection ();} Public connection getconn ()  {        return conn;    }    public void  Begintranscation ()  throws sqlexception {conn.setautocommit (false);} Public void commit ()  throws sqlexception {conn.commit ();} @SuppressWarnings ("Unchecked") Public list<map> query (string sql, object[]  params)  throws SQLException {PreparedStatement pStmt = null; resultset rset = null; List<map> list = new arraylist<map> ();try {pstmt =  Conn.preparestatement (SQL);for  (int i = 0; i < params.length; i++)  {pstmt.setobject (I + 1, params[i]);} Rset = pstmt.executequery (); Resultsetmetadata rsmd = rset.getmetadata (); String[] names = new string[rsmd.getcolumncount ()];for  (int i = 0;  i < names.length;  i++)  {names[i] = rsmd.getcolumnname (i + 1);} while  (Rset.next ())  {map row = new hashmap ();for  (int i = 0;  i < names.length; i++)  {row.put (Names[i], rset.getobject (i + 1));} List.add (row);}}  catch (sqlexception e)  {e.printstacktrace (); throw e;}  finally {if  (Rset != null)  {try {rset.close ();}  catch (sqlexception e)  {}}if  (pstmt != null)  {try {pstmt.close ();}  catch (Sqlexception e)  {}}}return list;} Public int insert (String sql, object[] params)  throws sqlexception { Return executeupdate (sql, params);} Public int executeupdate (String sql, object[] params)  throws SQLException  {PreparedStatement pStmt = null; resultset rset = null;int result = 0;try {pstmt = conn.preparestatement (SQL);for  (int i = 0; i  < params.length; i++)  {pstmt.setobject (I + 1, params[i]);} Result = pstmt.executeupdate ();}  catch (sqlexception e)  {e.printstacktrace (); throw e;}  finally {if  (Pstmt != null)  {try {pstmt.close ();}  catch (Sqlexception e)  {}}}return result;} Public void close ()  {if  (Conn != null)  {dbmanager.closeconn (conn);}}

Package javacommon.base;import java.sql.connection;import java.sql.databasemetadata;import  java.sql.SQLException;import java.util.Properties;import javax.sql.DataSource;import  org.apache.commons.dbcp.basicdatasource;import org.apache.commons.logging.log;import  org.apache.commons.logging.logfactory;/** *  database connection Pool management classes  *  @author  sn11631 * * /public class dbmanager {private static final log log =  Logfactory.getlog (dbmanager.class);p rivate static datasource datasource;static {properties  properties = propertieshandler.readpropertiesfile ("/opt/hr/hr-info-sync.properites");try  {Basicdatasource basicdatasource = new basicdatasource ();if  (null !=  Properties.getproperty ("datasource.accesstounderlyingconnectionallowed"))  { Basicdatasource.setaccesstounderlyingconnectionallowed (Boolean.valueof (PropertiEs.getproperty ("datasource.accesstounderlyingconnectionallowed")));}  if  (Null !=// properties.getproperty ("Datasource.connectioninitsqls"))// { Basicdatasource.setconnectioninitsqls (Properties.getproperty ("Datasource.connectioninitsqls"));} if  (Null != properties.getproperty ("Datasource.connectionproperties"))  { Basicdatasource.setconnectionproperties (Properties.getproperty ("datasource.connectionproperties"));} if  (Null != properties.getproperty ("Datasource.defaultautocommit"))  { Basicdatasource.setdefaultautocommit (Boolean.valueof (Properties.getproperty ("Datasource.defaultautocommit"));} if  (Null != properties.getproperty ("Datasource.defaultcatalog"))  { Basicdatasource.setdefaultcatalog (Properties.getproperty ("Datasource.defaultcatalog"));} if  (Null != properties.getproperty ("Datasource.defaultreadonly"))  { Basicdatasource.setdefaultreadonly (Boolean.valueof (Properties.getproperty ("DatasoUrce.defaultreadonly ")));} if  (Null != properties.getproperty ("Datasource.defaulttransactionisolation"))  { Basicdatasource.setdefaulttransactionisolation (Integer.valueof (Properties.getproperty (" Datasource.defaulttransactionisolation ")));}  if  (Null !=// properties.getproperty ("Datasource.driverclassloader"))// { Basicdatasource.setdriverclassloader (Properties.getproperty ("Datasource.driverclassloader"));} if  (Null != properties.getproperty ("Datasource.driverclassname"))  { Basicdatasource.setdriverclassname (Properties.getproperty ("Datasource.driverclassname"));} if  (Null != properties.getproperty ("Datasource.initialsize"))  { Basicdatasource.setinitialsize (Integer.valueof (Properties.getproperty ("datasource.initialsize"));} if  (Null != properties.getproperty ("datasource.logabandoned"))  { Basicdatasource.setlogabandoned (Boolean.valueof (Properties.getproperty ("datasource.logabandoned"));} If&nbSP; (Null != properties.getproperty ("Datasource.logintimeout"))  {basicdatasource.setlogintimeout (Integer.valueof (Properties.getproperty ("datasource.logintimeout"));}  if  (Null != properties.getproperty ("Datasource.logwriter"))// { Basicdatasource.setlogwriter (Properties.getproperty ("Datasource.logwriter"));} if  (Null != properties.getproperty ("datasource.maxactive"))  {basicdatasource.setmaxactive ( Integer.valueof (Properties.getproperty ("datasource.maxactive"));} if  (Null != properties.getproperty ("Datasource.maxidle"))  {basicdatasource.setmaxidle ( Integer.valueof (Properties.getproperty ("Datasource.maxidle"));} if  (Null != properties.getproperty ("datasource.maxopenpreparedstatements"))  { Basicdatasource.setmaxopenpreparedstatements (Integer.valueof (Properties.getproperty (" Datasource.maxopenpreparedstatements ")));} if  (Null != properties.getproperty ("datasource.maxwait"))  {baSicdatasource.setmaxwait (Long.valueof (Properties.getproperty ("datasource.maxwait"));} if  (Null != properties.getproperty ("Datasource.minevictableidletimemillis"))  { Basicdatasource.setminevictableidletimemillis (Long.valueof (Properties.getproperty (" Datasource.minevictableidletimemillis ")));} if  (Null != properties.getproperty ("Datasource.minidle"))  {basicdatasource.setminidle ( Integer.valueof (Properties.getproperty ("Datasource.minidle"));} if  (Null != properties.getproperty ("Datasource.numtestsperevictionrun"))  { Basicdatasource.setnumtestsperevictionrun (Integer.valueof (Properties.getproperty (" Datasource.numtestsperevictionrun ")));} if  (Null != properties.getproperty ("Datasource.password"))  {basicdatasource.setpassword ( Properties.getproperty ("Datasource.password"));} if  (Null != properties.getproperty ("datasource.poolpreparedstatements"))  { Basicdatasource.setpoolpreparedstatements (Boolean.valueOf (Properties.getproperty ("datasource.poolpreparedstatements")));} if  (Null != properties.getproperty ("datasource.removeabandoned"))  { Basicdatasource.setremoveabandoned (Boolean.valueof (Properties.getproperty ("datasource.removeabandoned"));} if  (Null != properties.getproperty ("Datasource.removeabandonedtimeout"))  { Basicdatasource.setremoveabandonedtimeout (Integer.valueof (Properties.getproperty (" Datasource.removeabandonedtimeout ")));} if  (Null != properties.getproperty ("Datasource.testonborrow"))  { Basicdatasource.settestonborrow (Boolean.valueof (Properties.getproperty ("Datasource.testonborrow"));} if  (Null != properties.getproperty ("Datasource.testonreturn"))  { Basicdatasource.settestonreturn (Boolean.valueof (Properties.getproperty ("Datasource.testonreturn"));} if  (Null != properties.getproperty ("Datasource.testwhileidle"))  { Basicdatasource.settestwhileidle (Boolean.valueof (Properties.getpropertY ("Datasource.testwhileidle")));} if  (Null != properties.getproperty ("Datasource.timebetweenevictionrunsmillis"))  { Basicdatasource.settimebetweenevictionrunsmillis (Long.valueof (Properties.getproperty (" Datasource.timebetweenevictionrunsmillis ")));} if  (Null != properties.getproperty ("Datasource.url"))  {basicdatasource.seturl ( Properties.getproperty ("Datasource.url"));} if  (Null != properties.getproperty ("Datasource.username"))  {basicdatasource.setusername ( Properties.getproperty ("Datasource.username"));} if  (Null != properties.getproperty ("Datasource.validationquery"))  { Basicdatasource.setvalidationquery (Properties.getproperty ("Datasource.validationquery"));} if  (Null != properties.getproperty ("Datasource.validationquerytimeout"))  { Basicdatasource.setvalidationquerytimeout (Integer.valueof (Properties.getproperty (" Datasource.validationquerytimeout ")));} datasource = basicdatasource; ConnecTion conn = getconn ();D atabasemetadata mdm = conn.getmetadata (); Log.info ("connected to "  + mdm.getdatabaseproductname ()  +  " " +  Mdm.getdatabaseproductversion ());if  (Conn != null)  {conn.close ();}}  catch  (exception e)  {log.error ("Failed to initialize connection pool:"  + e);}} /**  *  get the link and remember to close   *  @see  {@link  dbmanager#closeconn (Connection)}   *  @return   */public static final connection getconn ()  { Connection conn = null;try {conn = datasource.getconnection ();}  catch  (sqlexception e)  {log.error ("Get Database Connection failed:"  + e);} Return conn;} /** *  closing connections  *  *  @param  conn *              need to close the connection  */public static void closeconn (Connection  conn)   {try {if  (conn != null && !conn.isclosed ())  {conn.setAutoCommit ( true); Conn.close ();}}  catch  (sqlexception e)  {log.error ("Failed to close database connection:"  + e);}}

package javacommon.base;import java.io.fileinputstream;import java.io.ioexception;import  Java.io.inputstream;import java.util.properties;/** * properties processing  *  @author   sn11631 * */public class propertieshandler {/** *  reading resource Files  *  @param  filename  file name  *  @return  Properties */public static Properties  Readpropertiesfile (String filename)   {      Properties  Properties = new properties ();      try       {          inputstream inputstream =  new fileinputstream (filename);           Properties.load (InputStream);           inputstream.close ();  //Closing the stream       }      catch  (ioexception e)        {        e.printstacktrace ();       }        return properties;}}

JDBC Connection DB Instance