Spring & JdbcTemplate

In this blog, I will show you how to use Spring JdbcTemplate to access a PostgreSQL database. I am assuming you are already familiar with DAOs, JDBC, Spring, Maven, SQL, and PostgreSQL.

1. Assuming you already have a table in your database to access, create a DAO class. Here is an example:

package com.noushin.spring.db;

import java.util.UUID;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * This class handles accessing data in a PostgreSQL database.
 * 
 * @author nbashir
 *
 */
@Repository
public class MyDao {

   private JdbcTemplate jdbcTemplate;

   @Autowired
   public void setDataSource(DataSource dataSource) {
       this.jdbcTemplate = new JdbcTemplate(dataSource);
   }

   public UUID getId() {
      String sql = "SELECT id FROM my_schema.my_table WHERE my_ip_address=inet('10.1.1.1')";
      UUID id = jdbcTemplate.queryForObject(sql, UUID.class);
      return id;
   }
}

2. Create a class to test MyDao.

package com.noushin.spring.db;

import java.util.UUID;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration
public class MyDaoTest {

   @Autowired
   MyDao myDao;
   
   @Test
   public void testGetId() {
      UUID id = myDao.getd();
      System.out.println("Id: " + id);
   }
}

3. Create a test configuration file in ~/workspace/db/src/test/resources/com/noushin/spring/db called MyDaoTest-context.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-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <context:component-scan base-package="com.noushin.spring.db" />
    <context:property-placeholder location="jdbc.properties" />
    
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>
</beans>

4. Add jdbc.properties to ~/workspace/db/src/test/resources

jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://my.db.server:my.port/my.db.name
jdbc.username=my.username
jdbc.password=my.password

5. And last but not least, here is your pom file.

<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/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>
    <groupId>com.noushin.spring</groupId>
    <artifactId>db</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>db</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <commons-dbcp.version>1.2.2</commons-dbcp.version>
        <junit.version>4.10</junit.version>
        <postgres.version>9.1-901.jdbc4</postgres.version>
        <spring.version>3.2.4.RELEASE</spring.version>
    </properties>

    <dependencies>
        <!-- Test -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>org.springframework.test</artifactId>
            <version>${spring.version}</version>
            <scope>test</scope>
        </dependency>
        <!-- Spring JDBC -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>${commons-dbcp.version}</version>
        </dependency>
        <!-- Postgres Driver -->
        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>${postgres.version}</version>
        </dependency>
    </dependencies>

</project>

6. Populate your database with some data, and you should be able to run your test case.