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
            }
        });
    }

cs




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


각 코드가 상당히 간소화 되었다.