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 1 2 3 4 5 6 7 8 9 10 11 12 13 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 1 2 3 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:

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> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 < 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) :

database.properties jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/JavaHonk jdbc.username=root jdbc.password=admin 1 2 3 4 jdbc . driver = com . mysql . jdbc . Driver jdbc . url = jdbc : mysql : //localhost:3306/JavaHonk jdbc . username = root jdbc . password = admin

dispather-servlet.xml:

dispatcher-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> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <? 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:

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); } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 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:

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); } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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

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); } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 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

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); } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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:

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); } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 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