newsquery.java

某高校的门户网站源码

package com.zzx.notes;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import net.ijsp.news.news.*;

import com.zzx.util.DataConn;

 public class NewsQuery {
   

	  //根据关键字查询
    public  List<News> queryLike(String keyword){
    	DataConn conn = new DataConn();
    	PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from news WHERE title like ? or editer like ? ";

    	List<News> all = new ArrayList<News>();	    	
    	try {
    		
			psmt = conn.getConn().prepareStatement(sql);
			psmt.setString(1,"%"+keyword+"%");
			psmt.setString(2,"%"+keyword+"%");
			//psmt.setInt(3, Integer.valueOf("%"+keyword+"%"));
						
			rs = psmt.executeQuery();
		    int count=0;
			while(rs.next()){
				count++ ;
				Factory factory  = Factory.getInstance();
				News news = factory.getNews();
				news.setID(rs.getInt(1));
				news.setBoardID(rs.getInt(2));
				news.setEditer(rs.getString(3));
				news.setTitle(rs.getString(4));
			    news.setNewsFile(rs.getString(5));
                news.setDate(rs.getLong(6));
             
				all.add(news);
			}

			rs.close();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				conn.closeConn();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return all;
    }
    
   
    
    public  List<News> queryLike(String keyword,int pageCurrent,int lineSize){
    	 int count=0;
    	DataConn conn = new DataConn();
    	PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from news WHERE title like ? or editer like ? order by id desc limit "+(pageCurrent-1)*lineSize+","+lineSize;
    	List<News> all = new ArrayList<News>();	    	
    	try {
    		
			psmt = conn.getConn().prepareStatement(sql);
			psmt.setString(1,"%"+keyword+"%");
			psmt.setString(2,"%"+keyword+"%");	
			rs = psmt.executeQuery();
		   
			while(rs.next()){
				count++ ;
				Factory factory  = Factory.getInstance();
				News news = factory.getNews();
				news.setID(rs.getInt(1));
				news.setBoardID(rs.getInt(2));
				news.setEditer(rs.getString(3));
				news.setTitle(rs.getString(4));
			    news.setNewsFile(rs.getString(5));
                news.setDate(rs.getLong(6));
             
				all.add(news);
			}

			rs.close();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				conn.closeConn();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return all;
    }
    
    
    public  int querycount(String keyword){
    	int count=0;
    	DataConn conn = new DataConn();
    	PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from news WHERE title like ? or editer like ?";   	
    	try {
    		
			psmt = conn.getConn().prepareStatement(sql);
			psmt.setString(1,"%"+keyword+"%");
			psmt.setString(2,"%"+keyword+"%");
			rs = psmt.executeQuery();
		   
			while(rs.next()){
				count++;           
			}
			rs.close();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				conn.closeConn();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}		
		return count;
    }
}