Spring JdbcDaoSupport Example

Spring JdbcDaoSupport Example

In our previous tutorial you saw how to use JDBCTemplate. Today you will see how to use JdbcDaoSupport to make call to the database:

• Create table to MySQL database: Please use below script to create table and insert sample data for test to the MySQL database:

DROP TABLE person
GO
CREATE TABLE person (
    id int NOT NULL AUTO_INCREMENT,
	First_Name 	varchar(25) NULL,
	Last_Name  	varchar(25) NULL,
	Street_Name	varchar(25) NULL,
	City       	varchar(25) NULL,
	State      	varchar(25) NULL,
	Country    	varchar(25) NULL,
    PRIMARY KEY (id) 
	)
GO

•  Insert data script:

INSERT INTO person(FirstName, LastName, street, city, state, country) 
    VALUES('Java', 'Honk', 'John St.', 'NY', 'NY', 'USA')
GO

• Create maven project name: SpringJdbcDaoSupport. Below is final project structure:

• Class diagram:

• 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>SpringJdbcDaoSupport</groupId>
  <artifactId>SpringJdbcDaoSupport</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>SpringJdbcDaoSupport Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <properties>
		<junit.version>3.8.1</junit.version>
		<SpringVersion>4.0.6.RELEASE</SpringVersion>
		<spring-jdbc.version>4.0.6.RELEASE</spring-jdbc.version>
		<json.version>20140107</json.version>
		<jackson.version>1.9.10</jackson.version>
		<log4j.version>1.2.16</log4j.version>
		<servletAPI.version>2.5</servletAPI.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>${junit.version}</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>servlet-api</artifactId>
			<version>${servletAPI.version}</version>
		</dependency>
		<!-- Spring dependencies -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${SpringVersion}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${SpringVersion}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${SpringVersion}</version>
		</dependency>
		<!-- Spring and Transactions -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring-jdbc.version}</version>
		</dependency>

		<!-- Jackson JSON Mapper -->
		<dependency>
			<groupId>org.codehaus.jackson</groupId>
			<artifactId>jackson-mapper-asl</artifactId>
			<version>${jackson.version}</version>
		</dependency>
		<dependency>
			<groupId>org.json</groupId>
			<artifactId>json</artifactId>
			<version>${json.version}</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.29</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>${log4j.version}</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>SpringJdbcDaoSupport</finalName>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>1.6</source>
					<target>1.6</target>
				</configuration>
				<version>3.1</version>
			</plugin>			
		</plugins>
	</build>
</project>

• database.properties(Note: Please replace with your database information) :

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/JavaHonk
jdbc.username=root
jdbc.password=admin

•  dispather-servlet.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="
        http://www.springframework.org/schema/beans     
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc.xsd">

	<context:component-scan base-package="com.javahonk.controller" />
	<mvc:annotation-driven />
	<context:property-placeholder location="classpath:database/database.properties"/>
	
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${jdbc.driver}"></property>
		<property name="url" value="${jdbc.url}"></property>
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>

	<bean id="springJdbDaoSupportDAO" class="com.javahonk.dao.SpringJdbcDaoSupportDAOImpl">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	<bean id="springJdbDaoSupportService" class="com.javahonk.services.SpringJdbcDaoSupportServiceImpl" /> 
    
</beans>

• SpringJdbcDaoSupportController.java:

package com.javahonk.controller;

import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.javahonk.services.SpringJdbcDaoSupportService;

/**
 * @author javahonk
 *
 */
@Controller
@RequestMapping("service")
public class SpringJdbcDaoSupportController {
	
	@Autowired 
	SpringJdbcDaoSupportService springJdbDaoSupportService;
	
	private static final Logger logger = Logger.getLogger(SpringJdbcDaoSupportController.class);
	
	@RequestMapping(value = "/getPersonTable")
	public @ResponseBody List<Map<String, Object>> getPersonTable()  {		
		logger.debug("Entered List<Map<String, Object>> populateActivePSwapBasket()");
        return springJdbDaoSupportService.getPersonTable();
	}
	
	@RequestMapping(value = "/getPersonTableByName/{FirstName}")
	public @ResponseBody List<Map<String, Object>> getPersonTableByName(@PathVariable String FirstName)  {		
		logger.debug("Entered List<Map<String, Object>> populateActivePSwapBasket()");
        return springJdbDaoSupportService.getPersonTableByName(FirstName);
	}
	
}

• SpringJdbcDaoSupportService.java:

package com.javahonk.services;

import java.util.List;
import java.util.Map;

/**
 * @author javahonk
 *
 */
public interface SpringJdbcDaoSupportService {
	
	List<Map<String, Object>> getPersonTable();
	List<Map<String, Object>> getPersonTableByName(String firstName);
	
	
}

• SpringJdbcDaoSupportServiceImpl.java

package com.javahonk.services;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;

import com.javahonk.dao.SpringJdbcDaoSupportSDAO;

/**
 * @author javahonk
 *
 */
public class SpringJdbcDaoSupportServiceImpl implements SpringJdbcDaoSupportService{
	
	@Autowired
	SpringJdbcDaoSupportSDAO springJdbDaoSupportDAO;	

	@Override
	public List<Map<String, Object>> getPersonTable() {
		return springJdbDaoSupportDAO.getPersonTable();
	}

	@Override
	public List<Map<String, Object>> getPersonTableByName(String firstName) {
		return springJdbDaoSupportDAO.getPersonTableByName(firstName);
	}	

	
		

}

• SpringJdbcDaoSupportSDAO.java

package com.javahonk.dao;

import java.util.List;
import java.util.Map;

/**
 * @author javahonk
 *
 */
public interface SpringJdbcDaoSupportSDAO {
	
	List<Map<String, Object>> getPersonTable();
	List<Map<String, Object>> getPersonTableByName(String firstName);

}

• SpringJdbcDaoSupportDAOImpl.java:

package com.javahonk.dao;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.support.JdbcDaoSupport;


/**
 * @author javahonk
 *
 */
public class SpringJdbcDaoSupportDAOImpl extends JdbcDaoSupport implements SpringJdbcDaoSupportSDAO {
	
	@Override
	public List<Map<String, Object>> getPersonTable() {
		String sql = "SELECT * FROM person";
		return getJdbcTemplate().queryForList(sql);
	}

	@Override
	public List<Map<String, Object>> getPersonTableByName(String firstName) {
		String sql = "SELECT * FROM person where FirstName = ?";
		return getJdbcTemplate().queryForList(sql,firstName);
	}
	
}

• Download project and run on any server below is sample output:

• That’s it for Spring JdbcDaoSupport Example. To find more details on JdbcDaoSupport please visit spring official site here

 Download Project: SpringJdbcDaoSupport