Isu keselamatan benang sambungan java ke mysql

Question

Saya mencari di Internet selama N hari, dan hampir tiada penyelesaian yang selamat untuk thread Masalah yang sama boleh diselesaikan dengan mudah dengan Redis Saya menukarnya kepada kelas alat yang saya temui dalam talian .Idea semasa saya adalah untuk menambah kata kunci yang disegerakkan, tetapi saya masih merasakan bahawa terdapat masalah.

class MySQLUtil {
  
  private static final String driver = "com.mysql.jdbc.Driver";
  private static final String url = "jdbc:mysql://192.168.31.103:3306/";
  private static final String character = "?useUnicode=true&characterEncoding=utf8";
  private static final String ssl = "&useSSL=false";
  private static final String user = "root";
  private static final String password = "111111";
  private static Connection connection = null;
  private static Statement statement = null;
  private static PreparedStatement ps = null;
  private static ResultSet rs = null;
  
  boolean TestConnection(String db) {
    try {
      Class.forName(driver);
      Connection connection = DriverManager.getConnection(url + db + character + ssl, user, password);
      
      if (!connection.isClosed()) {
        CloseConnection();
        return true;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return false;
  }
  
  synchronized private void ConnectToDB(String db) {
    try {
      Class.forName(driver);
      Connection connection = DriverManager.getConnection(url + db + character + ssl, user, password);
      
      if (!connection.isClosed()) {
        statement = connection.createStatement();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  
  synchronized private void CloseConnection() {
    try {
      if (rs != null) {
        rs.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    
    try {
      if (ps != null) {
        ps.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    
    try {
      if (connection != null) {
        connection.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  
  synchronized void ModifyData(String db, String data) {
    
    ConnectToDB(db);
    try {
      statement.execute(data);
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      CloseConnection();
    }
    
  }
  
  synchronized List ReadData(String db, String data) {
    List<String> list = new ArrayList<>();
    int count;
    ConnectToDB(db);
    
    try {
      rs = statement.executeQuery(data);
      ResultSetMetaData rsmd;
      rsmd = rs.getMetaData();
      count = rsmd.getColumnCount();
      
      while (rs.next()) {
        for (int i = 1; i <= count; i++) {
          String label = rsmd.getColumnLabel(i);
          list.add(label);
          String value = rs.getString(i);
          list.add(value);
        }
      }
      
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      CloseConnection();
    }
    return list;
  }
}

Answer 1

Untuk memastikan data antara sambungan adalah bebas (tidak dikongsi), saya rasa anda mahu melaksanakan Kolam sambungan:

ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" );
cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
cpds.setUser("caiyongji");
cpds.setPassword("test-password");

cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);

Answer 2

Selepas sedikit pengubahsuaian, mungkin lebih baik untuk mendengar rakan di atas dan menggunakan kumpulan sambungan pangkalan data yang matang. Tidak perlu mencipta semula roda

• Gunakan singleton untuk memastikan keunikan sambungan pangkalan data

• Ubah suai

  penggunaan kata kunci untuk meningkatkan kecekapansynchronized

• Tambah

  kata kunci untuk meningkatkan kestabilanvolatile

package com.singleton;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <b>功能：</b><br>
 * <br>
 * <b>完整路径：</b> com.singleton.MySQLUtil <br>
 * <b>创建日期：</b> 2017年6月15日 上午10:42:49 <br>
 * 
 * @author pfyangf<br>
 * @version 1.0
 */
class MySQLUtil {
    
    private MySQLUtil(){}
    
    private static volatile Connection connection = null;

    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String url = "jdbc:mysql://192.168.31.103:3306/";
    private static final String character = "?useUnicode=true&characterEncoding=utf8";
    private static final String ssl = "&useSSL=false";
    private static final String user = "axtest";
    private static final String password = "axtest123";
    private static Statement statement = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;
    
    public static void main(String[] args) {
        /*Connection newConnection;
        try {
            newConnection = MySQLUtil.connectToDB("xxx");
            System.out.println(newConnection.isClosed());
        } catch (Exception e) {
            //TODO 异常处理
            e.printStackTrace();
        }*/
        try {
            List<Map<String, Object>> data = MySQLUtil.readData("xxx", "select now() from dual");
            System.out.println(data.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    boolean TestConnection(String db) {
        try {
            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url + db + character + ssl, user, password);

            if (!connection.isClosed()) {
                CloseConnection();
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * <b>功能：获取DB连接</b><br>
     * <br>
     * @Author:pfyangf , 2017年6月15日
     * @param db
     * @return
     * @throws Exception Connection
     **/
    public static Connection connectToDB(String db) throws Exception {
        if(null == connection){
            synchronized (MySQLUtil.class) {
                if(null == connection){
                    Class.forName(driver);
                    connection = DriverManager.getConnection(url + db + character + ssl, user, password);
                    statement = connection.createStatement();
                }
            }
        }
        return connection;
    }

    private static void CloseConnection() {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void ModifyData(String db, String data) throws Exception {

        connectToDB(db);
        try {
            statement.execute(data);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            CloseConnection();
        }

    }

    public static List<Map<String, Object>> readData(String db, String sql) throws Exception {
        List<Map<String, Object>> list = new ArrayList<>();
        int count;
        connectToDB(db);

        try {
            rs = statement.executeQuery(sql);
            ResultSetMetaData rsmd;
            rsmd = rs.getMetaData();
            count = rsmd.getColumnCount();

            while (rs.next()) {
                Map<String, Object> map = null;
                for (int i = 1; i <= count; i++) {
                    map = new HashMap<>();
                    map.put(rsmd.getColumnLabel(i), rs.getString(i));
                    list.add(map);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            CloseConnection();
        }
        return list;
    }
}

Answer 3

Tidak perlu menyegerakkan, berbilang sambungan tidak penting.
Pangkalan data mempunyai kuncinya sendiri.
Anda juga boleh menggunakan kolam sambungan secara terus.

Answer 4

Terima kasih atas jawapan anda Saya menukar sedikit kod. Tolong bantu saya melihat jika ada masalah pembolehubah ahli Semua parameter dan Nilai pulangan diluluskan dan semua pembolehubah diisytiharkan dalam kaedah

class MySQLUtil {
    
    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String url = "jdbc:mysql://192.168.31.103:3306/";
    private static final String character = "?useUnicode=true&characterEncoding=utf8";
    private static final String ssl = "&useSSL=false";
    private static final String user = "root";
    private static final String password = "111111";
    
    boolean TestConnection(String db) {
        try {
            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url + db + character + ssl, user, password);
            
            if (!connection.isClosed()) {
                CloseConnection(connection, null);
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
    
    private List ConnectToDB(String db) {
        
        List<Object> list = new ArrayList<>();
        
        try {
            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url + db + character + ssl, user, password);
            
            if (!connection.isClosed()) {
                Statement statement = connection.createStatement();
                list.add(1, connection);
                list.add(2, statement);
                return list;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return list;
    }
    
    private void CloseConnection(Connection connection, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public void ModifyData(String db, String data) {
        
        List list = ConnectToDB(db);
        Connection connection = (Connection) list.get(1);
        Statement statement = (Statement) list.get(2);
        
        try {
            statement.execute(data);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            CloseConnection(connection, null);
        }
        
    }
    
    public List ReadData(String db, String data) {
        List<String> result = new ArrayList<>();
        ResultSet rs = null;
        int count;
        
        List list1 = ConnectToDB(db);
        Connection connection = (Connection) list1.get(1);
        Statement statement = (Statement) list1.get(2);
        
        try {
            rs = statement.executeQuery(data);
            ResultSetMetaData rsmd;
            rsmd = rs.getMetaData();
            count = rsmd.getColumnCount();
            
            while (rs.next()) {
                for (int i = 1; i <= count; i++) {
                    String label = rsmd.getColumnLabel(i);
                    result.add(label);
                    String value = rs.getString(i);
                    result.add(value);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            CloseConnection(connection, rs);
        }
        return result;
    }
}