I wanted to access my local postgresql database with JDBC and JPA in the J2SE environment. Refer to my previous post about setting up Postgres 8.3 on Ubuntu 8.04.
If you can’t tell, I’m using java. I’m compiling to java 5. I’m building my project with maven. Inside of a larger maven project which creates my ear file, I created a test project ‘runner’. This creates a jar.
runner/pom.xml
<project>
<modelVersion>4.0.0</modelVersion>
<groupId>org.myproject</groupId>
<artifactId>runner</artifactId>
<packaging>jar</packaging>
<name>JAR Runner</name>
<version>1.0</version>
<build>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.5</source>
<target>1.5</target>
</configuration>
</plugin>
</plugins>
</pluginManagement>
</build>
<dependencies>
<dependency>
<groupId>org.myproject</groupId>
<artifactId>util</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>8.3-603.jdbc4</version>
</dependency>
<dependency>
<groupId>javax.persistence</groupId>
<artifactId>persistence-api</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>ejb3-persistence</artifactId>
<version>1.0.2.GA</version>
</dependency>
<dependency>
<!--
Note this added other dependencies
- hibernate-commons-annotations-3.1.0.GA.jar
- slf4j-api.1.4.2.jar - exclude this because it is bunk and causes exceptions.
- hibernate-annotations-3.4.0.GA.jar
- antlr-2.7.6.jar
- commons-collections-3.1.jar
- dom4j-1.6.1.jar
- xml-apis-1.0.b2.jar
- jta-1.1.jar
- javassit-3.4.GA.jar
-->
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager
</artifactId>
<version>3.4.0.GA</version>
<exclusions>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
</exclusions>
</dependency>
<!--
The org.slf4j needed to be included because the one included in org.hibernate.ejb3-persistence did not work
-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.5.6</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.5.6</version>
</dependency>
</dependencies>
</project>
runner/src/main/resources/META-INF/persistence.xml file
The core of JPA uses the persistence.xml file to set up the database and entity mappings. I tried using Sun’s example but I found it to hard to configure. I know that hibernate provides a JPA implementation so I used that instead of the glassfish example. It was easy to add because it exists in the maven 2 repository. This persistence.xml is configured to talk to the postgresql 8.3 database. It also contains a mapping for my Entity.
<?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="pu1" transaction-type="RESOURCE_LOCAL">
<class>org.myproject.runner.MapEntity</class>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
<!-- <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/> -->
<property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
<property name="hibernate.connection.username" value="myuser"/>
<property name="hibernate.connection.password" value="mypassword"/>
<property name="hibernate.connection.url" value="jdbc:postgresql://localhost/mydb"/>
<property name="hibernate.max_fetch_depth" value="3"/>
<!-- cache configuration -->
<!-- This was blowing up -v
<property name="hibernate.ejb.classcache.org.hibernate.ejb.test.Item" value="read-write"/>
<property name="hibernate.ejb.collectioncache.org.hibernate.ejb.test.Item.distributors" value="read-write, RegionName"/>
-->
<!-- alternatively to <class> and <property> declarations, you can use a regular hibernate.cfg.xml file -->
<!-- property name="hibernate.ejb.cfgfile" value="/org/hibernate/ejb/test/hibernate.cfg.xml"/ -->
</properties>
</persistence-unit>
</persistence>
Our database is populated by some sql.
create table map (
pk_id int unique primary key,
width int,
height int,
name varchar(256),
last_updated timestamp
);
GRANT ALL ON map TO PUBLIC;
INSERT INTO map (pk_id, width, height, name, last_updated)
VALUES (1, 100, 200, 'test_map', CURRENT_TIMESTAMP);
MapEntity.java
Next, we have an entity to use against our database. It only maps one field, but this was just for testing purposes.
package org.myproject.runner;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="MAP")
public class MapEntity {
private long id;
@Id
@Column(name="PK_ID")
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
}
DatabaseAccess.java
Finally, we have a java class that can call the database via JDBC or JPA
package org.myproject.runner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
public class DatabaseAccess {
/**
public static void main(String[] args) {
testJdbc();
testJpa();
}
public static void testJdbc() {
String url = "jdbc:postgresql://localhost/mydb";
Properties props = new Properties();
props.setProperty("user","myuser");
props.setProperty("password","mypassword");
Connection conn = null;
try {
conn = DriverManager.getConnection(url, props);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from map");
while(rs.next()) {
long id = rs.getLong(1);
System.out.println("ID:" + id);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void testJpa() {
// Use persistence.xml configuration
EntityManagerFactory emf = Persistence.createEntityManagerFactory("pu1");
EntityManager em = emf.createEntityManager(); // Retrieve an application managed entity manager
// Work with the EM
MapEntity map = new MapEntity();
map.setId(5);
EntityTransaction transaction = em.getTransaction();
transaction.begin();
em.persist(map);
transaction.commit();
em.close();
emf.close(); //close at application end
// Create EntityManagerFactory for persistent unit named "pu1"
// to be used in this test
}
}
