[spring] JDBC

[spring] jdbc

 

1. JDBC를 이용한 반복코드 줄이기

   - DAO객체를 이용해서 DataBase의 데이터를 이용한다. 이때 매번 같은 동작을 반복하는 부분이 있다.

     (ex: 드라이버 로드, 커넥션 생성, DB연결, SQL실행, 자원해제) 이런 반복적인 작업들을 

     스프링에서는 간단하게 처리가 가능하다.

 

     * JDBC 드라이버 로드(DriverManager) -> 데이터베이스 연결(Connection) -> SQL문 실행(Statement) -> 데이터베이스 연결 해제(ResultSet)

       --> 이러한것들을 jdbcTemplate에서 한번에 해결할 수 있다.

 

2. Spring빈을 이용한 코드 간소화

   - jdbcTemplate 빈 안에는 Datasource빈이 있다. 이것을 java 파일에서 갖다쓴다. ( 빈 생성하고 자바파일에서 갖다쓰는게 스프링의 특성중 하나이다. )

 

3. 사용방법

   (1) dependency 추가

       [pom.xml]

<!-- JDBC Template -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.1.4.RELEASE</version>
</dependency>

 

   (2) JdbcTemplate 추가

       [BoardController.java]

....

@Controller
public class BController {
    BCommand command = null;

    public jdbcTemplate templat; // jdbcTemplate 추가

    // setter 생성
    public void setTemplate(JdbcTemplate template) {
    	this.template = template;
    }

....

 

   (3) JdbcTemplate 사용하기 위해 Spring Bean 생성

       [/src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml]

....

<beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
    <beans:property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" /> <!-- server에 context.xml에 하단에 보면 db접속 정보가 있다. 그걸 복사해서 쓰자.  -->
    <beans:property name="username" value="name" />
    <beans:property name="password" value="pass" />
</beans:bean>

<!-- jdbc를 사용하기 위한 bean객체 -->
<!-- template이라는 bean이 dataSource라는 bean을 갖고있다. 즉, 설정하는 부분을 template하나로 할 수 있다. -->
<beans:bean name="template" class="org.springframework.jdbc.core.JdbcTemplate">
	<beans:property name="dataSource" ref="dataSource" />
</beans:bean>

....

 

   (4) (2)번에서 생성한 template변수에 jdbcTemplate 객체가 들어가게 한다.

       // 자동으로 알아서 bean이 생성되서 할당된다.

       [BoardController.java]

....

@Controller
public class BController {
    BCommand command = null;

    public jdbcTemplate templat;

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

	....

 

   (5) jdbcTemplate을 어디서든 할당할 수 있게 패키지를 만들자.

       [/src/main/java/com/javaex/project/util/Constant.java]

public class Constant {
	public static JdbcTemplate template;
}

 

       [BoardController.java]

....

@Controller
public class BController {
    BCommand command = null;
    public jdbcTemplate templat;

    @Autowired
    public void setTemplate(JdbcTemplate template) {
        this.template = template;
        Constant.template = this.template; // 언제든지 아무때나 jdbcTemplate을 사용할 수 있다.
    }

    ....

 

4. JDBC Template의 메소드를 이용하여 코드를 간단하게 변경

   [DAO객체 JDBC Template 적용 전 코드]

public ArrayList<BDto> list() {

    ....

    public BDao() {
        // TODO Auto-generated constructor stub
        try {
            Context context = new InitialContext();
            dataSource = (DataSource) context.lookup("java:comp/env/jdbc/Oracle11g");
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }
    
    ....

    ArrayList<BDto> dtos = new ArrayList<BDto>();
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
        connection = dataSource.getConnection();
        String query = "select bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent from mvc_board order by bGroup desc, bStep asc";
        preparedStatement = connection.prepareStatement(query);
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            int bId = resultSet.getInt("bId");
            String bName = resultSet.getString("bName");
            String bTitle = resultSet.getString("bTitle");
            String bContent = resultSet.getString("bContent");
            Timestamp bDate = resultSet.getTimestamp("bDate");
            int bHit = resultSet.getInt("bHit");
            int bGroup = resultSet.getInt("bGroup");
            int bStep = resultSet.getInt("bStep");
            int bIndent = resultSet.getInt("bIndent");

            BDto dto = new BDto(bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent);
            dtos.add(dto);
        }
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
    } finally {
        try {
            if(resultSet != null) resultSet.close();
            if(preparedStatement != null) preparedStatement.close();
            if(connection != null) connection.close();
        } catch (Exception e2) {
            // TODO: handle exception
            e2.printStackTrace();
        }
    }

    return dtos;
}

 

   [DAO객체 JDBC Template 적용 후]

JdbcTemplate template = null;

public BDao() {
	template = Constant.template;
}

....

public ArrayList<BDto> list() {
    String query = "select bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent from mvc_board order by bGroup desc, bStep asc";
    return (ArrayList<BDto>) template.query(query, new BeanPropertyRowMapper<BDto>(BDto.class));
}

 

5. insert, update, delete 처리하기

   (1) contentView 메서드

        [DAO객체 JDBC Template 적용 전 코드]

public BDto contentView(String strID) {
    // TODO Auto-generated method stub
    upHit(strID);
    BDto dto = null;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
        connection = dataSource.getConnection();
        String query = "select * from mvc_board where bId = ?";
        preparedStatement = connection.prepareStatement(query);
        preparedStatement.setInt(1, Integer.parseInt(strID));
        resultSet = preparedStatement.executeQuery();

        if(resultSet.next()) {
            int bId = resultSet.getInt("bId");
            String bName = resultSet.getString("bName");
            String bTitle = resultSet.getString("bTitle");
            String bContent = resultSet.getString("bContent");
            Timestamp bDate = resultSet.getTimestamp("bDate");
            int bHit = resultSet.getInt("bHit");
            int bGroup = resultSet.getInt("bGroup");
            int bStep = resultSet.getInt("bStep");
            int bIndent = resultSet.getInt("bIndent");
            dto = new BDto(bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent);
        }
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
    } finally {
        try {
            if(resultSet != null) resultSet.close();
            if(preparedStatement != null) preparedStatement.close();
            if(connection != null) connection.close();
        } catch (Exception e2) {
            // TODO: handle exception
            e2.printStackTrace();
        }
    }

    return dto;
}

 

        [DAO객체 JDBC Template 적용 후]

public BDto contentView(String strID) {
    upHit(strID);  // 아래 (2)번 참조 view 업데이트
    String query = "select * from mvc_board where bId = " + strID;
    return template.queryForObject(query, new BeanPropertyRowMapper<BDto>(BDto.class));
}

 

   (2) upHit 메서드

        [DAO객체 JDBC Template 적용 전 코드]

private void upHit( String bId) {
    // TODO Auto-generated method stub
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try {
        connection = dataSource.getConnection();
        String query = "update mvc_board set bHit = bHit + 1 where bId = ?";
        preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, bId);
        int rn = preparedStatement.executeUpdate();
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
    } finally {
        try {
            if(preparedStatement != null) preparedStatement.close();
            if(connection != null) connection.close();
        } catch (Exception e2) {
            // TODO: handle exception
            e2.printStackTrace();
        }
    }
}

 

        [DAO객체 JDBC Template 적용 후]

private void upHit( final String bId) { // bId가 변경이 되면 영향을 받을 수 있으므로 변경되지말라고 final로 처리
    String query = "update mvc_board set bHit = bHit + 1 where bId = ?";
    
    template.update(query, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
        	ps.setInt(1, Integer.parseInt(bId));
        }
    });
}

 

   (3) write 메서드

       [DAO객체 JDBC Template 적용 전 코드]

public void write(String bName, String bTitle, String bContent) {
    // TODO Auto-generated method stub
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try {
        connection = dataSource.getConnection();
        String query = "insert into mvc_board (bId, bName, bTitle, bContent, bHit, bGroup, bStep, bIndent) values (mvc_board_seq.nextval, ?, ?, ?, 0, mvc_board_seq.currval, 0, 0 )";
        preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, bName);
        preparedStatement.setString(2, bTitle);
        preparedStatement.setString(3, bContent);
        int rn = preparedStatement.executeUpdate();
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
    } finally {
        try {
            if(preparedStatement != null) preparedStatement.close();
            if(connection != null) connection.close();
        } catch (Exception e2) {
            // TODO: handle exception
            e2.printStackTrace();
        }
    }
}

 

       [DAO객체 JDBC Template 적용 후]

public void write(final String bName, final String bTitle, final String bContent){
    template.update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            String query = "insert into mvc_board (bId, bName, bTitle, bContent, bHit, bGroup, bStep, bIndent) values (mvc_board_seq.nextval, ?, ?, ?, 0, mvc_board_seq.currval, 0, 0)";
            PreparedStatement pstmt = con.prepareStatement(query);
            pstmt.setString(1, bName);
            pstmt.setString(2, bTitle);
            pstmt.setString(3, bContent);

            return pstmt;
        }
    });
}

 

   (4) modify 메서드

       [DAO객체 JDBC Template 적용 전 코드]

public void modify(String bId, String bName, String bTitle, String bContent) {
    // TODO Auto-generated method stub
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try {
        connection = dataSource.getConnection();

        String query = "update mvc_board set bName = ?, bTitle = ?, bContent = ? where bId = ?";
        preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, bName);
        preparedStatement.setString(2, bTitle);
        preparedStatement.setString(3, bContent);
        preparedStatement.setInt(4, Integer.parseInt(bId));
        int rn = preparedStatement.executeUpdate();

    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
    } finally {
        try {
            if(preparedStatement != null) preparedStatement.close();
            if(connection != null) connection.close();
        } catch (Exception e2) {
            // TODO: handle exception
            e2.printStackTrace();
        }
    }
}

 

       [DAO객체 JDBC Template 적용 후]

public void modify(final String bId, final String  bName, final String  bTitle, final String  bContent) {
    String query = "update mvc_board set bName = ?, bTitle = ?, bContent = ? where bId = ?";
    
    template.update(query, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            // TODO Auto-generated method stub
            ps.setString(1, bName);
            ps.setString(2, bTitle);
            ps.setString(3, bContent);
            ps.setInt(4, Integer.parseInt(bId));
        }
    });
}

 

   (5) delete 메서드

[DAO객체 JDBC Template 적용 전 코드]

public void delete(String bId) {
    // TODO Auto-generated method stub
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try {
        connection = dataSource.getConnection();
        String query = "delete from mvc_board where bId = ?";
        preparedStatement = connection.prepareStatement(query);
        preparedStatement.setInt(1, Integer.parseInt(bId));
        int rn = preparedStatement.executeUpdate();
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
    } finally {
        try {
            if(preparedStatement != null) preparedStatement.close();
            if(connection != null) connection.close();
        } catch (Exception e2) {
            // TODO: handle exception
            e2.printStackTrace();
        }
    }
}

 

[DAO객체 JDBC Template 적용 후]

public void delete( final String strID) {
    String query = "delete from mvc_board where bId = ?";
    template.update(query, new PreparedStatementSetter() {
        @Override
            public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, strID);
        }
    });
}

 

    ** 위와 같은 식으로 간단하게 표현할 수 있다.

 

 

 

참고: https://www.inflearn.com/course/%EC%9E%90%EB%B0%94-%EC%8A%A4%ED%94%84%EB%A7%81-%EA%B0%95%EC%A2%8C/dashboard