JDBC - Inserting an array variable into a PostgreSQL table

Question

I am trying to insert an array variable into the table. The code is shown below

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

class PostgreSQLJDBC {

    public static void main(String args[]) {
        Connection c = null;
        Statement stmt = null;
        Statement stmt1 = null;
        int id[] = new int[3];
        int no = 1;
        id[0] = 2;
        id[1] = 14;
        id[2] = 4;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection("jdbc:postgresql://localhost:5432/ass2",
                            "postgres", "post");
            c.setAutoCommit(true);
            System.out.println("Opened database successfully");
            stmt = c.createStatement();
            String sql1 = "INSERT INTO COMPANY (NO,ID) "
                    + "VALUES (7, id);";
            stmt1 = c.createStatement();
            stmt1.executeUpdate(sql1);
            stmt1.close();
            c.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Operation done successfully");
    }
}

This code compiles but gives an PSQLexception.

Could someone please help in fixing this

id is hard-coded in your query. Also consider using PreparedStatement with placeholders . Finally, you are creating two Statement objects but only using one . — Arnaud
– Arnaud, Commented Feb 6, 2018 at 13:13
Many errors on what you are showing: 1. your request is not parameterized; 2. you are not setting your id; 3. what is the type of the ID column of the Company table. From what I see, you will have to execute 1 sql query for each of the ID value. — DamCx
– DamCx, Commented Feb 6, 2018 at 13:16
No, it is an array field in the table "COMPANY". Just like INT or VARCHAR, this is INT[] — raj
– raj, Commented Feb 6, 2018 at 13:21

Youcef LAIDANI · Accepted Answer · 2018-02-06 13:42:01Z

7

Try to use Prepared Statement so you can use setArray like this :

But first of all you can't set int[] you have to convert it to Array, so you can use :

Integer[] id = {2, 14, 4};
Array array = connection.createArrayOf("INTEGER", id);

Then create your Prepared Statement and set the array :

String sql = "INSERT INTO COMPANY (NO, ID) VALUES (?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql);) {

    pstmt.setInt(1, 7);   // Set NO
    pstmt.setArray(2, array);  // Set ID

    pstmt.executeUpdate();  // Execute the query
}

Note : please avoid UPPER LETTERS in the name of tables and columns in PostgreSQL! This can makes some problems, instead your query should look like :

INSERT INTO company (no, id) VALUES (?, ?)

edited Feb 6, 2018 at 13:42

answered Feb 6, 2018 at 13:20

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

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

2 Comments

raj Over a year ago

incompatible types: int[] cannot be converted into Array.

raj Over a year ago

Yes, it finally worked with the edit.Spent a lot of time on this .Thanks @YCF_L.

leurer · Accepted Answer · 2024-08-09 13:57:38Z

In addition to the accepted answer:

According to these docs: https://jdbc.postgresql.org/documentation/head/arrays.html

it is possible to use some native java arrays as parameter for a prepared statement using the PreparedStatement.setObject method.

So if you are using a primitive int[] instead of Integer[] and setObject instead of setArray it will work as well.

int[] ids = {2, 14, 4};
String sql = "INSERT INTO TEST(id_array) VALUES (?)";
try (Connection con = dataSource.getDataSource(); 
     PreparedStatement statement = conn.prepareStatement(sql))
{
    statement .setObject(1, ids);  // setObject NOT setArray!
    statement .executeUpdate();
}

This is more conventient than calling the createArrayOf method. Especially if you are working with some higher level frameworks spring JDBCTemplate and want to insert String[] arrays.

Edit: New link to documentation: https://jdbc.postgresql.org/documentation/server-prepare/#arrays

Collectives™ on Stack Overflow

JDBC - Inserting an array variable into a PostgreSQL table

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related