Basicdatasource Encapsulation Operations Database tool with Threadlocal, Apache dbutils queryrunner and DBCP2 database connection pool

/*** Database Operation helper class*/ Public classDatabasehelper {Private Static FinalLogger Logger = Loggerfactory.getlogger (databasehelper.class); Private Static FinalThreadlocal<connection>Connection_holder; Private Static FinalQueryrunner Query_runner; Private Static FinalBasicdatasource data_source; Static{Connection_holder=NewThreadlocal<connection>(); Query_runner=NewQueryrunner (); Data_source=NewBasicdatasource (); Properties conf= Propsutil.loadprops ("Config.properties"); String Driver= Conf.getproperty ("Jdbc.driver"); String URL= Conf.getproperty ("Jdbc.url"); String username= Conf.getproperty ("Jdbc.username"); String Password= Conf.getproperty ("Jdbc.password");        Data_source.setdriverclassname (driver);        Data_source.seturl (URL);        Data_source.setusername (username);    Data_source.setpassword (password); }    /*** Get database connection*/     Public StaticConnection getconnection () {Connection Connection=Connection_holder.get (); if(Connection = =NULL) {            Try{Connection=data_source.getconnection (); } Catch(SQLException e) {logger.error ("Get Connection Failure", E); Throw NewRuntimeException (e); } finally{connection_holder.set (CONNECTION); }        }        returnconnection; }    /*** Query Entity list*/     Public Static<T> list<t> queryentitylist (class<t>entityclass, String sql, Object ... params) {List<T>entitylist; Try{Connection conn=getconnection (); Entitylist= Query_runner.query (conn, SQL,NewBeanlisthandler<t>(Entityclass), params); } Catch(SQLException e) {logger.error ("Query Entity list failure", E); Throw NewRuntimeException (e); }        returnentitylist; }    /*** Query Entity*/     Public Static<T> T queryentity (class<t>entityclass, String sql, Object ... params)        {T entity; Try{Connection Connection=getconnection (); Entity= Query_runner.query (connection, SQL,NewBeanhandler<t>(Entityclass), params); } Catch(SQLException e) {logger.error ("Query entity Failure", E); Throw NewRuntimeException (e); }        returnentity; }    /*** Execute Query statement*/     Public StaticList<map<string, object>>executeQuery (String sql, Object ... params) {List<map<string, object>>result; Try{Connection Connection=getconnection (); Result= Query_runner.query (connection, SQL,NewMaplisthandler (), params); } Catch(Exception e) {logger.error ("Execute Query Failure", E); Throw NewRuntimeException (e); }        returnresult; }    /*** Execute UPDATE statements (UPDATE, INSERT, delete)*/     Public Static intexecuteupdate (String sql, Object ... params) {introws = 0; Try{Connection Connection=getconnection (); Rows=query_runner.update (connection, SQL, params); } Catch(SQLException e) {logger.error ("Execute update Failure", E); Throw NewRuntimeException (e); }        returnrows; }    /*** Insert Entity*/     Public Static<T>BooleanInsertentity (class<t> entityclass, map<string, object>FieldMap) {        if(Collectionutil.isempty (FieldMap)) {Logger.error ("Can not insert Entity:fieldmap is empty"); return false; } String SQL= "INSERT INTO" +Gettablename (Entityclass); StringBuilder Columns=NewStringBuilder ("("); StringBuilder Values=NewStringBuilder ("(");  for(String fieldName:fieldMap.keySet ()) {columns.append (fieldName). Append (", "); Values.append ("?, "); } columns.replace (Columns.lastindexof (","), Columns.length (), ")"); Values.replace (Values.lastindexof (","), Values.length (), ")"); SQL+ = columns + "values" +values; Object[] Params=fieldmap.values (). ToArray (); returnexecuteupdate (sql, params) = = 1; }    /*** Update Entity*/     Public Static<T>BooleanUpdateentity (class<t> Entityclass,LongID, map<string, object>FieldMap) {        if(Collectionutil.isempty (FieldMap)) {Logger.error ("Can not update Entity:fieldmap is empty"); return false; } String SQL= "Update" + gettablename (entityclass) + "set"; StringBuilder Columns=NewStringBuilder ();  for(String fieldName:fieldMap.keySet ()) {columns.append (fieldName). Append ("=?, "); } SQL+ = columns.substring (0, Columns.lastindexof (",")) + "where id=?"; List<Object> paramlist =NewArraylist<object>();        Paramlist.addall (Fieldmap.values ());        Paramlist.add (ID); Object[] Params=Paramlist.toarray (); returnexecuteupdate (sql, params) = = 1; }    /*** Delete Entity*/     Public Static<T>BooleanDeleteentity (class<t> Entityclass,LongID) {String SQL= "Delete from" + gettablename (entityclass) + "where id=?"; returnExecuteupdate (sql, id) = = 1; }    /*** Get table names by class name*/    Private StaticString Gettablename (class<?>Entityclass) {        returnEntityclass.getsimplename (); }    /*** Execute SQL file*/     Public Static voidexecutesqlfile (String filePath) {InputStream is=Thread.CurrentThread (). Getcontextclassloader (). getResourceAsStream (FilePath); BufferedReader Reader=NewBufferedReader (NewInputStreamReader (IS)); Try{String sql;  while((sql = Reader.readline ())! =NULL) {executeupdate (SQL); }        } Catch(IOException e) {logger.error ("Execute SQL File Failure", E); Throw NewRuntimeException (e); }    }}

Databasehelper

Related code Download

Places to look at

1, threadlocal<connection>

Specifying the storage data type in a generic type

2, database Connection object is not closed after creation, to Basicdatasource management

3, Java generic T and?

T is used to specify the parameter type or return type, and the generic method is labeled before the method name <T>

For generic type designations, such as CLASS<?>, any type of class;list<?> List, any type of collection

Basicdatasource Encapsulation Operations Database tool with Threadlocal, Apache dbutils queryrunner and DBCP2 database connection pool