HTML, JDBC
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<table border="1">
<!-- while(rs.next())로 반복해야하는 줄은 실제데이터줄이기 때문에 상단헤더줄은 고정 하고 다음줄부터 반복문 돌린다 -->
<tr>
<td>게시판 번호</td>
<td>제목</td>
<td>내용</td>
<td>작성자</td>
<td>작성일</td>
<td>조회</td>
<td>좋아요~</td>
</tr>
<%
Class.forName("com.mysql.jdbc.Driver");//-> MYSQL JDBC드라이버
Connection conn = null;
ResultSet rs = null;
Statement pst = null;
try {
String url = "jdbc:mysql://localhost:3306/sample?serverTimezone=Asia/Seoul";
String dbUser = "root";
String dbPw = "1234";
String query = "select * from board";
//executeUpdate -> update/insert/delete문을 사용할때
//String query = update/insert/delete각 질의문에 맞게 작성해주고
//rs = pst.executeQuery(query); 이부분을 rs = pst.executeUpdate(query);로 바꿔준다
conn = DriverManager.getConnection(url, dbUser, dbPw);
pst = conn.createStatement();
rs = pst.executeQuery(query);
out.println("DB연결성공");
while(rs.next()){//DB와 비교해서 데이터가 위치한 곳까지 한줄씩 뿌려준다 1번줄..2번줄..3번줄... 다음줄에 데이터가 있으면 True를반환하고 계속 반복돌림
%>
<fmt:formatDate value="${now}" pattern="yyyy-MM-dd hh:mm:ss" />
<!-- board테이블과 column매칭시켜준다 -->
<tr>
<td><%= rs.getInt("no") %></td>
<td><%= rs.getString("title") %></td>
<td><a href="PreparedStatement04.jsp?no=<%= rs.getInt("no") %>"><%= rs.getString("content") %></a></td>
<td><%= rs.getString("uid") %></td>
<td><%= rs.getString("writedate") %></td>
<td><%= rs.getInt("count") %></td>
<td><%= rs.getInt("good") %></td>
</tr>
<!-- DB에 정의되있는 column 순서를바꿔서 출력가능 -->
<%
}//반복문 실행하므로 catch와 finally는 밖으로빼서 예외검사
}catch (Exception ex) {
System.out.print(ex.getMessage());
out.println("보드DB연결실패");
} finally {
if (conn != null) {
conn.close();
}
}
%>
</table>
</body>
</html>BoardList
<form method="post">
<table border="1">
<tr>
<td>게시판 번호</td>
<td>제목</td>
<td>내용</td>
<td>작성자</td>
<td>작성일</td>
<td>조회</td>
<td>좋아요~</td>
</tr>
<%
int no = Integer.parseInt(request.getParameter("no")); //형변환
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
String url = "jdbc:mysql://localhost:3306/sample?serverTimezone=Asia/Seoul";
String dbUser = "root";
String dbPw = "1234";
String query = "select * from board where no = ?";
conn = DriverManager.getConnection(url, dbUser, dbPw);
psmt = conn.prepareStatement(query);
psmt.setInt(1, no);
rs = psmt.executeQuery();
if(rs.next()){
%>
<tr>
<td><input type="hidden" name="no" value="<%=rs.getInt("no")%>"></td>
<!-- hidden으로 게시글번호 수정못하게 숨김 -->
<td><input type="text" name="title" value="<%= rs.getString("title") %>"></td>
<td><textarea rows="5" cols="20" name="content">
<%=rs.getString("content")%></textarea></td>
<td><%=rs.getString("uid")%></td>
<td><%=rs.getString("writedate")%></td>
<td><%=rs.getInt("count")%></td>
<td><%=rs.getInt("good")%></td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="수정" formaction="../JSP05/modify.jsp">
<input type="submit" value="삭제" formaction="remove.jsp">
<!-- action은 하나인데 submit이 두개일때 formaction태그 사용
form태그 안에 action은 적지말고-->
</td>
</tr>
<%
}
} catch (SQLException ex) {
out.println("디테일보드DB연결실패");
} finally {
if (conn != null) {
conn.close();
}
}
%>
</table>
</form>Insert
<% request.setCharacterEncoding("utf-8"); %>
<!-- post방식으로 보내서 다시 인코딩해줘야함 -->
<body>
<table border="1">
<tr>
<td>제목</td>
<td><input type="text" size="10" name="title"></td>
</tr>
<tr>
<td>내용</td>
<td><textarea rows="10" cols="30" name="content"></textarea></td>
<!-- row=height, cols=weight -->
</tr>
<tr>
<td colspan="2">
<fmt:formatDate value="${now}" pattern="yyyy-MM-dd hh:mm:ss" /></td>
</tr>
</table>
<%
String title = request.getParameter("title");
String content = request.getParameter("content");
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
PreparedStatement psmt = null;
try{
String url = "jdbc:mysql://localhost:3306/sample?serverTimezone=Asia/Seoul";
String dbUser = "root";
String dbPw = "1234";
String query = "insert into board(title,content) values(?,?)";
conn = DriverManager.getConnection(url, dbUser, dbPw);
psmt = conn.prepareStatement(query);
psmt.setString(1, title);
psmt.setString(2, content);
psmt.executeUpdate();//insert 됨
response.sendRedirect("../JSP04/viewBoard04.jsp");
//추가한뒤에 이동할 페이지
out.println("추가 성공");
} catch (Exception ex) {
System.out.print(ex.getMessage());
out.println("DB연결실패");
} finally {
if (conn != null) {
conn.close();
}
}
%>
</body>Update
<% request.setCharacterEncoding("utf-8"); %>
<!-- 수정할때는 수정이필요한 <td>태그에 input을 추가해줘야함 -->
<%
int no = Integer.parseInt(request.getParameter("no"));
String title = request.getParameter("title");
String content = request.getParameter("content");
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
PreparedStatement psmt = null;
try{
String url = "jdbc:mysql://localhost:3306/sample?serverTimezone=Asia/Seoul";
String dbUser = "root";
String dbPw = "1234";
String query = "update board set title = ?, content = ? where no = ?";
conn = DriverManager.getConnection(url, dbUser, dbPw);
psmt = conn.prepareStatement(query);
psmt.setString(1, title);
psmt.setString(2, content);
psmt.setInt(3, no);
psmt.executeUpdate();//insert 됨
out.println("수정 성공");
response.sendRedirect("../JSP04/viewBoard04.jsp");
} catch (Exception ex) {
System.out.print(ex.getMessage());
out.println("수정DB연결실패");
} finally {
if (conn != null) {
conn.close();
}
}
%>Delete
<% request.setCharacterEncoding("utf-8"); %>
<%
int no = Integer.parseInt(request.getParameter("no"));
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
PreparedStatement psmt = null;
try{
String url = "jdbc:mysql://localhost:3306/sample?
serverTimezone=Asia/Seoul";
String dbUser = "root";
String dbPw = "1234";
String query = "delete from board where no = ?";
conn = DriverManager.getConnection(url, dbUser, dbPw);
psmt = conn.prepareStatement(query);
psmt.setInt(1,no);
psmt.executeUpdate();
response.sendRedirect("../JSP04/viewBoard04.jsp");
} catch (Exception ex) {
System.out.print(ex.getMessage());
out.println("삭제DB연결실패");
} finally {
if (conn != null) {
conn.close();
}
}
%>delete는 게시글번호만 일치하면 되므로 no 만 필요
response.sendRedirect
처리한뒤 이동할 jsp페이지
주의:
response.sendRedirect의 jsp경로 지정
list에서 name서로 일치시켜주기
모든 예외 받기
Exception ex
System.out.print(ex.getMessage());
기계 그잡채가 되고싶다