I want to use Executors to speed up the import of a list of available data to the database
public static void main(String[] args) {
methodOpenDatabaseConnection();
ExecutorService newFixedThreadPool = Executors.newScheduledThreadPool(100);
for (long i = 0; i < 1000; i++) {
newFixedThreadPool.execute(new Runnable() {
@Override
public void run( {
methodSaveDataToDatabaseMysql();
}
});
}
newFixedThreadPool.shutdown();
newFixedThreadPool.shutdown();
newFixedThreadPool.awaitTermination(60, TimeUnit.SECONDS);
if (newFixedThreadPool.isShutdown()) {
methodCloseConnectionDatabase();
}
}
public void methodSaveDataToDatabaseMysql() {
try {
preparedStatement = connection.prepareStatement("INSERT INTO `demo`"
+ " (`name`,`name2`,`name3`) VALUES (?,?,?)");
preparedStatement.setString(1, "name1");
preparedStatement.setString(2, "name2");
preparedStatement.setString(3, "name3");
preparedStatement.executeUpdate();
} catch (SQLException ex) {
Logger.getLogger(WebsiteResourcesImpl1.class.getName()).log(Level.SEVERE, null, ex);
}
}
It generated an error,If I add synchronized in the save method.It will succeed but the speed does not improve
java.sql.SQLException: No value specified for parameter 3
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:569)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:537)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:527)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:512)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:480)
at com.mysql.cj.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2159)
I think there is no problem with the save statement, because it can still save data successfully but not stable in ExecutorService
Based upon this code preparedStatement = connection.prepareStatement...
then preparedStatement
is not thread safe.
Consider to make it local to the methodSaveDataToDatabaseMysql
method