May 14, 2021 Spring
The SimpleJdbcCall class can be used to call a stored procedure that contains IN and OUT parameters. You can use this method when working with any RDBMS, like Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase.
To understand this approach, we use the Student table, which can be created in the MySQL TEST database using the following DDL:
CREATE TABLE Student(
ID INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
PRIMARY KEY (ID)
);
Next, consider the next MySQL stored procedure that uses the student ID and returns the name and age of the appropriate student using the OUT parameter. So let's create this stored procedure in your TEST database using the MySQL command prompt:
DELIMITER $$
DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
CREATE PROCEDURE `TEST`.`getRecord` (
IN in_id INTEGER,
OUT out_name VARCHAR(20),
OUT out_age INTEGER)
BEGIN
SELECT name, age
INTO out_name, out_age
FROM Student where id = in_id;
END $$
DELIMITER ;
Now let's write our Spring JDBC application, which enables the creation and read of our Student database tables. Let's put the Eclipse IDE to work, and then follow these steps to create a Spring application:
Steps | Describe |
---|---|
1 | Create a project called SpringExample, and create a package called com.tutorialspoint under the src folder of the project you created. |
2 | Use the Add External JARs option to add the required Spring library files, as explained in the Spring Hello World Example section. |
3 | Add the latest library files specified by Spring JDBC to your project mysql-connector-java.jar, org.springframework.jdbc.jar and org.springframework.transaction .jar. If you don't have the library files you need, you can download them. |
4 | Create the DAO interface StudentDAO and list all the methods you need. Even if he's not required, you can write the StudentJDBCTemplate class directly, but as a good practice, let's write it. |
5 | Create additional Java-class Student, StudentMapper, StudentJDBCTemplate, and MainApp under the com.tutorialspoint package. |
6 | Make sure you have created the Student table in the TEST database. Also make sure that your MySQL server is working properly, and that you can use a given username and password to have read/write access to the database. |
7 | Create the Beans profile Beans file under the src .xml. |
8 | The final step is to create the contents of all Java files and bean profiles and run the application as explained below. |
Here's what the data access object interface file StudentDAO .java does:
package com.tutorialspoint;
import java.util.List;
import javax.sql.DataSource;
public interface StudentDAO {
/**
* This is the method to be used to initialize
* database resources ie. connection.
*/
public void setDataSource(DataSource ds);
/**
* This is the method to be used to create
* a record in the Student table.
*/
public void create(String name, Integer age);
/**
* This is the method to be used to list down
* a record from the Student table corresponding
* to a passed student id.
*/
public Student getStudent(Integer id);
/**
* This is the method to be used to list down
* all the records from the Student table.
*/
public List<Student> listStudents();
}
Here's .java student file:
package com.tutorialspoint;
public class Student {
private Integer age;
private String name;
private Integer id;
public void setAge(Integer age) {
this.age = age;
}
public Integer getAge() {
return age;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getId() {
return id;
}
}
Here's what .java studentMapper files:
package com.tutorialspoint;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class StudentMapper implements RowMapper<Student> {
public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
return student;
}
}
Here is the implementation of class file StudentJDBCTemplate .java, which defines the DAO interface StudentDAO:
package com.tutorialspoint;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
public class StudentJDBCTemplate implements StudentDAO {
private DataSource dataSource;
private SimpleJdbcCall jdbcCall;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcCall = new SimpleJdbcCall(dataSource).
withProcedureName("getRecord");
}
public void create(String name, Integer age) {
JdbcTemplate jdbcTemplateObject = new JdbcTemplate(dataSource);
String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, name, age);
System.out.println("Created Record Name = " + name + " Age = " + age);
return;
}
public Student getStudent(Integer id) {
SqlParameterSource in = new MapSqlParameterSource().
addValue("in_id", id);
Map<String, Object> out = jdbcCall.execute(in);
Student student = new Student();
student.setId(id);
student.setName((String) out.get("out_name"));
student.setAge((Integer) out.get("out_age"));
return student;
}
public List<Student> listStudents() {
String SQL = "select * from Student";
List <Student> students = jdbcTemplateObject.query(SQL,new StudentMapper());
return students;
}
}
A few words about the above project: The lesson call you write executes code that involves creating a SqlParameterSource that contains in parameters. T he matching of names is important, and the name can be used to provide input values using the parameter names declared in the stored procedure summary. cute method uses the IN parameter to return a map that contains any external parameter keys specified by the name during storage. Now let's move the main application file MainApp .java, as follows:
package com.tutorialspoint;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tutorialspoint.StudentJDBCTemplate;
public class MainApp {
public static void main(String[] args) {
ApplicationContext context =
new ClassPathXmlApplicationContext("Beans.xml");
StudentJDBCTemplate studentJDBCTemplate =
(StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
System.out.println("------Records Creation--------" );
studentJDBCTemplate.create("Zara", 11);
studentJDBCTemplate.create("Nuha", 2);
studentJDBCTemplate.create("Ayan", 15);
System.out.println("------Listing Multiple Records--------" );
List<Student> students = studentJDBCTemplate.listStudents();
for (Student record : students) {
System.out.print("ID : " + record.getId() );
System.out.print(", Name : " + record.getName() );
System.out.println(", Age : " + record.getAge());
}
System.out.println("----Listing Record with ID = 2 -----" );
Student student = studentJDBCTemplate.getStudent(2);
System.out.print("ID : " + student.getId() );
System.out.print(", Name : " + student.getName() );
System.out.println(", Age : " + student.getAge());
}
}
Here's the profile .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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">
<!-- Initialization for data source -->
<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/TEST"/>
<property name="username" value="root"/>
<property name="password" value="password"/>
</bean>
<!-- Definition for studentJDBCTemplate bean -->
<bean id="studentJDBCTemplate"
class="com.tutorialspoint.StudentJDBCTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
Once you've finished creating the source file and bean profile, let's run the application. If your application is all right, this will output the following message:
------Records Creation--------
Created Record Name = Zara Age = 11
Created Record Name = Nuha Age = 2
Created Record Name = Ayan Age = 15
------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 11
ID : 2, Name : Nuha, Age : 2
ID : 3, Name : Ayan, Age : 15
----Listing Record with ID = 2 -----
ID : 2, Name : Nuha, Age : 2