topicsdao.java

一个很好的BBS论坛项目

package com.ibm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.ibm.common.DBConnection;
import com.ibm.dto.TopicDTO;
import com.ibm.vo.TopicVO;


public class TopicsDAO {
	private static TopicsDAO tdao;
	
	private TopicsDAO()
	{
		
	}
	public static TopicsDAO getInstance()
	{
		if(tdao == null)
		{
			tdao = new TopicsDAO();
		}
		return tdao;
	}
	
	
	//插入主题
	public void insertTopic(Connection conn,TopicDTO tdto)
	{
		String sql = "insert into topic values(?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		Timestamp publishtime = new Timestamp(System.currentTimeMillis());
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, tdto.getTopicid());
			ps.setString(2, tdto.getTitle());
			ps.setString(3, tdto.getContent());
			ps.setTimestamp(4,publishtime );
			ps.setTimestamp(5, publishtime);
			ps.setInt(6,tdto.getUid());
			ps.setInt(7, tdto.getBoardid());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
	}
	
	//查询主题
	public ArrayList queryAllTopic(Connection conn)
	{
		ResultSet rs = null;
		ArrayList list = new ArrayList();
		String sql = "select * from topic";
		PreparedStatement ps = null;
		TopicVO tvo = null;
	//	Timestamp publishtime = new Timestamp(System.currentTimeMillis());
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				tvo = new TopicVO();
				tvo.setTopicid(rs.getInt(1));
				tvo.setTitle(rs.getString(2));
				tvo.setContent(rs.getString(3));
				tvo.setPublishtime(rs.getTimestamp(4));
				tvo.setModifytime(rs.getTimestamp(5));
				tvo.setUid(rs.getInt(6));
				tvo.setBoardid(rs.getInt(7));
				list.add(tvo);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
		return list;
	}
	
	public ArrayList queryByid(Connection conn,int boardid)
	{
		ResultSet rs = null;
		ArrayList list = new ArrayList();
		String sql = "select * from topic where boardid=?";
		PreparedStatement ps = null;
		TopicVO tvo = null;
//		Timestamp publishtime = new Timestamp(System.currentTimeMillis());
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, boardid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				tvo = new TopicVO();
				tvo.setTopicid(rs.getInt(1));
				tvo.setTitle(rs.getString(2));
				tvo.setContent(rs.getString(3));
				tvo.setPublishtime(rs.getTimestamp(4));
				tvo.setModifytime(rs.getTimestamp(5));
				tvo.setUid(rs.getInt(6));
				tvo.setBoardid(rs.getInt(7));
				list.add(tvo);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
		return list;
	}
	
	
	public ArrayList queryByname(Connection conn,String boardname)
	{
		ResultSet rs = null;
		ArrayList list = new ArrayList();
		String sql = "select * from topic where boardname=?";
		PreparedStatement ps = null;
		TopicVO tvo = null;
//		Timestamp publishtime = new Timestamp(System.currentTimeMillis());
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, boardname);
			rs = ps.executeQuery();
			while(rs.next())
			{
				tvo = new TopicVO();
				tvo.setTopicid(rs.getInt(1));
				tvo.setTitle(rs.getString(2));
				tvo.setContent(rs.getString(3));
				tvo.setPublishtime(rs.getTimestamp(4));
				tvo.setModifytime(rs.getTimestamp(5));
				tvo.setUid(rs.getInt(6));
				tvo.setBoardid(rs.getInt(7));
				list.add(tvo);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
		return list;
	}
}