Spring JDBCTemplate Communications link failure during mass insert into DB

Question

It started with the error message in the topic. As I added connectionProperties to the DriverManagerDataSource, now I get

Exception in thread "main" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

This happens after a lot of successful selects and inserts (several thousands). What am I missing, what am I doing wrong? mysql Connector is 5.1.40, mysql Server is 5.5.35 and Spring Framework 4.0.1

Here is what I am doing:

private void doTheTransfer() {
String requestStr = "SELECT edv_nr, edv_var, protokoll FROM HeaderBlock order by edv_nr, edv_var;";

JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(getMySQLDriverManagerDatasource());

List<HeaderBlockValueObject> rows = jdbcTemplate.query( requestStr, new HeaderBlockRowMapper());
int iEDVNr;
int iEDVVar;
String sProtokoll;
String sqlEntries;
String sqlInsert;
List<EdvProtocolValueObject> existingEntries;
RowMapper myRowMapper = new EdvProtocolRowMapper(true);
for (HeaderBlockValueObject headerBlockValueObject : rows) {
    iEDVNr        = headerBlockValueObject.getEdvNr();
    iEDVVar       = headerBlockValueObject.getEdvVar();
    sProtokoll    = headerBlockValueObject.getProtokoll();

    if ( (iEDVNr > 0) && (sProtokoll != null) && (!sProtokoll.trim().isEmpty()) ){
        int iNewProtocolID;
        sqlEntries = "SELECT MIN(PROTOCOL_ID) FROM EDV_PROTOCOL"
                + " where edv_nr = " + iEDVNr
                + " and edv_var = " + iEDVVar;
        existingEntries = jdbcTemplate.query( sqlEntries, myRowMapper);
        if ( existingEntries == null || existingEntries.isEmpty()
                || existingEntries.get(0).getProtokoll_ID() > 0) {
            iNewProtocolID = -1;
        } else {
            iNewProtocolID = (int) existingEntries.get(0).getProtokoll_ID() - 1;
        }
        sqlInsert = "insert into edv_protocol set EDV_NR = " + iEDVNr
                + ", EDV_VAR = " + iEDVVar
                + ", PROTOCOL_ID = " + iNewProtocolID
                + ", DESCRIPTION = '" + sProtokoll + "'"
                + ";";
        try{
            jdbcTemplate.execute(sqlInsert);
            saveLogEntry(sqlInsert + " successful");
        } catch ( DataAccessException exception ) {
            saveLogEntry(sqlInsert + " failed");
            saveLogEntry( exception.getLocalizedMessage() );
        }
    }
}

}

public DriverManagerDataSource getMySQLDriverManagerDatasource(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setPassword("goforgold");
dataSource.setUrl("jdbc:mysql://localhost:3306/edvNewRelease");
dataSource.setUsername("root");
Properties connectionProperties = new Properties();
connectionProperties.setProperty("autoReconnect", "true");
connectionProperties.setProperty("maxActive", "750");
connectionProperties.setProperty("maxIdle", "30");
connectionProperties.setProperty("useUnicode", "true");
connectionProperties.setProperty("characterEncoding", "utf8");
connectionProperties.setProperty("validationQuery", "Select 1");
connectionProperties.setProperty("maxWait", "10000");
dataSource.setConnectionProperties(connectionProperties);
return dataSource;

}

Answer 1

Solved with using Tomcat JDBC Datasource (connection pooling) instead of DriverManagerDataSource (no connection pooling) How-to