Spring JdbcTemplate

JdbcTemplate of spring framework is a solution to run database queries from java code. When we use jdbc api, there is a lot of boiler-plate code that we need to write and write many times. This problem is solved by using JdbcTemplate. Let’s understand this with an example

1. Create a maven project and add following dependencies

<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.sks</groupId>
	<artifactId>spring-jdbc-template-example</artifactId>
	<version>0.0.1-SNAPSHOT</version>


	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>5.3.17</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>5.3.17</version>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc8</artifactId>
			<version>19.3</version>
		</dependency>
	</dependencies>

</project>

2. Create Book.java class

package com.sks;

public class Book {
	
	private int id;
	private String name;
	private int price;
	
	//getter and setter methods
	//toString method
	//constructors
	
	

}

3. Create bean mapping files and datasource entry in applicationContext.xml

For annotation based configuration

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
public class AppConfig {

    @Bean
    public DataSource dataSource1() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        dataSource.setUrl("jdbc:oracle:thin:@localhost:1521/xepdb1");
        dataSource.setUsername("singh");
        dataSource.setPassword("azad");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource1());
    }
    
    @Bean
    public BookDAO bookDAO() {
        BookDAO bookDAO = new BookDAO();
        return bookDAO;
    }
}

For xml based configuration

<?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="dataSource1" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
     	<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
     	<property name="url" value="jdbc:oracle:thin:@localhost:1521/xepdb1"/>
     	<property name="username" value="singh"/>
     	<property name="password"  value="azad"/>
     </bean>
     
     
     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     	<property name="dataSource" ref="dataSource1"/>
     </bean>
     
     
     <bean id="bookDAO" class="com.sks.BookDAO">
     	<property name="jdbcTemplate" ref="jdbcTemplate"/>
     </bean>
               
</beans>

4. Create BookDAO class

package com.sks;

import org.springframework.jdbc.core.JdbcTemplate;

public class BookDAO {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	//This method saves book record
	public boolean saveBookRecord(Book book) {
		
		boolean insertionResult = false;
		
		String query = "insert into Book values(" + book.getId() + ", '" + book.getName() + "', " + book.getPrice() + ")";
		
		int result = jdbcTemplate.update(query);
		
		if(result > 0) {
			System.out.println("The book record has been inserted successfully.");
			insertionResult = true;
		}
		else {
			System.out.println("The insertion is not successful.");
		}
		
		return insertionResult;
		
	}
}

5. Create main testing class for xml based configuration

package com.sks;

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

public class BookMain {
	
	
	public static void main(String[] args) {
		
		
		ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
		
		
		BookDAO bookDAO = (BookDAO)context.getBean("bookDAO");
		
		Book b1 = new Book(1, "Learn Java", 500);
		Book b2 = new Book(2, "Learn Hibernate", 400);
		Book b3 = new Book(3, "Learn Spring", 600);
		
		bookDAO.saveBookRecord(b1);
		bookDAO.saveBookRecord(b2);
		bookDAO.saveBookRecord(b3);
		
		System.out.println("Database transaction complete.");
		
		((ClassPathXmlApplicationContext)context).close();
		
	}
}

Testing class for annotation based configuration

package com.sks;

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

public class BookMain {
	
	
	public static void main(String[] args) {
		
		
		AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);
		
		
		BookDAO bookDAO = (BookDAO)context.getBean("bookDAO");
		
		Book b1 = new Book(1, "Learn Java", 500);
		Book b2 = new Book(2, "Learn Hibernate", 400);
		Book b3 = new Book(3, "Learn Spring", 600);
		
		bookDAO.saveBookRecord(b1);
		bookDAO.saveBookRecord(b2);
		bookDAO.saveBookRecord(b3);
		
		System.out.println("Database transaction complete.");
		
		context.close();
		
	}
}

Output :
The book record has been inserted successfully.
The book record has been inserted successfully.
The book record has been inserted successfully.
Database transaction complete.

6. Test the database, there should be 3 entries in the book table