manage.java

学生信息管理系统

package bbs.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class Manage
{
	/**
	 * 存储帖子
	 * @param bean 帖子对象
	 * @throws Exception
	 */
	public static void save(BBSBean bean) throws Exception
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		try
		{
			conn = DBUtil.getConn();
			
			StringBuilder sql = new StringBuilder();
			sql.append("insert into msg");
			sql.append("(");
			sql.append("	parentId,");
			sql.append("	subject,");
			sql.append("	content,");
			sql.append("	sendTime,");
			sql.append("	sender");
			sql.append(")values(");
			sql.append("	?,");
			sql.append("	?,");
			sql.append("	?,");
			sql.append("	CURRENT_TIMESTAMP,");
			sql.append("	?");
			sql.append(")");

			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, bean.getParentId());
			pstmt.setString(2, bean.getSubject());
			pstmt.setString(3, bean.getContent());
			//pstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
			pstmt.setString(4, bean.getSender());
			
			pstmt.execute();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			
			throw e;
		}
		finally
		{
			try
			{
				if (conn != null)
				{
					conn.close();
				}
			}
			catch (SQLException e)
			{
			}
		}

	}
	
	/**
	 * 获得全部帖子
	 * @return
	 * @throws Exception
	 */
	public static ArrayList<BBSBean> getList() throws Exception
	{
		ArrayList<BBSBean> retList = new ArrayList<BBSBean>();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtil.getConn();

			StringBuilder sql = new StringBuilder();
			//查询起始贴集合
			sql.append("select * from msg where parentId = 0 order by sendTime desc");

			pstmt = conn.prepareStatement(sql.toString());
			
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				//查询一条起始贴
				
				//创建起始贴对象
				BBSBean subBean = new BBSBean();
				//将起始贴对象加入集合
				retList.add(subBean);
				
				subBean.setId(rs.getInt("id"));
				subBean.setSubject(rs.getString("subject"));
				//subBean.setContent(rs.getString("content"));
				subBean.setSendTime(rs.getTimestamp("sendTime"));
				subBean.setSender(rs.getString("sender"));
			}
				
			//针对每个起始贴，获取回帖及回帖的回帖
			for (BBSBean subBean : retList)
			{
				getSubList(subBean, conn);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			
			throw e;
		}
		finally
		{
			try
			{
				conn.close();
			}
			catch (Exception e)
			{
				
			}
		}
		
		return retList;
	}
	
	/**
	 * 递归获得跟帖列表
	 * @param bean 当前帖子对象
	 * @param conn 数据库连接
	 * @throws Exception
	 */
	private static void getSubList(BBSBean bean, Connection conn) throws Exception
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			StringBuilder sql = new StringBuilder();
			sql.append("select * from msg where parentId = ?");

			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, bean.getId());
			
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				//查询一条回帖
				
				//创建回帖对象
				BBSBean subBean = new BBSBean();
				//将回帖对象加入父贴的回帖集合
				bean.followList.add(subBean);
				
				subBean.setId(rs.getInt("id"));
				subBean.setSubject(rs.getString("subject"));
				//subBean.setContent(rs.getString("content"));
				subBean.setSendTime(rs.getTimestamp("sendTime"));
				subBean.setSender(rs.getString("sender"));
			}
				
			//针对每个回帖，递归获取回帖的回帖列表
			for (BBSBean subBean : bean.followList)
			{
				getSubList(subBean, conn);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			
			throw e;
		}

	}
	
	/**
	 * 获得id对应的帖子
	 * @param id 帖子id
	 * @return 返回帖子对象。如果ID不存在返回null
	 * @throws Exception
	 */
	public static BBSBean getContent(int id) throws Exception
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtil.getConn();

			StringBuilder sql = new StringBuilder();
			//查询起始贴集合
			sql.append("select * from msg where id = ?");

			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				BBSBean bean = new BBSBean();
				
				bean.setId(rs.getInt("id"));
				bean.setSubject(rs.getString("subject"));
				bean.setContent(rs.getString("content"));
				bean.setSendTime(rs.getTimestamp("sendTime"));
				bean.setSender(rs.getString("sender"));
				
				return bean;
			}
			else
			{
				return null;
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			
			throw e;
		}
		finally
		{
			try
			{
				conn.close();
			}
			catch (Exception e)
			{
				
			}
		}
	}
	
	/**
	 * 时间转换
	 * @param date
	 * @return
	 * @throws Exception
	 */
	public static String date2String(Date date)
	{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    	String s = sdf.format(date);
    	return s;
	}
}