JDBCUtil, a universal tool class for database operations, joins the efficient database connection pool and takes advantage of parameter binding to effectively prevent SQL injection

Reprint please indicate the source: blog.csdn.net/linglongxin… This post is from DylanAndroid’s blog.

JDBCUtil, a universal tool class for database operations, joins the efficient database connection pool and takes advantage of parameter binding to effectively prevent SQL injection

After learning MySQL and Oracle, then, for example, connect to these two databases in Java. In this lightweight utility class, database connection pooling is used to make database connections more efficient and preparedStatements are used to precompile SQL, preventing SQL injection problems.

Set up jdbC-mysql. properties under project New Config package and add the following configuration

JDBC. DriverClassName = com. Mysql, JDBC Driver. JDBC url = JDBC: mysql: / / 127.0.0.1:3306 / test? characterEncoding=utf8 jdbc.username=root jdbc.password=rootCopy the code

Prepare database connection pool objects: singletons

package util;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/** * Created by yuandl on 2016-12-16. */
public class DBConnectionPool {
    private static volatile DBConnectionPool dbConnection;
    private ComboPooledDataSource cpds;

    /** * gets the database connection */ when the constructor is initialized
    private DBConnectionPool(a) {
        try {
            /** Get database connection parameter values from the properties file **/
            Properties properties = new Properties();
            FileInputStream fileInputStream = new FileInputStream("src/config/jdbc-mysql.properties");
            properties.load(fileInputStream);
            /** Gets the value **/ in the properties file
            String driverClassName = properties.getProperty("jdbc.driverClassName");
            String url = properties.getProperty("jdbc.url");
            String username = properties.getProperty("jdbc.username");
            String password = properties.getProperty("jdbc.password");

            /** Database connection pool object **/
            cpds = new ComboPooledDataSource();

            /** Set the database connection driver **/
            cpds.setDriverClass(driverClassName);
            /** Set the database connection address **/
            cpds.setJdbcUrl(url);
            /** Set the database connection user name **/
            cpds.setUser(username);
            /** Set the database connection password **/
            cpds.setPassword(password);

            /** The number of connections created during initialization should be between minPoolSize and maxPoolSize. The default is 3 * * /
            cpds.setInitialPoolSize(3);
            /** The maximum number of connections retained in the connection pool. The default is 15 * * /
            cpds.setMaxPoolSize(10);
            /** When the pool runs out of connections, C3PO creates a new number of connections at once; * * /
            cpds.setAcquireIncrement(1);
            /** The number of seconds at which idle connections in all connection pools are checked. The default value is 0, indicating no checks. * * /
            cpds.setIdleConnectionTestPeriod(60);
            /** The maximum idle time. Connections exceeding the idle time will be discarded. Zero or negative data is never discarded. The default is 0; * * /
            cpds.setMaxIdleTime(3000);

            /** Please use it only when you need it because of high performance consumption. If set to true, each connection commit is validated. Methods such as idleConnectionTestPeriod or automaticTestTable are recommended to improve the performance of connection tests. Default: false**/
            cpds.setTestConnectionOnCheckout(true);

            /** If set to true, the connection is validated while being acquired. Default: false **/
            cpds.setTestConnectionOnCheckin(true);
            /** Defines the number of attempts to get a new connection from the database after it fails. Default is 30; * * /
            cpds.setAcquireRetryAttempts(30);
            /** The interval between two connections is 1000 ms **/ by default
            cpds.setAcquireRetryDelay(1000);
            /** Failure to get a connection will cause all threads waiting to get a connection to fail, but the data source remains valid and attempts to get a connection will continue the next time getConnection() is called. If set to true, the data source declares that it has been disconnected and permanently closed after an attempt to obtain a connection fails. The default is false * * /
            cpds.setBreakAfterAcquireFailure(true);
        } catch (IOException e) {
            e.printStackTrace();
        } catch(PropertyVetoException e) { e.printStackTrace(); }}/** * get the database connection object, singleton **@return* /
    public static DBConnectionPool getInstance(a) {
        if (dbConnection == null) {
            synchronized (DBConnectionPool.class) {
                if (dbConnection == null) {
                    dbConnection = newDBConnectionPool(); }}}return dbConnection;
    }

    /** * get database connection **@returnDatabase connection */
    public final synchronized Connection getConnection(a) throws SQLException {
        return cpds.getConnection();
    }

    The /** * Finalize () method is called on an object before the garbage collector removes it. * *@throws Throwable
     */
    protected void finalize(a) throws Throwable {
        DataSources.destroy(cpds);
        super.finalize(); }}Copy the code

Three. To achieve the two core methods of new, modify, delete, query operations: can achieve any complex SQL, and through the way of data binding there will be no SQL injection problems

    /** * You can add, modify, and delete **@paramSQL SQL statement *@paramBindArgs Binding parameter *@returnNumber of rows affected *@throwsSQLException SQL exception */
    public static int executeUpdate(String sql, Object[] bindArgs) throws SQLException {
        /** Number of rows affected **/
        int affectRowCount = -1;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            /** Get database connection **/ from database connection pool
            connection = DBConnectionPool.getInstance().getConnection();
            /** Perform SQL precompilation **/
            preparedStatement = connection.prepareStatement(sql.toString());
            /** Set not to commit automatically so that the database can be rolled back in case of an exception
            connection.setAutoCommit(false);
            System.out.println(getExecSQL(sql, bindArgs));
            if(bindArgs ! =null) {
                /** The binding parameter sets the value **/ in the SQL placeholder
                for (int i = 0; i < bindArgs.length; i++) {
                    preparedStatement.setObject(i + 1, bindArgs[i]); }}Execute SQL / * * * * /
            affectRowCount = preparedStatement.executeUpdate();
            connection.commit();
            String operate;
            if (sql.toUpperCase().indexOf("DELETE FROM") != -1) {
                operate = "Delete";
            } else if (sql.toUpperCase().indexOf("INSERT INTO") != -1) {
                operate = "New";
            } else {
                operate = "Change";
            }
            System.out.println("Success" + operate + "The" + affectRowCount + "Line");
            System.out.println();
        } catch (Exception e) {
            if(connection ! =null) {
                connection.rollback();
            }
            e.printStackTrace();
            throw e;
        } finally {
            if(preparedStatement ! =null) {
                preparedStatement.close();
            }
            if(connection ! =null) { connection.close(); }}return affectRowCount;
    }


   /** * Execute query **@paramSQL SQL statements to be executed *@paramBindArgs binding parameter *@returnList<Map<String, Object>> Result set Object *@throwsSQLException SQL execution exception */
    public static List<Map<String, Object>> executeQuery(String sql, Object[] bindArgs) throws SQLException {
        List<Map<String, Object>> datas = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            /** Get the connection **/ from the database connection pool
            connection = DBConnectionPool.getInstance().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if(bindArgs ! =null) {
                /** Sets the value **/ in the SQL placeholder
                for (int i = 0; i < bindArgs.length; i++) {
                    preparedStatement.setObject(i + 1, bindArgs[i]);
                }
            }
            System.out.println(getExecSQL(sql, bindArgs));
            /** Execute SQL statement to get result set **/
            resultSet = preparedStatement.executeQuery();
            getDatas(resultSet);
            System.out.println();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if(resultSet ! =null) {
                resultSet.close();
            }
            if(preparedStatement ! =null) {
                preparedStatement.close();
            }
            if(connection ! =null) { connection.close(); }}return datas;
    }Copy the code

Perform the new simplified operations

 /** * Perform the database insert operation **@paramValueMap Inserts a Map object * whose key is the column name and value is the corresponding column value in the data table@paramTableName specifies the tableName * of the database to be inserted@returnNumber of rows affected *@throwsSQLException SQL exception */
    public static int insert(String tableName, Map<String, Object> valueMap) throws SQLException {

        /** Gets the value of the key-value pair of the Map inserted by the database **/
        Set<String> keySet = valueMap.keySet();
        Iterator<String> iterator = keySet.iterator();
        /** insert the field SQL, actually with the key of the **/
        StringBuilder columnSql = new StringBuilder();
        /** The value of the field to be inserted. * * /
        StringBuilder unknownMarkSql = new StringBuilder();
        Object[] bindArgs = new Object[valueMap.size()];
        int i = 0;
        while (iterator.hasNext()) {
            String key = iterator.next();
            columnSql.append(i == 0 ? "" : ",");
            columnSql.append(key);

            unknownMarkSql.append(i == 0 ? "" : ",");
            unknownMarkSql.append("?");
            bindArgs[i] = valueMap.get(key);
            i++;
        }
        /** Start collating SQL statements **/
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ");
        sql.append(tableName);
        sql.append("(");
        sql.append(columnSql);
        sql.append(" ) VALUES (");
        sql.append(unknownMarkSql);
        sql.append(")");
        return executeUpdate(sql.toString(), bindArgs);
    }Copy the code

5. Perform simplified operations of the update

  /** * Perform the update operation **@paramTableName table name *@paramValueMap Value to change *@paramWhereMap conditions *@returnNumber of rows affected *@throwsSQLException SQL exception */
    public static int update(String tableName, Map<String, Object> valueMap, Map<String, Object> whereMap) throws SQLException {
        /** Gets the value of the key-value pair of the Map inserted by the database **/
        Set<String> keySet = valueMap.keySet();
        Iterator<String> iterator = keySet.iterator();
        /** Start collating SQL statements **/
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ");
        sql.append(tableName);
        sql.append(" SET ");

        /** the field to be changed is SQL, which is actually spelled with key **/
        StringBuilder columnSql = new StringBuilder();
        int i = 0;
        List<Object> objects = new ArrayList<>();
        while (iterator.hasNext()) {
            String key = iterator.next();
            columnSql.append(i == 0 ? "" : ",");
            columnSql.append(key + "=? ");
            objects.add(valueMap.get(key));
            i++;
        }
        sql.append(columnSql);

        /** update condition: the SQL field to be changed is actually the **/ with key
        StringBuilder whereSql = new StringBuilder();
        int j = 0;
        if(whereMap ! =null && whereMap.size() > 0) {
            whereSql.append(" WHERE ");
            iterator = whereMap.keySet().iterator();
            while (iterator.hasNext()) {
                String key = iterator.next();
                whereSql.append(j == 0 ? "" : " AND ");
                whereSql.append(key + "=? ");
                objects.add(whereMap.get(key));
                j++;
            }
            sql.append(whereSql);
        }
        return executeUpdate(sql.toString(), objects.toArray());
    }Copy the code

Perform simplified deletion operations

  /** * Perform delete operation **@paramTableName Specifies the name of the table to drop *@paramWhereMap delete condition *@returnNumber of rows affected *@throwsSQLException SQL execution exception */
    public static int delete(String tableName, Map<String, Object> whereMap) throws SQLException {
        /** SQL statement to be deleted **/
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ");
        sql.append(tableName);

        /** update condition: the SQL field to be changed is actually the **/ with key
        StringBuilder whereSql = new StringBuilder();
        Object[] bindArgs = null;
        if(whereMap ! =null && whereMap.size() > 0) {
            bindArgs = new Object[whereMap.size()];
            whereSql.append(" WHERE ");
            /** Gets the value of the key-value pair of the Map inserted by the database **/
            Set<String> keySet = whereMap.keySet();
            Iterator<String> iterator = keySet.iterator();
            int i = 0;
            while (iterator.hasNext()) {
                String key = iterator.next();
                whereSql.append(i == 0 ? "" : " AND ");
                whereSql.append(key + "=? ");
                bindArgs[i] = whereMap.get(key);
                i++;
            }
            sql.append(whereSql);
        }
        return executeUpdate(sql.toString(), bindArgs);
    }Copy the code

Seven. Four ways to query

1. Run the SQL command to specify the search criteria based on the Map

/ * * * 1.. Execute SQL query * * using Map<String, Object> to define query conditions@paramTableName table name *@paramWhereMap select * from whereMap where@return List<Map<String, Object>>
   * @throws SQLException
   */
  public static List<Map<String, Object>> query(String tableName,
                                                Map<String, Object> whereMap) throws SQLException {
      String whereClause = "";
      Object[] whereArgs = null;
      if(whereMap ! =null && whereMap.size() > 0) {
          Iterator<String> iterator = whereMap.keySet().iterator();
          whereArgs = new Object[whereMap.size()];
          int i = 0;
          while (iterator.hasNext()) {
              String key = iterator.next();
              whereClause += (i == 0 ? "" : " AND ");
              whereClause += (key + "=? "); whereArgs[i] = whereMap.get(key); i++; }}return query(tableName, false.null, whereClause, whereArgs, null.null.null.null);
  }Copy the code

2. Execute the query in the form of BINDING SQL condition parameters

  /** * 2. Execute SQL condition parameter binding form query **@paramTableName table name *@paramSQL * for whereClause where condition@paramThe value * in the placeholder in the whereArgs where condition@return List<Map<String, Object>>
   * @throws SQLException
   */
  public static List<Map<String, Object>> query(String tableName,
                                                String whereClause,
                                                String[] whereArgs) throws SQLException {
      return query(tableName, false.null, whereClause, whereArgs, null.null.null.null);
  }Copy the code

3. A query method that contains all query conditions

   /** * Execute full structured SQL query **@paramTableName table name *@paramDistinct to *@paramColumns Specifies the column name *@paramSelection WHERE conditions *@paramSelectionArgs The value * in the placeholder in the WHERE condition@paramGroupBy group *@paramHaving screening *@paramOrderBy sorting *@paramLimit the paging *@return List<Map<String, Object>>
    * @throws SQLException
    */
   public static List<Map<String, Object>> query(String tableName,
                                                 boolean distinct,
                                                 String[] columns,
                                                 String selection,
                                                 Object[] selectionArgs,
                                                 String groupBy,
                                                 String having,
                                                 String orderBy,
                                                 String limit) throws SQLException {
       String sql = buildQueryString(distinct, tableName, columns, selection, groupBy, having, orderBy, limit);
       return executeQuery(sql, selectionArgs);
     }Copy the code

4. Through simple SQL query data, careful use, there will be SQL injection problems, just to facilitate the query, the actual development will not use this method

     * 4.through SQL query data, * careful use, there will be SQL injection problems just to facilitate the query, the actual development will not use this method * *@param sql
     * @returnThe data set of the query *@throws SQLException
     */
    public static List<Map<String, Object>> query(String sql) throws SQLException {
        return executeQuery(sql, null);
    }Copy the code

Complete code for DBUtil

package util;

import com.sun.istack.internal.Nullable;

import java.sql.*;
import java.util.*;
import java.util.regex.Pattern;

/** * Created by yuandl on 2016-12-16. */
public class DBUtil {

 /** * Perform the database insert operation **@paramValueMap Inserts a Map object * whose key is the column name and value is the corresponding column value in the data table@paramTableName specifies the tableName * of the database to be inserted@returnNumber of rows affected *@throwsSQLException SQL exception */
 public static int insert(String tableName, Map<String, Object> valueMap) throws SQLException {

     /** Gets the value of the key-value pair of the Map inserted by the database **/
     Set<String> keySet = valueMap.keySet();
     Iterator<String> iterator = keySet.iterator();
     /** insert the field SQL, actually with the key of the **/
     StringBuilder columnSql = new StringBuilder();
     /** The value of the field to be inserted. * * /
     StringBuilder unknownMarkSql = new StringBuilder();
     Object[] bindArgs = new Object[valueMap.size()];
     int i = 0;
     while (iterator.hasNext()) {
         String key = iterator.next();
         columnSql.append(i == 0 ? "" : ",");
         columnSql.append(key);

         unknownMarkSql.append(i == 0 ? "" : ",");
         unknownMarkSql.append("?");
         bindArgs[i] = valueMap.get(key);
         i++;
     }
     /** Start collating SQL statements **/
     StringBuilder sql = new StringBuilder();
     sql.append("INSERT INTO ");
     sql.append(tableName);
     sql.append("(");
     sql.append(columnSql);
     sql.append(" ) VALUES (");
     sql.append(unknownMarkSql);
     sql.append(")");
     return executeUpdate(sql.toString(), bindArgs);
 }

 /** * Perform the update operation **@paramTableName table name *@paramValueMap Value to change *@paramWhereMap conditions *@returnNumber of rows affected *@throwsSQLException SQL exception */
 public static int update(String tableName, Map<String, Object> valueMap, Map<String, Object> whereMap) throws SQLException {
     /** Gets the value of the key-value pair of the Map inserted by the database **/
     Set<String> keySet = valueMap.keySet();
     Iterator<String> iterator = keySet.iterator();
     /** Start collating SQL statements **/
     StringBuilder sql = new StringBuilder();
     sql.append("UPDATE ");
     sql.append(tableName);
     sql.append(" SET ");

     /** the field to be changed is SQL, which is actually spelled with key **/
     StringBuilder columnSql = new StringBuilder();
     int i = 0;
     List<Object> objects = new ArrayList<>();
     while (iterator.hasNext()) {
         String key = iterator.next();
         columnSql.append(i == 0 ? "" : ",");
         columnSql.append(key + "=? ");
         objects.add(valueMap.get(key));
         i++;
     }
     sql.append(columnSql);

     /** update condition: the SQL field to be changed is actually the **/ with key
     StringBuilder whereSql = new StringBuilder();
     int j = 0;
     if(whereMap ! =null && whereMap.size() > 0) {
         whereSql.append(" WHERE ");
         iterator = whereMap.keySet().iterator();
         while (iterator.hasNext()) {
             String key = iterator.next();
             whereSql.append(j == 0 ? "" : " AND ");
             whereSql.append(key + "=? ");
             objects.add(whereMap.get(key));
             j++;
         }
         sql.append(whereSql);
     }
     return executeUpdate(sql.toString(), objects.toArray());
 }

 /** * Perform delete operation **@paramTableName Specifies the name of the table to drop *@paramWhereMap delete condition *@returnNumber of rows affected *@throwsSQLException SQL execution exception */
 public static int delete(String tableName, Map<String, Object> whereMap) throws SQLException {
     /** SQL statement to be deleted **/
     StringBuilder sql = new StringBuilder();
     sql.append("DELETE FROM ");
     sql.append(tableName);

     /** update condition: the SQL field to be changed is actually the **/ with key
     StringBuilder whereSql = new StringBuilder();
     Object[] bindArgs = null;
     if(whereMap ! =null && whereMap.size() > 0) {
         bindArgs = new Object[whereMap.size()];
         whereSql.append(" WHERE ");
         /** Gets the value of the key-value pair of the Map inserted by the database **/
         Set<String> keySet = whereMap.keySet();
         Iterator<String> iterator = keySet.iterator();
         int i = 0;
         while (iterator.hasNext()) {
             String key = iterator.next();
             whereSql.append(i == 0 ? "" : " AND ");
             whereSql.append(key + "=? ");
             bindArgs[i] = whereMap.get(key);
             i++;
         }
         sql.append(whereSql);
     }
     return executeUpdate(sql.toString(), bindArgs);
 }

 /** * You can add, modify, and delete **@paramSQL SQL statement *@paramBindArgs Binding parameter *@returnNumber of rows affected *@throwsSQLException SQL exception */
 public static int executeUpdate(String sql, Object[] bindArgs) throws SQLException {
     /** Number of rows affected **/
     int affectRowCount = -1;
     Connection connection = null;
     PreparedStatement preparedStatement = null;
     try {
         /** Get database connection **/ from database connection pool
         connection = DBConnectionPool.getInstance().getConnection();
         /** Perform SQL precompilation **/
         preparedStatement = connection.prepareStatement(sql.toString());
         /** Set not to commit automatically so that the database can be rolled back in case of an exception
         connection.setAutoCommit(false);
         System.out.println(getExecSQL(sql, bindArgs));
         if(bindArgs ! =null) {
             /** The binding parameter sets the value **/ in the SQL placeholder
             for (int i = 0; i < bindArgs.length; i++) {
                 preparedStatement.setObject(i + 1, bindArgs[i]); }}Execute SQL / * * * * /
         affectRowCount = preparedStatement.executeUpdate();
         connection.commit();
         String operate;
         if (sql.toUpperCase().indexOf("DELETE FROM") != -1) {
             operate = "Delete";
         } else if (sql.toUpperCase().indexOf("INSERT INTO") != -1) {
             operate = "New";
         } else {
             operate = "Change";
         }
         System.out.println("Success" + operate + "The" + affectRowCount + "Line");
         System.out.println();
     } catch (Exception e) {
         if(connection ! =null) {
             connection.rollback();
         }
         e.printStackTrace();
         throw e;
     } finally {
         if(preparedStatement ! =null) {
             preparedStatement.close();
         }
         if(connection ! =null) { connection.close(); }}return affectRowCount;
 }

 /** * Use SQL to query data, ** with caution, there will be SQL injection problems **@param sql
  * @returnThe data set of the query *@throws SQLException
  */
 public static List<Map<String, Object>> query(String sql) throws SQLException {
     return executeQuery(sql, null);
 }

 /** * Execute SQL query with Map<String, Object> limit query criteria **@paramTableName table name *@paramWhereMap select * from whereMap where@return List<Map<String, Object>>
  * @throws SQLException
  */
 public static List<Map<String, Object>> query(String tableName,
                                               Map<String, Object> whereMap) throws SQLException {
     String whereClause = "";
     Object[] whereArgs = null;
     if(whereMap ! =null & whereMap.size() > 0) {
         Iterator<String> iterator = whereMap.keySet().iterator();
         whereArgs = new Object[whereMap.size()];
         int i = 0;
         while (iterator.hasNext()) {
             String key = iterator.next();
             whereClause += (i == 0 ? "" : " AND ");
             whereClause += (key + "=? "); whereArgs[i] = whereMap.get(key); i++; }}return query(tableName, false.null, whereClause, whereArgs, null.null.null.null);
 }

 /** * Execute query ** in the form of SQL condition parameter binding@paramTableName table name *@paramSQL * for whereClause where condition@paramThe value * in the placeholder in the whereArgs where condition@return List<Map<String, Object>>
  * @throws SQLException
  */
 public static List<Map<String, Object>> query(String tableName,
                                               String whereClause,
                                               String[] whereArgs) throws SQLException {
     return query(tableName, false.null, whereClause, whereArgs, null.null.null.null);
 }

 /** * Execute full structured SQL query **@paramTableName table name *@paramDistinct to *@paramColumns Specifies the column name *@paramSelection WHERE conditions *@paramSelectionArgs The value * in the placeholder in the WHERE condition@paramGroupBy group *@paramHaving screening *@paramOrderBy sorting *@paramLimit the paging *@return List<Map<String, Object>>
  * @throws SQLException
  */
 public static List<Map<String, Object>> query(String tableName,
                                               boolean distinct,
                                               String[] columns,
                                               String selection,
                                               Object[] selectionArgs,
                                               String groupBy,
                                               String having,
                                               String orderBy,
                                               String limit) throws SQLException {
     String sql = buildQueryString(distinct, tableName, columns, selection, groupBy, having, orderBy, limit);
     return executeQuery(sql, selectionArgs);

 }

 /** * Execute query **@paramSQL SQL statements to be executed *@paramBindArgs binding parameter *@returnList<Map<String, Object>> Result set Object *@throwsSQLException SQL execution exception */
 public static List<Map<String, Object>> executeQuery(String sql, Object[] bindArgs) throws SQLException {
     List<Map<String, Object>> datas = new ArrayList<>();
     Connection connection = null;
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;

     try {
         /** Get the connection **/ from the database connection pool
         connection = DBConnectionPool.getInstance().getConnection();
         preparedStatement = connection.prepareStatement(sql);
         if(bindArgs ! =null) {
             /** Sets the value **/ in the SQL placeholder
             for (int i = 0; i < bindArgs.length; i++) {
                 preparedStatement.setObject(i + 1, bindArgs[i]);
             }
         }
         System.out.println(getExecSQL(sql, bindArgs));
         /** Execute SQL statement to get result set **/
         resultSet = preparedStatement.executeQuery();
         getDatas(resultSet);
         System.out.println();
     } catch (Exception e) {
         e.printStackTrace();
         throw e;
     } finally {
         if(resultSet ! =null) {
             resultSet.close();
         }
         if(preparedStatement ! =null) {
             preparedStatement.close();
         }
         if(connection ! =null) { connection.close(); }}return datas;
 }


 /** * encapsulates the result set Object as a List<Map<String, Object>> Object **@paramResultSet results think more *@returnEncapsulation of results *@throws SQLException
  */
 private static List<Map<String, Object>> getDatas(ResultSet resultSet) throws SQLException {
     List<Map<String, Object>> datas = new ArrayList<>();
     /** Get the result set's data structure object **/
     ResultSetMetaData metaData = resultSet.getMetaData();
     while (resultSet.next()) {
         Map<String, Object> rowMap = new HashMap<>();
         for (int i = 1; i <= metaData.getColumnCount(); i++) {
             rowMap.put(metaData.getColumnName(i), resultSet.getObject(i));
         }
         datas.add(rowMap);
     }
     System.out.println("Successful query" + datas.size() + Row data);
     for (int i = 0; i < datas.size(); i++) {
         Map<String, Object> map = datas.get(i);
         System.out.println("The first" + (i + 1) + "Line." + map);
     }
     return datas;
 }


 /**
  * Build an SQL query string from the given clauses.
  *
  * @param distinct true if you want each row to be unique, false otherwise.
  * @param tables   The table names to compile the query against.
  * @param columns  A list of which columns to return. Passing null will
  *                 return all columns, which is discouraged to prevent reading
  *                 data from storage that isn't going to be used.
  * @param where    A filter declaring which rows to return, formatted as an SQL
  *                 WHERE clause (excluding the WHERE itself). Passing null will
  *                 return all rows for the given URL.
  * @param groupBy  A filter declaring how to group rows, formatted as an SQL
  *                 GROUP BY clause (excluding the GROUP BY itself). Passing null
  *                 will cause the rows to not be grouped.
  * @param having   A filter declare which row groups to include in the cursor,
  *                 if row grouping is being used, formatted as an SQL HAVING
  *                 clause (excluding the HAVING itself). Passing null will cause
  *                 all row groups to be included, and is required when row
  *                 grouping is not being used.
  * @param orderBy  How to order the rows, formatted as an SQL ORDER BY clause
  *                 (excluding the ORDER BY itself). Passing null will use the
  *                 default sort order, which may be unordered.
  * @param limit    Limits the number of rows returned by the query,
  *                 formatted as LIMIT clause. Passing null denotes no LIMIT clause.
  * @return the SQL query string
  */
 private static String buildQueryString(
         boolean distinct, String tables, String[] columns, String where,
         String groupBy, String having, String orderBy, String limit) {
     if(isEmpty(groupBy) && ! isEmpty(having)) {throw new IllegalArgumentException(
                 "HAVING clauses are only permitted when using a groupBy clause");
     }
     if(! isEmpty(limit) && ! sLimitPattern.matcher(limit).matches()) {throw new IllegalArgumentException("invalid LIMIT clauses:" + limit);
     }

     StringBuilder query = new StringBuilder(120);

     query.append("SELECT ");
     if (distinct) {
         query.append("DISTINCT ");
     }
     if(columns ! =null&& columns.length ! =0) {
         appendColumns(query, columns);
     } else {
         query.append("*");
     }
     query.append("FROM ");
     query.append(tables);
     appendClause(query, " WHERE ", where);
     appendClause(query, " GROUP BY ", groupBy);
     appendClause(query, " HAVING ", having);
     appendClause(query, " ORDER BY ", orderBy);
     appendClause(query, " LIMIT ", limit);
     return query.toString();
 }

 /** * Add the names that are non-null in columns to s, separating * them with commas. */
 private static void appendColumns(StringBuilder s, String[] columns) {
     int n = columns.length;

     for (int i = 0; i < n; i++) {
         String column = columns[i];

         if(column ! =null) {
             if (i > 0) {
                 s.append(",");
             }
             s.append(column);
         }
     }
     s.append(' ');
 }

 /**
  * addClause
  *
  * @param s      the add StringBuilder
  * @param name   clauseName
  * @param clause clauseSelection
  */
 private static void appendClause(StringBuilder s, String name, String clause) {
     if (!isEmpty(clause)) {
         s.append(name);
         s.append(clause);
     }
 }

 /**
  * Returns true if the string is null or 0-length.
  *
  * @param str the string to be examined
  * @return true if str is null or zero length
  */
 private static boolean isEmpty(@Nullable CharSequence str) {
     if (str == null || str.length() == 0)
         return true;
     else
         return false;
 }

 /** * the pattern of limit */
 private static final Pattern sLimitPattern =
         Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?");

 /**
  * After the execution of the complete SQL statement, not necessarily the actual implementation of the SQL statement
  *
  * @param sql      SQL statement
  * @param bindArgs Binding parameters
  * @return Replace? SQL statement executed after the
  */
 private static String getExecSQL(String sql, Object[] bindArgs) {
     StringBuilder sb = new StringBuilder(sql);
     if(bindArgs ! =null && bindArgs.length > 0) {
         int index = 0;
         for (int i = 0; i < bindArgs.length; i++) {
             index = sb.indexOf("?", index);
             sb.replace(index, index + 1, String.valueOf(bindArgs[i])); }}returnsb.toString(); }}Copy the code

The usage nine.

The test code

import util.DBUtil;

import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* Created by yuandl on 2016-12-16.
*/
public class DBTest {

public static void main(String[] args) {
    System.out.println("Raw data from the database");
    testQuery3();
    testInsert();
    System.out.println("Perform inserted data");
    testQuery3();
    testUpdate();
    System.out.println("Execute modified data");
    testQuery3();
    testDelete();
    System.out.println("Perform deleted data");
    testQuery3();
    System.out.println("Query 1 with conditions");
    testQuery2();
    System.out.println("Query with Conditions 2");
    testQuery1();
}



/** * test insert */
private static void testInsert(a) {
    Map<String, Object> map = new HashMap<>();
    map.put("emp_id".1013);
    map.put("name"."JDBCUtil test");
    map.put("job"."developer");
    map.put("salary".10000);
    map.put("hire_date".new java.sql.Date(System.currentTimeMillis()));
    try {
        int count = DBUtil.insert("emp_test", map);
              } catch(SQLException e) { e.printStackTrace(); }}/** * Test update */
private static void testUpdate(a) {
    Map<String, Object> map = new HashMap<>();
    map.put("name"."Test update");

    Map<String, Object> whereMap = new HashMap<>();
    whereMap.put("emp_id"."1013");
    try {
        int count = DBUtil.update("emp_test", map, whereMap);
    } catch(SQLException e) { e.printStackTrace(); }}/** * Test delete */
private static void testDelete(a) {
    Map<String, Object> whereMap = new HashMap<>();
    whereMap.put("emp_id".1013);
    whereMap.put("job"."developer");
    try {
        int count = DBUtil.delete("emp_test", whereMap);
    } catch(SQLException e) { e.printStackTrace(); }}/** ** Query method 1 */
public static void testQuery1(a) {
    Map<String,Object> whereMap=new HashMap<>();
    whereMap.put("salary"."10000");
    try {
        DBUtil.query("emp_test",whereMap);
    } catch(SQLException e) { e.printStackTrace(); }}/** * query method 2 */
public static void testQuery2(a) {
    String where = "job = ? AND salary = ? ";
    String[] whereArgs = new String[]{"clerk"."3000"};

    try {
        List<Map<String, Object>> list = DBUtil.query("emp_test", where, whereArgs);

    } catch(SQLException e) { e.printStackTrace(); }}/** * query method three */
public static void testQuery3(a) {
    try {
        List<Map<String, Object>> list = DBUtil.query("emp_test".false.null.null.null.null.null.null.null);

    } catch(SQLException e) { e.printStackTrace(); }}}Copy the code

Print the result

SELECT * FROM emp_test SELECT * FROM emp_test14The first line of data1Line: {DEPT_TEST_ID =10, EMP_ID=1001, SALARY=10000, HIRE_DATE=2010- 01- 12, BONUS=2000, MANAGER=1005, JOB=Manager, NAME= Zhang Wuji} d2Line: {DEPT_TEST_ID =10, EMP_ID=1002, SALARY=8000, HIRE_DATE=2011- 01- 12, BONUS=1000, MANAGER=1001, JOB=Analyst, NAME= Liu Cangsong} d3Line: {DEPT_TEST_ID =10, EMP_ID=1003, SALARY=9000, HIRE_DATE=2010.- 11, BONUS=1000, MANAGER=1001, JOB=Analyst, NAME= Li Yi} dI4Line: {DEPT_TEST_ID =10, EMP_ID=1004, SALARY=5000, HIRE_DATE=2010.- 11, BONUS=null, MANAGER=1001, JOB=Programmer, NAME=5Line: {DEPT_TEST_ID =20, EMP_ID=1005, SALARY=15000, HIRE_DATE=2008.- 15, BONUS=null, MANAGER=null, JOB=President, NAME= zhang Sanfeng6Line: {DEPT_TEST_ID =20, EMP_ID=1006, SALARY=5000, HIRE_DATE=2009.- 01, BONUS=400, MANAGER=1005, JOB=Manager, NAME= Yanxiaosi67Line: {DEPT_TEST_ID =20, EMP_ID=1007, SALARY=3000, HIRE_DATE=2009.- 01, BONUS=500, MANAGER=1006, JOB=clerk, NAME= clerk8Line: {DEPT_TEST_ID =30, EMP_ID=1008, SALARY=5000, HIRE_DATE=2009- 05- 01, BONUS=500, MANAGER=1005, JOB=Manager, NAME= yellow9Line: {DEPT_TEST_ID =30, EMP_ID=1009, SALARY=4000, HIRE_DATE=2009.- 20, BONUS=null, MANAGER=1008JOB=salesman = NAME= Trinket10Line: {DEPT_TEST_ID =30, EMP_ID=1010, SALARY=4500, HIRE_DATE=2009- 05- 10, BONUS=500, MANAGER=1008JOB=salesman = NAME= Guo Jing11Line: {DEPT_TEST_ID =null, EMP_ID=1011, SALARY=null, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Yu Zecheng} dI12Line: {DEPT_TEST_ID =null, EMP_ID=1012, SALARY=null, HIRE_DATE=2011- 08- 10, BONUS=null, MANAGER=null, JOB=nullThe first, NAME = Amy}13Line: {DEPT_TEST_ID =null, EMP_ID=1014, SALARY=8000, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Zhang Wuji} di14Line: {DEPT_TEST_ID =20, EMP_ID=1015, SALARY=null, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=nullINSERT INTO emp_test (NAME,hire_date,job,salary,emp_id) VALUES (JDBCUtil,2016- 12- 17,developer,10000.1013) successfully added1SELECT * FROM emp_test SELECT * FROM emp_test15The first line of data1Line: {DEPT_TEST_ID =10, EMP_ID=1001, SALARY=10000, HIRE_DATE=2010- 01- 12, BONUS=2000, MANAGER=1005, JOB=Manager, NAME= Zhang Wuji} d2Line: {DEPT_TEST_ID =10, EMP_ID=1002, SALARY=8000, HIRE_DATE=2011- 01- 12, BONUS=1000, MANAGER=1001, JOB=Analyst, NAME= Liu Cangsong} d3Line: {DEPT_TEST_ID =10, EMP_ID=1003, SALARY=9000, HIRE_DATE=2010.- 11, BONUS=1000, MANAGER=1001, JOB=Analyst, NAME= Li Yi} dI4Line: {DEPT_TEST_ID =10, EMP_ID=1004, SALARY=5000, HIRE_DATE=2010.- 11, BONUS=null, MANAGER=1001, JOB=Programmer, NAME=5Line: {DEPT_TEST_ID =20, EMP_ID=1005, SALARY=15000, HIRE_DATE=2008.- 15, BONUS=null, MANAGER=null, JOB=President, NAME= zhang Sanfeng6Line: {DEPT_TEST_ID =20, EMP_ID=1006, SALARY=5000, HIRE_DATE=2009.- 01, BONUS=400, MANAGER=1005, JOB=Manager, NAME= Yanxiaosi67Line: {DEPT_TEST_ID =20, EMP_ID=1007, SALARY=3000, HIRE_DATE=2009.- 01, BONUS=500, MANAGER=1006, JOB=clerk, NAME= clerk8Line: {DEPT_TEST_ID =30, EMP_ID=1008, SALARY=5000, HIRE_DATE=2009- 05- 01, BONUS=500, MANAGER=1005, JOB=Manager, NAME= yellow9Line: {DEPT_TEST_ID =30, EMP_ID=1009, SALARY=4000, HIRE_DATE=2009.- 20, BONUS=null, MANAGER=1008JOB=salesman = NAME= Trinket10Line: {DEPT_TEST_ID =30, EMP_ID=1010, SALARY=4500, HIRE_DATE=2009- 05- 10, BONUS=500, MANAGER=1008JOB=salesman = NAME= Guo Jing11Line: {DEPT_TEST_ID =null, EMP_ID=1011, SALARY=null, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Yu Zecheng} dI12Line: {DEPT_TEST_ID =null, EMP_ID=1012, SALARY=null, HIRE_DATE=2011- 08- 10, BONUS=null, MANAGER=null, JOB=nullThe first, NAME = Amy}13Line: {DEPT_TEST_ID =null, EMP_ID=1014, SALARY=8000, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Zhang Wuji} di14Line: {DEPT_TEST_ID =20, EMP_ID=1015, SALARY=null, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Liu Cangsong} dI15Line: {DEPT_TEST_ID =null, EMP_ID=1013, SALARY=10000, HIRE_DATE=2016- 12- 17, BONUS=null, MANAGER=null, JOB=developer, NAME=JDBCUtil test} UPDATE EMP_test SET NAME= test UPDATE WHERE emp_id =1013Successfully modified1SELECT * FROM emp_test SELECT * FROM emp_test15The first line of data1Line: {DEPT_TEST_ID =10, EMP_ID=1001, SALARY=10000, HIRE_DATE=2010- 01- 12, BONUS=2000, MANAGER=1005, JOB=Manager, NAME= Zhang Wuji} d2Line: {DEPT_TEST_ID =10, EMP_ID=1002, SALARY=8000, HIRE_DATE=2011- 01- 12, BONUS=1000, MANAGER=1001, JOB=Analyst, NAME= Liu Cangsong} d3Line: {DEPT_TEST_ID =10, EMP_ID=1003, SALARY=9000, HIRE_DATE=2010.- 11, BONUS=1000, MANAGER=1001, JOB=Analyst, NAME= Li Yi} dI4Line: {DEPT_TEST_ID =10, EMP_ID=1004, SALARY=5000, HIRE_DATE=2010.- 11, BONUS=null, MANAGER=1001, JOB=Programmer, NAME=5Line: {DEPT_TEST_ID =20, EMP_ID=1005, SALARY=15000, HIRE_DATE=2008.- 15, BONUS=null, MANAGER=null, JOB=President, NAME= zhang Sanfeng6Line: {DEPT_TEST_ID =20, EMP_ID=1006, SALARY=5000, HIRE_DATE=2009.- 01, BONUS=400, MANAGER=1005, JOB=Manager, NAME= Yanxiaosi67Line: {DEPT_TEST_ID =20, EMP_ID=1007, SALARY=3000, HIRE_DATE=2009.- 01, BONUS=500, MANAGER=1006, JOB=clerk, NAME= clerk8Line: {DEPT_TEST_ID =30, EMP_ID=1008, SALARY=5000, HIRE_DATE=2009- 05- 01, BONUS=500, MANAGER=1005, JOB=Manager, NAME= yellow9Line: {DEPT_TEST_ID =30, EMP_ID=1009, SALARY=4000, HIRE_DATE=2009.- 20, BONUS=null, MANAGER=1008JOB=salesman = NAME= Trinket10Line: {DEPT_TEST_ID =30, EMP_ID=1010, SALARY=4500, HIRE_DATE=2009- 05- 10, BONUS=500, MANAGER=1008JOB=salesman = NAME= Guo Jing11Line: {DEPT_TEST_ID =null, EMP_ID=1011, SALARY=null, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Yu Zecheng} dI12Line: {DEPT_TEST_ID =null, EMP_ID=1012, SALARY=null, HIRE_DATE=2011- 08- 10, BONUS=null, MANAGER=null, JOB=nullThe first, NAME = Amy}13Line: {DEPT_TEST_ID =null, EMP_ID=1014, SALARY=8000, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Zhang Wuji} di14Line: {DEPT_TEST_ID =20, EMP_ID=1015, SALARY=null, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Liu Cangsong} dI15Line: {DEPT_TEST_ID =null, EMP_ID=1013, SALARY=10000, HIRE_DATE=2016- 12- 17, BONUS=null, MANAGER=null, JOB=developer, NAME= test update} DELETE FROM emp_test WHERE JOB=developer AND emp_id =1013Successfully deleted1SELECT * FROM emp_test SELECT * FROM emp_test14The first line of data1Line: {DEPT_TEST_ID =10, EMP_ID=1001, SALARY=10000, HIRE_DATE=2010- 01- 12, BONUS=2000, MANAGER=1005, JOB=Manager, NAME= Zhang Wuji} d2Line: {DEPT_TEST_ID =10, EMP_ID=1002, SALARY=8000, HIRE_DATE=2011- 01- 12, BONUS=1000, MANAGER=1001, JOB=Analyst, NAME= Liu Cangsong} d3Line: {DEPT_TEST_ID =10, EMP_ID=1003, SALARY=9000, HIRE_DATE=2010.- 11, BONUS=1000, MANAGER=1001, JOB=Analyst, NAME= Li Yi} dI4Line: {DEPT_TEST_ID =10, EMP_ID=1004, SALARY=5000, HIRE_DATE=2010.- 11, BONUS=null, MANAGER=1001, JOB=Programmer, NAME=5Line: {DEPT_TEST_ID =20, EMP_ID=1005, SALARY=15000, HIRE_DATE=2008.- 15, BONUS=null, MANAGER=null, JOB=President, NAME= zhang Sanfeng6Line: {DEPT_TEST_ID =20, EMP_ID=1006, SALARY=5000, HIRE_DATE=2009.- 01, BONUS=400, MANAGER=1005, JOB=Manager, NAME= Yanxiaosi67Line: {DEPT_TEST_ID =20, EMP_ID=1007, SALARY=3000, HIRE_DATE=2009.- 01, BONUS=500, MANAGER=1006, JOB=clerk, NAME= clerk8Line: {DEPT_TEST_ID =30, EMP_ID=1008, SALARY=5000, HIRE_DATE=2009- 05- 01, BONUS=500, MANAGER=1005, JOB=Manager, NAME= yellow9Line: {DEPT_TEST_ID =30, EMP_ID=1009, SALARY=4000, HIRE_DATE=2009.- 20, BONUS=null, MANAGER=1008JOB=salesman = NAME= Trinket10Line: {DEPT_TEST_ID =30, EMP_ID=1010, SALARY=4500, HIRE_DATE=2009- 05- 10, BONUS=500, MANAGER=1008JOB=salesman = NAME= Guo Jing11Line: {DEPT_TEST_ID =null, EMP_ID=1011, SALARY=null, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Yu Zecheng} dI12Line: {DEPT_TEST_ID =null, EMP_ID=1012, SALARY=null, HIRE_DATE=2011- 08- 10, BONUS=null, MANAGER=null, JOB=nullThe first, NAME = Amy}13Line: {DEPT_TEST_ID =null, EMP_ID=1014, SALARY=8000, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= Zhang Wuji} di14Line: {DEPT_TEST_ID =20, EMP_ID=1015, SALARY=null, HIRE_DATE=null, BONUS=null, MANAGER=null, JOB=null, NAME= liu Cangsong} with conditional query1
SELECT  * FROM emp_test WHERE job = clerk  AND salary = 3000Successful query1The first line of data1Line: {DEPT_TEST_ID =20, EMP_ID=1007, SALARY=3000, HIRE_DATE=2009.- 01, BONUS=500, MANAGER=1006, JOB=clerk, NAME= clerk2
SELECT  * FROM emp_test WHERE salary = 10000Successful query1The first line of data1Line: {DEPT_TEST_ID =10, EMP_ID=1001, SALARY=10000, HIRE_DATE=2010- 01- 12, BONUS=2000, MANAGER=1005, JOB=Manager, NAME= Zhang Wuji}Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

JDBCUtil, a universal tool class for database operations, joins the efficient database connection pool and takes advantage of parameter binding to effectively prevent SQL injection

JDBCUtil, a universal tool class for database operations, joins the efficient database connection pool and takes advantage of parameter binding to effectively prevent SQL injection

Set up jdbC-mysql. properties under project New Config package and add the following configuration

Prepare database connection pool objects: singletons

Three. To achieve the two core methods of new, modify, delete, query operations: can achieve any complex SQL, and through the way of data binding there will be no SQL injection problems

Perform the new simplified operations

5. Perform simplified operations of the update

Perform simplified deletion operations

Seven. Four ways to query

Complete code for DBUtil

The usage nine.

Ten.GitHub

JDBCUtil, a universal tool class for database operations, joins the efficient database connection pool and takes advantage of parameter binding to effectively prevent SQL injection

JDBCUtil, a universal tool class for database operations, joins the efficient database connection pool and takes advantage of parameter binding to effectively prevent SQL injection

Set up jdbC-mysql. properties under project New Config package and add the following configuration

Prepare database connection pool objects: singletons

Three. To achieve the two core methods of new, modify, delete, query operations: can achieve any complex SQL, and through the way of data binding there will be no SQL injection problems

Perform the new simplified operations

5. Perform simplified operations of the update

Perform simplified deletion operations

Seven. Four ways to query

Complete code for DBUtil

The usage nine.

Ten.GitHub

Related Posts

Blood spitting arrangement, the most complete data structure quickly fix: binary tree + red black tree +B&B+ tree

The primary and secondary MySQL replication clusters are set up

(6) The realization of AQS sharing mode based on Semaphore and CountDownLatch