mainbean.java

运用java+jsp写的一个新闻发布系统

		//检索子菜单表编号
		sql = "select ID from menuchild where isthis=0 and IDPARENT in (select ID from Menu where id3<>0 and Id1="+id1+" and MENU2='"+menu2+"')";
		rs = selectRecord(sql);
		while(rs.next())
		{
			String id4 = rs.getString("ID");
			v_child.add(id4);
			Stack s4 = new Stack();
			s4.push(id4);

			while(!s4.empty())//当有孩子节点时继续搜索
			{
				id4 = (String)s4.pop();
				sql = "select count(id) from menuchild where isthis=1 and IDPARENT="+id4;
				ResultSet rs4 = selectRecord(sql);
				try{
					int count = 0;
					if(rs4.next())
						count = rs4.getInt(1);
					if(count>0)
					{
						sql = "select id from menuchild where isthis=1 and  IDPARENT="+id4;					
						ResultSet rs_t = selectRecord(sql);
						try{
							while(rs_t.next())
							{
								String id_t = rs_t.getString("id");	
								s4.push(id_t);
								v_child.add(id_t);
							}
						}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)运行时出错："+e);}
						finally{
							if(rs_t!=null)try{ stmt = rs_t.getStatement(); rs_t.close();}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)关闭记录集rs时出错"+e);}
							if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)关闭声明时statement出错"+e);}		
						}
					}
				}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)运行时出错："+e);}
				finally{
					if(rs4!=null)try{ stmt = rs4.getStatement(); rs4.close();}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)关闭记录集rs时出错"+e);}
					if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)关闭声明时statement出错"+e);}		
				}
			}
		}
		//if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)关闭记录集rs时出错"+e);}
		//if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)关闭声明时statement出错"+e);}		

		//检索文章编号
		sql = "";
		for(int i=0;i<v_menu.size();i++)
		{
			if(!sql.equals(""))sql += ",";
			sql += (String)v_menu.get(i);
			//if(!sql.equals(""))sql += " union ";
			//sql += "select id from article where whichtable='1' and ifshow='1' and ORGANID='00000000000000000000' and menuid="+(String)v_menu.get(i);
		}
		for(int i=0;i<v_child.size();i++)
		{
			if(!sql.equals(""))sql += ",";
			sql += (String)v_menu.get(i);
			//if(!sql.equals(""))sql += " union ";
			//sql += "select id from article where whichtable='0' and ifshow='1' and ORGANID='00000000000000000000' and menuid="+(String)v_child.get(i);
		}

		sql = "select id from article where whichtable='1' and ifshow='1' and ORGANID='00000000000000000000' and menuid in ("+sql+")";
		
		sql = "select id,title,addtime,hits from article where id in ("+sql+") order by edittime desc";
		rs = selectRecord(sql);
		int i = 0;
		ResultSetMetaData rsmd = rs.getMetaData();
		int cols = 0;
		cols = rsmd.getColumnCount();				
		while(rs.next())
		{
			Hashtable hash = new Hashtable();
			for(i=1;i<=cols;i++)
			{
				String field = ds.toString(rsmd.getColumnName(i));
				String value = ds.toString(rs.getString(i));
				hash.put(field,value);
			}
			v_article.add(hash);//把文章信息存入
			i++;
			if(i==num)break;
		}
		}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)运行时出错："+e);}
		finally{
			//if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)关闭记录集rs时出错"+e);}
			//if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle1(String id1,String menu2)关闭声明时statement出错"+e);}		
		}
		if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id)关闭记录集rs时出错"+e);}
			if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id)关闭声明时statement出错"+e);}		
		
		return v_article;
	}

	//根据一级菜单下的取得文章------------------------------------------------------------------------
	public Vector getArticle(String id1,String menu2,int cur,int records)
	{
		Vector v_menu = new Vector();
		Vector v_article = new Vector();
		if(id1.equals(""))
			return v_article;

		//检索菜单表编号		
		String sql = "select num from nb_zyszb where id=20";  
		ResultSet rs = null;
		Statement stmt = null;
		try{
		int num = records;
		if(num==0)
		{
			rs = selectRecord(sql);
			if(rs.next())
				num = rs.getInt(1);
		}
		//if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭记录集rs时出错"+e);}
		//if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭声明时statement出错"+e);}		


		sql = "select ID from Menu where id3<>0 and Id1="+id1+" and MENU2='"+menu2+"'";
		rs = selectRecord(sql);
		while(rs.next())
		{
			v_menu.add(rs.getString("ID"));
		}
		if(v_menu.isEmpty()) return v_menu;

		//if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭记录集rs时出错"+e);}
		//if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭声明时statement出错"+e);}		
		
		//检索子菜单表编号
		sql = "select ID from menuchild where isthis=0 and IDPARENT in (select ID from Menu where id3<>0 and Id1="+id1+" and MENU2='"+menu2+"')";
		rs = selectRecord(sql);
		Vector v_child = new Vector();
		while(rs.next())
		{
			String id4 = rs.getString("ID");
			v_child.add(id4);
			Stack s4 = new Stack();
			s4.push(id4);

			while(!s4.empty())//当有孩子节点时继续搜索
			{
				id4 = (String)s4.pop();
				sql = "select count(id) from menuchild where isthis=1 and IDPARENT="+id4;
				ResultSet rs4 = selectRecord(sql);
				int count = 0;
				try{
					if(rs4.next())
						count = rs4.getInt(1);
					if(count>0)
					{
						sql = "select id from menuchild where isthis=1 and  IDPARENT="+id4;					
						ResultSet rs_t = selectRecord(sql);
						try{
							while(rs_t.next())
							{
								String id_t = rs_t.getString("id");	
								s4.push(id_t);
								v_child.add(id_t);
							}
						}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)运行时出错："+e);}
						finally{
							if(rs_t!=null) try{ stmt = rs_t.getStatement(); rs_t.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭记录集rs时出错"+e);}
							if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭声明时statement出错"+e);}		
						}
					}
				}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)运行时出错："+e);}
				finally{
					if(rs4!=null)try{ stmt = rs4.getStatement(); rs4.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭记录集rs时出错"+e);}
					if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭声明时statement出错"+e);}		
				}
			}
		}

		//if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭记录集rs时出错"+e);}
		//if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭声明时statement出错"+e);}		
		
		//检索文章编号
		sql = "";
		for(int i=0;i<v_menu.size();i++)
		{
			if(!sql.equals(""))sql += ",";
			sql += (String)v_menu.get(i);
			//if(!sql.equals(""))sql += " union ";
			//sql += "select id from article where whichtable='1' and ifshow='1' and ORGANID='00000000000000000000' and menuid="+(String)v_menu.get(i);
		}
		for(int i=0;i<v_child.size();i++)
		{
			if(!sql.equals(""))sql += ",";
			sql += (String)v_menu.get(i);
			//if(!sql.equals(""))sql += " union ";
			//sql += "select id from article where whichtable='0' and ifshow='1' and ORGANID='00000000000000000000' and menuid="+(String)v_child.get(i);
		}

		sql = "select id from article where whichtable='0' and ifshow='1' and ORGANID='00000000000000000000' and menuid in ("+sql+")";
		

		//计算总页数
		rs = selectRecord("select count(id) from article where id in ("+sql+") order by edittime desc");
		rs.next();
		int rows = rs.getInt(1);
		int sum = rows/records;
		if(rows%records!=0||rows==0)sum++;
		v_article.add(""+sum);
		v_article.add(""+rows);
		int pos = (cur-1)*records+1;

		//if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭记录集rs时出错"+e);}
		//if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭声明时statement出错"+e);}		

		sql = "select id,title,addtime,hits from article where id in ("+sql+") order by edittime desc";
		rs = selectRecord(sql);
		int i = 0;
		ResultSetMetaData rsmd = rs.getMetaData();
		int cols = 0;
		cols = rsmd.getColumnCount();	
		while(rs.next())
		{
			i++;
			if(i<pos)continue;
			Hashtable hash = new Hashtable();
			for(int j=1;j<=cols;j++)
			{
				String field = ds.toString(rsmd.getColumnName(j));
				String value = ds.toString(rs.getString(j));
				hash.put(field,value);
			}
			v_article.add(hash);//把焦点文章信息存入
			if((v_article.size()-2)==num)break;
			if(i==rows)break;
		}
			//if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭记录集rs时出错"+e);}
			//if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭声明时statement出错"+e);}		
	

		}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)运行时出错："+e);}
		finally{
			//if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭记录集rs时出错"+e);}
			//if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id1,String menu2,int cur,int records)关闭声明时statement出错"+e);}		
		}if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id)关闭记录集rs时出错"+e);}
			if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.getArticle(String id)关闭声明时statement出错"+e);}		
		
		return v_article;
	}


	//生三级以下成树并返回根节点--------------------------------------------------------------------
	public Vector buildTree()
	{	

		Vector vt = new Vector();
		String sql =" Select * from MENU Where ID1 = "+id1+" and ID2 = "+id2+" and ID3 = 0";
		ResultSet rs1 = selectRecord(sql);
		Statement stmt = null;
		try{
		ResultSetMetaData rsmd1 = rs1.getMetaData();
		int cols1 = rsmd1.getColumnCount();
		if(rs1.next())
		{			

			Hashtable hash1 = new Hashtable();
			for (int i1 = 1; i1 <= cols1; i1++) 
			{
				String field1 = ds.toString(rsmd1.getColumnName(i1));
				String value1 = ds.toString(rs1.getString(i1));
				hash1.put(field1, value1);
			}
			DefaultMutableTreeNode root1 = new DefaultMutableTreeNode(hash1);//生成二级节点，二级版面
			sql = "select * from menu where id1="+id1+" and id2="+id2+" and id3<>0 order by id3";
			ResultSet rs2 = selectRecord(sql);
			MenuChildBean mcb = null;
			try{
			ResultSetMetaData rsmd2 = rs2.getMetaData();
			int cols2 = rsmd2.getColumnCount();	
			mcb = new MenuChildBean();		
			while(rs2.next())
			{
				Hashtable hash2 = new Hashtable();
				for (int i2 = 1; i2 <= cols2; i2++) 
				{
					String field2 = ds.toString(rsmd2.getColumnName(i2));
					String value2 = ds.toString(rs2.getString(i2));
					hash2.put(field2, value2);
				}
				DefaultMutableTreeNode root2 = new DefaultMutableTreeNode(hash2);//生成三级节点，三级版面
				String str = (String)hash2.get("ID");
				mcb.setParentID(Integer.parseInt(str));
				Vector child = mcb.buildTree();
				if(child.size()!=0)
				{
					for(int a=0;a<child.size();a++)
					{
						DefaultMutableTreeNode root3 = (DefaultMutableTreeNode)child.get(a);
						root2.add(root3);
					}
				}
				root1.add(root2);
			}
			vt.add(root1);
			}catch(Exception e){System.out.println("MainBean.buildTree()运行时出错："+e);}
			finally{
				if(mcb!=null) mcb.closeConn();
				if(rs2!=null)try{ stmt = rs2.getStatement(); rs2.close();}catch(Exception e){System.out.println("MainBean.buildTree()关闭记录集rs时出错"+e);}
				if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.buildTree()关闭声明时statement出错"+e);}		
			}
		}
		}catch(Exception e){System.out.println("MainBean.buildTree()运行时出错："+e);}
		finally{
			if(rs1!=null)try{ stmt = rs1.getStatement(); rs1.close();}catch(Exception e){System.out.println("MainBean.buildTree()关闭记录集rs时出错"+e);}
			if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("MainBean.buildTree()关闭声明时statement出错"+e);}		
		}
		return vt;
	}	


	//取得所有首页图片新闻-------------------------------------------------------------------------------
	public Vector getPicNew()
	{
		Vector vect = new Vector();
		String sql = "";
		sql = " select ID,MENUID,TITLE,TOPICID,FROMSITE,WRITER,ADDTIME,KEYWORD,IFHEAD,IFGOOD,IFSHOW,IMG,HITS,ORGID,EDITER,EDITTIME,EDITIP,TIMES,FTPFILE,WHICHTABLE,DEMOID,IFIMG,VOTE,ORGANID,WZLX,AUTHOR,OLDORGID,STRSIZE,CURSIZE from ARTICLE where IFSHOW = '1' and IFIMG='1' and IFHEAD='1' and ORGANID='00000000000000000000' order by edittime desc";
		ResultSet rs = selectRecord(sql);
		Statement stmt = null;
		try
		{
			//取得列数和列名
			ResultSetMetaData rsmd = rs.getMetaData();
			int cols = rsmd.getColumnCount();
			while(rs.next())