boardmanager.java

JSP源码-5。

	}
	
	// 醚 其捞瘤 荐 拌魂
	public int getTotalPage(String find, String word) throws Exception {
		int totalPage = 1;
		int totalArticle = getTotalArticle(find, word);
		if((totalArticle % 10) == 0) {
			totalPage = totalArticle / 10;
		} else {
			totalPage = totalArticle / 10 + 1;
		}
		return totalPage;
	}
	
	// 醚 霸矫拱 荐 拌魂
	public int getTotalArticle(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 count(*) from " + BOARD_TABLE + " where title like '" + cond + "'";
		} else if(find != null && find.equals("content")) {
			query = "select count(*) from " + BOARD_TABLE + " where content like '" + cond + "'";
		} else if(find != null && find.equals("total")) {
			query = "select count(*) from " + BOARD_TABLE + " where title like '" + cond + "' or content like '" + cond + "'";
		} else if(find != null && find.equals("name")) {
			query = "select count(*) from " + BOARD_TABLE + " where name like '" + cond + "'";
		} else {
			query = "select count(*) from " + BOARD_TABLE;
		}

		int totalArticle = 0;
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		if(rs.next()) {
			totalArticle = rs.getInt(1);
		}
		
		stmt.close();
		freeConnection();
		
		return totalArticle;
	}
	
	// 坷疵 霸矫拱 荐 拌魂
	public int getTodayArticle(String find, String word) throws Exception {
		Calendar now = Calendar.getInstance();
		String nowString = Integer.toString(now.get(Calendar.YEAR)) + "-" + Integer.toString(now.get(Calendar.MONTH) + 1) + "-" + Integer.toString(now.get(Calendar.DATE));
		getConnection();
		
		String query, cond = null;
		if(word != null) cond = '%' + word.trim() + '%';
		if(find != null && find.equals("title")) {
			query = "select count(*) from " + BOARD_TABLE + " where substring(moment, 1, 10) = ? and title like '" + cond + "'";
		} else if(find != null && find.equals("content")) {
			query = "select count(*) from " + BOARD_TABLE + " where substring(moment, 1, 10) = ? and content like '" + cond + "'";
		} else if(find != null && find.equals("total")) {
			query = "select count(*) from " + BOARD_TABLE + " where substring(moment, 1, 10) = ? and (title like '" + cond + "' or content like '" + cond + "')";
		} else if(find != null && find.equals("name")) {
			query = "select count(*) from " + BOARD_TABLE + " where substring(moment, 1, 10) = ? and name like '" + cond + "'";
		} else {
			query = "select count(*) from " + BOARD_TABLE + " where substring(moment, 1, 10) = ?";
		}

		int todayArticle = 0;
		PreparedStatement pstmt = conn.prepareStatement(query);
		pstmt.setString(1, nowString);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			todayArticle = rs.getInt(1);
		}
		
		pstmt.close();
		freeConnection();
		
		return todayArticle;
	}
	
	/* 霸矫拱 郴侩 焊扁俊辑 荤侩窍扁 困秦 霸矫拱狼 牢郸胶甫 牢磊肺 林搁
	 * 秦寸 霸矫拱阑 佬绢辑 BoardBean俊 弊 蔼阑 持绢辑 府畔秦林绰 皋家靛
	 */
	public BoardBean getBoardBean(int idx) throws Exception {
		getConnection();
		
		String query = "select * from " + BOARD_TABLE + " where idx = ?";
		PreparedStatement pstmt = conn.prepareStatement(query);
		pstmt.setInt(1, idx);
		ResultSet rs = pstmt.executeQuery();
		rs.next();
		
		BoardBean boardBean = new BoardBean();

		// bean property 技泼
		boardBean.setIdx(rs.getInt("idx"));
		boardBean.setName(rs.getString("name"));
		boardBean.setEmail(rs.getString("email"));
		boardBean.setPassword(rs.getString("password"));
		boardBean.setMoment(rs.getString("moment"));
		boardBean.setHtml(rs.getInt("html"));
		boardBean.setTitle(rs.getString("title"));
		boardBean.setContent(rs.getString("content"));
		boardBean.setCount(rs.getInt("count"));
		boardBean.setFilename(rs.getString("filename"));
		boardBean.setFilesize(rs.getLong("filesize"));
		boardBean.setRef(rs.getInt("ref"));
		boardBean.setStep(rs.getInt("step"));
		boardBean.setRe_level(rs.getInt("re_level"));
		
		pstmt.close();
		freeConnection();
		
		return boardBean;
	}
	
	// 炼雀荐 盎脚
	public void updateCount(int idx) throws Exception {
		getConnection();
		
		String query = "select count from " + BOARD_TABLE + " where idx = ?";
		PreparedStatement pstmt = conn.prepareStatement(query);
		ResultSet rs;
		
		pstmt.setInt(1, idx);
		rs = pstmt.executeQuery();
		rs.next();
		int count = rs.getInt(1) + 1;
		pstmt.close();
		
		query = "update " + BOARD_TABLE + " set count = ? where idx = ?";
		PreparedStatement updatePstmt = conn.prepareStatement(query);
		
		updatePstmt.setInt(1, count);
		updatePstmt.setInt(2, idx);
		updatePstmt.executeUpdate();
		
		updatePstmt.close();
		freeConnection();
		
		return;
	}
	
	// 霸矫拱 荐沥 皋家靛, 霸矫拱 荐沥俊辑绰 颇老 诀肺靛唱 诀肺靛等 颇老 昏力绰 且 荐 绝霸 沁促.
	public void updateArticle(BoardBean boardBean, int idx) throws Exception {
		getConnection();
		
		String query = "update " + BOARD_TABLE + " set name = ?, email = ?, password = ?, html = ?, title = ?, content = ? where idx = ?";
		PreparedStatement pstmt = conn.prepareStatement(query);
		pstmt.setString(1, CharacterSet.toKorean(boardBean.getName()));
		pstmt.setString(2, CharacterSet.toKorean(boardBean.getEmail()));
		pstmt.setString(3, boardBean.getPassword());
		pstmt.setInt(4, boardBean.getHtml());
		pstmt.setString(5, CharacterSet.toKorean(boardBean.getTitle()));
		pstmt.setString(6, CharacterSet.toKorean(boardBean.getContent()));
		pstmt.setInt(7, idx);
		pstmt.executeUpdate();
		
		pstmt.close();
		freeConnection();
		
		return;
	}
	
	// 秦寸 牢郸胶狼 霸矫拱 昏力
	public void deleteArticle(int idx) throws Exception {
		getConnection();
		
		String query = "select filename from " + BOARD_TABLE + " where idx = ?";
		PreparedStatement pstmt = conn.prepareStatement(query);
		pstmt.setInt(1, idx);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			String filename = rs.getString(1);
			if(!filename.equals("")) {
				File file = new File(UPLOAD_PATH + filename);
				file.delete();
			}
		}
		pstmt.close();
				
		query = "delete from " + BOARD_TABLE + " where idx = ?";
		PreparedStatement deletePstmt = conn.prepareStatement(query);
		deletePstmt.setInt(1, idx);
		deletePstmt.executeUpdate();
		
		deletePstmt.close();
		freeConnection();
		
		return;
	}

	// 捞傈 臂狼 牢郸胶甫 府畔窍绰 皋家靛
	public int getPrevArticleIdx(int idx, String find, String word) throws Exception {
		getConnection();
		
		int prevIdx;

		String query, cond = null;
		if(word != null) cond = '%' + word.trim() + '%';
		if(find != null && find.equals("title")) {
			query = "select idx from " + BOARD_TABLE + " where title like '" + cond + "' order by ref desc, step asc";
		} else if(find != null && find.equals("content")) {
			query = "select idx from " + BOARD_TABLE + " where content like '" + cond + "' order by ref desc, step asc";
		} else if(find != null && find.equals("total")) {
			query = "select idx 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 idx from " + BOARD_TABLE + " where name like '" + cond + "' order by ref desc, step asc";
		} else {
			query = "select idx from " + BOARD_TABLE + " order by ref desc, step asc";
		}

		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		
		while(rs.next()) {
			if(idx == rs.getInt(1)) {
				if(rs.previous()) {
					prevIdx = rs.getInt(1);
					stmt.close();
					freeConnection();
					return prevIdx;
				} else {
					stmt.close();
					freeConnection();
					return idx;
				}
			}
		}
		stmt.close();
		freeConnection();
		return idx; // error!
	}
	
	// 促澜 臂狼 牢郸胶甫 府畔窍绰 皋家靛
	public int getNextArticleIdx(int idx, String find, String word) throws Exception {
		getConnection();
		
		int nextIdx;

		String query, cond = null;
		if(word != null) cond = '%' + word.trim() + '%';
		if(find != null && find.equals("title")) {
			query = "select idx from " + BOARD_TABLE + " where title like '" + cond + "' order by ref desc, step asc";
		} else if(find != null && find.equals("content")) {
			query = "select idx from " + BOARD_TABLE + " where content like '" + cond + "' order by ref desc, step asc";
		} else if(find != null && find.equals("total")) {
			query = "select idx 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 idx from " + BOARD_TABLE + " where name like '" + cond + "' order by ref desc, step asc";
		} else {
			query = "select idx from " + BOARD_TABLE + " order by ref desc, step asc";
		}

		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		
		while(rs.next()) {
			if(idx == rs.getInt(1)) {
				if(rs.next()) {
					nextIdx = rs.getInt(1);
					stmt.close();
					freeConnection();
					return nextIdx;
				} else {
					stmt.close();
					freeConnection();
					return idx;
				}
			}
		}
		stmt.close();
		freeConnection();
		return idx; // error!
	}
}