SPRING3.0 #07( jdbc )

 

 

예제 >  members 테이블에 간단히 데이터 조회, 입력, 수정, 삭제 하는 예제.

- DB : 오라클 11G XE

- SCOTT/TIGER 에 members 테이블 생성.

)
(
 ID         VARCHAR2 (20),
 PWD        VARCHAR2 (10),
 EMAIL      VARCHAR2 (50),
 PHONE      VARCHAR2 (20),
 REG_DATE   DATE
) 

 

 

소스 구조 >>

 

- 관련 lib는 Spring User lib 만들어 이곳에 연결.

 

 

 

members테이블 테스트용으로 간단히 만든다.

 

 CREATE TABLE MEMBERS(
    ID VARCHAR2(10) PRIMARY KEY,
    PWD VARCHAR2(10),
    EMAIL VARCHAR2(15),
    PHONE VARCHAR2(20),
    REGDATE DATE);

 

 

 

(1)  TestMain.java

package test.app.main;

 

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import test.dao.MembersDao;
import test.vo.MembersVo;

 

public class TestMain {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("test/app/app.xml");
        MembersDao dao = (MembersDao)context.getBean("membersDao");

        int n = dao.insert(new MembersVo("test3","test","test","test",null));
        if(n>0){
            System.out.println("회원추가 성공");
        }

        n= dao.delete("test3");
        if ( n > 0 ){
            System.out.println("회원삭제 성공");
        }

        n = dao.update(new MembersVo("test1","33","44","55",null));
        if(n>0){
            System.out.println("수정성공");
        }

        List<MembersVo> list = dao.getList();
        for(MembersVo vo:list){
            System.out.println("id:" + vo.getId());
            System.out.println("Pwd:" + vo.getPwd());
            System.out.println("Email:" + vo.getEmail());
        }
        System.out.println("-------------------------------------");

        System.out.println("[[  like 조회  ]]");
        list = dao.getList("mang");
        for(MembersVo vo:list){
            System.out.println("아이디:" + vo.getId());
            System.out.println("비밀번호:" + vo.getPwd());
            System.out.println("이메일:" + vo.getEmail());
            System.out.println("전화번호:" + vo.getPhone());
            System.out.println("등록일:" + vo.getRegdate());
        }
        System.out.println("-------------------------------------");

        System.out.println("[[   equal 조회    ]]");
        MembersVo vv = dao.getInfo("019");
        if(vv!=null){
            System.out.println("아이디:" + vv.getId());
            System.out.println("비밀번호:" + vv.getPwd());
            System.out.println("이메일:" + vv.getEmail());
            System.out.println("전화번호:" + vv.getPhone());
            System.out.println("등록일:" + vv.getRegdate());
        }       
    }   
}

 

 

 

[[ jdbc연동순서 ]]

 

1. DataSource 설정 ( DB연결 정보 )
2. JdbcTemplate설정 ( DataSource 주입 )
3. MembersDao 설정( JdbcTemplate주입 )

 

 

(2) app.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.xsd">

    <!--     1. DataSource 설정 ( DB연결 정보 )        -->
   <bean id="dataSource" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
        <property name="username" value="scott"/>
        <property name="password" value="tiger"/>
    </bean>
   
    <!--     2. JdbcTemplate설정 ( DataSource 주입 )   -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
   
    <!--     3. MembersDao 설정( JdbcTemplate주입 )      -->
    <bean id="membersDao" class="test.app.dao.MembersDao">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

 

; <bean id="dataSource" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" ...

   --> tomcat-dbcp.jar

 

 

(3) MembersDao.java
; 스프링에서는 jdbc와 연동할때 정형화된 코드를 템플릿화해서 만든 jdbcTemplete을 사용한다.

 

package test.app.dao;

 

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

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

import test.vo.MembersVo;

public class MembersDao {
    private JdbcTemplate jdbcTemplate;

    // 스프링이 생성해준 객체를 주입해야 하므로 setter메소드 작성.
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int insert(MembersVo vo) {
        String sql = "insert into members values (?,?,?,?,sysdate)";
        // sql : 실행할 sql구문
        // parameter : ?에 대응되는 값들
        int n = jdbcTemplate.update(sql, vo.getId(), vo.getPwd(), vo.getEmail(), vo.getPhone());
        return n;
    }

    public int delete(String id) {
        String sql = "delete from members where id=?";
        int n = jdbcTemplate.update(sql, id);
        return n;
    }

    public int update(MembersVo vo) {
        String sql = "update members set pwd=?,email=?,phone=? where id=?";
        int n = jdbcTemplate.update(sql, vo.getPwd(), vo.getEmail(),
                vo.getPhone(), vo.getId());
        return n;
    }

    // 전체회원 정보 가져오는 메소드
    public List<MembersVo> getList() {
        String sql = "select * from members order by id";
        // annoynmouse 추상클래스 사용
        RowMapper<MembersVo> rowMapper = new RowMapper<MembersVo>() {
            @Override
            public MembersVo mapRow(ResultSet rs, int row) throws SQLException {
                MembersVo vo = new MembersVo();
                vo.setId(rs.getString("id"));
                vo.setPwd(rs.getString("pwd"));
                vo.setEmail(rs.getString("email"));
                vo.setPhone(rs.getString("phone"));
                vo.setRegdate(rs.getTimestamp("regdate"));
                return vo;
            }
        };
        List<MembersVo> list = jdbcTemplate.query(sql, rowMapper);
        return list;
    }

    public List<MembersVo> getList(String find) {
        String sql = "select * from members where email like '%'||?||'%'";

         // RowMapper<MembersVo> rowMapper = new RowMapper<MembersVo>() {
         //
         // @Override public MembersVo mapRow(ResultSet rs, int row) throws
         // SQLException { MembersVo vo = new MembersVo();
         // vo.setId(rs.getString("id")); vo.setPwd(rs.getString("pwd"));
         // vo.setEmail(rs.getString("email"));
         // vo.setPhone(rs.getString("phone"));
         // vo.setRegdate(rs.getTimestamp("regdate")); return vo; } };

        Object obj[] = { find };
        List<MembersVo> list = jdbcTemplate.query(sql, obj, // new
                                                            // Object[]{find}
                new RowMapper<MembersVo>() {
                    @Override
                    public MembersVo mapRow(ResultSet rs, int row)
                            throws SQLException {
                        MembersVo vo = new MembersVo();
                        vo.setId(rs.getString("id"));
                        vo.setPwd(rs.getString("pwd"));
                        vo.setEmail(rs.getString("email"));
                        vo.setPhone(rs.getString("phone"));
                        vo.setRegdate(rs.getTimestamp("regdate"));
                        return vo;
                    }
                });
        return list;
    }

    public MembersVo getInfo(String id) {
        String sql = "select * from members where id=?";
        try {
            MembersVo vo = jdbcTemplate.queryForObject(sql,
                    new Object[] { id }, new RowMapper<MembersVo>() {
                        @Override
                        public MembersVo mapRow(ResultSet rs, int row)
                                throws SQLException {

                            MembersVo vo = new MembersVo();
                            vo.setId(rs.getString("id"));
                            vo.setPwd(rs.getString("pwd"));
                            vo.setEmail(rs.getString("email"));
                            vo.setPhone(rs.getString("phone"));
                            vo.setRegdate(rs.getTimestamp("regdate"));
                            return vo;
                        }
                    });
            return vo;
        } catch (EmptyResultDataAccessException es) {//검색된 데이터가 없으면 예외.
            System.out.println(es.getMessage());
            return null;
        }
    }
}

 

 

(4) MembersVo.java

package test.app.vo;

 

import java.sql.Timestamp;

public class MembersVo {
    private String id;
    private String pwd;
    private String email;
    private String phone;
    private Timestamp regdate;
   
    public MembersVo(){}
   
    public MembersVo(String id, String pwd, String email, String phone, Timestamp regdate) {
        super();
        this.id = id;
        this.pwd = pwd;
        this.email = email;
        this.phone = phone;
        this.regdate = regdate;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public Timestamp getRegdate() {
        return regdate;
    }
    public void setRegdate(Timestamp regdate) {
        this.regdate = regdate;
    }
}