Spring JdbcTemplate Sample

The Spring JdbcTemplate is a robust utility to easily connect to a database and execute SQL queries. Under the covers, the JdbcTemplate makes use of the Java Database Connection (JDBC) API. The JdbcTemplate hides a lot of the complexity inherent with implementing a JDBC connection in your Java application.


JDBC API Complexity

Programatically speaking, implementing the JDBC API is not a simple or straight forward task. Challenges with the JDBC API from Java programmers will likely include:

  • Having to write a lot of code before and after executing any SQL query. Code that includes: creating database connection, statement, closing a result set, closing a connection, and more.
  • Having to perform exception handling code on the database logic.
  • Having to handle transactions.
  • Repetition of all the coding from one to another database logic is a tedious task and waste of valuable time.


Why Use Spring JdbcTemplate

The Spring JdbcTemplate eliminates all the problems and complexities of the JDBC API. JdbcTemplate provides methods to just write the queries directly, which saves a lot of time and resources.


Spring JDBC Options

The Spring Framework provides a couple of different options for JDBC Database access.

  • JdbcTemplate – The central class in the JDBC core package that simplifies the use of JDBC and helps to avoid common errors.
  • NamedParameterJdbcTemplate – Template class with a basic set of JDBC operations, allowing the use of named parameters rather than traditional ( ? ) placeholders.
  • SimpleJdbcTemplate – Depreciate since Spring 3.1
  • SimpleJdbcInsert – A multi-threaded, reusable object providing easy insert capabilities for a table.
  • SimpleJdbcCall – A multi-threaded, reusable object representing a call to a stored procedure or a stored function.


Spring Dependencies in pom.xml

<properties>
    <spring.version>5.0.5.RELEASE</spring.version>
</properties>
 
<dependencies>
 
  <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-core</artifactId>
     <version>${spring.version}</version>
  </dependency>
 
  <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-web</artifactId>
     <version>${spring.version}</version>
  </dependency>
 
  <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-webmvc</artifactId>
     <version>${spring.version}</version>
  </dependency>
 
   <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-jdbc</artifactId>
     <version>${spring.version}</version>
   </dependency>

</dependencies>


JdbcTemplate Class

The JdbcTemplate Class is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results.

This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the org.springframework.dao package.

Spring JdbcTemplate Sample

For our sample code here, you will need access to a Database, which could be Oracle, MySql, or H2 Database. You will need to create a new table in your database that looks like this.

create table widget(  

   id number(10),  
   info varchar(200),  
   size number(10)  

); 


Widget.java

This class contains 3 variables with one constructor and three get methods.

package com.jcd.spring.tutorials;

public class Widget {
	
	private int id;  
	private String name;  
	private int size;  
	
	public int getId() {
		return id;
	}
	
	public String getName() {
		return name;
	}
	
	public int getSize() {
		return size;
	}

}


WidgetDao.java

Class contains one property jdbcTemplate and three methods saveWidget(), updateWIdget and deleteWidget().

package com.jcd.spring.tutorials;

import org.springframework.jdbc.core.JdbcTemplate;

public class WidgetDao {
	
	private JdbcTemplate jdbcTemplate;  
	  
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
	    this.jdbcTemplate = jdbcTemplate;  
	}  
	  
	public int saveWidget(Widget w) {  
	    String query="insert into widget values('"+w.getId()+"','"+w.getName()+"','"+w.getSize()+"')";  
	    return jdbcTemplate.update(query);  
	}  
	
	public int updateWidget(Widget w) {  
	    String query="update widget set name='"+w.getName()+"',size='"+w.getSize()+"' where id='"+w.getId()+"' ";  
	    return jdbcTemplate.update(query);  
	}  
	
	public int deleteWidget(Widget w) {  
	    String query="delete from widget where id='"+w.getId()+"' ";  
	    return jdbcTemplate.update(query);  
	}  

}


WidgetContext.xml

The DriverManagerDataSource is used to contain the information about the database such as driver class name, connnection URL, username and password.

There are a property named datasource in the JdbcTemplate class of DriverManagerDataSource type. So, we need to provide the reference of DriverManagerDataSource object in the JdbcTemplate class for the datasource property.

Here, we are using the JdbcTemplate object in the WidgetDao class, so we are passing it by the setter method but you can use constructor also.

    <?xml version="1.0" encoding="UTF-8"?>  
    <beans  
        xmlns="http://www.springframework.org/schema/beans"  
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
        xmlns:p="http://www.springframework.org/schema/p"  
        xsi:schemaLocation="http://www.springframework.org/schema/beans   
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">  
      
    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />  
    <property name="url" value="jdbc:oracle:thin:@localhost:your_port:your_sid" />  
    <property name="username" value="your_username" />  
    <property name="password" value="your_password" />  
    </bean>  
      
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
    <property name="dataSource" ref="ds"></property>  
    </bean>  
      
    <bean id="wdao" class="com.javatpoint.WidgetDao">  
    <property name="jdbcTemplate" ref="jdbcTemplate"></property>  
    </bean>  
      
    </beans>  


WidgetTest.java

This class gets the bean from the WidgetContext.xml file and calls the saveWidget() method. You can also call updateWidget() and deleteWidget() methods.

package com.jcd.spring.tutorials;

import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class WidgetTest {
	
	public static void main(String[] args) {  
		
	    ConfigurableApplicationContext appCon = new ClassPathXmlApplicationContext("WidgetConfig.xml");
	      
	    WidgetDao dao = (WidgetDao) appCon.getBean("wdao");  
	    
	    //Save Widget
	    int statusSave = dao.saveWidget(new Widget(1000,"BigWidget",100)); 	    
	    System.out.println("Save Status="+statusSave);  
	    
	    //Update Widget
	    int statusUpdate = dao.updateWidget(new Widget(1000,"BigWidget",100)); 	    
	    System.out.println("Update Status="+statusUpdate);  
	    
	    //Close the connection
	    appCon.close();
	}
}


Add a Comment

Your email address will not be published. Required fields are marked *