java spring 커넥션 풀 , jdbcTemplate

1. 컨테이너 초기 설정

       <beans:bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

             <beans:property name="driverClass" value="oracle.jdbc.driver.OracleDriver" />

             <beans:property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:xe" />

             <beans:property name="user" value="scott" />

             <beans:property name="password" value="tiger" />

             <beans:property name="maxPoolSize" value="200" />

             <beans:property name="checkoutTimeout" value="60000" />

             <beans:property name="maxIdleTime" value="1800" />

             <beans:property name="idleConnectionTestPeriod" value="600" />

       </beans:bean>

 

또는

1_1 자파 파일로 만들기

@Configuration

public class DBConfig {

       @Bean
       public ComboPooledDataSource dataSource() throws PropertyVetoException {

             ComboPooledDataSource dataSource = new ComboPooledDataSource();

              dataSource.setDriverClass("oracle.jdbc.driver.OracleDriver");

              dataSource.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:xe");

             dataSource.setUser("scott");

             dataSource.setPassword("tiger");

             dataSource.setMaxPoolSize(200);

             dataSource.setCheckoutTimeout(60000);

             dataSource.setMaxIdleTime(1800);

             dataSource.setIdleConnectionTestPeriod(600);
          
             return dataSource;

       }
}

2. 자동 주입

       @Autowired

       public MemberDao(ComboPooledDataSource dataSource) {

             this.template = new JdbcTemplate(dataSource);

       }

 

3. JdbcTemplate 사용

      @Override

       public int memberInsert(final Member member) {

             int result = 0;

             final String sql = "INSERT INTO member (memId, memPw, memMail) values (?,?,?)";

             result = template.update(sql, new PreparedStatementSetter() {
                 
                   @Override

                    public void setValues(PreparedStatement pstmt) throws SQLException {

                           pstmt.setString(1, member.getMemId());

                           pstmt.setString(2, member.getMemPw());

                           pstmt.setString(3, member.getMemMail());
                       
                    }

             });
            
             return result;

       }

 

       @Override
       public Member memberSelect(final Member member) {
    
             List<Member> members = null;
          
             final String sql = "SELECT * FROM member WHERE memId = ? AND memPw = ?";
            
//           1st

             members = template.query(sql, new PreparedStatementSetter() {
                 
                    @Override

                    public void setValues(PreparedStatement pstmt) throws SQLException {
                           pstmt.setString(1, member.getMemId());
                           pstmt.setString(2, member.getMemPw());
                    }

             }, new RowMapper<Member>() {

                    @Override

                    public Member mapRow(ResultSet rs, int rowNum) throws SQLException {

                           Member mem = new Member();

                           mem.setMemId(rs.getString("memId"));

                           mem.setMemPw(rs.getString("memPw"));

                           mem.setMemMail(rs.getString("memMail"));

                           mem.setMemPurcNum(rs.getInt("memPurcNum"));

                           return mem;

                    }

             });
  
             if(members.isEmpty())
                    return null;

             return members.get(0);

       }

 

 

 

      @Override
       public int memberUpdate(final Member member) {           

             int result = 0;

             final String sql = "UPDATE member SET memPw = ?, memMail = ? WHERE memId = ?";

            

//           1st

//           result = template.update(sql, member.getMemPw(), member.getMemMail(),  member.getMemId());

 

//           2nd

//           result = template.update(new PreparedStatementCreator() {

//                 

//                  @Override

//                  public PreparedStatement createPreparedStatement(Connection conn)

//                               throws SQLException {

//                         PreparedStatement pstmt = conn.prepareStatement(sql);

//                         pstmt.setString(1, member.getMemPw());

//                         pstmt.setString(2, member.getMemMail());

//                        pstmt.setString(3, member.getMemId());

//                        

//                         return pstmt;

//                  }

//           });
           

//           3rd

             result = template.update(sql, new PreparedStatementSetter() {


                    @Override

                    public void setValues(PreparedStatement pstmt) throws SQLException {

                           pstmt.setString(1, member.getMemPw());

                           pstmt.setString(2, member.getMemMail());

                           pstmt.setString(3, member.getMemId());

                    }

             });

             return result;
       }

 

 

       @Override
       public int memberDelete(final Member member) {

             int result = 0;

             final String sql = "DELETE member WHERE memId = ? AND memPw = ?";        

//           1st

//           result = template.update(sql, member.getMemId(), member.getMemPw());

            

//           2nd

//           result = template.update(new PreparedStatementCreator() {

//                 

//                  @Override

//                  public PreparedStatement createPreparedStatement(Connection conn)

//                               throws SQLException {

//                         PreparedStatement pstmt = conn.prepareStatement(sql);

//                         pstmt.setString(1, member.getMemId());

//                         pstmt.setString(2, member.getMemPw());

//                        

//                         return pstmt;

//                  }

//           });

            

//           3rd
             result = template.update(sql, new PreparedStatementSetter() {

                                

                    @Override

                    public void setValues(PreparedStatement pstmt) throws SQLException {

                           pstmt.setString(1, member.getMemId());

                           pstmt.setString(2, member.getMemPw());

                    }

             });

             return result;
       }