Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL types and Java types

From: Andrew <archa(at)pacific(dot)net(dot)au>
To: mario(dot)g(dot)pavlov(at)gmail(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: PostgreSQL types and Java types
Date: 2008-03-07 22:52:19
Message-ID: 47D1C723.3050603@pacific.net.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi Mario,

As far as I'm aware there is no documentation around with an equivalent 
mapping, at least I've not seen one, but I'm a newbie to the Postgresql 
community myself.  The postgresql driver is an almost fully compliant 
JDBC 3.0 level driver with some JDBC level 4.0 features.  So other than 
the exclusions, http://jdbc.postgresql.org/todo.html, and the driver/db 
specific extensions, 
http://jdbc.postgresql.org/documentation/83/ext.html, you can pretty 
much derive the mappings, and if you get stuck, you can always jump into 
the source code of the driver.  Any mapping is two step: from db to 
java.sql.Types to java.  So you can get the java.sql.Types to java 
mapping from lots of online sources such as 
http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html#996858, 
or http://java.sun.com/javase/6/docs/api/java/sql/Types.html and the 
java.sql.Types to db mappings from the postgresql jdbc driver source 
code, http://jdbc.postgresql.org/download.html.

When using the driver directly with jdbc from Java, I haven't 
experienced any problems with a wide range of data types.  However 
Hibernate has been a whole different story.  Its postgresql dialect is 
somewhat deficient.  When attempting to do any reverse engineering, 
character types are mapped incorrectly to char[] and accordingly fail, 
while character varying gets correctly mapped to String.  The new uuid 
data type gets mapped to either Serializable, byte[], or in some cases 
to b[ of which there is no actual java type, all of which is also 
incorrect, but also indicates that there are at least 3 distinct paths 
through the Hibernate reverse engineering code to derive three different 
java types from the one database type, one of which isn't even a type at 
all.  So to get JPA/Hibernate working through the postgresql JDBC driver 
has taken some level of manual intervention and some database specific 
modifications in my code that limit the portability of the DAO layer.

Let me know if I have not addressed your question or if you are still 
struggling with mapping issues, not that I'll necessarily be able to assist.

Cheers,

Andy

mario(dot)g(dot)pavlov(at)gmail(dot)com wrote:
> Hi list,
> I'm sorry for asking this question, I'm sure it's been answered many times but 
> I didn't manage to find the answer anywhere.
> So the question is how do PostgreSQL data types map to Java data types ?
> I've found that MySQL answers this question very good: 
> http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-type-conversions.html
> I hope there is similar peace of documentation for PostgreSQL!
>
> thank you
>
> Regards
> MGP
>
>   


In response to

Responses

pgsql-jdbc by date

Next:From: mario.g.pavlovDate: 2008-03-08 12:42:02
Subject: Re: PostgreSQL types and Java types
Previous:From: mario.g.pavlovDate: 2008-03-07 19:04:58
Subject: PostgreSQL types and Java types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group