Postgres Pro
Downloads
Home   >   mailing lists

Thread: mapping java objects with postgresql types

mapping java objects with postgresql types

From
Oussema Benjemaa
Date:
 Hi,

I want to pass  array of complex type from java into plpgsql function

I already tried using JDBC Array and Struct types,   but postgresql jdbc driver doesn't implement Struct yet so we weren't able to get thinks work

(in addition of the fact that. (Connection.createStruct is not implemented in postgres JDBC)

and actually as solution , i'm using PGObject to push the data to the database with some diffuclts with the syntax and this is  mainly for types composites having bytea datatype ..

but finally i  succeded to push data to the database with the usage of the  text representation of the array of the type composite .

But i'm strongly ,  looking for an alternative of the PGObject , to avoid passing complex structures (array of types) from jdbc with text representation.

ALso i didn't found documentation for passing such as complex structures including the the rules for text representation of nested types .

Could you please advice ...

Oussema BenJemaa
Senior DBA
 

Fw: mapping java objects with postgresql types

From
Oussema Benjemaa
Date:








 Hi,

I want to pass  array of complex type from java into plpgsql function

I already tried using JDBC Array and Struct types,   but postgresql jdbc driver doesn't implement Struct yet so we weren't able to get thinks work

(in addition of the fact that. (Connection.createStruct is not implemented in postgres JDBC)

and actually as solution , i'm using PGObject to push the data to the database with some diffuclts with the syntax and this is  mainly for types composites having bytea datatype ..

but finally i  succeded to push data to the database with the usage of the  text representation of the array of the type composite .

But i'm strongly ,  looking for an alternative of the PGObject , to avoid passing complex structures (array of types) from jdbc with text representation.

ALso i didn't found documentation for passing such as complex structures including the the rules for text representation of nested types .

Could you please advice ...

Oussema BenJemaa
Senior DBA
 


Re: Fw: mapping java objects with postgresql types

From
Andreas Joseph Krogh
Date:
På torsdag 22. januar 2015 kl. 17:00:25, skrev Oussema Benjemaa <ousema2003@yahoo.fr>:
 
 Hi,
 
I want to pass  array of complex type from java into plpgsql function
 
I already tried using JDBC Array and Struct types,   but postgresql jdbc driver doesn't implement Struct yet so we weren't able to get thinks work
 
(in addition of the fact that. (Connection.createStruct is not implemented in postgres JDBC)
 
and actually as solution , i'm using PGObject to push the data to the database with some diffuclts with the syntax and this is  mainly for types composites having bytea datatype ..
 
but finally i  succeded to push data to the database with the usage of the  text representation of the array of the type composite .
 
But i'm strongly ,  looking for an alternative of the PGObject , to avoid passing complex structures (array of types) from jdbc with text representation.
 
 
ALso i didn't found documentation for passing such as complex structures including the the rules for text representation of nested types .
 
Could you please advice ...
 
Oussema BenJemaa
Senior DBA
 
Try https://github.com/impossibl/pgjdbc-ng
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: Fw: mapping java objects with postgresql types

From
Oussema Benjemaa
Date:
i downloaded this JAR pgjdbc-ng-0.3-complete.jar ...
i'm asking if there is exemple for mapping java objects to postgresql ... with this api
 


Oussema Benjemaa 
Sr Oracle DBA / Trading Software Development
SunGard Financial Systems, Tunis
E-mail : ousema2003@yahoo.fr
Mobile : 0021622800497


On Thursday, 22 January 2015, 13:14, Andreas Joseph Krogh <andreas@visena.com> wrote:


På torsdag 22. januar 2015 kl. 17:00:25, skrev Oussema Benjemaa <ousema2003@yahoo.fr>:
 
 Hi,
 
I want to pass  array of complex type from java into plpgsql function
 
I already tried using JDBC Array and Struct types,   but postgresql jdbc driver doesn't implement Struct yet so we weren't able to get thinks work
 
(in addition of the fact that. (Connection.createStruct is not implemented in postgres JDBC)
 
and actually as solution , i'm using PGObject to push the data to the database with some diffuclts with the syntax and this is  mainly for types composites having bytea datatype ..
 
but finally i  succeded to push data to the database with the usage of the  text representation of the array of the type composite .
 
But i'm strongly ,  looking for an alternative of the PGObject , to avoid passing complex structures (array of types) from jdbc with text representation.
 
 
ALso i didn't found documentation for passing such as complex structures including the the rules for text representation of nested types .
 
Could you please advice ...
 
Oussema BenJemaa
Senior DBA
 
Try https://github.com/impossibl/pgjdbc-ng
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 


Attachment

Re: Fw: mapping java objects with postgresql types

From
Dave Cramer
Date:
Hi Oussema,

Please refer any questions regarding pgjdbc-ng to them

Thanks,


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 23 January 2015 at 09:36, Oussema Benjemaa <ousema2003@yahoo.fr> wrote:
i downloaded this JAR pgjdbc-ng-0.3-complete.jar ...
i'm asking if there is exemple for mapping java objects to postgresql ... with this api
 


Oussema Benjemaa 
Sr Oracle DBA / Trading Software Development
SunGard Financial Systems, Tunis
E-mail : ousema2003@yahoo.fr
Mobile : 0021622800497


On Thursday, 22 January 2015, 13:14, Andreas Joseph Krogh <andreas@visena.com> wrote:


På torsdag 22. januar 2015 kl. 17:00:25, skrev Oussema Benjemaa <ousema2003@yahoo.fr>:
 
 Hi,
 
I want to pass  array of complex type from java into plpgsql function
 
I already tried using JDBC Array and Struct types,   but postgresql jdbc driver doesn't implement Struct yet so we weren't able to get thinks work
 
(in addition of the fact that. (Connection.createStruct is not implemented in postgres JDBC)
 
and actually as solution , i'm using PGObject to push the data to the database with some diffuclts with the syntax and this is  mainly for types composites having bytea datatype ..
 
but finally i  succeded to push data to the database with the usage of the  text representation of the array of the type composite .
 
But i'm strongly ,  looking for an alternative of the PGObject , to avoid passing complex structures (array of types) from jdbc with text representation.
 
 
ALso i didn't found documentation for passing such as complex structures including the the rules for text representation of nested types .
 
Could you please advice ...
 
Oussema BenJemaa
Senior DBA
 
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
 




--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc


Re: Fw: mapping java objects with postgresql types

From
Andreas Joseph Krogh
Date:
På fredag 23. januar 2015 kl. 15:36:52, skrev Oussema Benjemaa <ousema2003@yahoo.fr>:
 
i downloaded this JAR pgjdbc-ng-0.3-complete.jar ...
i'm asking if there is exemple for mapping java objects to postgresql ... with this api
 
You should use 0.4: https://github.com/impossibl/pgjdbc-ng/releases
 
Simple example of returning an array of project name-id pairs for all parent-projects:
CREATE TYPE BigIntVarChar AS (f1 bigint, f2 varchar);

SELECT ARRAY(WITH RECURSIVE t AS (SELECT
                                      1 AS level,                                      p.entity_id,                                      p.id,                                      p.parent_id,                                      p.name
                                  FROM onp_crm_project p                                  WHERE p.entity_id = project.entity_id                                  UNION ALL
                                  SELECT
                                      t.level + 1,                                      c.entity_id,                                      c.id,                                      c.parent_id,                                      c.name
                                  FROM onp_crm_project c JOIN t ON c.id = t.parent_id)             SELECT
                     ROW (t.entity_id, t.name) :: BigIntVarChar             FROM t
             ORDER BY level DESC) AS project_parent_array
I use a class like this: 
import java.sql.{SQLOutput, SQLInput, SQLData}

class BigIntVarcharJdbcData extends SQLData {
   var id: Long = 0
   var name: String = null
   var sqlType: String = null

   override def getSQLTypeName: String = {      sqlType
   }
   override def writeSQL(stream: SQLOutput): Unit = {      stream.writeLong(id)      stream.writeNString(name)   }
   override def readSQL(stream: SQLInput, typeName: String): Unit = {      sqlType = typeName      id = stream.readLong()      name = stream.readString()   }
}

val typeMap = {   val h = new HashMap[String, Class[_]]()   h.put("bigintvarchar", classOf[BigIntVarcharJdbcData])   Collections.unmodifiableMap[String, Class[_]](h)
}

rs.getObject("project_parent_array", RowMapperHelper.typeMap).asInstanceOf[Array[BigIntVarcharJdbcData]]
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: Fw: mapping java objects with postgresql types

From
Kevin Wooten
Date:
The biggest failing of the implementation at this point is its documentation. Rest assured though, it is JDBC 4.1 compliant.  So the JDBC documentation regarding UDTs (using SQLOutput, SQLInput, etc) applies directly.

On Jan 23, 2015, at 7:36 AM, Oussema Benjemaa <ousema2003@yahoo.fr> wrote:

i downloaded this JAR pgjdbc-ng-0.3-complete.jar ...
i'm asking if there is exemple for mapping java objects to postgresql ... with this api
 


Oussema Benjemaa 
Sr Oracle DBA / Trading Software Development
SunGard Financial Systems, Tunis
E-mail : ousema2003@yahoo.fr
Mobile : 0021622800497


On Thursday, 22 January 2015, 13:14, Andreas Joseph Krogh <andreas@visena.com> wrote:


På torsdag 22. januar 2015 kl. 17:00:25, skrev Oussema Benjemaa <ousema2003@yahoo.fr>:
 
 Hi,
 
I want to pass  array of complex type from java into plpgsql function
 
I already tried using JDBC Array and Struct types,   but postgresql jdbc driver doesn't implement Struct yet so we weren't able to get thinks work
 
(in addition of the fact that. (Connection.createStruct is not implemented in postgres JDBC)
 
and actually as solution , i'm using PGObject to push the data to the database with some diffuclts with the syntax and this is  mainly for types composites having bytea datatype ..
 
but finally i  succeded to push data to the database with the usage of the  text representation of the array of the type composite .
 
But i'm strongly ,  looking for an alternative of the PGObject , to avoid passing complex structures (array of types) from jdbc with text representation.
 
 
ALso i didn't found documentation for passing such as complex structures including the the rules for text representation of nested types .
 
Could you please advice ...
 
Oussema BenJemaa
Senior DBA
 
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 


<Mail Attachment.png>
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Re: Fw: mapping java objects with postgresql types

From
Kevin Wooten
Date:
Also the driver does provide a tool pgjdbc-ng-udt for generating the Java UDT code; which is suggested by the JDBC docs.  Although, I believe most people find this route overkill if they are dealing with just a few small types.

On Jan 23, 2015, at 9:08 AM, Kevin Wooten <kdubb@me.com> wrote:

The biggest failing of the implementation at this point is its documentation. Rest assured though, it is JDBC 4.1 compliant.  So the JDBC documentation regarding UDTs (using SQLOutput, SQLInput, etc) applies directly.

On Jan 23, 2015, at 7:36 AM, Oussema Benjemaa <ousema2003@yahoo.fr> wrote:

i downloaded this JAR pgjdbc-ng-0.3-complete.jar ...
i'm asking if there is exemple for mapping java objects to postgresql ... with this api
 


Oussema Benjemaa  
Sr Oracle DBA / Trading Software Development
SunGard Financial Systems, Tunis
E-mail : ousema2003@yahoo.fr
Mobile : 0021622800497


On Thursday, 22 January 2015, 13:14, Andreas Joseph Krogh <andreas@visena.com> wrote:


På torsdag 22. januar 2015 kl. 17:00:25, skrev Oussema Benjemaa <ousema2003@yahoo.fr>:
 
 Hi,
 
I want to pass  array of complex type from java into plpgsql function
 
I already tried using JDBC Array and Struct types,   but postgresql jdbc driver doesn't implement Struct yet so we weren't able to get thinks work
 
(in addition of the fact that. (Connection.createStruct is not implemented in postgres JDBC)
 
and actually as solution , i'm using PGObject to push the data to the database with some diffuclts with the syntax and this is  mainly for types composites having bytea datatype ..
 
but finally i  succeded to push data to the database with the usage of the  text representation of the array of the type composite .
 
But i'm strongly ,  looking for an alternative of the PGObject , to avoid passing complex structures (array of types) from jdbc with text representation.
 
 
ALso i didn't found documentation for passing such as complex structures including the the rules for text representation of nested types .
 
Could you please advice ...
 
Oussema BenJemaa
Senior DBA
 
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 


<Mail Attachment.png>
-- 
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc