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.

It should be for(int i=0; i<device.size(); i++) If device.size()=3, this will run the loop 3 times. Your loop runs the code for 2 times only, for the same case. — Riya
– Riya, Commented May 29, 2017 at 15:17
Hi. @Riya Your comment do help on solving my problem. Thanks. — Yih Wei
– Yih Wei, Commented May 30, 2017 at 1:18

Jan · Accepted Answer · 2017-05-29 15:27:48Z

1

Two things:

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.

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

edited May 29, 2017 at 15:27

answered May 29, 2017 at 15:20

Jan

13.9k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yih Wei Over a year ago

Hi. @Jan The first thing in the answer is sufficient to solve my problem. I tried the second thing in the answer and it update duplicate data on my table on single attempt. Please refer attached picture for more info. Picture

Collectives™ on Stack Overflow

Insert JSON Nested Arrays Into MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related