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)
Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.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

  • Pingback: right here()

  • Pingback: http://makemoney.camkingz.com/()

  • Pingback: alkaline water()

  • Pingback: view more()

  • Pingback: water ionizer()

  • Pingback: loan payment plan()

  • Pingback: pay per day loan plans()

  • Pingback: electrician tool bag photos()

  • Pingback: useful site()

  • Pingback: house blue()

  • Pingback: bottled alkaline water()

  • Pingback: m tech plumbers()

  • Pingback: plumber requirements()

  • Pingback: locksmith miami()

  • Pingback: electricians backpack()

  • Pingback: electrician 92129()

  • Pingback: water ionizer loan()

  • Pingback: parking()

  • Pingback: stop parking()

  • Pingback: mobile porn movies()

  • Pingback: laan nu og her()

  • Pingback: Blue Coaster33()

  • 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

    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

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

  • 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

    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

    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

    why dont u use try catch block with jdbcTemplate

  • jaliya

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

  • kzk

    Thank you for easily understandable tutorial! Really helpful!

  • pam

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

  • http://www.mkyong.com 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

    thanks for tutorial.

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

    &lt;bean id=&quot;dataSource2&quot; 
             class=&quot;org.springframework.jdbc.datasource.DriverManagerDataSource&quot;&gt;
     
    		&lt;property name=&quot;driverClassName&quot; value=&quot;com.mysql.jdbc.Driver&quot; /&gt;
    		&lt;property name=&quot;url&quot; value=&quot;jdbc:mysql://localhost:8808/takaDB&quot; /&gt;
    		&lt;property name=&quot;username&quot; value=&quot;root&quot; /&gt;
    		&lt;property name=&quot;password&quot; value=&quot;123456&quot; /&gt;
    	&lt;/bean&gt;

    thanks

  • faraz

    Thanks for wonderful tutorial..

    Can we have same thing for Stored Procedure ?

  • Levan

    Thank you for the another compact and clear tutorial!

  • Surasin

    How to control a transaction here?

  • Pingback: Spring Tutorial()

  • 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 = 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