Thread: PostgreSQL types and Java types
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
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.g.pavlov@gmail.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 > >
Hi Andy, Thank you very much for your reply! It's really helpful. Maybe the PostgreSQL-JDBC crew should consider providing detailed documentation about the data types... Regards MGP > 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.g.pavlov@gmail.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-convers > >ions.html I hope there is similar peace of documentation for PostgreSQL! > > > > thank you > > > > Regards > > MGP