SQL Stored Procedure in Spring
CREATE TABLE Student( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID) );
MariaDB [none]> use TestDB;
MariaDB [TestDB]> drop procedure if exists getRecord;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [TestDB]> delimiter $$
MariaDB [TestDB]> CREATE PROCEDURE 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 $$
Query OK, 0 rows affected (0.00 sec)
MariaDB [TestDB]> delimiter ;
StudentDAO.java:
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(); }
Student.java:
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; } }
StudentMapper.java:
package com.tutorialspoint; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; public class StudentMapper implements RowMapper { 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; } }
StudentJDBCTemplate.java:
package com.tutorialspoint;
import java.sql.Types;
import java.util.List;
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 JdbcTemplate jdbcTemplateObject;
private SimpleJdbcCall jdbcCall;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
this.jdbcCall = new SimpleJdbcCall(dataSource).
withProcedureName("getRecord");
}
public void create(String name, Integer age) {
Object[] params = {name, age};
int[] types = {Types.VARCHAR, Types.INTEGER};
JdbcTemplate jdbcTemplateObject = new JdbcTemplate(dataSource);
String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, params, types);
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;
}
}
호출(call) 실행을 위해 작성한 코드는 IN parameter를 포함하는 SqlParameterSource를 생성하는 것을 말한다. Stored Procedure내 선언된 parameter이름과 입력값을 위해 제공되는 이름이 일치하는 것은 중요하다. execute함수는 IN parameter를 갖고 stored procdure에 정의된 것과 같은 이름으로 out paramter가 맞춰진 Map을 반환한다.
MainApp.java:
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()); } }
Beans.xml: datasource부분은 환경에 맞게 설정해야 한다.
<?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="org.mariadb.jdbc.Driver"/>
<property name="url" value="jdbc:mariadb://10.211.55.23:3306/TestDB"/>
<property name="username" value="root"/> <property name="password" value="dab"/> </bean> <!-- Definition for studentJDBCTemplate bean --> <bean id="studentJDBCTemplate" class="com.tutorialspoint.StudentJDBCTemplate"> <property name="dataSource" ref="dataSource" /> </bean> </beans>
POM.xml: Maven환경에서 작성하였기 때문에 이 파일도 확인해야 한다.
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>3.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>1.1.7</version>
</dependency>
위와 같이 의존관계를 추가해 주어야 한다. 여기서 주의점은 SimpleJdbcCall이 Spring 2.5이상에서 지원된다는 점이다. 이전 강좌까지는 이 버전이 2.0.6이었다. 이부분을 수정하지 않으면 import에 에러가 발생할 것이다.
실행 결과는 아래와 같다.
------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