how to insert array in table using JDBC insert

Question

I am trying to insert values into a table using jdbc driver. In my table, one column is defined as array datatype.

Table as follows

    CREATE TABLE userType
    (
      id bigserial NOT NULL, // auto Inc
      type character varying,
      userRole bigint[] // array
    )

I am having an array in my code, which is converted from arraylist.

    List<Long> ids = new ArrayList<Long>();
    ids.add("1");
    ids.add("2");
    ids.add("3");
    ids.add("4");

    Long[] idArr = new Long[ids.size()];
    idArr = ids.toArray(idArr);

I am using the following code to insert the data in table.

    String querys = "insert into userType(type,fk_last_modified_by,userRole)"
                + " values ('Auto',1,"+ idArr+")";
        Connection connections = sessionFactory.getCurrentSession().connection();
        Statement stmts = connections.createStatement();
        int count =stmts.executeUpdate(querys);
        System.out.println("count---"+count);
        connections.close();

I am getting the following error while executing the above.

    org.postgresql.util.PSQLException: ERROR: syntax error at or near "["
      Position: 99
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:196)
        at com.mmf.controllers.UpdateReconcileController.save(com.mmf.controllers.UpdateReconcileController:123)

Then I just followed the solution provided by Jagdesh,

        String querys = "insert into userType(type,fk_last_modified_by,userRole)"
                + " values (?,?,?)";
        System.out.println(querys);
        Connection connections = sessionFactory.getCurrentSession().connection();
        CallableStatement stmts = connections.prepareCall(query);
        stmts.setString(1, "Auto");
        stmts.setInt(2, 1);
        stmts.setArray(3, connections.createArrayOf("integer", idArr));
        stmts.executeUpdate(querys);;
        connections.close();

Now I am getting the following error,

    org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
        at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:53)
        at org.postgresql.core.v3.SimpleParameterList.setLiteralParameter(SimpleParameterList.java:114)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.bindLiteral(AbstractJdbc2Statement.java:2172)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.setLong(AbstractJdbc2Statement.java:1227)
        at org.apache.commons.dbcp.DelegatingCallableStatement.setLong(DelegatingCallableStatement.java:252)

Can anyone point me where I am doing mistake?

Answer 1

I think you want to use a PreparedStatement, not CallableStatement. CallableStatements are used for calling a SQL function, and it has kinda weird syntax.

Answer 2

insert into reconcile_process (process_type,fk_last_modified_by,fk_bank_stmt_id) values ('Auto',1,'[Ljava.lang.Long;@b318fc5')

So you cannot just + a long type to a string.

Answer 3

Instead of above use PreparedStatement

String querys = "insert into reconcile_process (process_type,fk_last_modified_by,fk_bank_stmt_id)"
                + " values (?,?,?)";

 Connection connections = sessionFactory.getCurrentSession().connection();
        PreparedStatement pstmts = connections.createStatement();
    pstmts.SetString("Auto");
    pstmts.SetInt(1);
    pstmts.setArray(3, conn.createArrayOf("integer", idArr));
        pstmts.executeUpdate(querys);