[JSP] 게시판 ① - DB, 메인페이지, DBCP, DTO
03 Jan 2022 -
8 minute read
BBS (Bulletin Board System)
글 작성 / 파일 업로드&다운로드 / 답변글 작성이 가능한 게시판 만들기
“Connector/J && cos.jar” 라이브러리 잊지 말자!
DB
create database bbsDB;
use bbsDB;
create table tblBoard (
num int auto_increment,
uName char(20) not null,
subject char(50) not null,
content text null,
pos int not null,
ref int not null,
depth int not null,
regDate date not null,
pass char(20) null,
ip char(15) null,
count int not null,
fileName char(50) null,
fileSize int null,
constraint primary key(num)
);
Index.jsp
header 부분은 템플릿으로 계속 사용 할 예정
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>BBS 메인페이지</title>
<link rel="stylesheet" href="/Proj_BBS/style/Style.css">
</head>
<body>
<div id="wrap">
<h1>BBS 메인 페이지</h1>
<h3 align="center">텍스트타입 계층형 게시판</h3>
<header id="header">
<nav>
<ul>
<li><a href="/Proj_BBS/Index.jsp">메인</a></li>
<li><a href="#">로그인</a></li>
<li><a href="/Proj_BBS/bbs/Post.jsp">글쓰기</a></li>
<li><a href="/Proj_BBS/bbs/List.jsp">자유게시판</a></li>
</ul>
</nav>
</header>
<!-- header#header -->
<main id="main">
<h2>자유게시판 메인</h2>
</main>
<!-- main#main -->
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="/Proj_BBS/script/Script.js"></script>
</body>
</html>
style/Style.css
@charset "UTF-8";
* {
-webkit-box-sizing: border-box;
-moz-box-sizing: border-box;
box-sizing: border-box;
}
.flex-container { display: flex; }
div#wrap {
width: 800px;
border: 1px solid #000;
margin: 10px auto;
padding: 20px;
}
#header, #main { margin: 10px 0; }
#main { padding: 40px; }
h1 { text-align: center; }
ul {
list-style: none;
display: flex;
}
li {
width: 170px;
border: 1px solid #000;
text-align: center;
}
li:hover{ background-color: #ddd; }
a {
font-size: 20px;
text-decoration: none;
color: #000;
}
pack_BBS/DBConnectionMgr.java
package pack_BBS;
/**
* Copyright(c) 2001 iSavvix Corporation (http://www.isavvix.com/)
*
* All rights reserved
*
* Permission to use, copy, modify and distribute this material for
* any purpose and without fee is hereby granted, provided that the
* above copyright notice and this permission notice appear in all
* copies, and that the name of iSavvix Corporation not be used in
* advertising or publicity pertaining to this material without the
* specific, prior written permission of an authorized representative of
* iSavvix Corporation.
*
* ISAVVIX CORPORATION MAKES NO REPRESENTATIONS AND EXTENDS NO WARRANTIES,
* EXPRESS OR IMPLIED, WITH RESPECT TO THE SOFTWARE, INCLUDING, BUT
* NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
* FITNESS FOR ANY PARTICULAR PURPOSE, AND THE WARRANTY AGAINST
* INFRINGEMENT OF PATENTS OR OTHER INTELLECTUAL PROPERTY RIGHTS. THE
* SOFTWARE IS PROVIDED "AS IS", AND IN NO EVENT SHALL ISAVVIX CORPORATION OR
* ANY OF ITS AFFILIATES BE LIABLE FOR ANY DAMAGES, INCLUDING ANY
* LOST PROFITS OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES RELATING
* TO THE SOFTWARE.
*
*/
import java.sql.*;
import java.util.Properties;
import java.util.Vector;
/**
* Manages a java.sql.Connection pool.
*
* @author Anil Hemrajani
*/
public class DBConnectionMgr {
private Vector connections = new Vector(10);
private String _driver = "com.mysql.cj.jdbc.Driver",
_url = "jdbc:mysql://127.0.0.1:3306/bbsDB?useSSL=false&serverTimezone=Asia/Seoul&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true",
_user = "root",
_password = "1234";
private boolean _traceOn = false;
private boolean initialized = false;
private int _openConnections = 10;
private static DBConnectionMgr instance = null;
public DBConnectionMgr() {
}
/** Use this method to set the maximum number of open connections before
unused connections are closed.
*/
public static DBConnectionMgr getInstance() {
if (instance == null) {
synchronized (DBConnectionMgr.class) {
if (instance == null) {
instance = new DBConnectionMgr();
}
}
}
return instance;
}
public void setOpenConnectionCount(int count) {
_openConnections = count;
}
public void setEnableTrace(boolean enable) {
_traceOn = enable;
}
/** Returns a Vector of java.sql.Connection objects */
public Vector getConnectionList() {
return connections;
}
/** Opens specified "count" of connections and adds them to the existing pool */
public synchronized void setInitOpenConnections(int count)
throws SQLException {
Connection c = null;
ConnectionObject co = null;
for (int i = 0; i < count; i++) {
c = createConnection();
co = new ConnectionObject(c, false);
connections.addElement(co);
trace("ConnectionPoolManager: Adding new DB connection to pool (" + connections.size() + ")");
}
}
/** Returns a count of open connections */
public int getConnectionCount() {
return connections.size();
}
/** Returns an unused existing or new connection. */
public synchronized Connection getConnection()
throws Exception {
if (!initialized) {
Class c = Class.forName(_driver);
DriverManager.registerDriver((Driver) c.newInstance());
initialized = true;
}
Connection c = null;
ConnectionObject co = null;
boolean badConnection = false;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.get(i);
// If connection is not in use, test to ensure it's still valid!
if (!co.inUse) {
try {
badConnection = co.connection.isClosed();
if (!badConnection)
badConnection = (co.connection.getWarnings() != null);
} catch (Exception e) {
badConnection = true;
e.printStackTrace();
}
// Connection is bad, remove from pool
if (badConnection) {
connections.removeElementAt(i);
trace("ConnectionPoolManager: Remove disconnected DB connection #" + i);
continue;
}
c = co.connection;
co.inUse = true;
trace("ConnectionPoolManager: Using existing DB connection #" + (i + 1));
break;
}
}
if (c == null) {
c = createConnection();
co = new ConnectionObject(c, true);
connections.addElement(co);
trace("ConnectionPoolManager: Creating new DB connection #" + connections.size());
}
return c;
}
/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
public synchronized void freeConnection(Connection c) {
if (c == null)
return;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.get(i);
if (c == co.connection) {
co.inUse = false;
break;
}
}
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.get(i);
if ((i + 1) > _openConnections && !co.inUse)
removeConnection(co.connection);
}
}
public void freeConnection(Connection c, PreparedStatement p, ResultSet r) {
try {
if (r != null) r.close();
if (p != null) p.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void freeConnection(Connection c, Statement s, ResultSet r) {
try {
if (r != null) r.close();
if (s != null) s.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void freeConnection(Connection c, PreparedStatement p) {
try {
if (p != null) p.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void freeConnection(Connection c, Statement s) {
try {
if (s != null) s.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
public synchronized void removeConnection(Connection c) {
if (c == null)
return;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.get(i);
if (c == co.connection) {
try {
c.close();
connections.removeElementAt(i);
trace("Removed " + c.toString());
} catch (Exception e) {
e.printStackTrace();
}
break;
}
}
}
private Connection createConnection()
throws SQLException {
Connection con = null;
try {
if (_user == null)
_user = "";
if (_password == null)
_password = "";
Properties props = new Properties();
props.put("user", _user);
props.put("password", _password);
con = DriverManager.getConnection(_url, props);
} catch (Throwable t) {
throw new SQLException(t.getMessage());
}
return con;
}
/** Closes all connections and clears out the connection pool */
public void releaseFreeConnections() {
trace("ConnectionPoolManager.releaseFreeConnections()");
Connection c = null;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.get(i);
if (!co.inUse)
removeConnection(co.connection);
}
}
/** Closes all connections and clears out the connection pool */
public void finalize() {
trace("ConnectionPoolManager.finalize()");
Connection c = null;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.get(i);
try {
co.connection.close();
} catch (Exception e) {
e.printStackTrace();
}
co = null;
}
connections.removeAllElements();
}
private void trace(String s) {
if (_traceOn)
System.err.println(s);
}
}
class ConnectionObject {
public java.sql.Connection connection = null;
public boolean inUse = false;
public ConnectionObject(Connection c, boolean useFlag) {
connection = c;
inUse = useFlag;
}
}
pack_BBS/BoardBean.java
package pack_BBS;
public class BoardBean {
private int num;
private String uName;
private String subject;
private String content;
private int pos; // (답변글) 순서
private int ref; // (답변글) 답변글이 달린 원본글을 참조하는 번호
private int depth; // (답변글) 답변글 제목 들여쓰기 정도
private String regDate;
private String uPw;
private String ip;
private int count;
private String fileName;
private int fileSize;
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getuName() {
return uName;
}
public void setuName(String uName) {
this.uName = uName;
}
public String getSubject() {
return subject;
}
public void setSubject(String subject) {
this.subject = subject;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getPos() {
return pos;
}
public void setPos(int pos) {
this.pos = pos;
}
public int getRef() {
return ref;
}
public void setRef(int ref) {
this.ref = ref;
}
public int getDepth() {
return depth;
}
public void setDepth(int depth) {
this.depth = depth;
}
public String getRegDate() {
return regDate;
}
public void setRegDate(String regDate) {
this.regDate = regDate;
}
public String getuPw() {
return uPw;
}
public void setuPw(String uPw) {
this.uPw = uPw;
}
public String getIp() {
return ip;
}
public void setIp(String ip) {
this.ip = ip;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public int getFileSize() {
return fileSize;
}
public void setFileSize(int fileSize) {
this.fileSize = fileSize;
}
}