Spring + JdbcTemplate + JdbcDaoSupport examples

In Spring JDBC development, you can use JdbcTemplate and JdbcDaoSupport classes to simplify the overall database operation processes.

In this tutorial, we will reuse the last Spring + JDBC example, to see the different between a before (No JdbcTemplate support) and after (With JdbcTemplate support) example.

1. Example Without JdbcTemplate

Witout JdbcTemplate, you have to create many redundant codes (create connection , close connection , handle exception) in all the DAO database operation methods – insert, update and delete. It just not efficient, ugly, error prone and tedious.


	private DataSource dataSource;
		
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
		
	public void insert(Customer customer){
			
		String sql = "INSERT INTO CUSTOMER " +
				"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
		Connection conn = null;
			
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, customer.getCustId());
			ps.setString(2, customer.getName());
			ps.setInt(3, customer.getAge());
			ps.executeUpdate();
			ps.close();
				
		} catch (SQLException e) {
			throw new RuntimeException(e);
				
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

2. Example With JdbcTemplate

With JdbcTemplate, you save a lot of typing on the redundant codes, becuase JdbcTemplate will handle it automatically.


	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
		
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void insert(Customer customer){
			
		String sql = "INSERT INTO CUSTOMER " +
			"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
				 
		jdbcTemplate = new JdbcTemplate(dataSource);
				
		jdbcTemplate.update(sql, new Object[] { customer.getCustId(),
			customer.getName(),customer.getAge()  
		});
				
	}

See the different?

3. Example With JdbcDaoSupport

By extended the JdbcDaoSupport, set the datasource and JdbcTemplate in your class is no longer required, you just need to inject the correct datasource into JdbcCustomerDAO. And you can get the JdbcTemplate by using a getJdbcTemplate() method.


	public class JdbcCustomerDAO extends JdbcDaoSupport implements CustomerDAO
	{
	   //no need to set datasource here
	   public void insert(Customer customer){
			
		String sql = "INSERT INTO CUSTOMER " +
			"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
				 
		getJdbcTemplate().update(sql, new Object[] { customer.getCustId(),
				customer.getName(),customer.getAge()  
		});
				
	}

<beans xmlns="http://www.springframework.org/schema/beans"
	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-2.5.xsd">
	
	<bean id="dataSource" 
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">

		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/mkyongjava" />
		<property name="username" value="root" />
		<property name="password" value="password" />
	</bean>
	
</beans>

<beans xmlns="http://www.springframework.org/schema/beans"
	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-2.5.xsd">

	<bean id="customerDAO" class="com.mkyong.customer.dao.impl.JdbcCustomerDAO">
		<property name="dataSource" ref="dataSource" />
	</bean>
	
</beans>
Note
In Spring JDBC development, it’s always recommended to use JdbcTemplate and JdbcDaoSupport, instead of coding JDBC code yourself.

Download Source Code

Download it – Spring-JDBC-Example.zip (15 KB)

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
sawan
Guest
sawan

very good example,,,one thing dont understand why dont you have mentioned schema name in the datasource.

In DataSource there is only URL, username and password dont have schema name also in the insert query there is not a schema name.

devsena
Guest
devsena

String sql = “INSERT INTO CUSTOMER ” +
“(CUST_ID, NAME, AGE) VALUES (?, ?, ?)”;

getJdbcTemplate().update(sql, new Object[] { customer.getCustId(),
customer.getName(),customer.getAge()
});

Can we use this for select statement

Devi Prasad Patnaik
Guest
Devi Prasad Patnaik

//insert with named parameter
public void insertNamedParameter(Customer customer){

//not supported

}

Hello Young,

not supported means jdbcdaosupprt class doesnot support namedparameter

please explain young or some one else

Thanks & Regards,
Devi Patnaik

sathishkumar
Guest
sathishkumar

could anyone please help me

what is the need of above bean? if It is links with db then how?

could anyone please help me

what is the need of above bean? if It is links with db then how?

could anyone please help me

what is the need of above bean? if It is links with db then how?

sathishkumar
Guest
sathishkumar

could anyone please help me

what is the need of above bean? if It is links with db then how?

hemalait
Guest
hemalait

Hi,

I have a requirement where I need to use 2 different datasources.This is what I am trying to do in spring xml and DAOImpl, but its not working.

In the DAOImpl I am doing

public OpsysDAOImpl(DataSource dataSource1, DataSource dataSource2 ){
setDataSource(dataSource1);
setDataSource(dataSource2);
JdbcTemplate jdbcTemplate = getJdbcTemplate();
}

Saurav Jha
Guest
Saurav Jha

thank you so much … it would be great if u can mention what jar files are needed to be added

??? ???
Guest
??? ???

Really big thanks for this tuto :)

Krithika
Guest
Krithika

Hi,

Such a wonderful post.I have implemented this in code.
But I am not able to log the SQLs executed by the jdbctemplate.

Can you help me out ?

Thanks.

Mano
Guest
Mano

when i start the server it will through the following error
Class not found error :org/springframework/dao/DataAccessException
Im using spring 3.2.1

Marco
Guest
Marco

Can we create a GenericDao that extends the jdbcDaoSupport and then inyect it to other Daos ?

Marco Almeida
Guest
Marco Almeida

Hi, nice post. Can u tell me how to create a GenericDao that extends the jdbc template and then inyect it to other Daos. Ty

Alvin
Guest
Alvin

Hi,

I have followed this guide, but apparently I am using an Oracle database so instead of this:

I used this:


Everything is good, aside from the fact that when I call:
getJdbcTemplate().queryForList(sql);

the getJdbcTemplate() returns null.
How do I debug where is the error? I am pretty sure that the values I insert in the xml file are correct because I used those values in a normal jdbc.

Thanks in advance.
Regards,
Alvin

Ramesh
Guest
Ramesh

Hi,

Before using JdbcDaoSupport, my beans were autowired in the test class and all tests were passing. But after implementing JdbcDaoSupport, the beans are not getting injected to the test classed and my tests are not passing. If I use applicationContext and get the bean, I am getting the bean but as proxy.

Can you please let me know what could be wrong?

Thanks

demirelozgur
Guest
demirelozgur

why dont u use try catch block with jdbcTemplate

jaliya
Guest
jaliya

pleas
Provide the solution for jdbctemplate with to use mysql view s and stored procedures

kzk
Guest
kzk

Thank you for easily understandable tutorial! Really helpful!

pam
Guest
pam

Hi,
I am new to Spring. Your tutorials are really nice and quick to pick up.
Thanks

praveen
Guest
praveen

Hi sir how to write simple spring jdbc code to print student details into a table plz give me the code am new to spring

Taka
Guest
Taka

thanks for tutorial.

can i set up multiple dataSource?
can i create 2 datasources in xml file?
such as

<bean id="dataSource2" 
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:8808/takaDB" />
		<property name="username" value="root" />
		<property name="password" value="123456" />
	</bean> 

thanks

Mallesh
Guest
Mallesh

Hi Taka,
Thanks, it is nice post.
I got stuck at same thing. I have to work with multiple data sources. Could you please let me know if you are already done, thanks in advance.

faraz
Guest
faraz

Thanks for wonderful tutorial..

Can we have same thing for Stored Procedure ?

Levan
Guest
Levan

Thank you for the another compact and clear tutorial!

Surasin
Guest
Surasin

How to control a transaction here?

trackback
Spring Tutorial

[…] JDBC database operation.Spring + JDBC example Example to show how to integrate Spring and JDBC.JdbcTemplate + JdbcDaoSupport example Example to use Spring’s JdbcTemplate and JdbcDaoSupport classes to simplify the overall JDBC […]

Nommi
Guest
Nommi
hmmmmmmmmmmm nice usage of jdbcTemplate well wat is your CustomerDAO code can you show it… mostly i use samething like that in spring com.dps.midsurvey.dataaccess.orm.MaintainQuestionair com.dps.midsurvey.dataaccess.orm.campus com.dps.midsurvey.dataaccess.orm.Survey org.hibernate.dialect.SQLServerDialect true and in code of DAO class simply use hibernate session SessionFactory sessionFactory = null; Session session = null; BasicQuestionair basicQuestionair; /** * * @param sessionFactory */ public campusDAO(SessionFactory sessionFactory) { this.sessionFactory = sessionFactory; } /** * @param entity * BasicQuestionair entity to persist * @throws RuntimeException * when the operation fails */ public void createcampus(campus entity) { LoogingHelper.log(“Try to saving BasicQuestionair instance”, Level.INFO, null); try { session = getSession(); Transaction tx =… Read more »
Kunal
Guest
Kunal

Hi All ,

The corrected mysql query used for this program is as follows –

1. create database springJdbcTemplateExample;

2. use springJdbcTemplateExample

3. CREATE TABLE customer(CUST_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
NAME varchar(100) NOT NULL,
AGE smallint(5) unsigned NOT NULL,
PRIMARY KEY (CUST_ID))
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;;

4. Run the program…