Re: PostgreSQL types and Java types

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
>
>