대충벌레 블로그
article thumbnail
Published 2021. 1. 6. 22:00
MVC 없이 Dynamic Web Project [게시판] IT 기술/웹[JSP,HTML]
728x90
반응형

게시판 작성을 위한 board 테이블 생성

더보기 
create table or_board(
	idx number primary key, 
	name varchar2(30),	 
	email varchar2(30), 
	title varchar2(50), 
	content varchar2(2000), 
	password varchar2(20),
	write_date varchar2(50),
	hit number(10)
);

select * from OR_BOARD;

create sequence board_idx increment by 1 start with 1;

SELECT password from or_board where idx=0

update OR_BOARD set email='이메일수정', title='타이틀 수정',content='내용수정' where idx=4;

select * from OR_BOARD where name like '이동%';

create table or_board(
	idx number primary key, 
	name varchar2(30),	 
	email varchar2(30), 
	title varchar2(50), 
	content varchar2(2000), 
	password varchar2(20),
	write_date varchar2(50),
	hit number(10)
);

select * from OR_BOARD;

create sequence board_idx increment by 1 start with 1;

SELECT password from or_board where idx=0

update OR_BOARD set email='이메일수정', title='타이틀 수정',content='내용수정' where idx=4;

select * from OR_BOARD where name like '이동%';

 

DB Connection 을 위한 java 소스파일 

package mydb.poolfact;

import java.sql.*;

public class ConnectionFactory {
	
	public static final int ODBC=1;
	public static final int ORACLE=2;
	public static final int MSSQL=3;
	public static final int MYSQL=4;
	
	private String url="localhost";
	private String port="1521";
	private String dbname="디비명";
	private String dbuser="유저명";
	private String pswd="패스워드";
	private String unicode="true";
	private String encode="euc_kr"; // utf-8
	
	public ConnectionFactory() {
		// TODO Auto-generated constructor stub
	}
	
	public Connection getConnection(int dbms) throws SQLException{
		Connection conn=null;
		
		if(dbms==ConnectionFactory.ODBC) {
			try {
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				conn=DriverManager.getConnection("jdbc:odbc:dbdsn","id","password");
			} catch (ClassNotFoundException cnfe) {
				// TODO: handle exception
				System.out.println(cnfe);
			}
		}
		
		else if(dbms==ConnectionFactory.ORACLE) {
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn=DriverManager.getConnection("jdbc:oracle:thin:@"+url+":"+port+":"+dbname,dbuser,pswd);
			} catch (ClassNotFoundException e) {
				// TODO: handle exception
				System.out.println(e);
			}
		}
		
		else if(dbms==ConnectionFactory.MSSQL) {
			try {
				Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
				conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433","novel","jsp");
			} catch (ClassNotFoundException e) {
				// TODO: handle exception
				System.out.println(e);
			}
		}
		
		else if(dbms==ConnectionFactory.MYSQL) {
			try {
				Class.forName("org.gjt.mm.mysql.Driver");
				conn=DriverManager.getConnection("jdbc:mysql://"+url+":"+port+"/"+dbname+"?user="+dbuser
						+"&password="+pswd+"&useUnicode="+unicode+"&characterEncoding="+encode);
			} catch (ClassNotFoundException e) {
				// TODO: handle exception
				System.out.println(e);
			}
		}
		
		return conn;
	}

}

ConnectionPool.java

package mydb.poolfact;

import java.sql.*;
import java.util.*;

public class ConnectionPool {
	
	private static ConnectionPool cp=null;
	private ConnectionFactory cf=null;
	private Vector pool=null;
	private int initCon=0;
	private int maxCon=0;
	private int users=0;
	
	public ConnectionPool() {
		super();
		// TODO Auto-generated constructor stub
		//factory 참조
	}
	
	
	private ConnectionPool(int initCon,int maxCon) throws SQLException{
		this.initCon=initCon;
		this.maxCon=maxCon;
		cf=new ConnectionFactory();
		pool=new Vector();
		for(int i=0;i<initCon;i++) {
			pool.add(createConnection());
		}
	}
	public static synchronized ConnectionPool getInstance() throws SQLException{
		if(cp==null) {
			cp=new ConnectionPool(4,20);
		}
		return cp;
	}
	
	public synchronized Connection getConnection() throws SQLException{
		Connection conn=null;
		while((conn=getPooledConnection())==null) {
			try {
				wait();
			} catch (InterruptedException ie) {
				// TODO: handle exception
			}
		} // while
		users++;
		return conn;
	}
	
	public synchronized void releaseConnection(Connection conn) {
		pool.add(conn);
		users--;
		notifyAll();
	}

	private Connection getPooledConnection() throws SQLException{
		Connection conn=null;
		int size=pool.size();
		
		if(size>0) {
			conn=(Connection)(pool.elementAt(0));
			pool.removeElementAt(0);
		}else if(users<maxCon || maxCon ==0) {
			//conn=createConnection();
			pool.add(createConnection());
		}
		return conn;
	}
	
	private Connection createConnection() throws SQLException{
		Connection conn=cf.getConnection(ConnectionFactory.ORACLE);
		System.out.println("== a connection was created");
		return conn;
	}
	
	
	

}

 

BoardDTO.java 작성

package board;

public class BoardDTO {
	private int idx;
	private String name;
	private String email;
	private String title;
	private String content;
	private String password;
	private String write_date;
	private  int hit;
	public int getIdx() {
		return idx;
	}
	public void setIdx(int idx) {
		this.idx = idx;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getWrite_date() {
		return write_date;
	}
	public void setWrite_date(String write_date) {
		this.write_date = write_date;
	}
	public int getHit() {
		return hit;
	}
	public void setHit(int hit) {
		this.hit = hit;
	}
	
	
	

}

 

BoardDAO.java 작성 앞으로 계속 추가함

package board;

import java.sql.Connection;

import pool.ConnectionPool;

public class BoardDAO {
	
	String board ="or_board"; //테이블명
	String idx="board_idx.nextval"; // 시퀀스명
	ConnectionPool pool=  null;
	
	public BoardDAO() {
		// TODO Auto-generated constructor stub
		try {
			pool=ConnectionPool.getInstance();
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("연결실패");
		}
	}// BoardDAO
	
	
	

}

 

글을 쓰기위한 write.jsp 작성 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글쓰기</title>
<script type="text/javascript">
	/* function check(form){
		if(form.name.value==""){
			alert('이름 기입');
			form.name.focus();
			return false;
		}
	} */
</script>
</head>
<body>
<center> 게시판 글쓰기 </center>

<form name="boardform" method="post" action="write_ok.jsp">
<table width="600" border="1" align="center">
	<tr><td width="20%"> 이름 </td> <td><input type="text" name="name" size="15"></td> </tr>
	<tr><td> 이메일 </td> <td><input type="text" name="email" size="30"></td> </tr>
	<tr><td> 제목 </td> <td><input type="text" name="title" size="50"></td> </tr>
	<tr><td> 내용 </td> <td><textarea name="content" rows="10" cols="40"></textarea></td> </tr>
	<tr><td> 비밀번호 </td> <td><input type="password" name="password" size="20"></td> </tr>
	<tr>
	<td colspan="2" align="center"><input type="submit" value="글쓰기">&nbsp;&nbsp;<input type="reset" value="다시쓰기"></td>
	</tr>
	<tr> <td align="center">[<a href="./list.jsp">리스트</a>]</td> </tr>
</table>
</form>

</body>
</html>

글을 처리하기 위한 페이지 write_ok.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <% request.setCharacterEncoding("utf-8"); %>
<!DOCTYPE html>
<html>
<jsp:useBean id="boardDTO" class="board.BoardDTO"/>
<jsp:useBean id="boardDAO" class="board.BoardDAO"/>

<jsp:setProperty property="*" name="boardDTO"/>
<%

boardDAO.boardInsert(boardDTO); 
%>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
	document.location.href="list.jsp";
</script>
<!-- <meta http-equiv="refresh" content="0;url=./list.jsp"> -->
</head>
<body>

</body>
</html>

BoardDAO.java 에 추가

// 게시글 넣기
	public void boardInsert(BoardDTO boardDTO) throws SQLException{
		Connection c =null;
		//Statement s =c.createStatement();  
		PreparedStatement p = null;
		String query = "";
		try {
			c = pool.getConnection();
			query= "INSERT INTO "+board+" values("+idx+",?,?,?,?,?,to_char(sysdate),0)";
			p=c.prepareStatement(query);
			p.setString(1, boardDTO.getName());
			p.setString(2, boardDTO.getEmail());
			p.setString(3, boardDTO.getTitle());
			p.setString(4, boardDTO.getContent());
			p.setString(5, boardDTO.getPassword());
			
			//s.execute(query)
			p.executeUpdate();
			System.out.println("글 입력 완료 ");
		}catch(Exception e) {
			System.out.println("boardInsert Exception :" +e);
		}finally {
			p.close();
			pool.releaseConnection(c);
		}
		
		
	}

 

입력이 잘 되는지 테스트 

DB에 입력된 값 확인 

// 21년 1월 6일  게시판 글쓰기 까지 완료 +++ 추가할것 ( 리스트 , 글보기 , 수정 ,삭제 )

 

// 21년 1월 7일 게시판 글쓰기 이후 글보기 , 수정 , 삭제 , 리스트 추가했음

 

[ list.jsp ] 리스트를 나타내는 페이지 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
    <%@page import="java.util.*,board.*" %>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<jsp:useBean id="boardDAO" class="board.BoardDAO"/>
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
	function send(form){
		if(form.search.value ==""){
			alert('검색어 입력 바람');
			form.search.focus();
			return false;
		}
		form.submit();
	}
</script>
<meta charset="UTF-8">
<title>게시글 리스트</title>

</head>
<body>
<%-- 페이징 처리??Go ? no .. PASS --%>

<% int boardcnt = boardDAO.boardCount(); %> 

<table border ="0" align="center">
	<tr> <td align='left'> [게시글 수 : <%=boardcnt %>]</td>
		<td align='center'> <font style='bold'>게시글 리스트 </font></td>
		<td align='right'> [<a href='write.jsp'>글쓰기</a>]</td>
	</tr>
</table> <!-- 상단 테이블 --><br>

<table width="600" align="center" border="1">
	<tr>
		<td width="10%">&nbsp;번호</td>
		<td width="40%">&nbsp;제목</td>
		<td width="15%">&nbsp;이름</td>
		<td width="10%">&nbsp;일자</td>
		
	</tr>
	
	<%
	ArrayList list = boardDAO.getBoardList(); 
	System.out.println("리스트 가져옴");
	// 빈 가져옴
	for(int i=0;i<list.size();i++){
		BoardDTO boardDTO = (BoardDTO)list.get(i);
	%>
	
	<tr>
		<td><%=boardDTO.getIdx() %></td>
		<td><a href='content.jsp?idx=<%=boardDTO.getIdx()%>'> <%=boardDTO.getTitle() %></a></td>
		<td> <%=boardDTO.getName() %></td>
		<td> <%=boardDTO.getWrite_date() %></td>
	</tr>
	<%} %>
</table><br>
	<!-- 검색 폼 -->
	<div align="center">
	<form action='search.jsp' method="post" name="searchform">
		<select name='type'>
			<option value=name>이름</option>
			<option value=title selected="selected">제목</option>
			<option value=content>내용</option>
		</select>
	
	<input type="text" name="search" size="20">
	<input type="button" value='찾기' onclick='send(this.form);'>
	</form>
	</div>


</body>
</html>

글 갯수를 가져오기 위한 BoardDAO 추가

// 총갯수 구하기
	public int boardCount() throws SQLException{
		int cnt =0;
		Connection c =null;
		PreparedStatement p =null;
		ResultSet r = null;
		String query="";
		
		try {
			c = pool.getConnection();
			query = "SELECT count(*) from "+board;
			p=c.prepareStatement(query);
			r=p.executeQuery(query);
			r.next();
			cnt=r.getInt(1);
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("boardCount Exception : "+e);
		}finally {
			r.close();
			p.close();
			pool.releaseConnection(c);
		}
		return cnt;
	}//boardCount()

리스트를 가져오기 위한 DAO 추가

// 게시글 리스트 가져오기
	public ArrayList getBoardList() throws SQLException{ //Vector , ArrayList
		Connection c= null;
		PreparedStatement p = null;
		ResultSet r = null;
		String query ="";
		ArrayList boardList = new ArrayList();
		try {
			c = pool.getConnection();
			query ="select * from "+board+" order by idx asc ";
			p=c.prepareStatement(query);
			r=p.executeQuery(query);
			while(r.next()) {
				BoardDTO boardDTO = new BoardDTO();
				boardDTO.setIdx(r.getInt("idx"));
				boardDTO.setName(r.getString("name"));
				boardDTO.setEmail(r.getString("email"));
				boardDTO.setTitle(r.getString("title"));
				boardDTO.setContent(r.getString("content"));
				boardDTO.setWrite_date(r.getString("write_date"));
				
				boardList.add(boardDTO);
			}
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("getBoardList Exception "+e);
		}finally {
			r.close();
			p.close();
			pool.releaseConnection(c);
		}
		return boardList;
	}//getBoardList

제대로 나오는지 체크

 

이제 글을 볼수 있게 상세정보.jsp 페이지를 생성

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ page import="board.*" %>
    <jsp:useBean id="boardDTO" class="board.BoardDTO"/>
    <jsp:useBean id="boardDAO" class="board.BoardDAO"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
	function update(idx){
		location.href="edit.jsp?idx="+idx;
	}
	function del(idx){
		location.href="del.jsp?idx="+idx
	}
</script>
</head>
<body>
<%
int idx = Integer.parseInt(request.getParameter("idx"));
// 조회수 올려? ...
	 
boardDTO = boardDAO.boardView(idx); // 글 보기
%>
<center> 글보기 </center>

<table width="600" border="1" align="center">
	<tr><td width="15%"> 이름 </td> <td><%=boardDTO.getName() %></td> </tr>
	<tr><td> 이메일 </td> <td><a href="mailto:<%=boardDTO.getEmail() %>"><%=boardDTO.getEmail() %></a></td> </tr>
	<tr><td> 제목 </td> <td><%=boardDTO.getTitle() %></td> </tr>
	<tr><td> 내용 </td> <td valign="top"><%=boardDTO.getContent() %></td> </tr>
	<tr><td> 작성일 </td> <td><%=boardDTO.getWrite_date() %></td> </tr>
	<tr> <td colspan="2"><input type="button" value="수정" onclick="update(<%=boardDTO.getIdx() %>)">&nbsp;&nbsp;
		<input type="button" value="삭제" onclick="del(<%=boardDTO.getIdx() %>)">&nbsp;&nbsp;
	    [<a href="./list.jsp">리스트</a>]</td> </tr>
</table>




</body>
</html>

글 보기 위한 DAO 추가

//글 보기 content
	public BoardDTO boardView(int idx) throws SQLException{
		Connection c=null;
		PreparedStatement p = null;
		ResultSet r = null;
		String query = null;
		BoardDTO boardDTO = new BoardDTO();
		
		try {
			c = pool.getConnection();
			query="select * from "+board+" where idx="+idx;
			p=c.prepareStatement(query);
			r=p.executeQuery(query);
			if(r.next()) {
				boardDTO.setIdx(r.getInt("idx"));
				boardDTO.setName(r.getString("name"));
				boardDTO.setEmail(r.getString("email"));
				boardDTO.setTitle(r.getString("title"));
				boardDTO.setContent(r.getString("content"));
				boardDTO.setWrite_date(r.getString("write_date"));
				
			}
		}catch (Exception e) {
			// TODO: handle exception
			System.out.println("boardView Exception" + e);
		}finally {
			r.close();
			p.close();
			pool.releaseConnection(c);
		}
		return boardDTO;
	}// boardView

글 상세보기 되는지 확인

 

 

이제 수정처리를 하기 위해 수정.jsp 를 만들어줌

글쓰는 창과 똑같으므로 화면소스는 비슷

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
    <%@page import="board.*" %>
    
    <jsp:useBean id="boardDTO" class="board.BoardDTO"/>
    <jsp:useBean id="boardDAO" class="board.BoardDAO"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
int idx = Integer.parseInt(request.getParameter("idx"));
boardDTO = boardDAO.boardView(idx);
%>

<center> 글수정 </center>
<form name="boardform" method="post" action="edit_ok.jsp">
<input type="hidden" name="idx" value="<%=boardDTO.getIdx()%>">
<table width="600" border="1" align="center">
	<tr><td width="15%"> 이름 </td> <td><input type="text" readonly="readonly" name="name" size="15" value="<%=boardDTO.getName()%>"></td> </tr>
	<tr><td> 이메일 </td> <td><input type="text" name="email" size="30" value="<%=boardDTO.getEmail()%>"></td> </tr>
	<tr><td> 제목 </td> <td><input type="text" name="title" size="50" value="<%=boardDTO.getTitle()%>"></td> </tr>
	<tr><td> 내용 </td> <td><textarea name="content" rows="10" cols="40"><%=boardDTO.getContent() %></textarea></td> </tr>
	<tr><td> 비밀번호 </td> <td><input type="password" name="password" size="20"></td> </tr>
	<tr>
	<td colspan="2" align="center"><input type="submit" value="수정하기">&nbsp;&nbsp;<input type="reset" value="다시쓰기"></td>
	</tr>
	<tr> <td align="center">[<a href="./content.jsp?idx=<%=boardDTO.getIdx()%>">돌아가기</a>]</td> </tr>
</table>
</form>

</body>
</html>

수정처리를 하기위한 edit_ok.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
     <% request.setCharacterEncoding("utf-8"); %>
    <jsp:useBean id="boardDTO" class="board.BoardDTO"/>
    <jsp:useBean id="boardDAO" class="board.BoardDAO"/>
    <jsp:setProperty property="*" name="boardDTO"/>
    
<!DOCTYPE html>
<html>
<head>
<%
int idx = boardDTO.getIdx();
boolean flag;
flag= boardDAO.Update(boardDTO); // 비밀번호맞으면 참 틀리면 거짓
System.out.println(flag);


 if(!flag){ //암호가 일치하지 않으면
	out.println("<Script type=\"text/javascript\">");
	out.println("alert(\"암호가 일치하지 않습니다\")");
	out.println("history.back()");
	out.println("</script>");
}  
else{ //암호가 일치하면
	out.println("<Script type=\"text/javascript\">");
	out.println("alert(\"글이 수정되었습니다\")");
	out.println("location.href=\'content.jsp?idx="+idx+"\'");
	out.println("</script>");
} 

%>

<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>



</body>
</html>

암호를 확인해서 맞으면 수정 하기 위한 DAO 추가

우선 패스워드 체크먼저 한다

// 암호 확인을 위한 패스워드 확인
	public boolean passwordcheck(int idx,String pwd) throws SQLException{
		Connection c =null;
		PreparedStatement p =null;
		ResultSet r = null;
		
		String query = "";
		String pwd2 = "";
		boolean check = false;
		
		try {
			c=pool.getConnection();
			query="SELECT password from "+board+" where idx="+idx;
			System.out.println(query);
			p=c.prepareStatement(query);
			r=p.executeQuery(query);
			
			r.next();
			
			pwd2=r.getString(1);
			pwd=pwd.trim(); //입력 폼 공란 제거
			pwd2=pwd2.trim(); // DB 암호 공란 제거
			
			if(pwd.equals(pwd2)) { // 암호 일치시
				check=true;
			}else {
				check=false;
			}
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("passwordcheck Exception :"+e);
		}finally {
			r.close();
			p.close();
			pool.releaseConnection(c);
		}
		
		return check;
	}// passwordcheck

update 추가

// 수정처리
	public boolean Update(BoardDTO boardDTO) throws Exception{
		Connection c= null;
		//PreparedStatement p =null;
		Statement s= null;
		String query= "";
		
		boolean flag = false;
		
		int idx=boardDTO.getIdx();
		String pwd = boardDTO.getPassword();
		String email= boardDTO.getEmail();
		String title = boardDTO.getTitle();
		String content = boardDTO.getContent();
		
		try {
			c = pool.getConnection(); // 제발좀 넣자..;
			s=c.createStatement();
			if(passwordcheck(idx, pwd)) {
				
				//query = "UPDATE "+board+" SET email= ? , title = ? , content = ? where idx= "+idx;
				query = "Update "+board+" SET email = '"+email+"', title='"+title+"', content='"+content+"' where idx ="+idx;
				s.executeUpdate(query);
				
				//p=c.prepareStatement(query);
				//p.setString(1, boardDTO.getEmail());
				//p.setString(2, boardDTO.getTitle());
				//p.setString(3, boardDTO.getContent());
				
				//p.executeUpdate();
				System.out.println(query);
				flag=true;
			}else {
				flag=false;
			}
		}catch (Exception e) {
			// TODO: handle exception
			System.out.println("Update Exception"+e);
		}finally {
			//p.close();
			s.close();
			pool.releaseConnection(c);
		}
		return flag;
		
	}//update

 

글 삭제를 위한 삭제.jsp 추가 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<% String idx =request.getParameter("idx"); %>
<meta charset="UTF-8">
<title>게시글 삭제</title>
</head>
<body>
<br><br><br>
<form method="post" action="del_ok.jsp?idx=<%=idx %>">
<div align="center">
	<p> 암호를 입력하세요 </p>
	<input type="password" name="password" size="20">&nbsp;<input type="submit" value="삭제">
</div>
</form>

</body>
</html>

삭제할때는 간단하게 비밀번호 맞으면 삭제

삭제처리를 할 del_ok.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
     <% request.setCharacterEncoding("utf-8"); %>
    <jsp:useBean id="boardDTO" class="board.BoardDTO"/>
    <jsp:useBean id="boardDAO" class="board.BoardDAO"/>
    <jsp:setProperty property="*" name="boardDTO"/>
    
<!DOCTYPE html>
<html>
<head>
<%
int idx = boardDTO.getIdx();
boolean flag;
flag= boardDAO.Update(boardDTO); // 비밀번호맞으면 참 틀리면 거짓
System.out.println(flag);


 if(!flag){ //암호가 일치하지 않으면
	out.println("<Script type=\"text/javascript\">");
	out.println("alert(\"암호가 일치하지 않습니다\")");
	out.println("history.back()");
	out.println("</script>");
}  
else{ //암호가 일치하면
	out.println("<Script type=\"text/javascript\">");
	out.println("alert(\"글이 수정되었습니다\")");
	out.println("location.href=\'content.jsp?idx="+idx+"\'");
	out.println("</script>");
} 

%>

<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>



</body>
</html>

 

마찬가지로 DAO 추가 해준다

 

//글 삭제
	public boolean boardDelete(int idx,String pwd) throws SQLException{
		Connection c= null;
		//PreparedStatement p = null;
		Statement s =null;
		String query = "";
		boolean check =false;
		try {
			c=pool.getConnection();
			s=c.createStatement();
			if(passwordcheck(idx, pwd)) {
				
				query="delete from "+board+" where idx="+idx;
				s.executeUpdate(query);
				//p=c.prepareStatement(query);
				//p.executeUpdate();
				check=true;
			}else {
				check =false;
			}
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("boardDelete Exception" +e);
		}finally {
			//p.close();
			s.close();
			pool.releaseConnection(c);
		}
		return check;
	}//boardDelete

 

마지막으로 게시글 검색을 위한 search.jsp 추가 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="board.*,java.util.*"%>
    <% request.setCharacterEncoding("utf-8"); %>
    <jsp:useBean id="boardDAO" class="board.BoardDAO"/>
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>검색</title>
<script type="text/javascript">
	function search(form){
		if(form.search.value ==""){
			alert('검색어 입력 바람');
			form.search.focus();
			return false;
		}
		form.submit();
	}
</script>
</head>
<body>
<%
String type = request.getParameter("type");
String search=request.getParameter("search");
int cnt =boardDAO.boardCount(type,search);

%>

<table border ="0" align="center"> 
	<tr> <td align='left'> [게시글 수 : <%=cnt %>]</td>
		<td align='center'> <font style='bold'>게시글 리스트 </font></td>
		<td align='right'> [<a href='write.jsp'>글쓰기</a>]</td>
	</tr>
</table> <!-- 상단 테이블 --><br>

<table width="600" align="center" border="1">
	<tr>
		<td width="10%">&nbsp;번호</td>
		<td width="40%">&nbsp;제목</td>
		<td width="15%">&nbsp;이름</td>
		<td width="10%">&nbsp;일자</td>
		
	</tr>
	
	<% 
	ArrayList list = boardDAO.getSearchList(type,search); 
	System.out.println("검색 리스트 가져옴");
	// 빈 가져옴
	for(int i=0;i<list.size();i++){
		BoardDTO boardDTO = (BoardDTO)list.get(i);
	%>
	
	<tr>
		<td><%=boardDTO.getIdx() %></td>
		<td><a href='content.jsp?idx=<%=boardDTO.getIdx()%>'> <%=boardDTO.getTitle() %></a></td>
		<td> <%=boardDTO.getName() %></td>
		<td> <%=boardDTO.getWrite_date() %></td>
	</tr>
	<%} %>
</table><br>
	<!-- 검색 폼 -->
	<div align="center">
	<form action='search.jsp' method="post" name="searchform">
		<select name='type'>
			<option value=name>이름</option>
			<option value=title>제목</option>
			<option value=content>내용</option>
		</select>
	
	<input type="text" name="search" size="20">
	<input type="button" value='찾기' onclick='search(this.form);'>
	</form>
	</div>

</body>
</html>

 

search할때 검색하는 게시글 수를 구하기위한 DAO 추가

 

// 검색글 총갯수
	public int boardCount(String type,String search) throws SQLException{
		Connection c = null;
		PreparedStatement p = null;
		ResultSet r = null;
		String query = "";
		int cnt =0;
		
		try {
			c= pool.getConnection();
			query="SELECT count(*) from "+board+" where "+type+" like "+"'%"+search+"%'";
			p=c.prepareStatement(query);
			r=p.executeQuery(query);
			System.out.println(query);
			r.next();
			cnt=r.getInt(1);
			System.out.println(cnt);
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("boardCount Exception :" +e);
		}finally {
			r.close();
			p.close();
			pool.releaseConnection(c);
		}
		return cnt;

검색한 글 리스트를 가져오기위한 DAO 추가

 

// 검색글 리스트 가져오기
	public ArrayList getSearchList(String type,String search) throws SQLException{
		Connection c= null;
		PreparedStatement p = null;
		ResultSet r= null;
		String query = "";
		ArrayList searchList = new ArrayList();
		try {
			c=pool.getConnection();
			query = "Select * from "+board+ " where "+type+" like '%"+search+"%' order by idx desc";
			p=c.prepareStatement(query);
			r=p.executeQuery(query);
			
			while(r.next()) {
				BoardDTO boardDTO = new BoardDTO();
				boardDTO.setIdx(r.getInt("idx"));
				boardDTO.setName(r.getString("name"));
				boardDTO.setEmail(r.getString("email"));
				boardDTO.setTitle(r.getString("title"));
				boardDTO.setContent(r.getString("content"));
				boardDTO.setWrite_date(r.getString("write_date"));
				
				searchList.add(boardDTO);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("getSearchList Exception : "+e);
		}finally {
			r.close();
			p.close();
			pool.releaseConnection(c);
		}
		return searchList;
	}// getSearchList

 

정상적으로 작동하는지 테스트 해본다.

테스트 검색시 아래와 같이 검색됨.

 

 

MVC 없이 기본적인 CRUD를 활용해 게시판을 만들어보았다.

만들면서 내가 헷갈렷던 부분이 있는데 PreparedStatement 이부분 사용해서 수정,삭제를 할려고 했는데

비밀번호가 맞았을때는 정상처리가 됐으나 틀렷을때는 자꾸 오류페이지로 이동이 되었다.

 

아시는분은 댓글 부탁드립니다.

 

만들었던 프로그램은 깃허브에 올려놓겠습니다.

https://github.com/inthegun/bug_board

 

inthegun/bug_board

오라클 게시판 . Contribute to inthegun/bug_board development by creating an account on GitHub.

github.com

 

반응형
저작자표시 비영리 동일조건

'IT 기술 > 웹[JSP,HTML]' 카테고리의 다른 글

MVC 패턴  (0) 2021.02.22
MVC 패턴 Oracle 게시판 [글 삽입 , 글 리스트 ]  (1) 2021.01.11
MVC 없이 Dynamic Web Project 회원가입, 로그인  (3) 2021.01.06
이클립스[Mysql,Oracle] Java로 연동테스트  (0) 2020.12.16
이클립스 Database 연동하기 [MySQL , Oracle]  (0) 2020.12.03
profile

대충벌레 블로그

@대충벌레

포스팅이 좋았다면 "좋아요❤️" 또는 "구독👍🏻" 해주세요!

검색 태그

서버
추상
2급
이클립스
영구정지
jdk
리눅스마스터
oracle
네트워크
자격증
데이터
카카오톡
리눅스
컬렉션
java
대학생
정보처리
안드로이드
대충벌레
기초
자바
해제
대충
족보
합격
산업기사
제네릭
시험
상속
네트워크관리사

티스토리툴바