Postgis and javax.persistence / JPA

[Eduard Witteveen-6]

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@19ac2e3)

Eduard Witteveen

 

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users