Insert JSON Nested Arrays Into MySQL

Question

I facing the problem as following. I getting JSON input from user which contains multiple nested arrays and store into MySQL database. The problem is like I am having data with device_id 2, 3 and 4 as in JSON Input below but after execute the JSON in POSTMan using POST method, I only able to store the data of device_id 2, 3 and 4 is unable to save into the database. I tried with other amount of data and it happens the same.

JSON Input:

{
    "app_id":"1",
    "token":"1",
    "device":
    [
        {
            "device_id":"2",
            "setting_id":"2",
            "batch_id":"2",
            "time":"2017-02-07 16:00:00",
            "power":"2"
        },
        {
            "device_id":"3",
            "setting_id":"3",
            "batch_id":"3",
            "time":"2017-02-07 15:00:00",
            "power":"3"
        },
        {
            "device_id":"4",
            "setting_id":"4",
            "batch_id":"4",
            "time":"2017-02-07 14:00:00",
            "power":"4"
        }
    ]
}

Database Code:

public void setSPowerCons(String app_id, String token, ArrayList<PowerConsA> device){
    Connection conn = null;
    PreparedStatement prst = null;

    try{
        //Register JDBC driver
        Class.forName(JDBC_DRIVER);
        //Open a connection
        conn = DriverManager.getConnection(DB_URL);
        String SQL = "INSERT INTO powerConsumption"
                + "(app_id, token, device_id, setting_id, batch_id, time, power)" 
        + "VALUES(?, ?, ?, ?, ?, ?, ?)";
        prst = conn.prepareStatement(SQL);
        //Bind VALUES into parameters
        int j=0;
        prst.setString(1, app_id);
        prst.setString(2, token);
        for(PowerConsA powerconsa : device){
            for(int i=0; i<device.size()-1; i++){
                prst.setString(3, device.get(i).getDevice_id());
                prst.setString(4, device.get(i).getSetting_id());
                prst.setString(5, device.get(i).getBatch_id());
                prst.setString(6, device.get(i).getTime());
                prst.setString(7, device.get(i).getPower());
                prst.addBatch();
            }   
            if(j%1000==0||j==device.size()){
                prst.executeBatch();
            }
            j++;
        }
        prst.close();
        conn.close();
    }
    catch(Exception e){
        e.printStackTrace();
    }
    finally{
        try{
            if(prst != null){
                prst.close();
            }
        }
        catch(SQLException sqle){
            sqle.printStackTrace();
        }
        try{
            if(conn != null){
                conn.close();
            }
        }
        catch(SQLException sqle1){
            sqle1.printStackTrace();
        }
    }
}

Database:

Thanks for everyone that helps for this question and helps pointing out my mistake.

Answer 1

Two things:

1. Your loop variable counts not far enough:

   for(int i=0; i

should be

  for(int i=0; i<device.size(); i++){

to be called 3 times.

2. I think your counter for executing the batch is off:

This is what you have:

        if(j%1000==0||j==device.size()){
            prst.executeBatch();
        }
        j++;

This will...

• execute on j == 0
• it will not execute on j == 1
• it will not execute on j == 2
• it will never be called for j == 3 (as the loop has already exited)

Change it to this:

        ++j;
        if(j%1000==0||j==device.size()){
            prst.executeBatch();
        }

And then it should work