PostgreSQL JDBC example with Spring Transactions

Using PostgreSQL JDBC with Spring.

This article came about after a user filed an issue on the JDBC list

The specific issue is that I’m using SimpleJdbcCall.execute() to call the database and getting back a Jdbc4Array. When I then try to do something like Jdbc4Array.getArray() I get a SQL error that can be tracked down to the Jdbc driver trying to use a connection object which has already been closed by the Spring Framework.

The problem is that once you get the array back more work has to be done to get the values out of it. The following code is an example of how to use transactions with spring and PostgreSQL JDBC

	package ca.credativ.jdbc;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.context.ApplicationContext;
	import org.springframework.context.support.ClassPathXmlApplicationContext;
	import java.sql.Array;
	/**
	* Created by davec on 2014-04-16.
	*/
	public class SpringTransactionExample
	{
	public static void main(String []args)
	{
	ApplicationContext applicationContext = new ClassPathXmlApplicationContext("context.xml");
	try
	{
	PostgresArray postgresArray = (PostgresArray)applicationContext.getBean("postgresArrayImpl");
	String []strings = postgresArray.get() ;
	for (int i=0;i < strings.length;i++)
	{
	System.out.println(strings[i]);
	}
	}
	catch( Exception ex )
	{
	ex.printStackTrace();
	}
	}
	}

view raw SimpleTransactionExample.java hosted with ❤ by GitHub

Simple interface to get a value out of an array

	package ca.credativ.jdbc;
	/**
	* Created by davec on 2014-04-16.
	*/
	public interface PostgresArray
	{
	String[] get() throws Exception;
	}

view raw PostgresArray.java hosted with ❤ by GitHub

Implementation which does the actual work Note the @Transaction annotation on line 22, this is required to ensure that the connection is not closed after the first call on line 42 without this annotation spring would close the connection and the next line 43 would throw an exception. The constructor line 27 is required for spring to create the implementation and inject the datasource defined in the context

	package ca.credativ.jdbc;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.jdbc.core.SqlOutParameter;
	import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
	import org.springframework.jdbc.core.simple.SimpleJdbcCall;
	import org.springframework.jdbc.core.support.JdbcDaoSupport;
	import org.postgresql.jdbc4.Jdbc4Array;
	import org.springframework.stereotype.Repository;
	import org.springframework.transaction.annotation.Transactional;
	import javax.sql.DataSource;
	import java.sql.SQLException;
	import java.util.Map;
	import java.util.logging.Level;
	import java.util.logging.Logger;
	/**
	* Created by davec on 2014-04-16.
	*/
	@Transactional
	@Repository
	public class PostgresArrayImpl extends JdbcDaoSupport implements PostgresArray
	{
	@Autowired
	public PostgresArrayImpl(DataSource dataSource) {
	setDataSource(dataSource);
	}
	@Override
	public String [] get() throws Exception
	{
	SimpleJdbcCall call = new SimpleJdbcCall(this.getJdbcTemplate());
	call.withFunctionName("usecase01_get");
	call.withSchemaName("public");
	call.withoutProcedureColumnMetaDataAccess();
	call.declareParameters(new SqlOutParameter("testcolumn", java.sql.Types.ARRAY));
	MapSqlParameterSource map = new MapSqlParameterSource();
	Map<String, Object> result = call.execute(map);
	Jdbc4Array jdbc4Array = (Jdbc4Array)result.get("testcolumn");
	try {
	Object o = jdbc4Array.getArray();
	Logger.getLogger(PostgresArrayImpl.class.getName()).log(Level.SEVERE, null, o);
	return (String [])o;
	} catch (SQLException ex) {
	Logger.getLogger(PostgresArrayImpl.class.getName()).log(Level.SEVERE, null, ex);
	throw ex;
	}
	}
	}

view raw PostgresArrayImpl.java hosted with ❤ by GitHub

Spring context

	<?xml version="1.0" encoding="UTF-8"?>
	<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
	http://www.springframework.org/schema/context
	http://www.springframework.org/schema/context/spring-context-3.2.xsd
	http://www.springframework.org/schema/tx
	http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="org.postgresql.Driver"/>
	<property name="url" value="jdbc:postgresql://localhost:5432/usecase"/>
	<property name="username" value="postgres"/>
	<property name="password" value="########"/>
	</bean>
	<context:component-scan base-package="ca.credativ.jdbc" />
	<tx:annotation-driven transaction-manager="txManager"/>
	<!-- a PlatformTransactionManager is still required -->
	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<!-- (this dependency is defined somewhere else) -->
	<property name="dataSource" ref="dataSource"/>
	</bean>
	</beans>

view raw context.xml hosted with ❤ by GitHub

SQL to create function and data

	CREATE TABLE usecase01 (
	testcolumn text[][]
	);
	CREATE FUNCTION usecase01_get()
	RETURNS TABLE
	(
	testcolumn text[][]
	)
	AS $$
	BEGIN
	RETURN QUERY SELECT
	usecase01.testcolumn
	FROM
	usecase01;
	RETURN;
	END;
	$$ LANGUAGE plpgsql;
	INSERT INTO usecase01 VALUES('{"Use","Case","01"}');
	--SELECT * FROM usecase01_get();

view raw UseCase01.sql hosted with ❤ by GitHub

Code for this can be found here SpringTransactionExample
Thanks to Michael Miller for the initial code used in this example