JAVA store string Array in postgres and retrieve a JSON data from it

Question

I'm getting into trouble with images paths storage in DB. If I pass a paths as string array and store it in text[] array column in PostgreSQL - I receiving a text value in response like

"item_id":44,"item_phone":"432432423423","item_price":67676,"item_descr":"iojgoewjgoiwejgiewjgojoij","item_images":"{8f8161d3a6bdf6ahrth8fd35725356.jpg,11136408_101535465482739704_1782611644_o.jpg,15032923164db54t54d7ee548.jpg.thumb.jpg}","item_title":"ojreogreoihIGJEI","item_email":"jfeiwojiejowegj@ijfe"

"item_images" array value is in " " in the response, so I receive it just as string value. But I want to receive it as JSON array/list

So, what is the better way to organize this images paths storage in DB to finally receive a data from it as a JSON list/array?

This is how it works now:

The model to receive a data from Post:

public class Item {
 public String title;
 public String descr;
 public String phone;
 public String email;
 public String images[];
 public int price;
}

This is how I store a values in DB:

 PreparedStatement statement=conn.prepareStatement("INSERT INTO goods (item_title, item_descr, item_email, item_phone, item_images, item_price)\n" +
 " VALUES ( ? , ? , ? , ? , ? , ? )");
 int index=1;
 statement.setInt(index++, item.condo_id);
 statement.setString(index++,item.title.trim());
 statement.setString(index++,item.descr.trim());
 statement.setString(index++,item.email.trim());
 statement.setString(index++,item.phone); //Assuming it is a String
 statement.setArray(index++, conn.createArrayOf("text", item.images));
 statement.setInt(index++,item.price); //Assuming it is a Double
 int rowsUpdated = statement.executeUpdate();

This is how I return this values in the form of JSONArray:

Statement st = conn.createStatement();
 ResultSet rs = st.executeQuery("query to select all data from DB");
 JSONArray jsonArray;
 jsonArray = convertToJSON(rs);
public static JSONArray convertToJSON(ResultSet resultSet) throws Exception {
 JSONArray jsonArray = new JSONArray();
 while (resultSet.next()) {
 int total_rows = resultSet.getMetaData().getColumnCount();
 JSONObject obj = new JSONObject();
 for (int i = 0; i < total_rows; i++) {
 obj.put(resultSet.getMetaData().getColumnLabel(i + 1)
 .toLowerCase(), resultSet.getObject(i + 1));
 }
 jsonArray.put(obj);
 }
 return jsonArray;
}

Answer 1

You can try to modify convertToJSON() method like this

 String name = resultSet.getMetaData().getColumnLabel(i + 1)
 .toLowerCase();
 int type = resultSet.getMetaData().getColumnType(i);
 if (java.sql.Types.ARRAY == type) {
 obj.put(name, resultSet.getArray(i + 1));
 } else {
 obj.put(name, resultSet.getObject(i + 1));
 }

• user1935987: doesn't help, same output( btw, should be int type = resultSet.getMetaData().getColumnType(i+1);

Answer 2

Isn't your problem that you are making images a string first?

public class Item {

public String title;
public String descr;
public String phone;
public String email;
public String images[]; 
***************** public Array images[];
public int price;

}

• user1935987: Don't think so. Anyway i store it as Array successfully ` statement.setArray(index++, conn.createArrayOf("text", item.images));`