Array value into database postgresql

Question

I have a set of array value, i want to insert into database but it comes out with the result that I don't want.

I wish my database can store like this:

id_code  | id_description
--------------------------- 
PO1      |  hello 
PO2      |  hi

but as I run the code, it store like this:

id_code        | id_description
-----------------------------------
[PO1,P02]      |  [hello, hi]

Here is the coding:

public void addID(IDInfo IDInformation) {
        try {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("INSERT INTO id (id_code, id_description)"
                            + "VALUES (?, ?)");
            preparedStatement.setString(1, Arrays.toString(IDInformation.getIDCode()));
            preparedStatement.setString(2, Arrays.toString(IDInformation.getIDDescription()));
            preparedStatement.executeUpdate();
            }
        }

        catch (SQLException e){
            e.printStackTrace();
        }
    }

Controller:

            String action = request.getParameter("action");
            IDInfo idInfo = new IDInfo();
            String[] Code = request.getParameterValues("idCode");
            String [] Describe = request.getParameterValues("idDescription");


            idInfo.setidCode(Code);
            idInfo.setidDescription(Describe);

How is this caused and how can I solve it?

JB Nizet · Accepted Answer · 2016-04-30 13:08:10Z

PreparedStatement preparedStatement = connection
   .prepareStatement("INSERT INTO id (id_code, id_description)"
                     + "VALUES (?, ?)");
preparedStatement.setString(1, Arrays.toString(IDInformation.getIDCode()));
preparedStatement.setString(2, Arrays.toString(IDInformation.getIDDescription()));
preparedStatement.executeUpdate();

Read the above code. What does it do? It executes a single request, creating a single row, containing two values.

What you want is two rows.

So you should execute the statement twice:

one with the first code and the first description as parameter
one with the second code and the second description as parameter

You're making your own task difficult by not using objects. Instead of having two arrays, one containing the codes, and one containing the descriptions, you should have a single array (or collection), containing objects. And each object would have a code and a description:

PreparedStatement preparedStatement = connection
   .prepareStatement("INSERT INTO id (id_code, id_description)"
                     + "VALUES (?, ?)");
for (Product product : products) {
    preparedStatement.setString(1, product.getCode());
    preparedStatement.setString(2, product.getDescription());
    preparedStatement.executeUpdate();
}

MSameer · Accepted Answer · 2016-04-30 15:37:15Z

The root cause as JB Nizet pointed out is your addID function. The following lines is preparing single db update statement and including all array elements.

//Arrays.toString(IDInformation.getIDCode())-> Returns  [PO1,P02] 
preparedStatement.setString(1, Arrays.toString(IDInformation.getIDCode()));
//Arrays.toString(IDInformation.getIDDescription())-> Returns  [hello, hi]
preparedStatement.setString(2, Arrays.toString(IDInformation.getIDDescription()));
preparedStatement.executeUpdate();

What you should do instead is ideally loop over all IDInfo's and prepare statements for each i.e.

public void addID(List<IDInfo> IDInfos) {
    try {
     for(IDInfo idInfo : IDInfos) {
      //Loop over all IdInfo & update db
      PreparedStatement preparedStatement = connection
                .prepareStatement("INSERT INTO id (id_code, id_description)"
                        + "VALUES (?, ?)");
      preparedStatement.setString(1, idInfo.getIDCode());
      preparedStatement.setString(2, idInfo.getIDDescription());
        preparedStatement.executeUpdate();
    }
   }
    catch (SQLException e){
        e.printStackTrace();
    }
}

The IDInfo here can be made a simple pojo:

public class IDInfo {

private String IDCode;

private String IDDescription;

public String getIDCode() {
    return IDCode;
}

public void setIDCode(String IDCode) {
    this.IDCode = IDCode;
}

public String getIDDescription() {
    return IDDescription;
}

public void setIDDescription(String IDDescription) {
    this.IDDescription = IDDescription;
} 
}

Hope this helps.

Collectives™ on Stack Overflow

Array value into database postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related