问用JDBC和c3p0对MySQL DB进行多线程写入
EN

Stack Overflow用户

提问于 2016-06-13 11:37:10

回答 2查看 588关注 0票数 2

我编写了一个DAO类，它允许ExecutorServices调用的多个线程写入MySQL DB。

编辑：--我正在使用c3p0创建一个JDBC。因此，每个新线程都将通过调用

DataBaseManager.getInstance().getConnection()

在执行时似乎存在随机并发问题，例如：

java.sql.SQLException: No value specified for parameter 1
at com.eanurag.dao.DataBaseManager.writeData(DataBaseManager.java:102)

我无法理解代码的所有问题。我应该同步整个writeData()吗？

public class DataBaseManager {

    private final static Logger logger = Logger.getLogger(DataBaseManager.class);

    private static volatile DataBaseManager dbInstance = null;

    private DataBaseManager() {
        cpds = new ComboPooledDataSource();
        try {
            cpds.setDriverClass("com.mysql.jdbc.Driver");
        } catch (PropertyVetoException e) {
            logger.error("Error in Initializing DB Driver class", e);
        }
        cpds.setJdbcUrl("jdbc:mysql://" + DB_HOST + "/" + DB_NAME);
        cpds.setUser(DB_USER);
        cpds.setPassword(DB_PASS);

        cpds.setMinPoolSize(MINIMUM_POOL_SIZE);
        cpds.setAcquireIncrement(INCREMENT_SIZE);
        cpds.setMaxPoolSize(MAXIMUM_POOL_SIZE);
        cpds.setMaxStatements(MAX_STATEMENTS);
    }

    public static DataBaseManager getInstance() {
        if (dbInstance == null) {
            synchronized (WorkerManager.class) {
                if (dbInstance == null) {
                    dbInstance = new DataBaseManager();
                }
            }
        }

        return dbInstance;
    }

    private ComboPooledDataSource cpds;

    private static final Integer MINIMUM_POOL_SIZE = 10;
    private static final Integer MAXIMUM_POOL_SIZE = 1000;
    private static final Integer INCREMENT_SIZE = 5;
    private static final Integer MAX_STATEMENTS = 200;

    private volatile Connection connection = null;
    private volatile Statement statement = null;
    private volatile PreparedStatement preparedStatement = null;

    private static final String DB_HOST = "localhost";
    private static final String DB_PORT = "3306";
    private static final String DB_USER = "root";
    private static final String DB_PASS = "";
    private static final String DB_NAME = "crawly";
    private static final String URL_TABLE = "url";


    public Connection getConnection() throws SQLException {
        logger.info("Creating connection to DB!");
        return this.cpds.getConnection();
    }

    public Boolean writeData(URL url) {
        StringBuffer writeDBStatement = new StringBuffer();
        writeDBStatement.append("insert into");
        writeDBStatement.append(" ");
        writeDBStatement.append(DB_NAME);
        writeDBStatement.append(".");
        writeDBStatement.append(URL_TABLE);
        writeDBStatement.append(" ");
        writeDBStatement.append("values (?,?,default)");

        Boolean dbWriteResult = false;

        try {
            connection = DataBaseManager.getInstance().getConnection();

                preparedStatement = connection.prepareStatement(writeDBStatement.toString());
                preparedStatement.setString(1, url.getURL());
                preparedStatement.setString(2, String.valueOf(url.hashCode()));
                dbWriteResult = (preparedStatement.executeUpdate() == 1) ? true : false;


            if(dbWriteResult){
                logger.info("Successfully written to DB!");
            }
        } catch (SQLException e) {
            logger.error("Error in writing to DB", e);
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return dbWriteResult;
    }


}

jdbc

java

mysql

multithreading

语音识别特惠，低至14.9元！

提供业界非常具有性价比的语音识别服务，超高识别准确率，适用多场景

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-06-13 11:52:25

这里发生了什么事？

public Connection getConnection() throws SQLException {
    logger.info("Creating connection to DB!");
    return this.cpds.getConnection();
}

也就是说，cpds.getConnection()是做什么的？当你打电话：

connection = DataBaseManager.getInstance().getConnection();

在这里，连接对象是应该是单例类的一个成员，但是对writeData()的每次调用都会用一个新的getConnection()调用覆盖它。getConnection()调用线程也不安全吗？

另外，为什么连接对象被声明为类成员，然后在每次调用writeData()时重写？在多线程环境中，存在的代码允许连接对象在调用getConnection()之前被另一个prepareStatement()调用覆盖，因为对writeData()的访问没有锁定。preparedStatement也一样。将它们移到writeData()方法中。

票数 2