SimpleJdbcCall

 

SimpleJdbcCall

Steps to develop spring JDBC application by using SimpleJdbcCall

1) create new maven project.

2) add required dependencies in POM.xml file

Spring JDBC dependency, oracle/sql driver etc.!

3) create beans configured Java/xml file.

4) write business logic inside src/main/java

5) create procedure/ function for calling data from database

 

Spring JDBC provides multiple templates to interact with Databases i.e., JdbcTemplate, NamedParameterJdbcTemplate,SimpleJdbcInsert, SimpleJdbcCall

based on requirement we can use any above templates.

JdbcTemplate, NamedParameterJdbcTemplate are thread-safe i.e., they are single thread objects, so they allow single thread at a time.

A SimpleJdbcCall is a multi-threaded, reusable object representing a call to a stored procedure or a stored function.

It provides meta-data processing to simplify the code needed to access basic stored procedures/functions.

All you need to provide is the name of the procedure/function and a Map containing the parameters when you execute the call.

The names of the supplied parameters will be matched up with in and out parameters declared when the stored procedure was created. 

Java model class  

	package com.smarttechguides.bank;
	public class HdfcBank {
	private int accountNumber;
	private String customerName;
	private int depositeAmount;
	private double withdrawAmount;
	private double totalAmount;
	public int getAccountNumber() {
	return accountNumber;
	}
	public void setAccountNumber(int accountNumber) {
	this.accountNumber = accountNumber;
	}
	public String getCustomerName() {
	return customerName;
	}
	public void setCustomerName(String customerName) {
	this.customerName = customerName;
	}
	public int getDepositeAmount() {
	return depositeAmount;
	}
	public void setDepositeAmount(int depositeAmount) {
	this.depositeAmount = depositeAmount;
	}
	public double getWithdrawAmount() {
	return withdrawAmount;
	}
	public void setWithdrawAmount(double withdrawAmount) {
	this.withdrawAmount = withdrawAmount;
	}
	public double getTotalAmount() {
	return totalAmount;
	}
	public void setTotalAmount(double totalAmount) {
	this.totalAmount = totalAmount;
	}
	@Override
	public String toString() {
	return "HdfcBank [accountNumber=" + accountNumber + ", customerName=" + customerName + ", depositeAmount="
	+ depositeAmount + ", withdrawAmount=" + withdrawAmount + ", totalAmount=" + totalAmount + "]";
	}
	}

view raw HdfcBank.java hosted with ❤ by GitHub

 

Spring DAO class

	package com.smarttechguides.trnximpl;
	import com.smarttechguides.bank.HdfcBank;
	public interface BankTransactions {
	public HdfcBank getUser(int accNumbers);
	}

view raw BankTransactions.java hosted with ❤ by GitHub

DAO class implementation 

	package com.smarttechguides.trnximpl;
	import java.util.Map;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
	import org.springframework.jdbc.core.namedparam.SqlParameterSource;
	import org.springframework.jdbc.core.simple.SimpleJdbcCall;
	import org.springframework.stereotype.Component;
	import com.smarttechguides.bank.HdfcBank;
	@Component
	public class BankTrxnImplements implements BankTransactions {
	@Autowired
	SimpleJdbcCall simpleJdbcTemplate;
	public HdfcBank getUser(int accNumbers) {
	SimpleJdbcCall procedureName = simpleJdbcTemplate.withProcedureName("HDFC_BANK");
	SqlParameterSource inParams = new MapSqlParameterSource().addValue("ACC_NUMBER", accNumbers);
	Map<String, Object> execute = procedureName.execute(inParams);
	HdfcBank bank = new HdfcBank();
	bank.setCustomerName((String) execute.get("CUSTOMER_NAME"));
	bank.setDepositeAmount(Integer.parseInt(String.valueOf(execute.get("DEPOSITE_AMOUNT"))));
	bank.setWithdrawAmount(Integer.parseInt(String.valueOf(execute.get("WITHDRAW_AMOUNT"))));
	bank.setTotalAmount(Integer.parseInt(String.valueOf(execute.get("TOTAL_AMOUNT"))));
	return bank;
	}
	}

view raw BankTrxnImplements.java hosted with ❤ by GitHub

Java based configuration file  

	package com.smarttechguides.configurations;
	import javax.sql.DataSource;
	import org.springframework.context.annotation.Bean;
	import org.springframework.context.annotation.ComponentScan;
	import org.springframework.context.annotation.Configuration;
	import org.springframework.jdbc.core.simple.SimpleJdbcCall;
	import org.springframework.jdbc.datasource.DriverManagerDataSource;
	@Configuration
	@ComponentScan(basePackages = "com.smarttechguides.trnximpl")
	public class DatabaseConfig {
	@Bean(name = "dataSource")
	public DataSource dataSource() {
	DriverManagerDataSource dds = new DriverManagerDataSource();
	dds.setUrl("jdbc:oracle:thin:@localhost:1521:orcl");
	dds.setUsername("system");
	dds.setPassword("manager");
	return dds;
	}
	@Bean(name = "template")
	public SimpleJdbcCall jdbcTemplate() {
	SimpleJdbcCall template = new SimpleJdbcCall(dataSource());
	return template;
	}
	}

view raw DatabaseConfig.java hosted with ❤ by GitHub

 

Java Main Class  

	package com.smarttechguides;
	import org.springframework.context.ApplicationContext;
	import org.springframework.context.annotation.AnnotationConfigApplicationContext;
	import com.smarttechguides.bank.HdfcBank;
	import com.smarttechguides.configurations.DatabaseConfig;
	import com.smarttechguides.trnximpl.BankTrxnImplements;
	public class BankApplication {
	public static void main(String[] args) {
	ApplicationContext context = new AnnotationConfigApplicationContext(DatabaseConfig.class);
	BankTrxnImplements bean = context.getBean(BankTrxnImplements.class);
	HdfcBank user = bean.getUser(100);
	System.out.println(user.getCustomerName() + "\n" + user.getTotalAmount());
	}
	}

view raw BankApplication.java hosted with ❤ by GitHub

  

pom.xml file  

	<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.smarttechguides</groupId>
	<artifactId>02-BankApplication</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<dependencies>
	<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>5.3.13</version>
	</dependency>
	<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-context</artifactId>
	<version>5.3.14</version>
	</dependency>
	<dependency>
	<groupId>com.oracle.database.jdbc</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>11.2.0.4</version>
	</dependency>
	</dependencies>
	</project>

view raw pom.xml hosted with ❤ by GitHub

Project Folder Structure

Create Procedure in database

	create or replace PROCEDURE HDFC_BANK
	(
	ACC_NUMBER IN NUMBER
	, CUSTOMER_NAME OUT VARCHAR2
	, DEPOSITE_AMOUNT OUT NUMBER
	, WITHDRAW_AMOUNT OUT NUMBER
	, TOTAL_AMOUNT OUT NUMBER
	) AS
	BEGIN
	select customer,deposite,withdraw,total
	INTO CUSTOMER_NAME,DEPOSITE_AMOUNT,WITHDRAW_AMOUNT,TOTAL_AMOUNT
	from bank where accnumber=ACC_NUMBER;
	END HDFC_BANK;

view raw HDFC_BANK.sql hosted with ❤ by GitHub

Create Table 

Note: spring beans we can configure in 3 ways

1) xml based configuration file

 Configure <beans> inside beans.xml file

 

2) annotation-based configuration

Configure <context:annotation-config/> inside beans.xml file and use annotations inside Java class.

 

3) Java based configuration

Configurations happen in Java file without xml file.

 

With the help of annotations like @configuration, @component, @service, @controller. etc.!