articledao.java

功能是公安和学校的管理系统,用struts+hibernate+spring等匡架组成,在eclepse就能编译

			while(rs.next())
			{
				arlm2id=rs.getInt(1);
			}
		}catch(Exception e)
		{
			System.out.println(e.toString());
		}finally{
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(stmt);
		}
		try
		{
		   
		   stmt=conn.prepareStatement("update w_article set ar_bt=?, ar_nr=?, ar_xszt=?,ar_lm1=?,ar_lm2=?,ar_lm2id=?,ar_fbsj=getDate() where ar_id=?");
		   stmt.setString(1, article.getAr_bt());
		   stmt.setString(2, article.getAr_nr());
		   stmt.setString(3, article.getAr_xszt());
		   stmt.setInt(4, article.getAr_lm1());
		   stmt.setString(5, article.getAr_lm2());
		   stmt.setInt(6, arlm2id);
		   stmt.setInt(7, article.getAr_id());
		   count = stmt.executeUpdate();
		   DBUtils.closeStatement(stmt);
		}
		catch(Exception e)
		{
		   System.out.println("ArticleDAO.editArticle-->"+e);
		}
		finally
		{
		  DBUtils.closeStatement(stmt);
		  DBUtils.closeConnection(conn);
		}
		if(count>0) flag = true ;
		return flag ;		
	}
	
	
	public Article queryArticleById(int id){
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null; 
		try
		{
		   conn=DBUtils.getConnection();
		   stmt=conn.prepareStatement("select ar_bt, ar_nr, ar_fbrzh, ar_fbsj from w_article where ar_xszt=1 and ar_id=?");
		   stmt.setInt(1, id);
		   rs=stmt.executeQuery();
		   if(rs.next())
		   {
		      String title = rs.getString("ar_bt");
		      String content = rs.getString("ar_nr");
		      String author = rs.getString("ar_fbrzh");
		      String date = formatDate(rs.getTimestamp("ar_fbsj"));		      
		      //设置Notice
		      article.setAr_bt(title);
		      article.setAr_nr(content);
		      article.setAr_fbrzh(author);
		      article.setAr_fbsj(date);
		   }
		   DBUtils.closeResultSet(rs) ;
		   DBUtils.closeStatement(stmt) ;
		}catch(Exception e)
		{
		   System.out.println("ArticleDAO.queryNoticeById"+e);
		}finally
		{
		  DBUtils.closeResultSet(rs);
		  DBUtils.closeStatement(stmt);
		  DBUtils.closeConnection(conn);
		}
		/*以上代码设置Notice在公告表中获取的信息，然后调用AppFilesDao中的获取附件的方法，
		  设置Notice的附件信息*/
		article.setFiles(queryArticleAllAppFilesById(id));
		return article;
	}
	
	/**
	 * 根据公告id查询对应的附件
	 * @param id	附件对应的公告id
	 * @return	AppFiles[]	附件信息数组
	 */
	public ArrayList<ArticleAppFiles> queryArticleAllAppFilesById(int id){
		ArrayList<ArticleAppFiles> files = new ArrayList<ArticleAppFiles>();
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null; 
		try
		{
		   conn=DBUtils.getConnection();
		   stmt=conn.prepareStatement("select aa_oldname, aa_newname from w_article_appendix where a_id=?");
		   stmt.setInt(1, id);
		   rs=stmt.executeQuery();
		   while(rs.next())
		   {
		      String oldFilename = rs.getString("aa_oldname");
		      String newFilename = rs.getString("aa_newname");      
		      //设置NoticeAppFiles
		      ArticleAppFiles file = new ArticleAppFiles();
		      file.setOldFilename(oldFilename);
		      file.setNewFilename(newFilename);
		      
		      files.add(file);
		   }
		   DBUtils.closeResultSet(rs) ;
		   DBUtils.closeStatement(stmt) ;
		}catch(Exception e)
		{
		   System.out.println("ArticleDAO.queryArticleAppFilesById-->"+e);
		}finally
		{
		  DBUtils.closeResultSet(rs);
		  DBUtils.closeStatement(stmt);
		  DBUtils.closeConnection(conn);
		}
		return files;
	}
	
	
	/**
	 * 根据id值设置notice的详细属性，用于编辑notice
	 * @param id
	 * @return Notice
	 */
	public Article getArticleInfoById(int id){
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null; 
		try
		{
		   conn=DBUtils.getConnection();
		   stmt=conn.prepareStatement("select * from w_article where ar_id=?");
		   stmt.setInt(1, id);
		   rs=stmt.executeQuery();
		   while(rs.next())
		   {
		      String title = rs.getString("ar_bt");
		      String content = rs.getString("ar_nr");
		      String author = rs.getString("ar_fbrzh");
		      String authorName = rs.getString("ar_fbrxm");
		      int schoolId = rs.getInt("ar_xxbh");
		      String schoolName = rs.getString("ar_xxmc");
		      String date = formatDate(rs.getTimestamp("ar_fbsj"));		      				
		      String flag = rs.getString("ar_xszt");
		      int ar_lm1=rs.getInt("ar_lm1");
		      String ar_lm2=rs.getString("ar_lm2");
		      
		      //设置Notice
		      article.setAr_id(id);
		      article.setAr_bt(title);
		      article.setAr_nr(content);
		      article.setAr_fbrzh(author);
		      article.setAr_fbsj(date);
		      article.setAr_fbrxm(authorName);
		      article.setAr_xxbh(schoolId);
		      article.setAr_xxmc(schoolName);
		      article.setAr_xszt(flag);
		      article.setAr_lm1(ar_lm1);
		      article.setAr_lm2(ar_lm2);
		      
		   }
		   DBUtils.closeResultSet(rs) ;
		   DBUtils.closeStatement(stmt) ;
		}catch(Exception e)
		{
		   System.out.println("ArticleDAO.getNoticeInfoById-->"+e);
		}finally
		{
		  DBUtils.closeResultSet(rs);
		  DBUtils.closeStatement(stmt);
		  DBUtils.closeConnection(conn);
		}
		/*以上代码设置Notice在公告表中获取的信息，然后调用queryNoticeAppFilesById获取附件的方法，
		  设置Notice的附件信息*/
		article.setFiles(queryArticleAllAppFilesById(id));
		return article;
	}
	
	
	public ArrayList<Article> queryAllArticle(){
		ArrayList<Article> allArticlesList = null ;
		Article notice = null ;
		ArticleAppFiles af = null ;
		
		Connection conn = null ;
		ResultSet rs_appFiles = null ;
		ResultSet rs_notice = null ;
		
		Statement stmt = null ;
		String sqlStr = "" ;
		
		int 	n_id;		//存储要修改的公告的id，更新公告数据时使用
		String n_bt;	//标题
		String n_nr;	//内容
		String n_fbrzh;	//发布人账户
		String n_fbsj;	//发布时间,存储格式化过后的时间
//		ArrayList<NoticeAppFiles> files;//附件
		ArrayList<ArticleAppFiles> fileList = null ;
		
		// 以下字段供添加公告使用
		String n_fbrxm; // 发布人姓名
		int n_xxbh; // 学校编号
		String n_xxmc; // 学校名称
		String n_xszt; // 显示状态 1－显示，0－不显示

		int na_id; // 自增id
		String oldFilename;
		String newFilename;
		boolean fileflag ;	 
		
		try {
			sqlStr = "select * from w_article" ;
			conn = DBUtils.getConnection() ;
			stmt = conn.createStatement() ;
			rs_notice = stmt.executeQuery(sqlStr) ;
			while(rs_notice.next()){
				n_id = rs_notice.getInt("ar_id") ;
				n_bt = rs_notice.getString("ar_bt") ;
				n_nr = rs_notice.getString("ar_nr") ;
				n_fbrzh = rs_notice.getString("ar_fbrzh") ;
				n_fbsj = String.valueOf(rs_notice.getTime("ar_fbsj")) ;
				n_fbrxm = rs_notice.getString("ar_fbrxm") ;
				n_xxbh = rs_notice.getInt("ar_xxbh") ;
				n_xxmc = rs_notice.getString("ar_xxmc") ;
				n_xszt = rs_notice.getString("ar_xszt") ;
				
				sqlStr = "select * from w_article_appendix where ar_id = "+n_id ;
				DBUtils.closeStatement(stmt) ;
				stmt = conn.createStatement() ;
				rs_appFiles = stmt.executeQuery(sqlStr) ;
				while(rs_appFiles.next()){
					na_id = rs_appFiles.getInt("aa_id") ;
					oldFilename = rs_appFiles.getString("oldFilename") ;
					newFilename = rs_appFiles.getString("newFileName") ;
					if("1".equals(n_xszt)) fileflag = true ;
					else fileflag = false ;
					af = new ArticleAppFiles ();
					
					
					af.setNewFilename(newFilename) ;
					af.setOldFilename(oldFilename) ;
					af.setFileflag(fileflag) ;
					fileList.add(af) ;
				}
				article.setFiles(fileList) ;
				article.setAr_bt(n_bt) ;
				article.setAr_fbrxm(n_fbrxm) ;
				article.setAr_fbrzh(n_fbrzh) ;
				article.setAr_fbsj(n_fbsj) ;
				article.setAr_id(n_id) ;
				article.setAr_nr(n_nr) ;
				article.setAr_xszt(n_xszt) ;
				article.setAr_xxbh(n_xxbh) ;
				article.setAr_xxmc(n_xxmc) ;
				
				allArticlesList.add(notice) ;
			}
			DBUtils.closeResultSet(rs_appFiles) ;
			DBUtils.closeResultSet(rs_notice) ;
			DBUtils.closeStatement(stmt) ;
			DBUtils.closeConnection(conn) ;
		} catch (Exception e) {
			System.out.println("ArticleDAO.queryAllNotice-->"+e)  ;
		}finally{
			DBUtils.closeResultSet(rs_appFiles) ;
			DBUtils.closeResultSet(rs_notice) ;
			DBUtils.closeStatement(stmt) ;
			DBUtils.closeConnection(conn) ;
		}		
		
		return allArticlesList ;
	}
}