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

為了確保連接間資料獨立（非共享），我猜你想實現連接池：

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

稍微修改了下,可能會好一些,建議還是聽上面那哥們的,使用成熟的數據庫連接池,沒必要重複造輪子

• 使用單例,保證資料庫連線的唯一性

• 修改synchronized關鍵字的用法,提高效率

• 增加volatile 關鍵字,提高穩定性

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

沒必要同步吧， 多個連線也沒關係啊。
資料庫自己有鎖的。
你也可以直接用連接池。

Answer 4

多謝大家的回答，我把程式碼改了一下，請大家幫我看看有沒有問題了，主要是沒做過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;
    }
}