[24] Spring JDBC 예제

 

>>>>>>>>>>>>>>>>> Spring JDBC >>>>>>>>>>>>>>>>

 

- DAO statement 처리 방식
  1) JdbcTemplate
  2) namedParameterJdbcTemplate
  3) SimpleJdbcTemplate

 

- JdbcTemplate 메소드
  1) update() : insert, update, delete 쿼리문
  2) queryForInt() : 여러가지 수치함수(sum, count등)에 의해 int type컬럼 값 하나를 가져올때
  3) queryForObject() : 하나의 오브젝트 리턴 타입(BbsDTO 클래스등)을 검색해서 가져올때
  4) query() : 여러개의 오브젝트 타입(List등)을 가져올때

- RowMapper : 스프링이 호출. List타입으로 결과를 반환하고 싶을때

- Dynamic Web Project : springJDBC 생성
  프로젝트 오른쪽 버튼 -> Configure -> Convert to AspectJ Project

- 오라클라이브러리, 스프링라이브러리 /lib/폴더에 복사

 

-----------------------------------------------[테이블생성]
create table board2(
     idx int primary key,
     name varchar(10),
     email varchar(50),
     homepage varchar(50),
     title varchar(50),
     content varchar(2000),
     pwd varchar(10),
     wdate date,
     hit int
);
create sequence board2_idx_seq;
-----------------------------------------------BbsDTO.java
package part1;

public class BbsDTO {
    private int idx;
    private String name;
    private String email;
    private String homepage;
    private String title;
    private String content;
    private String pwd;
    private String wdate;
    private int hit;
    public BbsDTO() {}

    //idx,wdate 인자없는 생성자
    public BbsDTO(String name, String email, String homepage,
            String title, String content, String pwd, int hit) {    
        this.name = name;
        this.email = email;
        this.homepage = homepage;
        this.title = title;
        this.content = content;
        this.pwd = pwd;       
        this.hit = hit;
    }

    getter와 setter생성
}
-----------------------------------------------Dao.java
package part1;

import java.util.List;

public interface Dao {
    public List<BbsDTO> list();
    public BbsDTO read();
    public int rowCount();
    public void insert(BbsDTO dto);   
    public void update(BbsDTO dto);   
    public void delete(int num);
}
-----------------------------------------------DaoImp.java
package part1;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class DaoImp implements Dao {
    private JdbcTemplate jt;
   
    public void setJt(JdbcTemplate jt) {
        this.jt = jt;
    }

 

    @Override
    public List<BbsDTO> list() {
        String sql="SELECT * FROM board2 WHERE idx>=? AND idx<=? ORDER BY idx DESC";
        Integer it[]=new Integer[2];
        it[0]=1;
        it[1]=10;
               
        List<BbsDTO> list=jt.query(sql, it,new RowMapper<BbsDTO>() {
            @Override
            public BbsDTO mapRow(ResultSet rs, int arg1) throws SQLException {
                BbsDTO dto=new BbsDTO();
                dto.setIdx(rs.getInt("idx"));
                dto.setName(rs.getString("name"));
                dto.setEmail(rs.getString("email"));
                dto.setHomepage(rs.getString("homepage"));
                dto.setTitle(rs.getString("title"));
                dto.setContent(rs.getString("content"));
                dto.setPwd(rs.getString("pwd"));
                dto.setWdate(rs.getString("wdate"));
                dto.setHit(rs.getInt("hit"));
                return dto;
            }
        });
        return list;
    }//목록 페이지

 

    @Override
    public BbsDTO read() {
        String sql="SELECT * FROM board2 WHERE idx=?";
        Integer it[]=new Integer[1];
        it[0]=1;
        BbsDTO bbsdto=jt.queryForObject(sql, it,new RowMapper<BbsDTO>() {
            @Override
            //new RowMapper<BbsDTO>() anonymous 클래스
            public BbsDTO mapRow(ResultSet rs, int arg1)
                    throws SQLException {
                BbsDTO dto=new BbsDTO();
                dto.setIdx(rs.getInt("idx"));
                dto.setName(rs.getString("name"));
                dto.setEmail(rs.getString("email"));
                dto.setHomepage(rs.getString("homepage"));
                dto.setTitle(rs.getString("title"));
                dto.setContent(rs.getString("content"));
                dto.setPwd(rs.getString("pwd"));
                dto.setWdate(rs.getString("wdate"));
                dto.setHit(rs.getInt("hit"));              
                return dto;
            }
        });
       
        return bbsdto;
    }//상세보기

 

    @Override
    public int rowCount() {
        String sql="SELECT count(*) FROM board2";
        return jt.queryForInt(sql);
    }

 

    @Override
    public void insert(BbsDTO dto) {
        String sql="INSERT INTO board2(idx,name,email,homepage,title,content,pwd,wdate,hit) ";
        sql+=" VALUES(board2_idx_seq.nextval,?,?,?,?,?,?,sysdate,?)";
        Object args[]=new Object[]{dto.getName(),dto.getEmail(),dto.getHomepage(),dto.getTitle()
                                  ,dto.getContent(),dto.getPwd(),dto.getHit()};       
        jt.update(sql,args);
    }

 

    @Override
    public void update(BbsDTO dto) {
        String sql="UPDATE board2 SET name=? WHERE idx=?";
        Object args[]=new Object[]{dto.getName(),dto.getIdx()};
        jt.update(sql,args);
    }

 

    @Override
    public void delete(int num) {
        String sql="DELETE FROM board2 WHERE idx=?";
        Object args[]=new Object[]{ num };
        jt.update(sql,args);
    }
}
-----------------------------------------------jdbc.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-2.0.xsd">
                          
    <!-- 1. dataSource선언 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin://@127.0.0.1:1521:xe"/>
        <property name="username" value="hr"/>
        <property name="password" value="hr"/>
    </bean>
   
    <!-- 2. JdbcTemplate선언 -->
    <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
   
    <!--  3. DaoImp선언 -->
    <bean id="dao" class="part1.DaoImp">
        <property name="jt" ref="jt"/>
    </bean>                     
</beans>
-----------------------------------------------JdbcMain.java
package part1;

import java.util.List;

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

public class JdbcMain {
    public static void main(String[] args) {
        ApplicationContext context=new FileSystemXmlApplicationContext("src/part1/jdbc.xml");      
       

        Dao dao=(Dao)context.getBean("dao");

 

        //갯수   
        System.out.println(dao.rowCount());

 

        //추가
        dao.insert(new BbsDTO("진달래","email","homepate","title","content","pwd",1));
       

        //수정       
        BbsDTO dto=new BbsDTO();
        dto.setIdx(1);
        dto.setName("무궁화");
        dao.update(dto);

 

        //삭제
        dao.delete(3);

 

        //목록       
        List<BbsDTO> list=dao.list();       
        for(BbsDTO dto:list)
        {
            System.out.printf("%d %s %s",dto.getIdx(),dto.getName(),dto.getEmail());
            System.out.printf("%s %s %s",dto.getHomepage(),dto.getTitle(),dto.getContent());
            System.out.printf("%s %s %d\n",dto.getPwd(),dto.getWdate(),dto.getHit());
        }
       
        //상세보기
        BbsDTO dto=dao.read();
        System.out.printf("%d %s %s",dto.getIdx(),dto.getName(),dto.getEmail());
        System.out.printf("%s %s %s",dto.getHomepage(),dto.getTitle(),dto.getContent());
        System.out.printf("%s %s %d\n",dto.getPwd(),dto.getWdate(),dto.getHit());
 
    }
}