boardmanager.java

JSP源码-5。

package board;

import java.io.*;
import java.util.*;
import java.sql.*;
import javax.servlet.http.*;
import DBConnectionManager; // DB Connection Pooling Manager
import CharacterSet; // 茄臂 胶飘傅 函券阑 困茄 蜡瓶 努贰胶
import MultipartRequest; // 颇老 诀肺靛甫 贸府窍绰 努贰胶

public class BoardManager {
	private DBConnectionManager connMgr;
	private Connection conn;
	// 颇老捞 诀肺靛 登绰 叼泛配府
	private final static String UPLOAD_PATH="c:/tomcat/webapps/jsp/upload/";
	// DB table 捞抚
	private final static String BOARD_TABLE="mybbs";

	// 目池记 钱傅俊辑 目池记阑 掘绢柯促
	private void getConnection() {
		connMgr = DBConnectionManager.getInstance();
		conn = connMgr.getConnection("mydb");
	}

	// 钱傅俊 目池记 馆券
	private void freeConnection() {
		connMgr.freeConnection("mydb", conn);
	}

	// 霸矫拱 静扁 贸府
	public boolean write(HttpServletRequest req) throws Exception {
		// 弥措 诀肺靛 颇老 农扁甫 5皋啊肺 力茄
		MultipartRequest multi = new MultipartRequest(req, UPLOAD_PATH, 5 * 1024 * 1024);

		// 霸矫拱 鞘靛
		String title = CharacterSet.toKorean(multi.getParameter("title"));
		String name = CharacterSet.toKorean(multi.getParameter("name"));
		String email = CharacterSet.toKorean(multi.getParameter("email"));
		String password = CharacterSet.toKorean(multi.getParameter("password"));
		String content = CharacterSet.toKorean(multi.getParameter("content"));
		String filename = "";
		long filesize = 0L;
		int html = 0;
		try {
			if(multi.getParameter("html").equals("on")) html = 1;
			else html = 0;
		} catch(Exception e) { }

		int idx;
		
		try {
			filename = multi.getFilesystemName("file");
			File f = multi.getFile("file");
			filesize = f.length();
		} catch(Exception e) {
			filename = "";
			filesize = 0L;
		}
		
		// 诀肺靛 叼泛配府俊 鞍篮 颇老捞 粮犁窍搁 false 府畔
		if(!filename.equals("")) {
			if(!isValidFilename(filename)) return false;
		}
		
		getConnection();

		// 霸矫拱 锅龋 汲沥
		String query = "select max(idx) from " + BOARD_TABLE;
		Statement stmt = conn.createStatement();
		ResultSet indexRs = stmt.executeQuery(query);
		if(indexRs.next()) idx = indexRs.getInt(1) + 1;
		else idx = 1;
		stmt.close();

		PreparedStatement insertPstmt = null;

		// DB俊 insert query甫 荐青
		query = "insert into " + BOARD_TABLE + "(idx, name, email, password, moment, html, title, content, filename, filesize, ref) values(?, ?, ?, ?, concat(current_date, current_time), ?, ?, ?, ?, ?, ?)";
		insertPstmt = conn.prepareStatement(query);
		insertPstmt.setInt(1, idx);
		insertPstmt.setString(2, name);
		insertPstmt.setString(3, email);
		insertPstmt.setString(4, password);
		insertPstmt.setInt(5, html);
		insertPstmt.setString(6, title);
		insertPstmt.setString(7, content);
		insertPstmt.setString(8, filename);
		insertPstmt.setLong(9, filesize);
		insertPstmt.setInt(10, idx);
		insertPstmt.executeUpdate();

		insertPstmt.close();
		freeConnection();
		return true;
	}
	
	// 霸矫拱 翠函 贸府
	public boolean reply(HttpServletRequest req) throws Exception {
		MultipartRequest multi = new MultipartRequest(req, UPLOAD_PATH, 5 * 1024 * 1024);

		String title = CharacterSet.toKorean(multi.getParameter("title"));
		String name = CharacterSet.toKorean(multi.getParameter("name"));
		String email = CharacterSet.toKorean(multi.getParameter("email"));
		String password = CharacterSet.toKorean(multi.getParameter("password"));
		String content = CharacterSet.toKorean(multi.getParameter("content"));
		String filename = "";
		long filesize = 0L;
		int html = 0;
		try {
			if(multi.getParameter("html").equals("on")) html = 1;
			else html = 0;
		} catch(Exception e) { }

		int idx;
		int ref = Integer.parseInt(multi.getParameter("idx"));
		int step, re_level;
				
		try {
			filename = multi.getFilesystemName("file");
			File f = multi.getFile("file");
			filesize = f.length();
		} catch(Exception e) {
			filename = "";
			filesize = 0L;
		}
		
		if(!filename.equals("")) {
			if(!isValidFilename(filename)) return false;
		}
		
		getConnection();

		String query = "select max(idx) from " + BOARD_TABLE;
		Statement stmt = conn.createStatement();
		ResultSet indexRs = stmt.executeQuery(query);
		if(indexRs.next()) idx = indexRs.getInt(1) + 1;
		else idx = 1;
		stmt.close();
		
		query = "select * from " + BOARD_TABLE + " where idx = ?";
		PreparedStatement pstmt = conn.prepareStatement(query);
		pstmt.setInt(1, ref);
		ResultSet rs = pstmt.executeQuery();
		rs.next();
		// 翠函茄 霸矫拱狼 ref, step, re_level 汲沥
		ref = rs.getInt("ref");
		step = rs.getInt("step");
		re_level = rs.getInt("re_level");
		pstmt.close();
		
		// 翠函茄 霸矫拱苞 包访等 霸矫拱甸狼 step阑 1究 刘啊
		query = "update " + BOARD_TABLE + " set step = step + 1 where ref = ? and step > ?";
		PreparedStatement updatePstmt = conn.prepareStatement(query);
		updatePstmt.setInt(1, ref);
		updatePstmt.setInt(2, step);
		updatePstmt.executeUpdate();
		updatePstmt.close();

		PreparedStatement insertPstmt = null;

		query = "insert into " + BOARD_TABLE + "(idx, name, email, password, moment, html, title, content, filename, filesize, ref, step, re_level) values(?, ?, ?, ?, concat(current_date, current_time), ?, ?, ?, ?, ?, ?, ?, ?)";
		insertPstmt = conn.prepareStatement(query);
		insertPstmt.setInt(1, idx);
		insertPstmt.setString(2, name);
		insertPstmt.setString(3, email);
		insertPstmt.setString(4, password);
		insertPstmt.setInt(5, html);
		insertPstmt.setString(6, title);
		insertPstmt.setString(7, content);
		insertPstmt.setString(8, filename);
		insertPstmt.setLong(9, filesize);
		insertPstmt.setInt(10, ref);
		// step苞 re_level 1究 刘啊
		insertPstmt.setInt(11, ++step);
		insertPstmt.setInt(12, ++re_level);
		insertPstmt.executeUpdate();

		insertPstmt.close();
		freeConnection();
		return true;
	}
	
	/* 诀肺靛 叼泛配府俊 鞍篮 颇老疙捞 粮犁窍绰瘤 犬牢窍绰 皋家靛
	 * DB狼 颇老疙 鞘靛俊辑 鞍篮 霸 乐绰瘤 炼荤茄促.
	 */
	public boolean isValidFilename(String filename) throws Exception {
		getConnection();
		
		String query = "select filename from " + BOARD_TABLE + " where filename = ?";
		PreparedStatement pstmt = conn.prepareStatement(query);
		pstmt.setString(1, filename);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			if(!rs.getString(1).equals("")) {
				pstmt.close();
				freeConnection();
				return false;
			}
			else {
				pstmt.close();
				freeConnection();
				return true;
			}
		} else {
			pstmt.close();
			freeConnection();
			return true;
		}
	}

	/* 霸矫拱 格废 免仿俊辑 荤侩窍扁 困秦 BoardBean阑 硅凯肺 府畔窍绰 皋家靛
	 * 扁夯利栏肺 茄 其捞瘤寸 10俺究 免仿窍扁 困秦 10俺狼 BoardBean阑 府畔.
	 * page 牢磊绰 割 其捞瘤狼 霸矫拱阑 啊廉棵 巴牢瘤甫 汲沥
	 * find, word 牢磊绰 霸矫拱 八祸狼 孽府
	 */
	public BoardBean[] getList(int page, String find, String word) throws Exception {
		getConnection();
		
		String query, cond = null;
		// 八祸俊辑 荤侩且 孽府		
		if(word != null) cond = '%' + word.trim() + '%';
		if(find != null && find.equals("title")) { // 臂 力格栏肺 八祸且 版快
			query = "select * from " + BOARD_TABLE + " where title like '" + cond + "' order by ref desc, step asc";
		} else if(find != null && find.equals("content")) { // 臂 郴侩栏肺 八祸且 版快
			query = "select * from " + BOARD_TABLE + " where content like '" + cond + "' order by ref desc, step asc";
		} else if(find != null && find.equals("total")) { // 臂 力格, 郴侩栏肺 八祸且 版快
			query = "select * from " + BOARD_TABLE + " where title like '" + cond + "' or content like '" + cond + "' order by ref desc, step asc";
		} else if(find != null && find.equals("name")) { // 累己磊狼 捞抚栏肺 八祸且 版快
			query = "select * from " + BOARD_TABLE + " where name like '" + cond + "' order by ref desc, step asc";
		} else { // 八祸窍瘤 臼绊 格废 免仿且 版快
			query = "select * from " + BOARD_TABLE + " order by ref desc, step asc";
		}
		
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);

		// 其捞瘤 荐父怒 扒呈囤促.
		for(int j = 0; j < (page - 1) * 10; j++)
			rs.next();

		int num = 10;
		// 付瘤阜 其捞瘤俊辑 霸矫拱捞 10俺啊 救瞪 版快 割 俺甫 佬阑 巴牢瘤 拌魂
		if(page == getTotalPage(find, word)) {
			int tempNum = getTotalArticle(find, word);
			if((tempNum % 10) == 0) num = 10;
			else num = tempNum % 10;
		}

		BoardBean[] boardBean = new BoardBean[num];

		for(int k = 0; k < boardBean.length; k++)
			boardBean[k] = new BoardBean();
					
		// bean property 技泼
		for(int i = 0; (rs.next() && (i < num)); i++) {
			boardBean[i].setIdx(rs.getInt("idx"));
			boardBean[i].setName(rs.getString("name"));
			boardBean[i].setEmail(rs.getString("email"));
			boardBean[i].setPassword(rs.getString("password"));
			boardBean[i].setMoment(rs.getString("moment"));
			boardBean[i].setHtml(rs.getInt("html"));
			boardBean[i].setTitle(rs.getString("title"));
			boardBean[i].setContent(rs.getString("content"));
			boardBean[i].setCount(rs.getInt("count"));
			boardBean[i].setFilename(rs.getString("filename"));
			boardBean[i].setFilesize(rs.getLong("filesize"));
			boardBean[i].setRef(rs.getInt("ref"));
			boardBean[i].setStep(rs.getInt("step"));
			boardBean[i].setRe_level(rs.getInt("re_level"));
		}
		
		stmt.close();
		freeConnection();
		
		return boardBean;