CODEWITHSUNDEEP
javapostgresql
Shmuli
Shmuli

Reputation: 155

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

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?

Upvotes: 3

Views: 3136

Answers (1)

Yaki Klein
Yaki Klein

Reputation: 4366

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());

Upvotes: 3

Related Questions