Spring JDBC

1. pom.xml에 dependncy  추가

       <dependency>
           <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.1.7.RELEASE</version>
        </dependency>

http://www.mvnrepository.com/  에서 검색

2. Server에 context.xml에 db정보를 넣어준다

 <Resource name="jdbc/board_db" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@127.0.0.1:1521:xe"
              username="test" password="1234" maxTotal="20" maxIdle="10"
              maxWaitMillis="-1"/>

http://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#Oracle_8i,_9i_&_10g

3. servlet-context 에 templet bean 작성

<beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <beans:property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
        <beans:property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe"/>
        <beans:property name="username" value="test"/>
        <beans:property name="password" value="1234"/>
    </beans:bean>
   
    <beans:bean name="template" class="org.springframework.jdbc.core.JdbcTemplate">
        <beans:property name="dataSource" ref="dataSource"/>
    </beans:bean>

4. controller에 templete 선언(com.spring.board.controller)

    public JdbcTemplate template;

   @Autowired
    public void setTemplate(JdbcTemplate template) {
        this.template = template;
        Contance.templete = this.template;
    }

5. templete을 받아줄 Contance 클라스 선언 (com.spring.board.util)

public class Contance {

    public static JdbcTemplate templete;

}

6. 템플릿 객체 선언 (DB정보 가져오기)

 6.1 기존

    DataSource dataSource;
    public BDao(){
        try {
            Context context = new InitialContext();
            dataSource = (DataSource)context.lookup("java:/comp/env/jdbc/board_db");

        } catch (Exception e) {
            // TODO: handle exception
        }
       
    }

  6.2 변경
    JdbcTemplate template;
    public BDao(){
        template = Contance.templete;
    }

7. 예제

  7.1 기존

public ArrayList<BDto> list(){
       
  
        ArrayList<BDto> list = new ArrayList<BDto>();
       
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from mvc_board order by bGroup DESC, bStep ASC";
       
        try {
            conn= dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
                BDto dto = new BDto();
                dto.setbId(rs.getInt("bId"));
                dto.setbName(rs.getString("bName"));
                dto.setbTitle(rs.getString("bTitle"));
                dto.setbContent(rs.getString("bContent"));
                dto.setbDate(rs.getTimestamp("bDate"));
                dto.setbHit(rs.getInt("bHit"));
                dto.setbStep(rs.getInt("bStep"));
                dto.setbGroup(rs.getInt("bGroup"));
                dto.setbIndent(rs.getInt("bIndent"));
                list.add(dto);
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
        finally{
            close(conn, pstmt, rs);
        }
       
        return list;
    }

   7.2 변경

    public ArrayList<BDto> list(){
       
        ArrayList<BDto> list = new ArrayList<BDto>();
        String sql = "select * from mvc_board order by bGroup DESC, bStep ASC";
        list =(ArrayList<BDto>) template.query(sql, new BeanPropertyRowMapper<BDto>(BDto.class));
       
        return list;

}