CODEWITHSUNDEEP
javapostgresql
user1935987
user1935987

Reputation: 3347

JAVA postrgresql insert String[] to text[] postrgresql cell

I'm trying to make item's images names storage in postgresql DB.

This is my query:

    Statement st = conn.createStatement();                
    String queryString = "INSERT INTO goods (item_title, item_descr, item_email, item_phone, item_images, item_price) " +
                                "VALUES ("+item.title.trim()+
                        "\',\'"+item.descr.trim()+
                        "\',\'"+item.email.trim()+
                        "\',\'"+item.phone+
                        "\',"+item.images+
                        ","+item.price+");";
int rowsUpdated = st.executeUpdate(queryString);

'item.images' defined as string array in model public String images[]; it is receives a value from JSON post, which is looks like this: "images":["6307839405_3wefb6ef051_o.jpg","8715662693_1c41ewf6f7d_o.jpg","15wef131f4574e383fb46b68.jpg"],"

item_images in DB defined as a 'text[]' array column.

So, i putting String array into the text[] column. Looks like should work?

But i`m getting this error:

ERROR: syntax error at or near "["
  Position: 189

So, if the problem is in the way how i do it - how do i properly need store this array in DB?

Upvotes: 0

Views: 576

Answers (2)

Nailgun
Nailgun

Reputation: 4179

INSERT for arrays in Postgres has different syntax. Please see the documentation.

Your result query would look like this: 

INSERT INTO goods ... ["6307839405_3wefb6ef051_o.jpg","8715662693_1c41ewf6f7d_o.jpg","15wef131f4574e383fb46b68.jpg"]

but should be:

INSERT INTO goods ... '{"6307839405_3wefb6ef051_o.jpg","8715662693_1c41ewf6f7d_o.jpg","15wef131f4574e383fb46b68.jpg"}'

Or:

INSERT INTO goods ... ARRAY['6307839405_3wefb6ef051_o.jpg','8715662693_1c41ewf6f7d_o.jpg','15wef131f4574e383fb46b68.jpg']

So you can change your code:

Statement st = conn.createStatement();                
String queryString = "INSERT INTO goods (item_title, item_descr, item_email, item_phone, item_images, item_price) " +
                                "VALUES ("+item.title.trim()+
                        "\',\'"+item.descr.trim()+
                        "\',\'"+item.email.trim()+
                        "\',\'"+item.phone+
                        "\',ARRAY"+item.images.replaceAll("\"", "'")+
                        ","+item.price+");";
int rowsUpdated = st.executeUpdate(queryString);

Upvotes: 1

crigore
crigore

Reputation: 400

My advice is not to build you SQL statements through concatenation of strings as you are doing. I would use the PreparedStatement class that brings methods to deal with so many different types, as arrays, and it brings additional benefits as protection from value injections from evil users.

For instance, your code will be like:

PreparedStatement statement=DataBaseConnector.getConnection().prepareStatement(
        "INSERT INTO goods (item_title, item_descr, item_email, item_phone, item_images, item_price)
        VALUES ( ? , ? , ? , ? , ? , ? )");
int index=1;
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++,item.images);
statement.setDouble(index++,item.price); //Assuming it is a Double
int rowsUpdated = statement.executeUpdate()

I hope it helps, Cris.

Upvotes: 1

Related Questions