Main Tutorials

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 Author

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

Comments

Subscribe
Notify of
29 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Jessica
1 month ago

this article was super helpful in clarifying these concepts for me, thank you so much!

Rahul Kumar
5 years ago

Hi Sir ,
Please upload Spring with JPA Integration.

Thanks

Rahul Kumar
5 years ago

Hi sir,
Please upload Spring with jpa integration with explanation

sawan
6 years ago

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
6 years ago

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
7 years ago

//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
7 years ago

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
7 years ago

could anyone please help me

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

hemalait
7 years ago

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
9 years ago

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

??? ???
10 years ago

Really big thanks for this tuto 🙂

Krithika
10 years ago

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
10 years ago

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
10 years ago

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

Marco Almeida
10 years ago

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
10 years ago

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
11 years ago

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
11 years ago

why dont u use try catch block with jdbcTemplate

jaliya
11 years ago

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

kzk
11 years ago

Thank you for easily understandable tutorial! Really helpful!

pam
11 years ago

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

praveen
11 years ago

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
11 years ago

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
10 years ago
Reply to  Taka

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
11 years ago

Thanks for wonderful tutorial..

Can we have same thing for Stored Procedure ?

Levan
12 years ago

Thank you for the another compact and clear tutorial!

Surasin
12 years ago

How to control a transaction here?

Nommi
14 years ago

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 = session.beginTransaction();
session.save(entity);
tx.commit();
LoogingHelper.log(“save successful”, Level.INFO, null);
} catch (HibernateException re) {
session.getTransaction().rollback();
LoogingHelper.log(“save failed”, Level.SEVERE, re);
throw re;
}
}

simply use of hibernate in spring

Kunal
10 years ago
Reply to  Nommi

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…