[019-1] Spring JDBC Example

Spring JDBC Example

(원문 위치 : http://www.tutorialspoint.com/spring/spring_jdbc_example.htm )

 JdbcTemplate class를 포함하는 Spring framework에 관련된 개념을 이해하기 위해 아래 Student table에서 모든  CRUD를 구현한 간단한 예제를 보자.

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

(--> 이 테이블은 이전 강좌에서 이미 생성해 놓았다.)

[003] HelloWorld Example강좌를 참고하여 프로젝트를 생성한다.

(원문에서는 JDBC관련 library를 추가하도록 되어 있지만, Maven환경에서는....체크가 필요하다)

( mysql-connector-java.jar, org.springframework.jdbc.jar, org.springframework.transaction.jar )

StudentDAO.java: DAO(Data Access Object) interface 파일 (--> interface 파일!! 이다...)

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();
   /** 
    * This is the method to be used to delete
    * a record from the Student table corresponding
    * to a passed student id.
    */
   public void delete(Integer id);
   /** 
    * This is the method to be used to update
    * a record into the Student table.
    */
   public void update(Integer id, Integer age);
}

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

StudentJDBCTemplate.java: DAO interface파일인 StudentDAO의 실제 구현 파일

(---> 원문의 소스가 오류가 발생하여 수정하였음. JdbcTemplate의 update함수의 인자값이 잘못 설정되고 있어 수정함. 

public class StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }

   public void create(String name, Integer age) {
	  Object[] params = { name, age };
	  int[] types = { Types.VARCHAR, Types.INTEGER };
      String SQL = "insert into Student (name, age) values (?, ?)";
      
      //jdbcTemplateObject.update( SQL, name, age);
      jdbcTemplateObject.update( SQL, params, types);
      System.out.println("Created Record Name = " + name + " Age = " + age);
      return;
   }

   public Student getStudent(Integer id) {
      String SQL = "select * from Student where id = ?";
      Student student = (Student) jdbcTemplateObject.queryForObject(SQL, 
                        new Object[]{id}, new StudentMapper());
      return student;
   }

   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, 
                                new StudentMapper());
      return students;
   }

   public void delete(Integer id){
	  Object[] params = {id};
	  int[] types = {Types.INTEGER};
      String SQL = "delete from Student where id = ?";
      
      jdbcTemplateObject.update(SQL, params, types);
      System.out.println("Deleted Record with ID = " + id );
      return;
   }

   public void update(Integer id, Integer age){
	  Object[] params = {id, age};
	  int[] types = {Types.INTEGER, Types.INTEGER};
      String SQL = "update Student set age = ? where id = ?";
      
      jdbcTemplateObject.update(SQL, params, types);
      System.out.println("Updated Record with ID = " + id );
      return;
   }

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("----Updating Record with ID = 2 -----" );
      studentJDBCTemplate.update(2, 20);

      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:

<?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="root"/>
   </bean>


   <!-- Definition for studentJDBCTemplate bean -->
   <bean id="studentJDBCTemplate" 
      class="com.tutorialspoint.StudentJDBCTemplate">
      <property name="dataSource"  ref="dataSource" />    
   </bean>
      
</beans>

실행 결과는 아래와 같다.

------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
----Updating Record with ID = 2 -----
Updated Record with ID = 2
----Listing Record with ID = 2 -----
ID : 2, Name : Nuha, Age : 20

위 예제 말고도 database에 접근하는 NamedParameterJdbTemplate와 SimpleJdbcTemplate class를 사용하는 다른 방법이 있으니 매뉴얼을 참고하기 바란다.

(--> 이번 강좌는 실제 환경에서 동작되도록 설정하는 시간이 생각보다 오래걸렸다. 나름 DB를 분래 운영하는 환경...... 하지만 여전히 eclipse의 DB source explore를 이용한 접근은 성공하지 못한 상태이다. 아무래도 mariaDB를 사용하고 있지만, mysql의 class가 로드되면서 그런 듯 싶다. - 둘이 완전히 동일해 상관없다고들 이야기 하지만 Beans.xml에서도 mariaDB로 바꾸지 않으면 실행시 exception이 발생했다.. 완전히 똑같지는 않은듯 싶다.)