/*** 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