java连接mysql的线程安全问题

Question

我在网上搜索了N天，几乎没有关于线程安全的解决办法，同样的问题Redis就很好解决，改了一个网上找来的工具类，请懂的大神帮我修改一下或者给点指导意见.我现在的想法就是加了synchronized关键字，但是总觉得还是有问题，非常感谢！

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

多谢大家的回答，我把代码改了一下，请大家帮我看看有没有问题了，主要是没做过java，我的处理方式就是：除了常量外，没有类成员变量，全部用参数和返回值传递，所有变量都在方法里申明 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; } }

Answer 2

没必要同步吧， 多个连接也没关系啊。 数据库自己有锁的。你也可以直接用连接池。