messagedao.java

BBS系统

package bbs.dao;

import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;

import javax.ejb.CreateException;

import bbs.bean.messageBean;

public class messageDao {
	public ArrayList<messageBean> getmessage(int id){
		ArrayList<messageBean> ar=new ArrayList<messageBean>();
		PreparedStatement pt=null;
		ResultSet rs=null;
		
		Connection con=conn.getInstance();
		try {
			pt=con.prepareStatement("select * from BBSmessage where forumID=? select * from BBSmessage order  by  messageID  desc ");
			pt.setInt(1,id );
			rs=pt.executeQuery();
			while(rs.next()){
				messageBean th=new messageBean();
				
				th.setMessageID(rs.getInt(1));
				th.setDeep(rs.getInt(2));
				th.setContext(rs.getString(3));
				th.setTitle(rs.getString(4));
				th.setClickDate(rs.getString(5));
				th.setCreateDate(rs.getString(6));
				th.setClickCnt(rs.getInt(7));
				th.setRewards(rs.getInt(8));
				th.setUserID(rs.getInt(9));
				th.setFlag(rs.getString(10));
				th.setForumID(rs.getInt(11));
				ar.add(th);	
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pt.close();
				con.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		return ar;
	}
	public ArrayList<messageBean> getmessagetwo(int forumID){
		ArrayList<messageBean> ar=new ArrayList<messageBean>();
		PreparedStatement pt=null;
		ResultSet rs=null;
		
		Connection con=conn.getInstance();
		try {
			pt=con.prepareStatement("select * from BBSmessage where forumID=? order by  messageID  desc ");
			pt.setInt(1,forumID );
			rs=pt.executeQuery();
			while(rs.next()){
				messageBean th=new messageBean();
				
				th.setMessageID(rs.getInt(1));
				th.setDeep(rs.getInt(2));
				th.setContext(rs.getString(3));
				th.setTitle(rs.getString(4));
				th.setClickDate(rs.getString(5));
				th.setCreateDate(rs.getString(6));
				th.setClickCnt(rs.getInt(7));
				th.setRewards(rs.getInt(8));
				th.setUserID(rs.getInt(9));
				th.setFlag(rs.getString(10));
				th.setForumID(rs.getInt(11));
				ar.add(th);	
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pt.close();
				con.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		return ar;
	}
	public ArrayList<messageBean> getmessagethree(int deep){
		ArrayList<messageBean> ar=new ArrayList<messageBean>();
		PreparedStatement pt=null;
		ResultSet rs=null;
		
		Connection con=conn.getInstance();
		try {
			pt=con.prepareStatement("select * from BBSmessage where deep=? order by  messageID  desc ");
			pt.setInt(1,deep );
			rs=pt.executeQuery();
			while(rs.next()){
				messageBean th=new messageBean();
				
				th.setMessageID(rs.getInt(1));
				th.setDeep(rs.getInt(2));
				th.setContext(rs.getString(3));
				th.setTitle(rs.getString(4));
				th.setClickDate(rs.getString(5));
				th.setCreateDate(rs.getString(6));
				th.setClickCnt(rs.getInt(7));
				th.setRewards(rs.getInt(8));
				th.setUserID(rs.getInt(9));
				th.setFlag(rs.getString(10));
				th.setForumID(rs.getInt(11));
				ar.add(th);	
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pt.close();
				con.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		return ar;
	}
	public ArrayList<messageBean> getmessagef(int messageID){
		ArrayList<messageBean> ar=new ArrayList<messageBean>();
		PreparedStatement pt=null;
		ResultSet rs=null;
		
		Connection con=conn.getInstance();
		try {
			pt=con.prepareStatement("select * from BBSmessage where messageID=? ");
			pt.setInt(1,messageID );
			rs=pt.executeQuery();
			rs.next();
				messageBean th=new messageBean();
				th.setMessageID(rs.getInt(1));
				th.setDeep(rs.getInt(2));
				th.setContext(rs.getString(3));
				th.setTitle(rs.getString(4));
				th.setClickDate(rs.getString(5));
				th.setCreateDate(rs.getString(6));
				th.setClickCnt(rs.getInt(7));
				th.setRewards(rs.getInt(8));
				th.setUserID(rs.getInt(9));
				th.setFlag(rs.getString(10));
				th.setForumID(rs.getInt(11));
				ar.add(th);	
			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pt.close();
				con.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		return ar;
	}
	public int getmessagecount(int userID,int deep){
		
		PreparedStatement pt=null;
		ResultSet rs=null;
		int	count=0;
		
		Connection con=conn.getInstance();
		try {
			if(deep==0)
			{
			pt=con.prepareStatement("select * from BBSmessage where userID=? and deep=?");
			pt.setInt(2, 0);
			}else
			pt=con.prepareStatement("select * from BBSmessage where userID=?");
			pt.setInt(1,userID );
			rs=pt.executeQuery();
		    
			while(rs.next()){
				count++;
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pt.close();
				con.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		return count;
	}
	int allRows=0;    //总行数
    int rowPage=10;     //每页行数
    int allPages=0;    //总页数
    int page=0;   //当前页
	public ArrayList<messageBean> getmessagepage(int pag,int forumID){
		ArrayList<messageBean> ar=new ArrayList<messageBean>();
		Statement pt=null;
		ResultSet rs=null;
		 
	     page=pag;
	     int nottop=(page-1)*rowPage;
	     System.out.println(nottop);
	     String sql="select top "+rowPage+" * from BBSmessage where messageID not in (select top "+nottop+"  messageID from BBSmessage where forumID="+forumID+" order by messageID  ) and forumID="+forumID+" order by messageID";
		Connection con=conn.getInstance();
		try {
			pt=con.createStatement();
			rs=pt.executeQuery(sql);
			while(rs.next()){
				messageBean th=new messageBean();
				
				th.setMessageID(rs.getInt(1));
				th.setDeep(rs.getInt(2));
				th.setContext(rs.getString(3));
				th.setTitle(rs.getString(4));
				th.setClickDate(rs.getString(5));
				th.setCreateDate(rs.getString(6));
				th.setClickCnt(rs.getInt(7));
				th.setRewards(rs.getInt(8));
				th.setUserID(rs.getInt(9));
				th.setFlag(rs.getString(10));
				th.setForumID(rs.getInt(11));
				ar.add(th);	
				
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pt.close();
				con.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		return ar;
	}
	 public int getAllPages()
     {
		return allPages;
     }
	 public int getAllRows(int forumID)
		{
			int allRows=0;
			Connection con=null;
			Statement sta=null;
			ResultSet res=null;
			
			con=conn.getInstance();
			try {
				sta=con.createStatement();
				res=sta.executeQuery("select count(*) from BBSmessage where forumID="+forumID+"");
				if(res.next())
				{
					allRows=res.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
			   try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				return allRows;
		}

}