Thread safety issues of java connection to mysql

Question

I have searched the Internet for N days, and there are almost no thread-safe solutions. The same problem can be easily solved with Redis. I changed it to a tool class found online. Please help me modify it or give me some advice. Guidance. My current idea is to add the synchronized keyword, but I always feel that there is still a problem. Thank you very much!

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

In order to ensure that the data between connections is independent (non-shared), I guess you want to implement Connection pool:

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

After a slight modification, it may be better. It is recommended to listen to the buddy above and use a mature database connection pool. There is no need to reinvent the wheel

• Use singleton to ensure the uniqueness of database connection

• Modify the usage of synchronized keyword to improve efficiency

• Added volatile keyword to improve stability

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

No need to synchronize, multiple connections don’t matter.
The database has its own lock.
You can also use the connection pool directly.

Answer 4

Thank you everyone for your answers. I changed the code a bit. Please help me see if there is any problem. The main reason is that I have never done Java. My way of handling it is: except for constants, there are no class member variables. All parameters and The return value is passed and all variables are declared in the method

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