[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: [postgis-users] Postgis and javax.persistence / JPA
From: Eduard Witteveen <eywitteveen () gmail ! com>
Date: 2009-10-21 12:33:46
Message-ID: d700a6e80910210533x1ce81fs603e3fdc68224f50 () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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
[Attachment #5 (text/html)]
Hello list,<br><br>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? <br> <br>I included my code underneath.<br><br>For this i started with the \
table project: <br><blockquote style="border-left: 1px solid rgb(204, 204, 204); \
margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;" class="gmail_quote">CREATE TABLE \
project<br> (<br> projectid serial PRIMARY KEY,<br> filter text NOT NULL,<br> \
projectname text NOT NULL<br>);<br>SELECT AddGeometryColumn('project', \
'extent', 4326, 'POLYGON', 2);<br>CREATE INDEX idx_project_extent ON \
project USING gist (extent);<br> </blockquote><div> <br>I started a new JPA project \
in eclipse and added the following jars:<br>- postgis-driver-1.0.jar<br>- \
postgis-jdbc-1.1.6.jar<br>- postgis-jdbc-jts-1.1.6.jar<br>- \
postgresql-8.40701.jar<br><br></div> <div>I created a pojo-class which will be my \
interface to a record:<br>(Notice that i use PGgeometry instead of Polygon \
internally: <a href="http://www.bastian-voigt.de/2009/01/31/using-postgis-geometry-obj \
ects-with-toplink-and-eclipselink/">http://www.bastian-voigt.de/2009/01/31/using-postgis-geometry-objects-with-toplink-and-eclipselink/</a> \
)<br> <br><blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt \
0pt 0pt 0.8ex; padding-left: 1ex;" class="gmail_quote">import \
javax.persistence.*;<br>import org.postgis.PGgeometry;<br>import \
org.postgis.Polygon;<br> import java.io.Serializable;<br><br>@Entity \
<br>@Table(name="PROJECT")<br>public class Project implements Serializable \
{ <br> private static final long serialVersionUID = -3237770608972654083L;<br> \
public Project() { <br> };<br> public Project(long projectID) {<br> \
this.projectID = projectID;<br> }; <br> @Id<br> \
@Column(name="PROJECTID")<br> protected long projectID;<br> public \
long getProjectID() {<br> return projectID; <br> }<br> public void \
setProjectID(long projectID) { <br> this.projectID = projectID; <br> }<br> \
@Column(name="PROJECTNAME")<br> protected String projectName;<br> \
public String getProjectName() {<br> return projectName; <br> }<br> \
public void setProjectName(String projectName) { <br> this.projectName = \
projectName; <br> }<br> @Column(name="FILTER")<br> protected String \
filter;<br> public String getFilter() {<br> return filter; <br> }<br> \
public void setFilter(String filter) { <br> this.filter = filter; <br> \
}<br> @Column(name="EXTENT")<br> protected PGgeometry extent;<br> \
public Polygon getExtent() {<br> if(extent == null) {<br> return \
null;<br> }<br> return (Polygon) extent.getGeometry();<br> }<br> \
public void setExtent(Polygon extent) {<br> this.extent = new PGgeometry(extent); \
<br> }<br>}<br></blockquote></div><br>Added PostGISCustomizer:<br><blockquote \
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; \
padding-left: 1ex;" class="gmail_quote"> import Project;<br>import \
org.eclipse.persistence.config.SessionCustomizer;<br>import \
org.eclipse.persistence.descriptors.RelationalDescriptor;<br>import \
org.eclipse.persistence.mappings.DirectToFieldMapping;<br>import \
org.eclipse.persistence.sessions.Session;<br> <br>public class PostGISCustomizer \
implements SessionCustomizer {<br> public void customize(Session s) throws \
Exception<br> {<br> RelationalDescriptor desc;<br> \
DirectToFieldMapping mapping;<br> <br> desc = (RelationalDescriptor) \
s.getDescriptor(Project.class);<br> mapping = (DirectToFieldMapping) \
desc.getMappingForAttributeName("extent");<br> \
mapping.setConverter(null);<br> }<br>}<br></blockquote><div><br> </div>Define \
everything in the persistence.xml:<br>
<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt \
0.8ex; padding-left: 1ex;" class="gmail_quote"><?xml version="1.0" \
encoding="UTF-8"?><br><persistence version="1.0" \
xmlns="<a href="http://java.sun.com/xml/ns/persistence">http://java.sun.com/xml/ns/persistence</a>" \
xmlns:xsi="<a href="http://www.w3.org/2001/XMLSchema-instance">http://www.w3.org/2001/XMLSchema-instance</a>" \
xsi:schemaLocation="<a \
href="http://java.sun.com/xml/ns/persistence">http://java.sun.com/xml/ns/persistence</a> \
<a href="http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd</a>"><br>
<persistence-unit name="ProjectService" \
transaction-type="RESOURCE_LOCAL"><br> \
<class>Project</class><br> <properties><br> \
<!-- database connection --> <br> <property \
name="eclipselink.jdbc.driver" \
value="org.postgresql.Driver"/><br> <property \
name="eclipselink.jdbc.url" \
value="jdbc:postgresql://localhost:5432/postgis"/><br> <property \
name="eclipselink.jdbc.user" value="postgres"/><br> \
<property name="eclipselink.jdbc.password" \
value="postgres"/><br> <!-- customizer --><br> \
<!-- <property name="eclipselink.session.customizer" \
value="PostGISCustomizer"/> --> <br> \
</properties> <br> \
</persistence-unit><br></persistence><br> </blockquote><div><br>When i \
test the code:<br><br><blockquote style="border-left: 1px solid rgb(204, 204, 204); \
margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;" class="gmail_quote">import \
javax.persistence.*;<br>import org.postgis.LinearRing;<br> import \
org.postgis.Point;<br>import org.postgis.Polygon;<br><br>import \
java.sql.SQLException;<br>import java.util.Collection;<br>public class ProjectTest \
{<br>public static void main(String[] args) throws SQLException {<br> \
EntityManagerFactory emf = \
Persistence.createEntityManagerFactory("ProjectService");<br> \
EntityManager em = emf.createEntityManager();<br> // create and persist an \
project<br> em.getTransaction().begin(); <br> Project project = new \
Project();<br> project.setProjectName("projectname");<br> \
project.setFilter("filter");<br> LinearRing[] rings = new \
LinearRing[1];<br> Point[] points = new Point[2];<br> points[0] = new \
Point(1,1);<br> points[1] = new Point(-1, -1);<br> rings[0] = new \
LinearRing(points);<br> project.setExtent(new Polygon(rings));<br> \
em.persist(project);<br> em.getTransaction().commit();<br> em.close();<br> \
emf.close();<br> }<br>}<br></blockquote><div> </div><div>This will fail on runtime \
with the database error:<br><blockquote style="border-left: 1px solid rgb(204, 204, \
204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;" class="gmail_quote"> [EL Info]: \
2009-10-21 14:29:35.046--ServerSession(3200426)--EclipseLink, version: Eclipse \
Persistence Services - 1.1.2.v20090612-r4475<br>[EL Info]: 2009-10-21 \
14:29:35.375--ServerSession(3200426)--file:/.../build/classes/-ProjectService login \
successful<br> [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<br>Internal Exception: \
org.postgresql.util.PSQLException: Unknown type null.<br> Error Code: 0<br>Call: \
INSERT INTO PROJECT (PROJECTID, EXTENT, PROJECTNAME, FILTER) VALUES (?, ?, ?, ?)<br> \
bind => [0, POLYGON ((1.0 1.0,-1.0 -1.0)), projectname, filter]<br>Query: \
InsertObjectQuery(Project@19ac2e3)<br> </blockquote><div><br>Eduard Witteveen \
<br></div> <br></div> </div>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic