java - 使用JDBC和c3p0在MySQL DB上进行多线程写入

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

编辑:我正在使用c3p0创建JDBC ConnectionPool。因此，每个新线程都将通过调用获得一个新的JDBC Connection

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


}

最佳答案

这是怎么回事

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()时被覆盖？在多线程环境中，由于未锁定对writeData()的访问，因此存在的代码允许在调用prepareStatement()之前立即用另一个getConnection()调用覆盖连接对象。与prepareStatement相同。将它们移到writeData()方法中。