java连接mysql的线程安全问题

问题描述

我在网上搜索了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; }}

问题解答

为了保证连接间数据独立（非共享），我猜你想实现连接池：

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：

稍微修改了下,可能会好一些,建议还是听上面那哥们的,使用成熟的数据库连接池,没必要重复造轮子

使用单例,保证数据库连接的唯一性

修改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; }}回答3：

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

多谢大家的回答，我把代码改了一下，请大家帮我看看有没有问题了，主要是没做过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; }}