JDBC DB연결 예제

1. 주어진 boardWrite.jsp 에 유저가 입력한 게시판 글을  jspdb 데이터베이스의 board 테이블에 입력하는

    boardWriteOk.jsp를 제작하시오.

 

# boardWrite

<h2>게시판 글쓰기</h2>
<hr>
<form action = "boardWriteOk.jsp">
  글제목 : <input type="text" name="btitle"><br><br>
  글내용 : <textarea rows="15" cols="50" name=bcontent></textarea><br><br>
  글쓴이 : <input type="text" name="memberid"><br><br>
  <input type="submit" value="글등록">
</form>

 

# boardWriteOk

<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
...
<% 
request.setCharacterEncoding("utf-8");
		
String bnum = request.getParameter("bnum");
String btitle = request.getParameter("btitle");
String bcontent = request.getParameter("bcontent");
String memberid = request.getParameter("memberid");
String bdate = request.getParameter("bdate");
			
String driverName = "com.mysql.jdbc.Driver";  
String url = "jdbc:mysql://localhost:3306/jspdb";   
String userName = "root";
String password = "12345";
			
//sql문 만들기
String sql = "INSERT INTO board(btitle, bcontent, memberid) 
              VALUES('"+ btitle +"','" +  bcontent + "','" + memberid + "')" ;
			
Connection conn = null;
Statement stmt = null ;  

		
try {
  Class.forName(driverName);  // MYSQL 드라이버 클래스 불러오기
  // conn(커넥션)이 메모리에 생성 = DB와 연결 커넥션 conn 생성
  conn = DriverManager.getConnection(url, userName, password); 
			
  stmt = conn.createStatement();  // stmt 객체 생성
			
  int sqlResult = stmt.executeUpdate(sql); //sql문 db에서 실행
				
 } catch (Exception e) {
	out.println("db 에러 발생"); 
	e.printStackTrace();  //에러 내용 출력
  } finally {  // 에러 유무와 상관없이 무조건 실행할 내용 입력 
	try {
	  if(stmt != null){  // stmt가 null이 아니면 닫기 --- conn보다 먼저 닫아야한다
	  stmt.close();
	 }
	 if(conn != null) {  // connection이 null이 아닌경우 닫기
	 conn.close();
  }
  }catch(Exception e) {
   e.printStackTrace();
    }
   }
			
%>

 

2. jspdb 데이터베이스 board 테이블의 모든 레코드를 가져와 모든 게시판 글 목록을 출력하는 boardList.jsp를 제작하시오.
   (단, BoardDto를 생성하여 제작하시오.)

 

# BoardDto

private String btitle;
private String bcontent;
private String memberid;
private String bdate;
	
public BoardDto() {
  }

public BoardDto(String btitle, String bcontent, String memberid, String bdate) {
	this.btitle = btitle;
	this.bcontent = bcontent;
	this.memberid = memberid;
	this.bdate = bdate;
  }

public String getBtitle() {
	return btitle;
  }

public void setBtitle(String btitle) {
	this.btitle = btitle;
  }

public String getBcontent() {
	return bcontent;
  }

public void setBcontent(String bcontent) {
	this.bcontent = bcontent;
  }

public String getMemberid() {
	return memberid;
  }

public void setMemberid(String memberid) {
	this.memberid = memberid;
  }

public String getBdate() {
	return bdate;
  }

public void setBdate(String bdate) {
	this.bdate = bdate;
  }

 

# boardList

<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.util.List"%>
<%@page import="com.kmii.member.BoardDto"%>
....
<% 

request.setCharacterEncoding("UTF-8");

String driverName = "com.mysql.jdbc.Driver";  
String url = "jdbc:mysql://localhost:3306/jspdb";   
String userName = "root";
String password = "12345";
				
String sql = "SELECT * FROM board";									

Connection conn = null;
Statement stmt = null ;  
ResultSet rs = null;

List<BoardDto> boardList = new ArrayList<BoardDto>(); 

try {
  Class.forName(driverName);
  conn = DriverManager.getConnection(url, username, password);
  stmt = conn.createStatement();
  rs = stmt.executeQuery(sql);
  
 while(rs.next()){
	 BoardDto boardDto = new BoardDto();
     
	 boardDto.setBtitle(rs.getString("btitle"));
	 boardDto.setBcontent(rs.getString("bcontent"));
	 boardDto.setMemberid(rs.getString("memberid"));
	 boardDto.setBdate(rs.getString("bdate"));
				 
	 boardList.add(boardDto);
   }
			 
 for(BoardDto board : boardList){
	 out.println(board.getBtitle() + "/");
	 out.println(board.getBcontent() + "/");
	 out.println(board.getMemberid() + "/");
	 out.println(board.getBdate());
    }
  } catch (Exception e) {
			out.println("DB 에러 발생");
			e.printStackTrace(); // 에러 내용 출력
  } finally { // 에러의 발생여부와 상관없이 Connection 닫기 실행
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) { // stmt 존재하면 닫아주기 (conn보다 먼저 실행되어야 함)
                stmt.close();
            }
            if (conn != null) { // Connection이 null이 아닌 경우 (존재하는 경우) 닫기
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
  }
%>