MySQL insert query not working in aws rds abruptly

Question

I have a java based web application in which am inserting a row in aws MySQL database.

The problem is that, after 1-2 hours, the code stops inserting the rows in the database and am not getting any sort of error in my log files.

The structure of the table is as below:

Now when am calling the servlet, am using this piece of code.

JSONObject result=t_s.ro(jc.getT_conn(), t,true); 

t is the json and true/false ia a boolean value according to my case.

Now inside jc.getT_conn() am using this code:

public static Connection getT_conn() throws ClassNotFoundException, JSONException {
        Connection c=null;
        if(t_conn==null)
        {
            c=rds_conn();
        }
        else
        {
            c=t_conn;
        }
        return c;
    }

Here t_conn is a global variable for that java file and rds_conn() returns me a new connection after creating it.

Now from t_s.ro class am calling a function which inserts the row into the database based on a condition, if that's satisfied.

Here is the code:

public static boolean dPOI(Connection conn,String d,String u,ArrayList<String> l,ArrayList<String> li) throws SQLException
        {
            long startTime=System.currentTimeMillis();
            System.out.println("Time for sql start is : "+System.currentTimeMillis());
            PreparedStatement stmt = null;
            boolean action=false;
            try {
                String sql="INSERT INTO `ce`.`cse`(`twsD`,`twsID`,`twsi`)VALUES(?,?,?)";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, u);
                stmt.setString(2, d);
                stmt.setString(3, l.toString()+"~"+li.toString());
                System.out.println(stmt.toString());
                action = stmt.execute();
                //conn.close();
            } catch (SQLException e) {
                //  handle sql exception
                System.out.println("SQL Exception");
                e.printStackTrace();
            }catch (Exception e) {
                // TODO: handle exception for class.forName
                System.out.println("Exception");
                e.printStackTrace();
            }

            stmt.close();
            long endTime=System.currentTimeMillis();
            System.out.println("Time taken inside sql Query is : "+(endTime-startTime));
            return action;
        }

Below is the log file which am getting.

Time for sql start is : 1486393105661

com.mysql.jdbc.JDBC42PreparedStatement@59037dda: INSERT INTO `ce`.`cse`(`twsD`,`twsID`,`twsi`)VALUES('Bana','2fdb0c926765','[\'FOM\', \'MONEY CENTER KOLA - BAORE\']~[83.80, 272.20]')

Time taken inside sql Query is : 1

Now if you can see, I am not getting any SQL exception or any other kind of exception. And moreover, the time taken is always 1 (when it stops inserting) otherwise it's somewhere between 20-25.

Moreover, thee auto increment ID always gets used up, by that what I mean is if the last row was inserted at ID 1, the subsequent query which I insert through MySQL workbench has an ID somewhere around 40 i.e if we assume that 39 of the remaining rows didn't get inserted.

Answer 1

Taking Mark B's comment as a starting point, I decided to create a connection pool which will be providing the connections.

Below is the code which I used:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbcp2.BasicDataSource;
public final class Database {
    private static final String SQL_EXIST = "show tables;";
    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        boolean exist = false;

        try (
            Connection connection = Database.getConnection();
            PreparedStatement statement = connection.prepareStatement(SQL_EXIST);
        )
        {


            try (ResultSet resultSet = statement.executeQuery()) {
                exist = resultSet.next();
            }
        }       

        System.out.println("Value is : "+ exist);
    }

    private static final BasicDataSource dataSource = new BasicDataSource();

    static {
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("CONNECTION_STRING");
        dataSource.setUsername("USERNAME");
        dataSource.setPassword("PASSWORD");
        dataSource.setMaxTotal(100);
    }

    private Database() {
        //
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

FIrst function was just for testing purpose.

Now after creating this Database class, just call Database.getConnection() whenever you need to get the connection. The connection pool will take care of providing you with a valid connection.

Correct me if am wrong.