guestbookmanagerbean.java

泽风留言本,非常小巧好用的东西啊.请多多照顾.

/**
 *  作者: 佟劲纬   创建日期: 2006-1-14
 *	
 *	QQ: 532443423 Email: [e-mail]
 */
package com.tjw.guestbook.model;

import java.io.*;
import java.sql.*;
import java.util.*;

import com.tjw.guestbook.database.*;
import com.tjw.guestbook.framework.util.*;

/**
 * 
 */
public class GuestBookManagerBean implements Serializable {

	private ConDatabase dataSource = null;

	/**
	 * 设置数据源。
	 */
	public void setDataSource(ConDatabase dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * 从数据库中查找所有留言记录。
	 */
	public List getItemList() throws SQLException {

		List itemList = new ArrayList();
		Connection conn = dataSource.getConnection();

		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		sql.append("a.id, a.userName, a.caption, a.dateTime, a.content, ");
		sql.append("isnull(b.qq, '') as qq, isnull(b.homepage, '') as homepage, isnull(b.email, '') as email, isnull(b.photo, '') as photo, isnull(b.admin, '') as admin, isnull(a.quote, '') as quote ");
		sql.append("from guestbook as a left join userinfo as b ");
		sql.append("on a.userName = b.userName ");
		sql.append("order by a.id desc");

		Statement smt = null;
		ResultSet rs = null;
		try {

			smt = conn.createStatement();
			rs = smt.executeQuery(sql.toString());

			while (rs.next()) {

				ListItemBean listItem = new ListItemBean();

				listItem.setId(rs.getInt("id"));
				listItem.setUserName(rs.getString("userName"));
				listItem.setCaption(StringTool.ConversionAll(rs
						.getString("caption")));
				listItem.setTime(rs.getDate("dateTime") + " "
						+ rs.getTime("dateTime"));
				listItem.setContent(StringTool.ConversionAll(rs
						.getString("content")));
				listItem.setQq(StringTool.ConversionAll(rs.getString("qq")));
				listItem.setHomepage(StringTool.ConversionAll(rs
						.getString("homepage")));
				listItem.setEmail(StringTool.ConversionAll(rs
						.getString("email")));
				listItem.setPhoto(rs.getString("photo"));
				listItem.setAdmin(rs.getString("admin"));
				listItem.setQuote(StringTool.ConversionAll(rs.getString("quote")));
				itemList.add(listItem);
			}
		} finally {

			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}

			if (smt != null) {
				try {
					smt.close();
				} catch (SQLException e) {
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		return itemList;
	}

	/**
	 * 把留言的信息添加到数据库。
	 */
	public void addItem(String userName, String caption, String content)
			throws SQLException {

		Connection conn = dataSource.getConnection();

		StringBuffer sql = new StringBuffer();
		sql.append("insert into guestbook (userName, caption, content) ");
		sql.append("values (?, ?, ?)");

		PreparedStatement ps = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, userName);
			ps.setString(2, caption);
			ps.setString(3, content);
			ps.executeUpdate();

		} finally {

			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void addQuote(String id, String content) throws SQLException {
		
		Connection conn = dataSource.getConnection();

		String sql = "update guestbook set quote = ? where id = ?";

		PreparedStatement ps = null;
		try {

			ps = conn.prepareStatement(sql);
			ps.setString(1, content);
			ps.setString(2, id);
			ps.executeUpdate();

		} finally {

			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}
	
	public void deleteItem(String id) throws SQLException {
		
		Connection conn = dataSource.getConnection();

		String sql = "delete from guestbook where id = ?";

		PreparedStatement ps = null;
		try {

			ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			ps.executeUpdate();

		} finally {

			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		
	}
}