問題描述

我在網(wǎng)上搜索了N天，幾乎沒有關(guān)于線程安全的解決辦法，同樣的問題Redis就很好解決，改了一個(gè)網(wǎng)上找來的工具類，請(qǐng)懂的大神幫我修改一下或者給點(diǎn)指導(dǎo)意見.我現(xiàn)在的想法就是加了synchronized關(guān)鍵字，但是總覺得還是有問題，非常感謝！

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

問題解答

為了保證連接間數(shù)據(jù)獨(dú)立（非共享），我猜你想實(shí)現(xiàn)連接池：

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);回答2：

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

使用單例,保證數(shù)據(jù)庫連接的唯一性

修改synchronized關(guān)鍵字的用法,提高效率

增加volatile 關(guān)鍵字,提高穩(wěn)定性

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>創(chuàng)建日期：</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; }}回答3：

沒必要同步吧， 多個(gè)連接也沒關(guān)系啊。 數(shù)據(jù)庫自己有鎖的。你也可以直接用連接池。

多謝大家的回答，我把代碼改了一下，請(qǐng)大家?guī)臀铱纯从袥]有問題了，主要是沒做過java，我的處理方式就是：除了常量外，沒有類成員變量，全部用參數(shù)和返回值傳遞，所有變量都在方法里申明

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