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