[postgis-users] Postgis and javax.persistence / JPA

[Eduard Witteveen]

Hello list,

I currently have a database, which has postgis extensions and data. I really
would like to use JPA to offer me a abstractionlayer to the database. Can
someone give me help me with this or give me some pointers how i can achieve
this?

I included my code underneath.

For this i started with the table project:

> CREATE TABLE project
> (
>   projectid serial PRIMARY KEY,
>   filter text NOT NULL,
>   projectname text NOT NULL
> );
> SELECT AddGeometryColumn('project', 'extent', 4326, 'POLYGON', 2);
> CREATE INDEX idx_project_extent ON project USING gist (extent);
>

I started a new JPA project in eclipse and added the following jars:
- postgis-driver-1.0.jar
- postgis-jdbc-1.1.6.jar
- postgis-jdbc-jts-1.1.6.jar
- postgresql-8.40701.jar

I created a pojo-class which will be my interface to a record:
(Notice that i use PGgeometry instead of Polygon internally:
http://www.bastian-voigt.de/2009/01/31/using-postgis-geometry-objects-with-toplink-and-eclipselink/)

import javax.persistence.*;
> import org.postgis.PGgeometry;
> import org.postgis.Polygon;
> import java.io.Serializable;
>
> @Entity
> @Table(name="PROJECT")
> public class Project implements Serializable {
>     private static final long serialVersionUID = -3237770608972654083L;
>     public Project() {
>     };
>     public Project(long projectID) {
>         this.projectID = projectID;
>     };
>     @Id
>     @Column(name="PROJECTID")
>     protected long projectID;
>     public long getProjectID() {
>         return projectID;
>     }
>     public void setProjectID(long projectID) {
>         this.projectID = projectID;
>     }
>     @Column(name="PROJECTNAME")
>     protected String projectName;
>     public String getProjectName() {
>         return projectName;
>     }
>     public void setProjectName(String projectName) {
>         this.projectName = projectName;
>     }
>     @Column(name="FILTER")
>     protected String filter;
>     public String getFilter() {
>         return filter;
>     }
>     public void setFilter(String filter) {
>         this.filter = filter;
>     }
>     @Column(name="EXTENT")
>     protected PGgeometry extent;
>     public Polygon getExtent() {
>         if(extent == null) {
>             return null;
>         }
>         return (Polygon) extent.getGeometry();
>     }
>     public void setExtent(Polygon extent) {
>         this.extent = new PGgeometry(extent);
>     }
> }
>

Added PostGISCustomizer:

> import Project;
> import org.eclipse.persistence.config.SessionCustomizer;
> import org.eclipse.persistence.descriptors.RelationalDescriptor;
> import org.eclipse.persistence.mappings.DirectToFieldMapping;
> import org.eclipse.persistence.sessions.Session;
>
> public class PostGISCustomizer implements SessionCustomizer {
>     public void customize(Session s) throws Exception
>     {
>         RelationalDescriptor desc;
>         DirectToFieldMapping mapping;
>
>         desc = (RelationalDescriptor) s.getDescriptor(Project.class);
>         mapping = (DirectToFieldMapping)
> desc.getMappingForAttributeName("extent");
>         mapping.setConverter(null);
>     }
> }
>


Define everything in the persistence.xml:

> <?xml version="1.0" encoding="UTF-8"?>
> <persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="
> http://java.sun.com/xml/ns/persistence
> http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
>     <persistence-unit name="ProjectService"
> transaction-type="RESOURCE_LOCAL">
>         <class>Project</class>
>         <properties>
>             <!--  database connection -->
>             <property name="eclipselink.jdbc.driver"
> value="org.postgresql.Driver"/>
>             <property name="eclipselink.jdbc.url"
> value="jdbc:postgresql://localhost:5432/postgis"/>
>             <property name="eclipselink.jdbc.user" value="postgres"/>
>             <property name="eclipselink.jdbc.password" value="postgres"/>
>             <!--  customizer  -->
>             <!--   <property name="eclipselink.session.customizer"
> value="PostGISCustomizer"/> -->
>         </properties>
>     </persistence-unit>
> </persistence>
>

When i test the code:

import javax.persistence.*;
> import org.postgis.LinearRing;
> import org.postgis.Point;
> import org.postgis.Polygon;
>
> import java.sql.SQLException;
> import java.util.Collection;
> public class ProjectTest {
> public static void main(String[] args) throws SQLException {
>         EntityManagerFactory emf =
> Persistence.createEntityManagerFactory("ProjectService");
>         EntityManager em = emf.createEntityManager();
>         // create and persist an project
>         em.getTransaction().begin();
>         Project project = new Project();
>         project.setProjectName("projectname");
>         project.setFilter("filter");
>         LinearRing[] rings = new LinearRing[1];
>         Point[] points = new Point[2];
>         points[0] = new Point(1,1);
>         points[1] = new Point(-1, -1);
>         rings[0] = new LinearRing(points);
>         project.setExtent(new Polygon(rings));
>         em.persist(project);
>         em.getTransaction().commit();
>         em.close();
>         emf.close();
>     }
> }
>

This will fail on runtime with the database error:

> [EL Info]: 2009-10-21 14:29:35.046--ServerSession(3200426)--EclipseLink,
> version: Eclipse Persistence Services - 1.1.2.v20090612-r4475
> [EL Info]: 2009-10-21
> 14:29:35.375--ServerSession(3200426)--file:/.../build/classes/-ProjectService
> login successful
> [EL Warning]: 2009-10-21 14:29:42.234--UnitOfWork(9956648)--Exception
> [EclipseLink-4002] (Eclipse Persistence Services - 1.1.2.v20090612-r4475):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: org.postgresql.util.PSQLException: Unknown type null.
> Error Code: 0
> Call: INSERT INTO PROJECT (PROJECTID, EXTENT, PROJECTNAME, FILTER) VALUES
> (?, ?, ?, ?)
>     bind => [0, POLYGON ((1.0 1.0,-1.0 -1.0)), projectname, filter]
> Query: InsertObjectQuery(Project at 19ac2e3)
>

Eduard Witteveen
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091021/2e8859fa/attachment.html>