articleservice.java

基于MVC模式的文章采集检索系统

package com.liubin.service.article;
import com.liubin.model.*;
import com.liubin.service.purview.PurviewService;
import com.liubin.database.*;

import java.sql.*;
import java.util.*;
public class ArticleService {
	
	public List<SubItem> getAllSubItems(){
		String sql="select * from(select a.sub_id,a.sub_name,b.item_name  from t_subitems a,t_items b where a.item_id=b.item_id)as td";
		JdbcBean bean=new JdbcBean();
		ResultSet rs =null;
		rs=bean.query(sql);
		SubItem subItem=null;
		List<SubItem> list=new ArrayList<SubItem>();
		try{
			while(rs.next()){
				subItem=new SubItem();
				subItem.setSub_name("【"+rs.getString("item_name")+"】"+rs.getString("sub_name"));
				subItem.setSub_id(rs.getString("sub_id"));
				
				list.add(subItem);
			}
			rs.close();
			
			
		}
		catch(Exception e){
			e.printStackTrace();
		}
		bean.close();
		return list;
		
		
	}
	public List<Level> getLevel(){
		String  sql="select * from t_level";
		JdbcBean bean=new JdbcBean();
		ResultSet rs=null;
		Level level=null;
		List<Level>list=new ArrayList<Level>();
		rs=bean.query(sql);
		try{
			while(rs.next()){
				level=new Level();
				level.setLevel_id(rs.getInt("level_id"));
				level.setLevel_name(rs.getString("level_name"));
				list.add(level);
			}
			rs.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		bean.close();
		return list;
	}


	
	public boolean addArticle(Article article){
		String table="t_article_"+article.getSubitem_id().substring(0,2);
		String sql ="insert into "+table+" values  ('"+article.getId()+"','"+article.getTitle()+"','"+
		article.getAuthor()+"','"+article.getDigest()+"','"+article.getContent()+"','"+article.getSubmit_date()+
		"',null,'"+article.getAudit_flag()+"','"+article.getAuditor()+"','"+
		article.getOrigin()+"','"+article.getSubitem_id()+"',"+article.getLevel_id()+")";
		System.out.println(sql);
		JdbcBean bean=new JdbcBean();
		int temp=bean.update(sql);
		if(temp>0){
			return true;
		}
		else
			return false;
		
	}
	public int getCountArticles(String submit_id){
		String table =submit_id.substring(0,2);
		String sql="select count(*) as t from t_article_"+table;
		JdbcBean bean =new JdbcBean();
		ResultSet rs=null;
		rs=bean.query(sql);
		int temp=0;
		try{
			if(rs.next()){
				temp=rs.getInt("t");
			}
			rs.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		bean.close();
		return temp;
		
	}
	public List<Article> getArticleById(String subitem_id,int  pagenum){
		String table="t_article_"+subitem_id.substring(0,2);
		String temp="  select id,title,author,digest,content,submit_date"+
		",pub_date,audit_flag,auditor,origin,subitem_id,level_id,row_number() over (order by id ) as r from "+table;
		String sql="select * from ("+temp+") as ta"+" where  r between "+(5*(pagenum-1)+1)+" and "+5*pagenum;
		
		System.out.println(sql);
		JdbcBean bean=new JdbcBean();
		ResultSet rs=null;
		Article article=null;
		ArrayList<Article> list=new ArrayList<Article>();
		rs=bean.query(sql);
		try{
			while(rs.next()){
				article=new Article();
				article.setId(rs.getString("id"));
				article.setTitle(rs.getString("title"));
				article.setAuthor(rs.getString("author"));
				article.setDigest(rs.getString("digest"));
				article.setContent(rs.getString("content"));
				article.setSubmit_date(rs.getString("submit_date"));
				article.setPub_date(rs.getString("pub_date"));
				article.setAudit_flag(rs.getString("audit_flag"));
				article.setAuditor(rs.getString("auditor"));
				article.setOrigin(rs.getString("origin"));
				article.setSubitem_id(rs.getString("subitem_id"));
				article.setLevel_id(rs.getString("level_id"));
				list.add(article);
		}
			
			rs.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		bean.close();
		return list;
		
		
		
		
	}
	public boolean updateBatch(String id,String subitem_id){
		Timestamp stamp=new Timestamp(System.currentTimeMillis());
		String	item_id=subitem_id.substring(0,2);
		String[] sqls=new String[2];
		sqls[0]="update t_article_"+item_id+" set audit_flag='Y', pub_date='"+stamp.toString()+"'"+
		" where id='"+id+"'";
		System.out.println(sqls[0]);
		
		sqls[1]="insert into t_index select  id ,title,author,digest,pub_date,origin,subitem_id,level_id from "+
		"t_article_"+item_id+" where id='"+id+"'";
		System.out.println(sqls[1]);
		JdbcBean bean =new JdbcBean();
		return bean.updateBatch(sqls);
		
		
		
	}
	public boolean  deleteBatch(String id,String subitem_id){
		String item_id=subitem_id.substring(0,2);
		String[]sqls=new String[2];
		sqls[0]="delete   from   t_article_"+item_id+" where id='"+id+"'";
		sqls[1]="delete from t_index  where id='"+id+"'";
		JdbcBean bean=new JdbcBean();
		return  bean.updateBatch(sqls);
	}
	public Article getArticleById(String id,String subitem_id){
		String item_id=subitem_id.substring(0,2);
		String sql="select * from t_article_"+item_id+" where id='"+id+"'";
		JdbcBean bean =new JdbcBean ();
		ResultSet rs=null;
		rs=bean.query(sql);
		Article article=null;
		try{
			if(rs.next()){
				article=new Article();
				article.setId(rs.getString("id"));
				article.setAudit_flag(rs.getString("audit_flag"));
				article.setAuditor(rs.getString("auditor"));
				article.setAuthor(rs.getString("author"));
				article.setContent(rs.getString("content"));
				article.setLevel_id(rs.getString("level_id"));
				article.setOrigin(rs.getString("origin"));
				article.setPub_date(rs.getString("pub_date"));
				article.setSubitem_id(rs.getString("subitem_id"));
				article.setSubmit_date(rs.getString("submit_date"));
				article.setDigest(rs.getString("digest"));
				article.setTitle(rs.getString("title"));
				}
			rs.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		bean.close();
		return article;
		
		
	}
	public String getItemName(String subitem_id){
		StringBuffer buffer=new StringBuffer(subitem_id);
		buffer.append("@");
		String sql="select * from (  select  sub.sub_id,sub.sub_name,item.item_name  from t_subitems as sub ,t_items as item  where  sub.sub_id='"+subitem_id+"' and"+
		" sub.item_id=item.item_id) as t ";
		
		System.out.println(sql);
		
		JdbcBean bean =new JdbcBean();
		ResultSet rs=null;
		rs=bean.query(sql);
		try{
			if(rs.next()){
				buffer.append(rs.getString("item_name"));
				buffer.append("@");
				buffer.append(rs.getString("sub_name"));
			}
			rs.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		bean.close();
		return buffer.toString();
		
		
		
	}
	
	public boolean updateBatch(Article article){
	String []sqls=new String[2];
	//id,title,author,digest,content,submit_date,pub_date,audit_flag,auditor,origin,subitem_id,level_id
	sqls[0]="update t_article_"+article.getSubitem_id().substring(0,2)+" set title='"+article.getTitle()+
	"',author='"+article.getAuthor()+"', origin='"+article.getOrigin()+"',content='"+article.getContent()+
	"',level_id="+article.getLevel_id()+" ,digest='"+article.getDigest()+"' where id='"+article.getId()+"'";
	sqls[1]="update t_index set title='"+article.getTitle()+
	"',author='"+article.getAuthor()+"', origin='"+article.getOrigin()+
	"',level_id="+article.getLevel_id()+" ,digest='"+article.getDigest()+"' where id='"+article.getId()+"'";
	JdbcBean bean=new JdbcBean();
	System.out.println(Arrays.toString(sqls));
	boolean temp=bean.updateBatch(sqls);
	return temp;
	}
	

	public boolean deleteBatch(String[] ids,String subitem_id){
		StringBuffer buffer1=new StringBuffer("(");
		for (int i=0;i<ids.length;i++){
			if(i==ids.length-1){
				buffer1.append("'"+ids[i].substring(1,ids[i].length())+"')");
			}
			else{
				buffer1.append("'"+ids[i].substring(1,ids[i].length())+"',");
			}
		}
			System.out.println(buffer1.toString());
			String[] sqls=new String[2];
			sqls[0]="delete from t_article_"+subitem_id.substring(0,2)+" where id in"+buffer1.toString();
			System.out.println(sqls[0]);
			sqls[1]="delete from t_index  where id in"+buffer1.toString();
			JdbcBean bean =new JdbcBean();
			boolean temp=bean.updateBatch(sqls);
			bean.close();
			return temp;
			
		
	}
	public boolean updateBatch(String[] ids,String subitem_id ){
		StringBuffer buffer=new StringBuffer("(");
		String item_id=subitem_id.substring(0,2);
		Timestamp stamp=new Timestamp(System.currentTimeMillis());
		for(int i=0;i<ids.length;i++){
		
				if(ids[i].subSequence(0,1).equals("N"))
				buffer.append("'"+ids[i].substring(1,ids[i].length())+"',");
			}
		buffer.append(" '0')");
		String[] sqls=new String[2];
		sqls[0]="update t_article_"+item_id+" set audit_flag='Y', pub_date='"+stamp.toString()+"'"+
		" where id in "+buffer.toString();
		System.out.println(sqls[0]);
		
		sqls[1]="insert into t_index select  id ,title,author,digest,pub_date,origin,subitem_id,level_id from "+
		"t_article_"+item_id+" where id in "+buffer.toString();
		
		JdbcBean bean =new JdbcBean();
		return bean.updateBatch(sqls);
		
	}
	
	public List<SubItem> getSubItemByItem(String item){
		String item_id=item.substring(0,2);
		SubItem sub=null;
		ArrayList<SubItem>list =new ArrayList<SubItem>();
		String sql="select * from t_subitems where item_id='"+item_id+"'";
		JdbcBean bean =new JdbcBean();
		ResultSet rs =null;
		rs=bean.query(sql);
		if(rs!=null){
			try{
				while(rs.next()){
					sub=new SubItem();
					sub.setSub_name(rs.getString("sub_name"));
					sub.setSub_id(rs.getString("sub_id"));
					list.add(sub);
				}
				rs.close();
			}
			catch(Exception e){
				e.printStackTrace();
			}
			
			
		}
		bean.close();
		return list;
	}
	public List<Article> getArticleByKey(User user,String[] keys,String pagenum){
		int level_id=user.getLevel_id();
		int role_id=user.getRole_id();
		int page=Integer.parseInt(pagenum);
		PurviewService purview =new PurviewService();
		List<String>list_subId=purview.getSubIdByRoleId(role_id);
		StringBuffer buffer1=new StringBuffer("(");
		for(int i=0;i<list_subId.size();i++){
			buffer1.append("'"+list_subId.get(i)+"',");
		}
		buffer1.append("'01')");
		StringBuffer buffer2=new StringBuffer();
		for(int i=0;i<keys.length;i++){
			buffer2.append(" and ( title like '%"   );
			buffer2.append(keys[i]+"%' or digest like '%");
			buffer2.append(keys[i]+"%')");
		}
		String sql="select * from (select id,title,digest,subitem_id,pub_date,origin,row_number() over(order by id) as num"+
		" from t_index where subitem_id in "+buffer1.toString()+" and level_id<="+level_id+
		"  "+buffer2.toString()+") as tb where num  between "+ (5*(page-1)+1)+" and "+5*page ;
		System.out.println(sql);
		JdbcBean bean=new JdbcBean();
		ResultSet rs=null;
		ArrayList<Article>list_article =new ArrayList<Article>();
		Article article=null;
		rs=bean.query(sql);
		try{
			while(rs.next()){
				article=new Article();
				article.setId(rs.getString("id"));
				article.setTitle(this.RedKey(rs.getString("title"), keys));
				article.setDigest(this.RedKey(rs.getString("digest"), keys));
				article.setSubitem_id(rs.getString("subitem_id"));
				article.setPub_date(rs.getString("pub_date"));
				article.setOrigin(rs.getString("origin"));
				list_article.add(article);
			}
			System.out.println("总的文章数"+list_article.size());
			rs.close();
		}
		catch(Exception  e){
			e.printStackTrace();
		}
		bean.close();
		return list_article;
		
	}
	public int  getCount(User user,String[] keys){
		int level_id=user.getLevel_id();
		int role_id=user.getRole_id();
		
		PurviewService purview =new PurviewService();
		List<String>list_subId=purview.getSubIdByRoleId(role_id);
		StringBuffer buffer1=new StringBuffer("(");
		for(int i=0;i<list_subId.size();i++){
			buffer1.append("'"+list_subId.get(i)+"',");
		}
		buffer1.append("'01')");
		StringBuffer buffer2=new StringBuffer();
		for(int i=0;i<keys.length;i++){
			buffer2.append(" and ( title like '%"   );
			buffer2.append(keys[i]+"%' or digest like '%");
			buffer2.append(keys[i]+"%')");
		}
		String sql="select   count(*) from (select id,title,digest,subitem_id,row_number() over(order by id) as num"+
		" from t_index where subitem_id in "+buffer1.toString()+" and level_id<="+level_id+
		"  "+buffer2.toString()+") as tb";
		JdbcBean bean=new JdbcBean();
		ResultSet rs=null;
		rs=bean.query(sql);
		try{
			if(rs.next()){
				return rs.getInt("1");
			}
			rs.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		bean.close();
		return 0;
		
	}
	
	public String RedKey(String content ,String []keys ){
		for(int i=0;i<keys.length;i++){
			content=content.replaceAll(keys[i], "<font color=red>"+keys[i]+"</font>");
			System.out.println(content);
		}
		return content;
	}
	public static void main(String[] args){
		User user=new User();
		user.setLevel_id(1);
		user.setRole_id(2);
		String[]keys={"d","a"};
		String pagenum="1";
		
		System.out.println(new ArticleService().RedKey("dddfffaaa", keys));
		
	}
	
}