notedao.java

一个简单的BBS论坛

package com.dao;

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


import com.bean.Responses;
import com.bean.Sort;
import com.bean.Topic;
import com.bean.User;

import com.util.DBUtil;

public class NoteDAO {

	//按论坛分类查找所有的帖子消息
	public ArrayList<Sort> find()
	{
		ArrayList<Sort> sortlist = new ArrayList<Sort>();
		Connection conn = null;
		conn = DBUtil.getJDBCConnection();
		String sql1 = "select sortname,master,count(sortid),sortid from sort,topic group by sortname,master,sort.id,sortid having sort.id =sortid";
		try {
				PreparedStatement stmt1 = conn.prepareStatement(sql1);
				ResultSet rs1 = stmt1.executeQuery();
				while(rs1.next())
				{
					String sortname = rs1.getString(1);
					String master = rs1.getString(2);
					String count = rs1.getString(3);
					int sortid = rs1.getInt(4);
					String sql2 = "select top 1 with ties topicname,time,owner,id from topic  where sortid='"+sortid+"'order by time desc";
					PreparedStatement stmt2 = conn.prepareStatement(sql2);
					ResultSet rs2 = stmt2.executeQuery();
					while(rs2.next())
					{
						String topicname = rs2.getString(1);
						String time = rs2.getString(2);
						String owner = rs2.getString(3);
						int id = rs2.getInt(4);
						Sort sort = new Sort();
						sort.setSortname(sortname);
						sort.setMaster(master);
						sort.setCount(count);
						sort.setId(sortid);
						Topic topic = new Topic();
						topic.setTopicname(topicname);
						topic.setTime(time);
						topic.setOwner(owner);
						topic.setId(id);
						sort.setTopic(topic);
						sortlist.add(sort);					
					}
				}			
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		finally
		{
			DBUtil.closeConnection(conn);
		}
		return sortlist;
	}
	
	//按论坛的主题查找帖子(根据主题编号)
	public ArrayList<Topic> findNote(int id)
	{
		ArrayList<Topic> notelist = new ArrayList<Topic>();
		//打开连接
		Connection conn = DBUtil.getJDBCConnection();		
		
		try {
			//要执行的SQL语句
			String sql = "select topic.id,topicname,time,owner," +
					"(select count(*) from responses where topic.id=topicid)," +
					"(select max([time]) from responses where topic.id=topicid)," +
					"sortname from topic,sort  where sort.id=? and sortid=? " +
					"group by sortname,topic.id,topicname,owner,[time] " +
					"order by (select max([time]) from responses where topic.id=topicid) desc";

			//预处理语句
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			stmt.setInt(2, id);

			//结果集
			ResultSet rs = stmt.executeQuery();

			while(rs.next())
			{
				int topicid = rs.getInt(1);
				String topicname = rs.getString(2);
				String time = rs.getString(3);
				String owner = rs.getString(4);
				//相关主题中的回复数
				String resCount = rs.getString(5);
				//最新回复的时间
				String resTime = rs.getString(6);
				String sortname = rs.getString(7);
				//封装数据
				Topic note = new Topic();
				note.setTopicname(topicname);
				note.setId(topicid);
				note.setOwner(owner);
				note.setTime(time);
				Sort sort = new Sort();
				sort.setSortname(sortname);
				Responses resp = new Responses();
				resp.setCount(resCount);
				resp.setTime(resTime);
				note.setRes(resp);
				note.setSort(sort);
				notelist.add(note);
				
				
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		
		return notelist;
		
	}
	

	//根据用户所选中的帖子查询该帖子的所有内容和全部回复
	public Sort findNoteById(int id)
	{
		Sort sort = null;
		
		Connection conn = null;
		
		conn = DBUtil.getJDBCConnection();
		
		//按编号查找帖子
		String sql1 = "select topicname,topiccontent,owner,topic.time,usergrade,nickname,signature,sex,id from topic,bbsuser where owner=username and id=?";
		
		try {
			PreparedStatement stmt = conn.prepareStatement(sql1);
					
			stmt.setInt(1, id);
			
			ResultSet rs1 = stmt.executeQuery();
			
			if(rs1.next())
			{
				
				String topicname = rs1.getString(1);
				String topiccontent = rs1.getString(2);
				String owner = rs1.getString(3);
				String time = rs1.getString(4);
				String usergrade = rs1.getString(5);
				String nickname = rs1.getString(6);
				String signature = rs1.getString(7);
				String sex = rs1.getString(8);
				int id1 = rs1.getInt(9);
				
				sort = new Sort();	//创建sort对象
				Topic topic = new Topic();//创建topic对象
				topic.setId(id1);
				topic.setTopiccontent(topiccontent);
				topic.setTopicname(topicname);
				topic.setOwner(owner);
				topic.setTime(time);
				User user = new User();//创建user对象
				user.setUsergrade(usergrade);
				user.setNickname(nickname);
				user.setSignature(signature);
				user.setSex(sex);
				sort.setTopic(topic);
				sort.setUser(user);
				
				//按帖子编号查找该帖子的所有回复内容
				String sql2 = "select title,content,responses.owner,responses.time,topicid,usergrade,nickname,sex,signature" +
						" from responses,bbsuser where responses.owner=username and  topicid="+id1;
				stmt = conn.prepareStatement(sql2);

				ResultSet rs2 = stmt.executeQuery();
				
				ArrayList<Responses> renotelist = new ArrayList<Responses>(); //回复内容集合对象
				while(rs2.next())
				{
					String title = rs2.getString(1);
					String content = rs2.getString(2);
					String owner2 = rs2.getString(3);
					String time2 = rs2.getString(4);
					int topicid = rs2.getInt(5);
					String usergrade2 = rs2.getString(6);
					String nickname2 = rs2.getString(7);
					String sex2 = rs2.getString(8);
					String signature2 = rs2.getString(9);
					
					//创建回复内容对象
					Responses resp = new Responses();
					resp.setTitle(title);
					resp.setContent(content);
					resp.setOwner(owner2);
					resp.setTime(time2);
					resp.setTopicid(topicid);
					User user2 = new User();
					user2.setUsergrade(usergrade2);
					user2.setNickname(nickname2);
					user2.setSex(sex2);
					user2.setSignature(signature2);
					resp.setUser(user2);
					
					//把回复内容对象添加到回复内容集合对象中

					renotelist.add(resp);
				}
				
				sort.setRes(renotelist);
				
			}
			
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		finally
		{
			DBUtil.closeConnection(conn);
		}
		
		return sort;
	}
	//添加新帖
	public boolean addNote(Topic topic)
	{
		boolean flag = false;
		
		Connection conn = null;
		
		conn = DBUtil.getJDBCConnection();
		String sql1 = "select max(id) from topic";
		
		try {
			
				PreparedStatement stmt1 = conn.prepareStatement(sql1);
				ResultSet rs1 = stmt1.executeQuery();
				while(rs1.next())
				{
					int id1 = rs1.getInt(1);
					int id2 = id1+1;
					System.out.println(id2);
					String sql2 = "insert into topic(id,topicname,topiccontent,owner,time,sortid) values(" + id2 + ",?,?,?,?,?)";
					PreparedStatement stmt2 = conn.prepareStatement(sql2);
					stmt2.setString(1, topic.getTopicname());	
					stmt2.setString(2, topic.getTopiccontent());	
					stmt2.setString(3, topic.getOwner());	
					stmt2.setString(4, topic.getTime());	
					stmt2.setString(5, topic.getSortid());
					stmt2.executeUpdate();
					flag = true;
				}
				
			
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			flag = false;
			e.printStackTrace();
		}
		finally
		{
			DBUtil.closeConnection(conn);
		}
		
		return flag;
	}
	//添加回复内容
	public boolean addReNote(Responses responses)
	{
		boolean flag = false;
		
		Connection conn = null;
		
		conn = DBUtil.getJDBCConnection();
		
		String sql1 = "select max(id) from responses";
		
		try {
			
				PreparedStatement stmt1 = conn.prepareStatement(sql1);
				ResultSet rs1 = stmt1.executeQuery();
				while(rs1.next())
				{
					int id1 = rs1.getInt(1);
					int id2 = id1+1;
					System.out.println(id2);
					String sql2 = "insert into responses(id,title,content,owner,time,topicid) values(" + id2 + ",?,?,?,?,?)";
					PreparedStatement stmt2 = conn.prepareStatement(sql2);
					stmt2.setString(1, responses.getTitle());	
					stmt2.setString(2, responses.getContent());	
					stmt2.setString(3, responses.getOwner());	
					stmt2.setString(4, responses.getTime());	
					stmt2.setInt(5, responses.getTopicid());
					stmt2.executeUpdate();
					flag = true;
				}
			
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			flag = false;
			e.printStackTrace();
		}
		finally
		{
			DBUtil.closeConnection(conn);
		}
		
		return flag;
	}
}