Posts Tagged ‘JDBC

31
Mar
11

Schema Maintenance Using DDLUtils

This page describes the process of creating in memory database and maintaining the schema using Apache DDLUtils within your web application. Since we will be using a pure java database it allows developers to rapidly develop and unit test database driven applications in isolation.

There is no need to maintain an external mysql or Oracle database. The database is self contained within the application. Once you are finished with development you would just remove the HSQLDB dependency from the pom file and change the connect string to connect with a live database.

The primary motivation for writing this was so that I could use this as a base for writing CRUD type web applications.

This page is part of a series of Rapid Development and prototyping tutorials.

Background

HyperSQL DataBase (HSQLDB) is a Java based in-memory database that allows you to develop and unit test your web application in isolation. It supports stored procedures (callable statements) as well.

Schema maintenance is a common problem for in-memory or sandbox databases. Changes to the data model need to be applied to your test database using DDL statements often during application development.

This page describes the process of using the Apache DDLUtils framework to incrementally apply the changes kept in an XML file each time the application restarts. Since the changes are incremental (ALTER TABLE etc..) and every effort is made to preserve the existing data.

The application below will create 2 tables when the application initializes. The DDL will be generated and executed from an xml file located in the classpath. The application initialization and shut-down are handled by writing ContextLoaderListeners.

Requirements

  • Maven
  • Basic understanding of the Spring Framework
  • Basic understanding of JDBC and SQL

Procedure

Start a new project

mvn archetype:generate -DarchetypeArtifactId=maven-archetype-webapp

groupId: com.test
artifactId: ddlutil

Answer the rest of the questions with defaults “Just hit the enter key”,

cd to the project base folder.

cd ddlutil

Since this is a web project maven2 does not create the java source folder.

Create missing folder
mkdir -p src/main/java/com/test

Modify the pom.xml file to look like this.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.test</groupId>
  <artifactId>ddlutil</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>ddlutil Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
		<groupId>org.apache.ddlutils</groupId>
		<artifactId>ddlutils</artifactId>
		<version>1.0</version>    
    </dependency>
		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>1.8.0.10</version>
		</dependency>
		    
		<dependency>
            <groupId>org.apache.geronimo.specs</groupId>
            <artifactId>geronimo-servlet_2.5_spec</artifactId>
            <version>1.2</version>
            <scope>provided</scope>
        </dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring</artifactId>
			<version>2.5.6</version>
		</dependency>
                
  </dependencies>
  <build>
    <finalName>ddlutil</finalName>
    
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.0.2</version>
				<configuration>
					<source>1.5</source>
					<target>1.5</target>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.mortbay.jetty</groupId>
				<artifactId>jetty-maven-plugin</artifactId>
				<version>7.0.0.v20091005</version>
				<configuration>
					<scanIntervalSeconds>2</scanIntervalSeconds>
				</configuration>
			</plugin>
		</plugins>    
  </build>
</project>

Spring configuration

The following contains a single bean that represents a un-pooled data source.

src/main/resources/applicationContext.xml

<?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"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
 
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
 
http://www.springframework.org/schema/context
 
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
 
<context:annotation-config/>
<context:component-scan base-package="com.test"/>
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
        <property name="url" value="jdbc:hsqldb:file:src/main/resources/db/testdb"/>
        <property name="username" value="sa"/>
        <property name="password" value=""/>
</bean>
</beans>

Servlet Context Listener

The following class listens to application life-cycle events like start-up and shut-down. It executes code to initialize and shut-down the database based on these events. Using listeners over Servlets is that the contextListeners will execute before any Servlets get a chance to run.

src/main/java/com/test/DBLifecycleContextListener.java

package com.test;

import java.io.InputStreamReader;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;

import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

/**
 * This listener initializes or gracefully shuts down the database based on
 * events from the web application.
 */
public class DBLifecycleContextListener implements ServletContextListener {
	private DataSource dataSource = null;
	private WebApplicationContext springContext = null;

	public void contextDestroyed(ServletContextEvent event) {
		SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);
		template.update("SHUTDOWN;");
		System.out.println("HSQLDB was shutdown. ");
	}

	public void contextInitialized(ServletContextEvent event) {
		springContext = WebApplicationContextUtils
				.getWebApplicationContext(event.getServletContext());
		dataSource = (DataSource) springContext.getBean("dataSource");

		Platform platform = PlatformFactory
				.createNewPlatformInstance(dataSource);

		Database database = new DatabaseIO().read(new InputStreamReader(
				getClass().getResourceAsStream("/ddl.xml")));

		System.out.println(database);

		platform.alterTables(database, false);

		System.out.println("HSQLDB is ready: " + platform);

	}

}

Database Schema (DDL)

The following file contains the schema that will be generated in the HSQLDB database. Any changes made to this file will result in “ALTER” table statements when the server restarts.

src/main/resources/ddl.xml

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="testdb">
  <table name="author">
    <column name="author_id"
            type="INTEGER"
            primaryKey="true"
            required="true"/>
    <column name="name"
            type="VARCHAR"
            size="50"
            required="true"/>
    <column name="organization"
            type="VARCHAR"
            size="50"
            required="false"/>
  </table>

  <table name="book">
    <column name="book_id"
            type="INTEGER"
            required="true"
            primaryKey="true"
            autoIncrement="true"/>
    <column name="isbn"
            type="VARCHAR"
            size="15"
            required="true"/>
    <column name="author_id"
            type="INTEGER"
            required="true"/>
    <column name="title"
            type="VARCHAR"
            size="255"
            required="true"/>

    <foreign-key foreignTable="author">
      <reference local="author_id" foreign="author_id"/>
    </foreign-key>  

    <index name="book_isbn">
      <index-column name="isbn"/>
    </index>
  </table>
</database>

Web Application Configuration

The following loads both the spring and the DBLifecycle ContextLoaderListeners. The ordering is critical as you want the spring application context to initialize before anything else.

src/main/webapp/WEB-INF/web.xml

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
	<display-name>Archetype Created Web Application</display-name>

	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>classpath:applicationContext.xml</param-value>
	</context-param>

	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<listener>
		<listener-class>com.test.DBLifecycleContextListener</listener-class>
	</listener>
</web-app>

Run the application

Return to the project main folder and run the following on the command line to start the jetty servlet engine.

mvn clean compile jetty:run

You should see the following at the console

Database [name=testdb; 2 tables]
Mar 31, 2011 10:12:38 PM org.apache.ddlutils.alteration.ModelComparator compare
INFO: Table author needs to be added
Mar 31, 2011 10:12:38 PM org.apache.ddlutils.alteration.ModelComparator compare
INFO: Table book needs to be added
Mar 31, 2011 10:12:38 PM org.apache.ddlutils.platform.SqlBuilder getForeignKeyName
WARNING: Encountered a foreign key in table book that has no name. DdlUtils will use the auto-generated and shortened name book_FK_author_id_author instead.
Mar 31, 2011 10:12:38 PM org.apache.ddlutils.platform.PlatformImplBase evaluateBatch
INFO: Executed 4 SQL command(s) with 0 error(s)
HSQLDB is ready: org.apache.ddlutils.platform.hsqldb.HsqlDbPlatform@10e9c592
2011-03-31 22:12:38.392:INFO::Started SelectChannelConnector@0.0.0.0:8080
[INFO] Started Jetty Server
[INFO] Starting scanner at interval of 2 seconds.

There is no need to open a browser to view the web application since all the work that was needed has already been done when the application initialized.

Shutdown the server by typing CTRL-C.

View the results

The server will initiate a graceful shut-down when you type CTRL-C at the console. The HSQLDB will write all data from the memory to a file located in the src/main/resources/db/ folder.

During runtime the data is kept in memory. Saving the data to a file-system allows the server to resume where it left of without loosing data when the server starts up again. We can use this to our advantage to peek into the file to see what is the state of the database.

After the first run the following file should look like this. Note the length of the AUTHOR.NAME field.

src/main/resources/db/testdb.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE BOOK(BOOK_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,ISBN VARCHAR(15) NOT NULL,AUTHOR_ID INTEGER NOT NULL,TITLE VARCHAR(255) NOT NULL)
CREATE INDEX BOOK_ISBN ON BOOK(ISBN)
CREATE MEMORY TABLE AUTHOR(AUTHOR_ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(50) NOT NULL,ORGANISATION VARCHAR(50))
ALTER TABLE BOOK ADD CONSTRAINT BOOK_FK_AUTHOR_ID_AUTHOR FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID)
ALTER TABLE BOOK ALTER COLUMN BOOK_ID RESTART WITH 0
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10

Altering the schema

Open up ddl.xml and increase the length of the name field of the “AUTHOR” table to VARCHAR(100) line 11 (highlighted).

1. Restart the jetty
2. shut it back down by typing CTRL-C.

Re-open the file below. You will notice that the auther.name field has been expanded to 100 characters.

src/main/resources/db/testdb.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE BOOK(BOOK_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,ISBN VARCHAR(15) NOT NULL,AUTHOR_ID INTEGER NOT NULL,TITLE VARCHAR(255) NOT NULL)
CREATE INDEX BOOK_ISBN ON BOOK(ISBN)
CREATE MEMORY TABLE AUTHOR(AUTHOR_ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(100) NOT NULL,ORGANISATION VARCHAR(50))
ALTER TABLE BOOK ADD CONSTRAINT BOOK_FK_AUTHOR_ID_AUTHOR FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID)
ALTER TABLE BOOK ALTER COLUMN BOOK_ID RESTART WITH 0
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10

That’s all for now!

Advertisements
03
Jun
10

Rapid Prototyping using HSQLDB

This page describes the process of setting up a simple database for the purpose of rapid development and prototyping.

Background

HSQLDB is a database is implemented purely in Java. It has a very small footprint and can run within the same JVM as the program being tested. Developing database applications using HSQLDB allows developers to rapidly develop and integration test their front end code or business logic without having a live database connection. Since all the data is stored and persisted locally, developers are free to work from locations that might not have database connectivity. For example outside the company firewall.

Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.

Requirements

  • Java 5
  • Maven
  • Understanding of JDBC and SQL
  • Basic understanding of Relational Databases

Start a new project

Start the project by creating one using a maven archetype.

mvn archetype:generate --batch-mode \
  -DarchetypeArtifactId=maven-archetype-quickstart \
  -DgroupId=com.test \
  -DartifactId=hsqldbTest

Once the project is created, then “cd to the project folder” and add the following depencency section in the pom.xml file

vi pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.test</groupId>
	<artifactId>hsqldbTest</artifactId>
	<packaging>jar</packaging>
	<version>20100603</version>
	<name>hsqldbTest</name>
	<url>http://maven.apache.org</url>
	
	<pluginRepositories>
		<pluginRepository> <!-- Ignore this repository. Its only used for document publication. -->
			<id>numberformat-releases</id>
			<url>https://raw.githubusercontent.com/numberformat/wordpress/master/20130213/repo/</url>
		</pluginRepository>
	</pluginRepositories>
		
	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>3.8.1</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>2.3.3</version>
			<scope>test</scope>
		</dependency>
	</dependencies>
<build>
		<plugins>
			<plugin> <!-- Ignore this plugin. Its only used for document publication. -->
				<groupId>github.numberformat</groupId>
				<artifactId>blog-plugin</artifactId>
				<version>1.0-SNAPSHOT</version>
				<configuration>
					<gitUrl>https://github.com/numberformat/wordpress/tree/master/${project.version}/${project.artifactId}</gitUrl>
				</configuration>
				<executions>
					<execution>
						<id>1</id>
						<phase>site</phase>
						<goals>
							<goal>generate</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
		</plugins>
</build>	
</project>

Regenerate the eclipse project by typing

mvn eclipse:clean eclipse:eclipse

Import the project into eclipse.

Configuration

There are 2 ways to configure the database.

  • Pure In memory database URL (InMemory): jdbc:hsqldb:mem:databaseName
  • Persistant URL (File system): jdbc:hsqldb:file:databaseFilename

The following is how it was done for this example:

  • File system database was chosen since we want the data to remain persistant after the program ends. The File system databse is s specified in the url.
  • The database files are placed in the location specified in the URL (src/main/resources/db/)
  • The database will be called “mydbname” (database and parent folders will be created if they don’t exist already)
  • The default credentials to use HSQLDB is “sa” and the password is blank. (Specified during JDBC connection)

Test the database

The following test case

  1. creates a table if one does not exit already
  2. inserts some data. Once data is inserted it
  3. selects the data and displays it to the console.

See complete output of the test case below

vi src/test/java/com/test/AppTest.java

package com.test;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;
 
public class AppTest extends TestCase {
    public AppTest(String testName) {
        super(testName);
    }
 
    public static Test suite() {
        return new TestSuite(AppTest.class);
    }
 
    public void testApp() throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        String url = "jdbc:hsqldb:file:src/main/resources/db/mydbname";
        String username = "sa";
        String password = "";
 
        Connection connection = DriverManager.getConnection(url, username,
                password);
 
        Statement statement = connection.createStatement();
        try {
            statement.executeQuery("select * from sec_edgar_filer;");
        } catch (SQLException ex) { // if no table then create one.
            statement
                    .execute("CREATE TABLE sec_edgar_filer ( cik VARCHAR(22) NOT NULL);");
        }
         
        try {
            statement.execute("insert into sec_edgar_filer values ( 'Hello World! "
                    + System.currentTimeMillis() + "' );");
            ResultSet resultset = statement
                    .executeQuery("select * from sec_edgar_filer;");
 
            int count = 0;
            while (resultset.next()) {
                System.out.println(resultset.getString(1));
                count++;
            }
            System.out.println("total rows returned: " + count);
             
        } finally {
            try {statement.execute("SHUTDOWN;");} catch (Exception ex) {}
            try {connection.close();} catch (Exception ex) {}          
        }
    }
}

Run the test case

At this point you have 2 options.

  1. Run the test case in eclipse using JUnit test runner
  2. Run the test by executing “mvn test” on the command line.

Either way you will see output similar to this…

Hello World! 1275621161302
Hello World! 1275621164539
Hello World! 1275621179581
Hello World! 1275621424099
total rows returned: 4

Be sure to Shutdown

All you need to do is to issue a SQL SHUTDOWN command to instruct HSQLDB to initiate a shutdown procedure. This is an important step to allow the data in memory to be saved to the disk.

You will notice that 2 files have been created in the src/main/resources/db directory.

  • mydbname.properties – contains the settings for the database.
  • mydbname.script – contains the data for the tables

References

Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.
20
May
10

Calling Stored Procedures using Spring 2.5 SimpleJdbcCall

The following page describes the process of calling Stored Procedures using Spring 2.5 SimpleJdbcCall. In the examples below I will be using DB2 but you can use any database that supports stored procedures or functions.

Background

The SimpleJdbcCall was introduced in Spring 2.5. It’s goal is to make calling stored procedures as simple as possible. It tries to use JDBC meta-data as much as possible, and it allows the developers to override settings. Using any other class like “JdbcTemplate” or extending from “StoredProcedure” is considered “old school spring”. Update your skill set and read on.

Requirements

  • Java 5 or above
  • Maven 2 (installed and configured) (see right nav on how to install maven)
  • DB2 JDBC Drivers installed in the compile and runtime class path
  • DB2 Database Server with a stored procedure pre-written ready to be called

Create a new Project

Create a project using Maven archetype. Open up the command prompt and navigate to an empty directory.

mvn archetype:generate -DarchetypeArtifactId=maven-archetype-quickstart

groupId: com.test
artifactId: springStoredProcedure

Answer the rest of the questions with defaults “Just hit the enter key”

Open up the pom.xml file and make sure it looks like the one below. In most cases you can just copy and paste the one below into your own.

Configure the project for Database Connectivity

I tried to make the configuration as simple as possible however you would need to go out and download the DB2 Driver files manually and either install them in your maven local repository or have them in the class path when you execute the program. In the example below I have installed them in the local repository by executing the following commands.

mvn install:install-file -DgroupId=com.ibm -DartifactId=db2jcc -Dversion=3.7.73 -Dpackaging=jar -Dfile=c:/db2jcc.jar

mvn install:install-file -DgroupId=com.ibm -DartifactId=db2jcc_license_cu -Dversion=3.7.73 -Dpackaging=jar -Dfile=c:/db2jcc_license_cu.jar

mvn install:install-file -DgroupId=com.ibm -DartifactId=db2jcc_license_cisuz -Dversion=3.7.73 -Dpackaging=jar -Dfile=c:/db2jcc_license_cisuz.jar

Add the following in the dependencies section of the pom.xml file.

pom.xml

	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring</artifactId>
		<version>2.5.6</version>
	</dependency>

	<dependency>
		<groupId>com.ibm</groupId>
		<artifactId>db2jcc</artifactId>
		<version>3.7.73</version>
	</dependency>

	<dependency>
		<groupId>com.ibm</groupId>
		<artifactId>db2jcc_license_cu</artifactId>
		<version>3.7.73</version>
	</dependency>

	<dependency>
		<groupId>com.ibm</groupId>
		<artifactId>db2jcc_license_cisuz</artifactId>
		<version>3.7.73</version>
	</dependency>	

Since we will be using annotations we will need to enable java5.

<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>2.0.2</version>
            <configuration>
            <source>1.5</source>
            <target>1.5</target>
            </configuration>
        </plugin>
    </plugins>
</build>

If you will be calling the stored procedure from a standalone application then you need to put the db2 JDBC driver files in the class path. In order to find the JDBC drivers for the IBM DB2 database just follow this link. If you are using a database other than DB2 you can download the drivers from the vendor’s website. You can use the following link to determine the URL value seen below.

src/main/resources/applicationContext.xml

<?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"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<context:annotation-config/>
<context:component-scan base-package="com.test"/>
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.ibm.db2.jcc.DB2Driver"/>
        <property name="url" value="jdbc:db2://servername:port/DBNAME"/>
        <property name="username" value="username"/>
        <property name="password" value="xxxxx"/>
</bean>
</beans>

Inject the Data-source into your Data Manager/DAO object.

Next we define a DataManager class that talks with the database.

src/main/java/com/test/CountryDataManagerImpl.java

package test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

@Repository("countryDataManager")
public class CountryDataManagerImpl {
 
    private SimpleJdbcCall countryProcedure;
 
    @Autowired
    @Required
    public void setDataSource(DataSource dataSource) {
...
    }
}

Calling stored procedures with output parameters

Define the stored procedure call like this:

    @Autowired
    @Required
    public void setDataSource(DataSource dataSource) {
        String procedureName = "QUALIFIER.PROCNAME";
 
        countryProcedure = new SimpleJdbcCall(dataSource)
        .withoutProcedureColumnMetaDataAccess()
        .withProcedureName(procedureName)
        .declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
        .declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR));
    }

It is possible to return values from stored procedures using output parameters. You can do this by calling the stored procedure like this…
The following is how its invoked.

    public void readOutParameters() {
        Map result = countryProcedure.execute();
        System.out.println("RETURNCODE: " + result.get("RETURNCODE"));
        System.out.println("RETURNMSG: " + result.get("RETURNMSG"));
    }

Parsing result sets

Define the stored procedure call like this:

    @Autowired
    @Required
    public void setDataSource(DataSource dataSource) {
        String procedureName = "QUALIFIER.SPNAME1";
 
		countryProcedure = new SimpleJdbcCall(dataSource)
		.withoutProcedureColumnMetaDataAccess()
		.withProcedureName(procedureName)
		.declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
		.declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))
		.returningResultSet("countries", new ParameterizedRowMapper<Country>() {
			public Country mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Country country = new Country();
				country.setId(rs.getInt(1));
				country.setName(rs.getString(2));
				return country;
			}
		});	
    }

The following is how its invoked.

    public void readResultSet() {
        Map result = procedureName.execute();
        System.out.println(result.get("countries"));
    }

Parsing Multiple Result sets

Define the stored procedure call like this:

    @Autowired
    @Required
    public void setDataSource(DataSource dataSource) {
        String procedureWithMultipleRS = "QUALIFIER.PROCNAME";
 
        procedureWithTwoResultSet = new SimpleJdbcCall(dataSource)
        .withoutProcedureColumnMetaDataAccess()
        .withProcedureName(procedureWithMultipleRS)
        .declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
        .declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))
        .returningResultSet("result1", new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                return rs.getString(1);
            }
        })
        .returningResultSet("result2", new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                return rs.getString(1);
            }
        });
    }

The following is how its invoked.

    public void readMultipleResultSets() {
        Map result = procedureWithTwoResultSet.execute();
        System.out.println("result 1: ");
        System.out.println(result.get("result1"));
 
        System.out.println("result 2:");
        System.out.println(result.get("result2"));
    }

Complete Source

src/main/java/com/test/CountryDataManagerImpl.java

package com.test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

@Repository("countryDataManager")
public class CountryDataManagerImpl {
	
	private SimpleJdbcCall countryProcedure;
	private SimpleJdbcCall procedureWithTwoResultSet;
	
	@Autowired
	@Required
	public void setDataSource(DataSource dataSource) {
		String procedureName = "QUALIFIER.PROCNAME";
		String procedureWithMultipleRS = "QUALIFIER.PROCNAME";
		
		countryProcedure = new SimpleJdbcCall(dataSource)
		.withoutProcedureColumnMetaDataAccess()
		.withProcedureName(procedureName)
		.declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
		.declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))
		.returningResultSet("countries", new ParameterizedRowMapper<Country>() {
			public Country mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Country country = new Country();
				country.setId(rs.getInt(1));
				country.setName(rs.getString(2));
				return country;
			}
		});		
		procedureWithTwoResultSet = new SimpleJdbcCall(dataSource)
		.withoutProcedureColumnMetaDataAccess()
		.withProcedureName(procedureWithMultipleRS)
		.declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
		.declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))
		.returningResultSet("result1", new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				return rs.getString(1);			
			}
		})		
		.returningResultSet("result2", new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				return rs.getString(1);			
			}
		});		
	}
	
	public void readOutParameters() {
		Map result = countryProcedure.execute();
		System.out.println("RETURNCODE: " + result.get("RETURNCODE"));
		System.out.println("RETURNMSG: " + result.get("RETURNMSG"));
	}
	
	public void readResultSet() {
		Map result = countryProcedure.execute();	
		System.out.println(result.get("countries"));
	}
	
	public void readMultipleResultSets() {
		Map result = procedureWithTwoResultSet.execute();
		System.out.println("result 1: ");
		System.out.println(result.get("result1"));
		
		System.out.println("result 2:");
		System.out.println(result.get("result2"));
	}
	
	public static void main(String args[]) {
		ApplicationContext context = new ClassPathXmlApplicationContext(
				new String[] { "applicationContext.xml" });
		CountryDataManagerImpl countryDataManager = (CountryDataManagerImpl) context
				.getBean("countryDataManager");
		countryDataManager.readOutParameters();
		countryDataManager.readResultSet();
		countryDataManager.readMultipleResultSets();
		
	}
}

src/main/java/com/test/Country.java

package com.test;

public class Country {
	private Integer id;
	private String name;
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Country [name=" + name + "]";
	}
}
14
Apr
10

Declarative Transactions Using Spring Framework

This page demonstrates very simple 1 resource (Local Transaction) commit and roll-back capabilities of the spring framework. The example on this page is heavy on practice and light on theory. For more information about spring transaction please check the resources section below for spring framework documentation.

Background

Based on the default behavior, when the application throws a run time exception any method that is defined to be transactional issues a roll-back on whatever resource is currently participating in the transaction. To define a method as transactional just put an @Transactional annotation before it.

Requirements

  • Java 5
  • MySQL installed and configured (not required if you just want to follow along)
  • Maven – (tutorial available on the right nav)
  • Basic understanding of Transactions

Implementation

The following example will demonstrate the transaction capabilities of the spring framework. Consider it a very basic “hello world” program for transactional code.

First step… Create the table… Its important that you specify the InnoDB engine. Otherwise the table will not support transactional capabilities.

Issue the following create statement in the mySQL database. If you don’t have access to a database to do this then just follow along with me below.

CREATE TABLE `test`.`test_table` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`data` TEXT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB

The Second step is to create a project using Maven archetype. Open up the command prompt and navigate to an empty directory.

mvn archetype:generate -DarchetypeArtifactId=maven-archetype-quickstart
groupId: com.test
artifactId: springTXTest

Answer the rest of the questions with defaults “Just hit the enter key”

Next, add a couple of entries in the dependencies section. Use the following pom.xml file as a guide. The 2 additional dependencies you need are the spring framework 2.5.5 and the mysql jdbc driver files. Both of these items are available using the maven central repository.

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelversion>4.0.0</modelversion>
  <groupId>com.test</groupId>
  <artifactId>springTXTest</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>springTXTest</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring</artifactId>
        <version>2.5.5</version>
    </dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.9</version>
	</dependency>
  </dependencies>
</project>

The applicationContext sets up the data source and transaction manager. Replace the database url, username, password “xxxx” with actual values.

src/main/resources/com/test/applicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xsi:schemalocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">

<!-- The following <span class="hiddenGrammarError" pre="following ">is used</span> for the @Repository, @Component, @Service, @Controller -->
	<context:component-scan base-package="com.test"></context:component-scan>

<!-- The following <span class="hiddenGrammarError" pre="following ">is used</span> for the @Autowired, @Required, @Resource etc... -->
	<context:annotation-config></context:annotation-config>

<!-- The following <span class="hiddenGrammarError" pre="following ">is used</span> for the @Transactional -->
	<tx:annotation-driven transaction-manager="txManager"></tx:annotation-driven>

<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	mysql.jdbc.Driver">
	xxxx:3306/test">
	<property name="username" value="xxxx"></property>
	<property name="password" value="xxxx"></property>
<!--<span class="hiddenSpellError" pre=""-->bean>

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>

The following is just an interface.

src/main/java/com/test/TestTransactionDataManager.java

package com.test;

public interface TestTransactionDataManager {
	public abstract Number writeData(String data);
}

The following DataManager inserts a row into the test_table using SimpleJdbcInsert. Since SimpleJDBCInsert is aware of any ongoing transactions the insert will happen in a transaction safe way. You could also use JdbcTemplate to achieve the same result.

src/main/java/com/test/TestTransactionDataManagerImpl.java

package com.test;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

/**
 * This data manager is used to test the transactional capabilities
 * of the spring framework. The code below is not marked as transactional
 * however it participates in one when called from a transactional method.
 */
@Repository("testTransactionDataManager")
public class TestTransactionDataManagerImpl implements TestTransactionDataManager {
	private SimpleJdbcInsert simpleInsert;

	@Required
	@Autowired
	public void setDataSource(DataSource dataSource) {
		simpleInsert = new SimpleJdbcInsert(dataSource).withTableName(
				"test_table").usingGeneratedKeyColumns("id");
	}
	/**
	 * Responsible for writing data to a transactional resource.
	 * @param data
	 */
	public Number writeData(String data) {
		Map<string, object=""> parameters = new HashMap<string, object="">(1);
        parameters.put("data", data);
        Number newId = simpleInsert.executeAndReturnKey(parameters);
        return newId.longValue();
	}
}

Just an interface…

src/main/java/com/test/TestTransactionModel.java

package com.test;

public interface TestTransactionModel {
	public abstract void writeDataWithTx();
	public abstract void writeDataWithOutTx();
	public abstract void writeDataWithMultipleCalls();
}

This is our Model class. The model layer is a natural place to define unit of work. Model methods are called from “control layer” in the MVC architecture. (Struts actions, SpringMVC Command, or Servlets) THERE SHOULD BE AT MOST ONE call to a transaction enabled model method to get a the “unit of work” behavior. Otherwise if you have more than one call to these methods and one succeeds and the other fails you will not get the “unit of work” behavior. Please keep this in mind… Only one call to a transactional method from the “control layer”.

src/main/java/com/test/TestTransactionModelImpl.java

package com.test;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service("testTransactionModel")
public class TestTransactionModelImpl implements TestTransactionModel {
	private TestTransactionDataManager testTransactionDataManager;

	/**
	 * This method will throw a runtime exception and the data will be rolled
	 * back.
	 */
	@Transactional  // remember @Transaction for public methods only!
	public void writeDataWithTx() {
		System.out.println("writing data within a transaction");
		Number number = testTransactionDataManager.writeData("writeDataWithTx");
		System.out.println("wrote item: " + number);
		System.out.println("issuing runtime exception in transactional method");
		throw new RuntimeException();
	}

	/**
	 * This method will throw a runtime exception and the data will still be
	 * persisted.
	 */
	public void writeDataWithOutTx() {
		System.out.println("writing data without a transaction");
		Number number = testTransactionDataManager.writeData("writeDataWithTx");
		System.out.println("wrote item: " + number);
		System.out.println("issuing runtime exception. you will still see this record in db.");
		throw new RuntimeException();
	}

	/**
	 * This method tests the capability of calling another method that is
	 * declared to be transactional where the second method throws an exception
	 * but this method still commits.
	 */
	@Transactional
	public void writeDataWithMultipleCalls() {
		try {
			writeDataWithTx();
		} catch(RuntimeException ex) {
			System.out.println("suppressing runtimeexception and committing anyway you should see this record in DB");
		}
	}
	@Autowired
	@Required
	public void setTestTransactionDataManager(
			TestTransactionDataManager testTransactionDataManager) {
		this.testTransactionDataManager = testTransactionDataManager;
	}
}

App.java#main() method is our entry point into the system. The doIt() method will call methods on the model layer. Catch any exceptions and continue processing the rest of the model methods.

src/main/java/com/test/App.java

package com.test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class App {
	private ApplicationContext context = new ClassPathXmlApplicationContext("com/test/applicationContext.xml");

	public void doIt() {
		TestTransactionModel model = (TestTransactionModel)context.getBean("testTransactionModel");

		try {
			model.writeDataWithOutTx();
		} catch (RuntimeException ex) { // do nothing }

		try {
			model.writeDataWithTx();
		} catch (RuntimeException ex) { // do nothing }

		try {
			model.writeDataWithMultipleCalls();
		} catch (RuntimeException ex) { // do nothing }
	}

	public App() {
		doIt();
	}
	public static void main(String[] args) {
		new App();
	}
}

Results

The following is the console output of the program.

writing data without a transaction
wrote item: 1
issuing runtime exception. you will still see this record in db.
writing data within a transaction
wrote item: 2
issuing runtime exception in transactional method
writing data within a transaction
wrote item: 3
issuing runtime exception in transactional method
suppressing runtimeexception and committing anyway you should see this record in DB

Checking the database you will see that only row id’s 1 and 3 got saved on the table.

Resources

That’s all for now!

19
Dec
09

database connectivity using spring framework

This page describes the process of setting up database connectivity using a spring framework based application. We are going to use the DriverManagerDataSource provided by the spring framework to get things started.

As stated by its javadoc:

Useful for test or standalone environments outside of a J2EE container, either as a DataSource bean in a corresponding ApplicationContext or in conjunction with a simple JNDI environment.

In other words this is a quick an dirty way of doing it. This is not production ready. In order to make it production ready you need to use database connection pools provided by your Web application container.

Driver Manager Names and Connection URL’s

In order to connect to databases you need one or more of the following items:

  • Driver Manager Class Name
  • URL
  • username
  • password

In some databases all this information is part of the URL in that case you only need a URL. It depends on the database vendor implementation.

Continue Reading about connection pools

Refer to the following page in order to setup a connection pool for quicker database access.

Database Connection Strings

The following is a list of popular databases and their connection strings.

mySQL Connection String

spring configuration

	<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://hostname:3306/dbname"/>
		<property name="username" value="username"/>
		<property name="password" value="xxxxx"/>
	</bean>

	<bean name="jdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
		<constructor-arg><ref bean="dataSource"/></constructor-arg>
	</bean>

Maven dependency

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.9</version>
</dependency> 

Sybase

Driver: com.sybase.jdbc.SybDriver
URL: jdbc:sybase:Tds:hostname:port/dbname

Oracle

Driver: oracle.jdbc.driver.OracleDriver
URL: jdbc:oracle:thin:myusername/mypassword@//localhost:1521/DBNAME
URL: jdbc:oracle:thin:@localhost:1521:MyDatabase

DB2

com.ibm.db2.jcc.DB2Driver
jdbc:db2://hostname:port/TEST

Microsoft SQL Server

Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver
URL: jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=northwind

Informix

URL: jdbc:informix-sqli://hostname:portnumber/MyDatabase:INFORMIXSERVER=MyServerName
Driver: com.informix.jdbc.IfxDriver

postgresql

URL: jdbc:postgresql:template1
Driver: org.postgresql.Driver

Derby

URL (Network): jdbc:derby://hostname:1527/sample;user=username;password=password
URL (FileSystem): jdbc:derby://hostname:1527/c:/directory/dbname;user=username;password=password
Driver: org.apache.derby.jdbc.ClientDriver

HSQLDB

URL (InMemory): jdbc:hsqldb:mem:aname
URL (File system): jdbc:hsqldb:file:aname
Driver: org.hsqldb.jdbcDriver

17
Dec
09

Querying a Database using Spring JdbcTemplate

This page describes the process of querying data from a database using The Spring Framework’s JdbcTemplate and SimpleJdbcTemplate.

One of the first things that catches our eye when we start using the spring framework is the JdbcTemplate.

The following is the DDL for the table we will be using to run Queries. It holds movie Genre’s. Its a simple table with 2 columns.

CREATE TABLE IF NOT EXISTS `GENRE` (
  `GNR_I` int(11) NOT NULL AUTO_INCREMENT,
  `GNR_X` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`GNR_I`)
)

JdbcTemplate

(legacy) This class is considered legacy. If you want to take advantages of the Java 5 features then use SimpleJdbcTemplate in the next section.

The following code queries the Genre table.

package test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component("genreDataManager")
public class GenreDataManagerImpl implements GenreDataManager {
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	@Autowired
	@Required
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Genre> getAllGenre() {
		List genreList = this.jdbcTemplate.query(
			    "select gnr_i, gnr_x from GENRE",
			    new RowMapper() {
			        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			            Genre genre = new Genre();
			            genre.setId(rs.getInt(1));
			            genre.setName(rs.getString(2));
			            return genre;
			        }
			    });
		return genreList;
	}
}

SimpleJdbcTemplate

This class takes advantage of varargs and autoboxing, and exposing only the most commonly required operations in order to simplify JdbcTemplate usage. Use this class instead of the plain old JdbcTemplate.

The following is the same thing but using SimpleJdbcTemplate

package test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

@Component("genreDataManager")
public class GenreDataManagerImpl implements GenreDataManager {
	private SimpleJdbcTemplate jdbcTemplate;

	public SimpleJdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	@Autowired
	@Required
	public void setJdbcTemplate(SimpleJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Genre> getAllGenre() {
		List<Genre> genreList = this.jdbcTemplate.<Genre>query(
			    "select gnr_i, gnr_x from GENRE",
			    new ParameterizedRowMapper<Genre>() {
			        public Genre mapRow(ResultSet rs, int rowNum) throws SQLException {
			            Genre genre = new Genre();
			            genre.setId(rs.getInt(1));
			            genre.setName(rs.getString(2));
			            return genre;
			        }
			    });
		return genreList;
	}
}

ApplicationContext.xml

The following file defines the jdbcTemplate and datasource. With these minimal settings you can successfully query a database.

applicationContext.xml

<?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"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<!-- The following is used for the @Autowired and @Required, @Resource etc... java.sql.DriverManager -->
	<context:annotation-config/>
	<context:component-scan base-package="test"/>
	<!-- This is a quick and dirty way to setup a datasource -->
	<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="org.gjt.mm.mysql.Driver"/>
		<property name="url" value="dburl"/>
		<property name="username" value="dbusername"/>
		<property name="password" value="password"/>
	</bean>

	<bean name="jdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
		<constructor-arg><ref bean="dataSource"/></constructor-arg>
	</bean>
</beans>
11
Nov
09

Configuring a connection pool in spring using DBCP

This page describes the process of setting up a connection pool using DBCP and spring framework.

This is good for small or prototypes applications that are running standalone and don’t have access to JNDI resources typically provided by a Java application server.

Include the following in your pom.xml file and regenerate the project to ensure you have dbcp in the classpath.

                <dependency>
                        <groupId>commons-dbcp</groupId>
                        <artifactId>commons-dbcp</artifactId>
                        <version>1.2.1</version>
                </dependency>

Inside the spring configuration file you should define the following.

        <bean id="dataSource"
                class="org.springframework.jdbc.datasource.DriverManagerDataSource">
                <property name="url" value="<put database connection url here>" />
                <property name="username" value="XXXXXX" />
                <property name="password" value="XXXXXXXX" />
                <property name="driverClassName" value="<database driver here>" />
        </bean>

        <bean id="pool" class="org.apache.commons.pool.impl.GenericObjectPool">
        <property name="minEvictableIdleTimeMillis"><value>300000</value></property>
        <property name="timeBetweenEvictionRunsMillis"><value>60000</value></property>
    </bean>

    <bean id="dsConnectionFactory" class="org.apache.commons.dbcp.DataSourceConnectionFactory">
        <constructor-arg><ref bean="dataSource"/></constructor-arg>
    </bean>

    <bean id="poolableConnectionFactory" class="org.apache.commons.dbcp.PoolableConnectionFactory">
        <constructor-arg index="0"><ref bean="dsConnectionFactory"/></constructor-arg>
        <constructor-arg index="1"><ref bean="pool"/></constructor-arg>
        <constructor-arg index="2"><null/></constructor-arg>
        <constructor-arg index="3"><null/></constructor-arg>
        <constructor-arg index="4"><value>false</value></constructor-arg>
        <constructor-arg index="5"><value>true</value></constructor-arg>
    </bean>

    <bean id="pooledDS" class="org.apache.commons.dbcp.PoolingDataSource" depends-on="poolableConnectionFactory">
        <constructor-arg><ref bean="pool"/></constructor-arg>
    </bean>

you can now inject “pooledDS” bean into any other spring enabled bean and use it just like any other dataSource.

This site is a collaborative effort! The complete text and sourcecode for this is available on GitHub. Corrections and enhancements are welcome, please make the change and submit a pull request in the comment area below.



Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 77 other followers

November 2017
S M T W T F S
« Oct    
 1234
567891011
12131415161718
19202122232425
2627282930  

Blog Stats

  • 842,358 hits