Spring
SpringMVC(JDBC 사용)
rlaxogns2
2016. 5. 19. 20:40
Spring JDBC
=> JDBC 를 이용하여 반복코드를 줄이고(드라이버로드, 커넥션 생성 및 DB 연결,
SQL 실행, 자원해제 등), Spring 빈을 이용하여 코드를 간소화 할 수 있다.
JDBC 를 이용한 리스트 목록 생성과 DB 연동
환경설정)
servlet-context.xml 에 추가
1 2 3 4 5 6 7 8 9 10 11 12 13 | <beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></beans:property> <beans:property name="url" value="jdbc:oracle:thin:@localhost:1521:XE"></beans:property> <beans:property name="username" value="hr"></beans:property> <beans:property name="password" value="orcl"></beans:property> </beans:bean> <beans:bean name="template" class="org.springframework.jdbc.core.JdbcTemplate"> <beans:property name="dataSource" ref="dataSource"></beans:property> </beans:bean> | cs |
오라클 데이터 베이스와 연동(ID : hr / PW : orcl)
pom.xml 에 추가
1 2 3 4 5 6 7 8 9 10 11 12 | <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0.3</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.1.4.RELEASE</version> </dependency> | cs |
위는 JDBC 아래는 JDBC Template 설정 부분
BController.java 에 추가
1 2 3 4 5 | @Autowired public void setTemplate(JdbcTemplate template) { this.template = template; com.java.spring_mvc_board.util.Constant.template = this.template; } | cs |
의존관계를 자동으로 설정(Template 해당 빈을 생성 후 값을 전달)
Constant.java 생성
1 2 3 4 5 6 7 8 | package com.java.spring_mvc_board.util; import org.springframework.jdbc.core.JdbcTemplate; public class Constant { public static JdbcTemplate template; } | cs |
ojdbc-11.2.0.3.jar 파일을 해당 경로에 추가
파일구조
게시판 Dao 코드의 간소화
BDao.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | public class BDao { DataSource dataSource; 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(); } } | cs |
▼
BDao.java
1 2 3 4 5 6 7 8 9 10 | public class BDao { DataSource dataSource; JdbcTemplate template = null; public BDao() { // TODO Auto-generated constructor stub template = Constant.template; } | cs |
list
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | public ArrayList<BDto> list() { 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; } | cs |
▼
list
1 2 3 4 5 6 | public ArrayList<BDto> list() { if(template != null) System.out.println("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)); } | cs |
write
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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(); } } } | cs |
▼
write
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 ps = con.prepareStatement(query); ps.setString(1, bName); ps.setString(2, bTitle); ps.setString(3, bContent); // TODO Auto-generated method stub return ps; } }); } | cs |
contentView
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | 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; } | cs |
▼
contentView
1 2 3 4 5 | public BDto contentView(String bId) { String query = "select * from mvc_board where bId =" + bId; upHit(bId); return template.queryForObject(query, new BeanPropertyRowMapper<BDto>(BDto.class)); } | cs |
modify
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 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(); } } } | cs |
▼
modify
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 { ps.setString(1, bName); ps.setString(2, bTitle); ps.setString(3, bContent); ps.setInt(4, Integer.parseInt(bId)); // TODO Auto-generated method stub } }); } |
delete
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | 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(); } } } | cs |
▼
delete
1 2 3 4 5 6 7 8 9 10 11 | public void delete(final String bId) { String query = "delete from mvc_board where bId = ?"; template.update(query, new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { // TODO Auto-generated method stub ps.setInt(1, Integer.parseInt(bId)); } }); } | cs |
reply_view
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | public BDto reply_view(String str) { // TODO Auto-generated method stub 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(str)); 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(preparedStatement != null) preparedStatement.close(); if(connection != null) connection.close(); } catch (Exception e2) { // TODO: handle exception e2.printStackTrace(); } } return dto; } | cs |
▼
reply_view
1 2 3 4 | public BDto reply_view(String str) { String query = "select * from mvc_board where bId = " +str; return template.queryForObject(query, new BeanPropertyRowMapper<BDto>(BDto.class)); } | cs |
reply
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | public void reply(String bId, String bName, String bTitle, String bContent, String bGroup, String bStep, String bIndent) { // TODO Auto-generated method stub replyShape(bGroup, bStep); Connection connection = null; PreparedStatement preparedStatement = null; try { connection = dataSource.getConnection(); String query = "insert into mvc_board (bId, bName, bTitle, bContent, bGroup, bStep, bIndent) values (mvc_board_seq.nextval, ?, ?, ?, ?, ?, ?)"; preparedStatement = connection.prepareStatement(query); preparedStatement.setString(1, bName); preparedStatement.setString(2, bTitle); preparedStatement.setString(3, bContent); preparedStatement.setInt(4, Integer.parseInt(bGroup)); preparedStatement.setInt(5, Integer.parseInt(bStep) + 1); preparedStatement.setInt(6, Integer.parseInt(bIndent) + 1); 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(); } } } | cs |
▼
reply
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | public void reply(final String bId, final String bName, final String bTitle, final String bContent, final String bGroup, final String bStep, final String bIndent) { template.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { // TODO Auto-generated method stub replyShape(bGroup, bStep); String query = "insert into mvc_board (bId, bName, bTitle, bContent, bGroup, bStep, bIndent) values (mvc_board_seq.nextval, ?, ?, ?, ?, ?, ?)"; PreparedStatement ps = con.prepareStatement(query); ps.setString(1, bName); ps.setString(2, bTitle); ps.setString(3, bContent); ps.setInt(4, Integer.parseInt(bGroup)); ps.setInt(5, Integer.parseInt(bStep)+1); ps.setInt(6, Integer.parseInt(bStep)+1); return ps; } }); } | cs |
replyShape
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | private void replyShape( String strGroup, String strStep) { // TODO Auto-generated method stub Connection connection = null; PreparedStatement preparedStatement = null; try { connection = dataSource.getConnection(); String query = "update mvc_board set bStep = bStep + 1 where bGroup = ? and bStep > ?"; preparedStatement = connection.prepareStatement(query); preparedStatement.setInt(1, Integer.parseInt(strGroup)); preparedStatement.setInt(2, Integer.parseInt(strStep)); 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(); } } } | cs |
▼
replyShape
1 2 3 4 5 6 7 8 9 10 11 12 | private void replyShape(final String strGroup, final String strStep) { String query = "update mvc_board set bStep = bStep + 1 where bGroup = ? and bStep > ?"; template.update(query, new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { // TODO Auto-generated method stub ps.setString(1, strGroup); ps.setString(2, strStep); } }); } | cs |
upHit
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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(); } } } } | cs |
▼
upHit
1 2 3 4 5 6 7 8 9 10 11 12 | private void upHit(final String bId) { String query = "update mvc_board set bHit = bHit + 1 where bId = ?"; template.update(query, new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { // TODO Auto-generated method stub ps.setInt(1, Integer.parseInt(bId)); } }); } | cs |
각 코드가 상당히 간소화 되었다.