messageboardmgr.java

《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 com.wxpn.tutorial.db.ConnectionPool;
import com.wxpn.tutorial.db.DB;

/**
 * 描述: 描述留言信息管理类
 * 
 * @Copyright (c) 2005-2008 Wang Xining
 * @author 王夕宁
 * @version 1.0
 */

public class MessageBoardMgr {

	public int addMessage(MessageBoard msg) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "insert into messageboard(MB_TITLE,MB_CONTENT,AUTHOR,"
					+ "COMPOSE_DATE) values('" + msg.getMb_title() + "','"
					+ msg.getMb_content() + "','" + msg.getAuthor() + "','"
					+ msg.getStrCompose_date() + "')";
			System.out.println(sql);
			sql=new String(sql.getBytes("ISO8859-1"),"GB2312");
			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 deleteMessage(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "delete from messageboard where MB_ID = '" + id + "')";
			// 执行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 updateMessage(MessageBoard msg) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "update messageboard set MB_TITLE='"
					+ msg.getMb_title() + "',MB_CONTENT='"
					+ msg.getMb_content() + "',AUTHOR='" + msg.getAuthor()
					+ "'," + "COMPOSE_DATE='" + msg.getStrCompose_date()
					+ "' where MB_ID='" + msg.getMb_id() + "')";
			// 执行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 MessageBoard getMessage(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from messageboard where MB_ID='" + id + "'";
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			MessageBoard msg = null;
			if (rs.next()) {
				msg = new MessageBoard();
				msg.setMb_title(rs.getString("MB_TITLE"));
				msg.setMb_content(rs.getString("MB_CONTENT"));
				msg.setAuthor(rs.getString("AUTHOR"));
				msg.setCompose_date(rs.getDate("COMPOSE_DATE"));
			}
			return msg;
		} 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 getAllMessages() {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from messageboard";
			// 执行sql语句：
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			MessageBoard msg = null;
			while (rs.next()) {
				msg = new MessageBoard();
				msg.setMb_id(rs.getInt("MB_ID"));
				msg.setMb_title(rs.getString("MB_TITLE"));
				msg.setMb_content(rs.getString("MB_CONTENT"));
				msg.setAuthor(rs.getString("AUTHOR"));
				msg.setCompose_date(rs.getDate("COMPOSE_DATE"));

				c.add(msg);
				msg = null;
			}
			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 getAllMessages(int pagesize, int page) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from messageboard order by MB_ID desc limit "
					+ (page - 1) * pagesize + "," + pagesize;

			// 执行sql语句：
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			MessageBoard msg = null;
			while (rs.next()) {
				msg = new MessageBoard();
				msg.setMb_id(rs.getInt("MB_ID"));
				msg.setMb_title(rs.getString("MB_TITLE"));
				msg.setMb_content(rs.getString("MB_CONTENT"));
				msg.setAuthor(rs.getString("AUTHOR"));
				msg.setCompose_date(rs.getDate("COMPOSE_DATE"));

				c.add(msg);
				msg = null;
			}
			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 int getCount() {
		if (getAllMessages() != null) {
			return getAllMessages().size();
		} else {
			return 0;
		}
	}
}