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

<?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 {
	
	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

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();
		
	}
}

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