Java to obtain various database query results _ MySQL

Java requires a piece of data to obtain various database query results. sometimes it requires a result set. However, sometimes the returned result is a statistical value, the query results can be obtained through the transformation of ResultSet and ResultSetMetaData. because it is difficult to manage data links in the connection pool, a tool class is appreciated,

Package com. sky. connect; import java. lang. reflect. invocationTargetException; import java. SQL. resultSet; import java. SQL. SQLException; import java. util. arrayList; import java. util. hashMap; import java. util. list; import java. util. map; import org. apache. commons. beanutils. beanUtils; import com. mysql. jdbc. connection; import com. mysql. jdbc. preparedStatement; import com. mysql. jdbc. resultSetMetaData; /*** DAO design mode ** @ author Pan Zhuo Wen **/public class DAO {/***** UPDATE Database operations ** @ param SQL * @ param args */public void update (String SQL, object... args) {Connection connection = null; PreparedStatement preparedStatement = null; try {connection = JDBCTools. getConnection (); preparedStatement = (PreparedStatement) connection. prepareStatement (SQL); for (int I = 0; I <args. length; I ++) {preparedStatement. setObject (I + 1, argspolici####preparedstatement.exe cuteUpdate ();} catch (Exception e) {e. printStackTrace ();} finally {JDBCTools. release (preparedStatement, connection) ;}}/*** common query method, returns a record ** @ param clazz * @ param SQL * @ param args * @ return */public
 
  
T get (Class
  
   
Clazz, String SQL, Object... args) {T entity = null; Connection connection = null; PreparedStatement preparedStatement = null; ResultSet result = null; try {connection = JDBCTools. getConnection (); preparedStatement = (PreparedStatement) connection. prepareStatement (SQL); for (int I = 0; I <args. length; I ++) {preparedStatement. setObject (I + 1, args [I]);} result = preparedStatement.exe cuteQuery (); Map
   
    
Map = new HashMap
    
     
(); ResultSetMetaData rsmd = (ResultSetMetaData) result. getMetaData (); if (result. next () {for (int I = 0; I <rsmd. getColumnCount (); I ++) {String columnLabel = rsmd. getColumnLabel (I + 1); Object value = result. getObject (I + 1); map. put (columnLabel, value) ;}} if (map. size ()> 0) {entity = clazz. newInstance (); for (Map. entry
     
      
Entry: map. entrySet () {String filedName = entry. getKey (); Object filedObject = entry. getValue (); BeanUtils. setProperty (entity, filedName, filedObject) ;}} catch (Exception e) {e. printStackTrace ();} finally {JDBCTools. release (result, preparedStatement, connection);} return entity;}/*** common query method, returns a result set ** @ param clazz * @ param SQL * @ param args * @ return */public
      
        List
       
         GetForList (Class
        
          Clazz, String SQL, Object... args) {List
         
           List = new ArrayList
          
            (); Connection connection = null; PreparedStatement preparedStatement = null; ResultSet result = null; try {connection = JDBCTools. getConnection (); preparedStatement = (PreparedStatement) connection. prepareStatement (SQL); for (int I = 0; I <args. length; I ++) {preparedStatement. setObject (I + 1, args [I]);} result = preparedStatement.exe cuteQuery (); List
           
             > Values = handleResultSetToMapList (result); list = transfterMapListToBeanList (clazz, values);} catch (Exception e) {e. printStackTrace ();} finally {JDBCTools. release (result, preparedStatement, connection);} return list ;} /***** @ param clazz * @ param values * @ return * @ throws InstantiationException * @ throws IllegalAccessException * @ throws InvocationTargetException */public
            
              List
             
               TransfterMapListToBeanList (Class
              
                Clazz, List
               
                 > Values) throws InstantiationException, IllegalAccessException, InvocationTargetException {List
                
                  Result = new ArrayList
                 
                   (); T bean = null; if (values. size ()> 0) {for (Map
                  
                    M: values) {bean = clazz. newInstance (); for (Map. Entry
                   
                     Entry: m. entrySet () {String propertyName = entry. getKey (); Object value = entry. getValue (); BeanUtils. setProperty (bean, propertyName, value);} // 13. put the Object into the list. result. add (bean) ;}} return result;}/***** @ param resultSet * @ return * @ throws SQLException */public List
                    
                      > HandleResultSetToMapList (ResultSet resultSet) throws SQLException {List
                     
                       > Values = new ArrayList
                      
                        > (); List
                       
                         ColumnLabels = getColumnLabels (resultSet); Map
                        
                          Map = null; while (resultSet. next () {map = new HashMap
                         
                           (); For (String columnLabel: columnLabels) {Object value = resultSet. getObject (columnLabel); map. put (columnLabel, value);} values. add (map);} return values;}/***** @ param resultSet * @ return * @ throws SQLException */private List
                          
                            GetColumnLabels (ResultSet resultSet) throws SQLException {List
                           
                             Labels = new ArrayList
                            
                              (); ResultSetMetaData rsmd = (ResultSetMetaData) resultSet. getMetaData (); for (int I = 0; I <rsmd. getColumnCount (); I ++) {labels. add (rsmd. getColumnLabel (I + 1);} return labels;}/*** common query method, return a value (possibly a statistical value) ** @ param SQL * @ param args * @ return */@ SuppressWarnings ("unchecked") public
                             
                               E getForValue (String SQL, Object... args) {Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try {connection = JDBCTools. getConnection (); preparedStatement = (PreparedStatement) connection. prepareStatement (SQL); for (int I = 0; I <args. length; I ++) {preparedStatement. setObject (I + 1, args [I]);} resultSet = preparedStatement.exe cuteQuery (); if (resultSet. next () {return (E) resultSet. getObject (1) ;}} catch (Exception e) {e. printStackTrace ();} finally {JDBCTools. release (resultSet, preparedStatement, connection);} return null ;}}
                             
                            
                           
                          
                         
                        
                       
                      
                     
                    
                   
                  
                 
                
               
              
             
            
           
          
         
        
       
      
     
    
   
  
 

Package com. sky. connect; import java. io. IOException; import java. io. inputStream; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; import java. util. properties; import com. mysql. jdbc. connection; import com. mysql. jdbc. driver; import com. mysql. jdbc. preparedStatement; import com. mysql. jdbc. statement;/*** JDBC Tool Version 1.0 ** @ author Pan Kong Wen **/public class JDBCTools {/*** PreparedStatement to update data ** @ param SQL * @ param args */public static void update (String SQL, Object... args) {Connection connection = null; PreparedStatement preparedStatement = null; try {connection = JDBCTools. getConnection (); preparedStatement = (PreparedStatement) connection. prepareStatement (SQL); for (int I = 0; I <args. length; I ++) {preparedStatement. setObject (I + 1, args [I]);} preparedStatemen T.exe cuteUpdate ();} catch (Exception e) {e. printStackTrace ();} finally {JDBCTools. release (preparedStatement, connection) ;}/ *** close the query result ** @ param rs * @ param statement * @ param conn */public static void release (ResultSet rs, statement statement, Connection conn) {if (rs! = Null) {try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (statement! = Null) {try {statement. close () ;}catch (Exception e2) {e2.printStackTrace () ;}} if (conn! = Null) {try {conn. close () ;}catch (Exception e2) {e2.printStackTrace () ;}}/ *** database update method ** @ param SQL */public void uodate (String SQL) {Connection connection = null; Statement statement = null; try {connection = JDBCTools. getConnection (); statement = (Statement) connection.createstatement({statement.exe cuteUpdate (SQL);} catch (Exception e) {e. printStackTrace ();} finally {JDBCTools. release (statement, Connection) ;}}/*** method for shutting down database connections ** @ param statement * @ param conn */public static void release (Statement statement, Connection conn) {if (statement! = Null) {try {statement. close () ;}catch (Exception e2) {e2.printStackTrace () ;}} if (conn! = Null) {try {conn. close () ;}catch (Exception e2) {e2.printStackTrace () ;}}/ *** write a common method to obtain any Database link, you do not need to modify the source program ** @ return * @ throws ClassNotFoundException * @ throws IllegalAccessException * @ throws InstantiationException * @ throws SQLException * @ throws IOException */public static Connection getConnection, classNotFoundException, SQLException, IOException {String driverClass = null; String jdbcUrl = null; String user = null; String password = null; // read the properties file InputStream in = JDBCTools. class. getClassLoader (). getResourceAsStream ("jdbc. properties "); Properties properties = new Properties (); properties. load (in); driverClass = properties. getProperty ("driver"); jdbcUrl = properties. getProperty ("url"); user = properties. getProperty ("user"); password = properties. getProperty ("password"); Class. forName (driverClass); Connection connection Connection = (Connection) DriverManager. getConnection (jdbcUrl, user, password); return connection ;}}