java連接mysql的線程安全問題

Question

我在網路上搜尋了N天，幾乎沒有關於線程安全的解決辦法，同樣的問題Redis就很好解決，改了一個網上找來的工具類，請懂的大神幫我修改一下或者給點指導意見.我現在的想法就是加了synchronized關鍵字，但總覺得還是...

给我你的怀抱 · Answer

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

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

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

使用單例,保證資料庫連線的唯一性
修改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;

/**
 * 功能：

 * 

 * 完整路径： com.singleton.MySQLUtil 

 * 创建日期： 2017年6月15日 上午10:42:49 

 * 
 * @author pfyangf

 * @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> 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;
    }

    /**
     * 功能：获取DB连接

     * 

     * @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> readData(String db, String sql) throws Exception {
        List> list = new ArrayList<>();
        int count;
        connectToDB(db);

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

            while (rs.next()) {
                Map 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

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

滿天的星座 · Answer

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