How to call a PostgreSQL stored procedure with a custom type parameter form java

Question

I have a PostgreSQL custom type:

CREATE TYPE seg_type AS
(
    _timestamp bigint,
    segment_type bigint,
    area_type bigint,
);

And I have a java class:

public class Segment
{
    private Long ts;
    private Long seg_type;
    private Long area_type;
}

In addition I have a stored procedure:

CREATE OR REPLACE FUNCTION SomeFunc(seg_type[])

I'm using postgresql-9.3-1102.jdbc41.jar to connect to my DB. And I want to be able to call my stored procedure from the java code with Segment[] like this:

String query = new String("{call SomeFunc(?)}");
CallableStatement stmt = dbConn.prepareCall(query);
stmt.setObject(1, Segment[], Type);

If this is the correct implementation so what should I put in the Type? or maybe the implementation is incorrect?

I had a quick look in the PgJDBC test suite and didn't find a test case for arrays of composite types. You might be able to use the standard Java array features (createArray and so on), but you'll probably have to implements PGobject for your type and handle the composite field packing/unpacking yourself. — Craig Ringer
– Craig Ringer, Commented Mar 30, 2015 at 0:17

Yaki Klein · Accepted Answer · 2015-04-15 22:14:19Z

A possible solution could be the following:

Create a function in the java Segment class that creates a string in the format of a postgreSQL type.

public class Segment
{
   private Long ts;
   private Long seg_type;
   private Long area_type;

   public pgType(){
      return String.format("\"(%d,%d,%d)\"",
         this.ts, this.seg_type, this.area_type)
   }
}

Note: for different data types you might need to format the string differently.

This will return a the following string "(ts, seg_type, area_type)" that represents the seg_type type in postgres.

now, you'll need to itirate through the Segment[] array to form a postgres array.

StringBuilder segTypeArray = new StringBuilder("{");
segTypeArray.append(segments.[0].getDBValue());  
for(int i = 1; i < segments.length; i++){
   segTypeArray.append("," + list.get(i).getDBValue());
}
segTypeArray.append("}");

This will create a postgres array of seg_type type {"(ts, seg_type, area_type)","(ts, seg_type, area_type)",.....}

When you create the query string, make sure to cast to seg_type[]

String query = new String("{call SomeFunc(?::seg_type[])}");
CallableStatement stmt = dbConn.prepareCall(query);
stmt.setObject(1, segTypeArray.toString());

Collectives™ on Stack Overflow

How to call a PostgreSQL stored procedure with a custom type parameter form java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related