newsmgr.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 NewsMgr {

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

			// sql语句：
			String sql = "insert into news(TITLE,content,AUTHOR,"
					+ "COMPOSE_DATE) values('" + msg.getTitle() + "','"
					+ msg.getContent() + "','" + 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 delete(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

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

			// sql语句：
			String sql = "update news set title='"
					+ msg.getTitle() + "',content='"
					+ msg.getContent() + "',AUTHOR='" + msg.getAuthor()
					+ "'," + "COMPOSE_DATE='" + msg.getStrCompose_date()
					+ "' where id='" + msg.getId() + "'";
			// 执行sql语句：
			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 News 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 news where id='" + id + "'";
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			News msg = null;
			if (rs.next()) {
				msg = new News();
				msg.setId(id);
				msg.setTitle(rs.getString("title"));
				msg.setContent(rs.getString("content"));
				msg.setAuthor(rs.getString("AUTHOR"));
				msg.setStrCompose_date(rs.getString("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 news";
			// 执行sql语句：
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			News msg = null;
			while (rs.next()) {
				msg = new News();
				msg.setId(rs.getInt("id"));
				msg.setTitle(rs.getString("title"));
				msg.setContent(rs.getString("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 news order by id desc limit "
					+ (page - 1) * pagesize + "," + pagesize;

			// 执行sql语句：
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			News msg = null;
			while (rs.next()) {
				msg = new News();
				msg.setId(rs.getInt("id"));
				msg.setTitle(rs.getString("title"));
				msg.setContent(rs.getString("content"));
				msg.setAuthor(rs.getString("AUTHOR"));
				msg.setStrCompose_date(rs.getString("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;
		}
	}
}