(오라클자바개발자실무교육,오엔제이프로그래밍실무교육센터)spring JDBC , JdbcTemplate CRUD 예제

오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터

(오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷  실무전문 강의)

www.onjprogramming.co.kr   

Spring JDBC CRUD 예제

CREATE TABLE customer(
   ID   NUMBER NOT NULL ,
   NAME VARCHAR(20) NOT NULL,
   AGE  NUMBER NOT NULL,
   PRIMARY KEY (ID)
   )

[Customer.java]
package onj.edu.jdbc1;

public class Customer {
private Integer id;
private String name;
private Integer age;

public void setId(Integer id) {this.id = id;}
public void setName(String name) {this.name = name;}
public void setAge(Integer age) {this.age = age;}
public Integer getId() {return id;}
public String getName() {return name;}
public Integer getAge() {return age;}
}

[CustomerDAO.java]

package onj.edu.jdbc1;

import java.util.List;

import javax.sql.DataSource;

public interface CustomerDAO {
 public void setDataSource(DataSource ds);
 public void create(Integer id, String name, Integer age);
 public Customer getCustomer(Integer id);
 public List<Customer> listCustomer();
 public void delete(Integer id);
 public void update(Integer id, Integer age);
}

[CustomerJDBCTemplate.java]
package onj.edu.jdbc1;

public class CustomerJDBCTemplate implements CustomerDAO {
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplate = new JdbcTemplate(dataSource);
}

public void create(Integer id, String name, Integer age) {
String SQL = "insert into customer (id, name, age) values (?, ?, ?)";
jdbcTemplate.update(SQL, id, name, age);
System.out.println("<<< customer insert ok ::: " + name + "," + age);
return;
}

public Customer getCustomer(Integer id) {
String SQL = "select * from customer where id = ? “;
Customer customer = jdbcTemplate.queryForObject(SQL,
              new Object[] {id}, new CustomerMapper());
return customer;
}

public List<Customer> listCustomer() {
String SQL = "select * from customer ";
List<Customer> customer = jdbcTemplate.query(SQL, new CustomerMapper());

return customer;
}

public void delete(Integer id) {
String SQL = "delete from customer where id = ?";
jdbcTemplate.update(SQL, id);
System.out.println("<<< customer delete ok ::: id = " + id);
return;
}

public void update(Integer id, Integer age) {
String SQL = "update customer set age = ? where id = ?";
jdbcTemplate.update(SQL, age, id);
System.out.println("<<< customer update ok ::: id = " + id);
}
}

[CustomerMapper.java]
package onj.edu.jdbc1;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class CustomerMapper implements RowMapper<Customer> {
   public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
 Customer customer = new Customer();

 customer.setId(rs.getInt("id"));
 customer.setName(rs.getString("name"));
 customer.setAge(rs.getInt("age"));

         return customer;
   }
}

[spring-jdbc1.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.2.xsd “>
   <!-- Initialization for data source -->
   <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="driverClassName">
   <value>oracle.jdbc.driver.OracleDriver</value>
  </property>
  <property name="url">
   <value>jdbc:oracle:thin:@localhost:1521:onj</value>
  </property>
  <property name="username">
   <value>scott</value>
  </property>
  <property name="password">
   <value>tiger</value>
  </property>
 </bean>
   <!-- Definition for customerJDBCTemplate bean -->
   <bean id=“customerJDBCTemplate"
      class="onj.edu.jdbc1.CustomerJDBCTemplate">
      <property name="dataSource"  ref="dataSource" />   
   </bean>     
</beans>

[JdbcClient.java]
package onj.edu.jdbc1;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class JdbcClient {
 public static void main(String[] args) {
  ApplicationContext ctx = new ClassPathXmlApplicationContext("spring-jdbc1.xml");

  CustomerJDBCTemplate jdbcTemplate = (CustomerJDBCTemplate)ctx.getBean("customerJDBCTemplate");

  //--- insert
  jdbcTemplate.create(1, "1길동", 1);
  jdbcTemplate.create(2, "2길동", 2);
  jdbcTemplate.create(3, "3길동", 3);

  //--- all select
  List <Customer> customers = jdbcTemplate.listCustomer();
  System.out.println("------- all select -------");
  for(Customer customer : customers) {
   System.out.print("id --> " + customer.getId());
   System.out.print("name --> " + customer.getName());
   System.out.println("age --> " + customer.getAge()); 
  }

  //--- update
  jdbcTemplate.update(1, 99);

  //--- 1번  data select
  Customer customer = jdbcTemplate.getCustomer(1);

  System.out.println("------- 변경된 1번 데이터 -------");
  System.out.println("id --> " + customer.getId());
  System.out.println("name --> " + customer.getName());
  System.out.println("age --> " + customer.getAge());

  //--- delete, 2번 삭제
  jdbcTemplate.delete(2);

  //--- all list
  customers = jdbcTemplate.listCustomer();
  System.out.println("--------- 2번 삭제 후 ----------");
  for(Customer customer1 : customers) {
   System.out.print("id --> " + customer1.getId());
   System.out.print("name --> " + customer1.getName());
   System.out.println("age --> " + customer1.getAge()); 
  }
 }
}

 

[출처] 오라클자바커뮤니티 - http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecSpring&wr_id=232