Jason’s Ranting & Raving

Those who don’t read have no advantage over those who can’t.

Postgres 8.3, JDBC, JPA, and Hibernate example setup.

Posted by jaystile on April 1, 2009

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.

<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);

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; } }

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 { /**
  • @param args
  • /
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 } }

4 Responses to “Postgres 8.3, JDBC, JPA, and Hibernate example setup.”

  1. […] What? Now, that you have a database why don’t you do something with it? Like write a java applicatio… Possibly related posts: (automatically generated)Setup PostgreSQL in […]

  2. Craig said

    Thank you so much for this article. While I didn’t need the JDBC stuff the JPA/Hibernate information was helpful.

    The one suggestion I have is that the example class being persisted would have been better if it had more than one field (the id field).

    And my only “negative” comment is that some things weren’t explained enough but I’m also very new to Maven 2 and Hibernate so things I didn’t understand right away were things you just learn over time with using Maven and Hibernate.

    Thanks again.

  3. JP said

    <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/

    Shouldn’t the dialect be:

    • jaystile said

      The configuration files that were posted were working examples from my test jboss / postgres setup. Other dialects could work.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: