bbs 论坛 jsp 系统

package com.wxpn.tutorial.servlet; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Collection; import java.util.Iterator; import com.wxpn.tutorial.db.ConnectionPool; import com.wxpn.tutorial.db.DB; /** * 描述: 描述留言信息管理类 * * @Copyright (c) 2005-2008 Wang Xining * @author 王夕宁 * @version 1.0 */ public class BBSMsgMgr { public int compose(BBSMsg msg) { // 创建数据库连接对象： ConnectionPool connPool = DB.getConnPool(); Connection conn = connPool.getConnection(); Statement stmt = null; ResultSet rs = null; try { int maxid = 1; String sql = "select max(item_id) as maxid from bbsitems"; // 创建数据记录集对象： stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { maxid = rs.getInt(1) + 1; } // sql语句： sql = "insert into bbsitems(item_id,item_title,item_content,author," + "compose_date,is_origional,fathers_id,replyed_times,browsed_times," + "latest_replication_id,modify_date,face) values('" + maxid + "','" + msg.getItem_title() + "','" + msg.getItem_content() + "','" + msg.getAuthor() + "','" + msg.getStrCompose_date() + "','1','" + maxid + "','0','0','" + maxid + "','','" + msg.getFace() + "')"; // System.out.println(sql); sql = new String(sql.getBytes("ISO8859-1"), "UTF-8"); // System.out.println(sql); // 执行sql语句： int i = stmt.executeUpdate(sql); return i; } catch (SQLException sqlExc) { sqlExc.printStackTrace(); return -1; } catch (Exception e) { e.printStackTrace(); return -2; } finally { // 关闭连接，释放数据库资源： try { if (stmt != null) { stmt.close(); } connPool.freeConnection(conn); } catch (SQLException sqlExc) { sqlExc.printStackTrace(); } } } public int modify(BBSMsg msg) { // 创建数据库连接对象： ConnectionPool connPool = DB.getConnPool(); Connection conn = connPool.getConnection(); Statement stmt = null; try { // 创建数据记录集对象： stmt = conn.createStatement(); // sql语句： String sql = "update bbsitems set item_title='" + msg.getItem_title() + "',item_content='" + msg.getItem_content() + "',modify_date='" + msg.getStrModify_date() + "',face='"+msg.getFace()+"' where item_id='" + msg.getItem_id() + "'"; // 执行sql语句： System.out.println(sql); sql = new String(sql.getBytes("ISO8859-1"), "UTF-8"); System.out.println(sql); int i = stmt.executeUpdate(sql); return i; } catch (SQLException sqlExc) { sqlExc.printStackTrace(); return -1; } catch (Exception e) { e.printStackTrace(); return -2; } finally { // 关闭连接，释放数据库资源： try { if (stmt != null) { stmt.close(); } connPool.freeConnection(conn); } catch (SQLException sqlExc) { sqlExc.printStackTrace(); } } } public int read(int id) { // 创建数据库连接对象： ConnectionPool connPool = DB.getConnPool(); Connection conn = connPool.getConnection(); Statement stmt = null; try { // 创建数据记录集对象： stmt = conn.createStatement(); // sql语句： String sql = "update bbsitems set BROWSED_TIMES=BROWSED_TIMES+1 " + "where item_id='" + id + "'"; // System.out.println(sql); // 执行sql语句： int i = stmt.executeUpdate(sql); return i; } catch (SQLException sqlExc) { sqlExc.printStackTrace(); return -1; } catch (Exception e) { e.printStackTrace(); return -2; } finally { // 关闭连接，释放数据库资源： try { if (stmt != null) { stmt.close(); } connPool.freeConnection(conn); } catch (SQLException sqlExc) { sqlExc.printStackTrace(); } } } public int reply(BBSMsg msg, int fatherId) { // 创建数据库连接对象： ConnectionPool connPool = DB.getConnPool(); Connection conn = connPool.getConnection(); Statement stmt = null; ResultSet rs = null; try { int maxid = 1; String sql = "select max(item_id) as maxid from bbsitems"; // 创建数据记录集对象： stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { maxid = rs.getInt(1) + 1; } // sql语句： sql = "insert into bbsitems(item_id,item_title,item_content,author," + "compose_date,is_origional,fathers_id,replyed_times,browsed_times," + "latest_replication_id,face) values('" + maxid + "','" + msg.getItem_title() + "','" + msg.getItem_content() + "','" + msg.getAuthor() + "','" + msg.getStrCompose_date() + "','0','" + fatherId + "','0','0','" + maxid + "','" + msg.getFace() + "')"; sql = new String(sql.getBytes("ISO8859-1"), "UTF-8"); stmt.addBatch(sql); sql = "update bbsitems set replyed_times=replyed_times+1,latest_replication_id='" + maxid + "' where item_id='" + fatherId + "'"; stmt.addBatch(sql); // 执行sql语句： stmt.executeBatch(); return 1; } catch (SQLException sqlExc) { sqlExc.printStackTrace(); return -1; } catch (Exception e) { e.printStackTrace(); return -2; } finally { // 关闭连接，释放数据库资源： try { if (stmt != null) { stmt.close(); } connPool.freeConnection(conn); } catch (SQLException sqlExc) { sqlExc.printStackTrace(); } } } public int delete(int id) { // 创建数据库连接对象： ConnectionPool connPool = DB.getConnPool(); Connection conn = connPool.getConnection(); Statement stmt = null; try { // 创建数据记录集对象： stmt = conn.createStatement(); // sql语句： String sql = "delete from bbsitems where item_id = '" + id + "'"; stmt.addBatch(sql); Collection c = getFamily(id); if (c != null) { Iterator iterator = c.iterator(); while (iterator.hasNext()) { sql = "delete from bbsitems where item_id = '" + ((Integer) iterator.next()).intValue() + "'"; // System.out.println("sql:" + sql); stmt.addBatch(sql); } } // 执行sql语句： stmt.executeBatch(); return 1; } catch (SQLException sqlExc) { sqlExc.printStackTrace(); return -1; } catch (Exception e) { e.printStackTrace(); return -2; } finally { // 关闭连接，释放数据库资源： try { if (stmt != null) { stmt.close(); } connPool.freeConnection(conn); } catch (SQLException sqlExc) { sqlExc.printStackTrace(); } } } public Collection getFamily(int id) { // 创建数据库连接对象： ConnectionPool connPool = DB.getConnPool(); Connection conn = connPool.getConnection(); Statement stmt = null; ResultSet rs = null; try { // 创建数据记录集对象： stmt = conn.createStatement(); // sql语句： String sql = "select item_id from bbsitems where fathers_id='" + id + "' and item_id!='" + id + "'"; // System.out.println(sql); // 执行sql语句，返回一个记录集到rs： rs = stmt.executeQuery(sql); Collection c = new ArrayList(); while (rs.next()) { if (c == null) { c = new ArrayList(); } int item_id = rs.getInt("item_id"); c.add(new Integer(item_id)); c.addAll(getFamily(item_id)); } return c; } catch (SQLException sqlExc) { sqlExc.printStackTrace(); return null; } catch (Exception e) { e.printStackTrace(); return null; } finally { // 关闭连接，释放数据库资源： try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } connPool.freeConnection(conn); } catch (SQLException sqlExc) { sqlExc.printStackTrace(); } } } public Collection getFamilyLevel(int id, int level) { // 创建数据库连接对象：