masternotedao.java

一个仿造淘宝的jsp网站。功能比较完善

package com.jc.taobao.gjj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jc.taobao.gjj.db.DBManager;
import com.jc.taobao.gjj.entity.FollowNote;
import com.jc.taobao.gjj.entity.MasterNote;
import com.jc.taobao.gjj.logic.MasterNoteBean;

public class MasterNoteDAO {
	DBManager dm = new DBManager();

	public int delete(Integer id) {
		String sql = "delete from masternote where mid=" + id;
		return dm.updb(sql);
	}

	public int merge(MasterNote mn) {
		String sql = "update masternote set userid=" + mn.getUserid() + ",modeid="
				+ mn.getModeid() + ",mastertitle='" + mn.getMastertitle()
				+ "',mastermessage='" + mn.getMastermessage()
				+ "' where mid=" + mn.getMid();
		return dm.updb(sql);
	}

	public  int save(MasterNote mn) {
		String sql = "insert into masternote values(" 
				+ mn.getUserid() + "," + mn.getModeid() + ",'"
				+ mn.getMastertitle() + "','" + mn.getMastermessage() +"','"+mn.getMastertime()+"')";
		return dm.updb(sql);
	}
	public int save2(MasterNote mn)
	{
		int mid=0;
		String sql="insert into masternote values(" 
				+ mn.getUserid() + "," + mn.getModeid() + ",'"
				+ mn.getMastertitle() + "','" + mn.getMastermessage() +"','"+mn.getMastertime()+"');select @@identity as mid;";
		try {
			ResultSet rs=dm.getRs(sql);
			while(rs.next())
			{
				mid=rs.getInt("mid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mid;
	}
	
	private List<MasterNote> query(String sql)//查询
	{
		List<MasterNote> list = new ArrayList<MasterNote>();
		ResultSet rs = dm.getRs(sql);
		try {
			while(rs.next())
			{
				MasterNote master = new MasterNote();
				master.setMid(rs.getInt("mid"));
				master.setUserid(rs.getInt("userid"));
				master.setModeid(rs.getInt("modeid"));
				master.setMastertitle(rs.getString("mastertitle"));
				master.setMastermessage(rs.getString("mastermessage"));
				master.setMastertime(rs.getString("mastertime"));
				list.add(master);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public List<MasterNote> queryALL()//查询所有主帖表信息
	{
		String sql="select * from masternote";
		return query(sql);
	}
	
	public int queryAllMasternoteSizeBymodeid(Integer modeid)
	   {
		   String sql="select * from masternote where modeid="+modeid;
		   return query(sql).size();
	   }
	  public List<MasterNote> queryMasterNoteByCurrentPagebymodeid(Integer page,Integer modeid)//模块主贴分页
	  {
		  String sql="select top 15 * from masternote where mid not in " +
		  		"(select top "+(page-1)*15+" mid from masternote) and modeid="+modeid+" order by mastertime desc";
		  return query(sql);
	  }
	
	public List<MasterNote> querybymid(Integer mid)//根据主帖编号查询主帖信息
	{
		String sql="select * from masternote where mid="+mid;
		return query(sql);
	}
	
	public List<MasterNote> querybymodeid(MasterNote master)//根据模块号查询主帖信息
	{
		String sql="select * from masternote where modeid="+master.getModeid();
		return query(sql);
	}
	public List<MasterNote> querybymastertitle(MasterNote master)//根据主贴标题查询跟贴信息
	 {
		 String sql="select * from masternote where mastertitle like'"+"%"+master.getMastertitle()+"%"+"'";
		 return query(sql);
	 }
	public List<MasterNote> querybymastertitlemohu(String mastertitle)
	{
		 String sql="select * from masternote where mastertitle like'"+"%"+mastertitle+"%"+"'";
		 return query(sql);
	}
	 public List<MasterNote> querybymastermessage(MasterNote master)//根据主贴内容查询跟贴信息
	 {
		 String sql="select * from masternote where mastermessage like'"+"%"+master.getMastermessage() +"%"+"'";
		 return query(sql);
	 }
	 public int queryMasterNoteCount()//查询主贴个数
	 {
		 int countmasternote=0;
		 String sql="select count(*) as countmasternote from masternote";
		 try {
			ResultSet rs=dm.getRs(sql);
			 while(rs.next())
			 {
				 countmasternote=rs.getInt("countmasternote");
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return countmasternote;
	 }
	public int queryMasterhotMode()//查询拥有用户最多的模块
	{
		int modeid=0;
		String sql="select top 1 modeid,count(modeid) as usercount from masternote  group by modeid order by count(modeid) desc";
		try {
			ResultSet rs=dm.getRs(sql);
			while(rs.next())
			{
			   modeid=rs.getInt("modeid");	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return modeid;
	}
	public List<MasterNote> querybyMasterbyuidandtime(int uid,String mastertime)
	{
		String sql="select * from masternote where userid="+uid+" and mastertime='"+mastertime+"'";
		return query(sql);
	}
	
	public int queryMasterhotMaster()//最火主贴
	{
		int userid=0;
		String sql="select top 1 mid,count(mid) as followcount from follownote  group by mid order by count(mid) desc";
		try {
			ResultSet rs=dm.getRs(sql);
			while(rs.next())
			{
				userid=rs.getInt("mid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userid;
	}
	
	public List<MasterNote> queryMasterNoteSuiji(Integer modeid)//随机抽取5个主贴
	{
		String sql="select top 5  * from masternote where modeid="+modeid+" order by newid()";
		return query(sql);
	}
	
	public ArrayList queryEreyXingMasterNote()
	{
		int xing;
		ArrayList alxing=new ArrayList();
		String sql="select top 5 userid ,aa=count(userid) from masternote group by userid order by  aa desc";
		ResultSet rs=dm.getRs(sql);
		try {
			while(rs.next())
			{
				xing=rs.getInt("userid");
				alxing.add(xing);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return alxing;
		
	}
	public List<MasterNote> querytopEarly9()
	{
		String sql="select top 9 * from masternote order by mastertime desc";
		return query(sql);
	}

}