输入框实现新闻列表分页显示（一）

今天学了新闻发布系统中的使用分页技术实现页面的分页显示，感觉开始很难做，渐渐的需要动手，慢慢透析。

分页显示的步骤

1、确定每页显示的数据数量

2、计算显示的页数

3、编写SQL语句

 

第一步：连接数据库

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



/**
 * 数据库连接帮助类
 * @author zql_pc
 * 单例设计模式
 */
public class DbHelp {

    
    //数据库连接对象
    private Connection conn = null;


    //数据库连接帮助
    public Connection lianjie() {
        
        if(conn==null){
        
            //数据库配置
            String driver = "oracle.jdbc.driver.OracleDriver";//驱动
            String url = "jdbc:oracle:thin:@localhost:1521:NEWS";//url
            String username = "epet";//用户名
            String password = "123456";//密码
            
            //1.加载驱动类
            try {
                Class.forName(driver);
                //2.建立连接
                conn = DriverManager.getConnection(url,username,password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
        }
        
        return conn;
        
    }
    
    
}

第二步：编写新闻实体类，进行封装

/**
 * 新闻实体类
 * @author zql_pc
 *
 */
public class news {

    private int id;
    private int t_id;
    private String title;
    private String author;
    private String createdate;
    private String picpath;
    private String content;
    private String modifydate;
    private String summay;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getT_id() {
        return t_id;
    }
    public void setT_id(int tId) {
        t_id = tId;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
    public String getCreatedate() {
        return createdate;
    }
    public void setCreatedate(String createdate) {
        this.createdate = createdate;
    }
    public String getPicpath() {
        return picpath;
    }
    public void setPicpath(String picpath) {
        this.picpath = picpath;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
    public String getModifydate() {
        return modifydate;
    }
    public void setModifydate(String modifydate) {
        this.modifydate = modifydate;
    }
    public String getSummay() {
        return summay;
    }
    public void setSummay(String summay) {
        this.summay = summay;
    }
}

第三步：查询新闻

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 查询新闻
 * @author zql_pc
 *
 */
public class SelectNews {
    
    DbHelp help = new DbHelp();
    
    //查询新闻数目
    public int newsCount() throws SQLException{
        //查询新闻条目
        Connection con = help.lianjie();
        
        //创建数据库操作对象
        Statement st = con.createStatement();
        
        String sql = "select count(*) from news";
        
        //获得结果集
        ResultSet rs = st.executeQuery(sql);
        
        //新闻数目
        int newsCount = 0;
        
        while(rs.next()){
            
            newsCount = rs.getInt(1);
            
        }
        
        return newsCount;
    }
    
    //计算分页
    public int getPages(int newsSize) throws SQLException{
        
        
        int newsCount = newsCount();
        
        int num = (newsCount%newsSize==0)?newsCount/newsSize:newsCount/newsSize+1;
        
        return num;
    }
    
    //根据要求查询新闻
    public List<news> getList(int pageIndex,int newsSize) throws SQLException{
        
        //计算上限和下限 
        //第1页  1 - 5
        //  2   6 - 10
        int up = newsSize * pageIndex;
        
        int down = newsSize * (pageIndex-1)+1;
        
        //获取数据库连接对象
        Connection con = help.lianjie();
        
        String sql = "select * from " +
                "(select n.*,rownum r from " +
                "(select * from news order by ncreatedate desc) n " +
                "where rownum<=?) where r>=?";
        
        PreparedStatement pst = con.prepareStatement(sql);
        
        pst.setInt(1, up);
        pst.setInt(2, down);
        
        //获取结果集
        ResultSet rs =  pst.executeQuery();
        
        //创建集合装新闻对象
        List<news> list = new ArrayList<news>(); 
        
        while(rs.next()){
            
            //创建新闻对象
            news news = new news();
            
            news.setTitle(rs.getString("ntitle"));
            news.setCreatedate(rs.getString("ncreatedate"));
            
            list.add(news);
        }
        
        return list;
    }
}

第四步：在页面分页显示

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="tools.SelectNews"%>
<%@page import="tools.news"%>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
  </head>
  <body>
    <table align="center" border="0">
        
        
        
        <%
            //展示新闻条数
            int newsSize = 5;
        
            //实例化新闻帮助对象
            SelectNews sn = new SelectNews();
            
            //获取页面数量
            int pages = sn.getPages(newsSize);
            
            //当前页码
            int pageIndex = 1;
            
            //获取提交页码
            String Pagetemp = request.getParameter("pageIndex");
            
            //判断页面是否是当前页提交
            if(Pagetemp!=null){
                
                pageIndex = Integer.parseInt(Pagetemp);
                
            }
            
            //=============查询新闻
            //获取装有新闻的集合
            List<news> NewsList = sn.getList(pageIndex,newsSize);
            
            //遍历打印
            for(news temp : NewsList){
                
                
            
        %>
            <tr>
                <td><%=temp.getTitle() %></td>
                <td><%=temp.getCreatedate() %></td>
            </tr>
        
        <%    } %>
        
        <tr>
            <td colspan="2">
                <%if(!(pageIndex<=1)){ %>
                    <a href="index.jsp?pageIndex=<%=1 %>">首页</a>
                    <a href="index.jsp?pageIndex=<%=pageIndex-1 %>">上一页</a>
                <%}
                if(!(pageIndex>=pages)){
                %>
                    <a href="index.jsp?pageIndex=<%=pageIndex+1 %>">下一页</a>
                    <a href="index.jsp?pageIndex=<%=pages %>">尾页</a>
                <%} %>
                
                <form action="index.jsp" method="post">
                    <input type="text" name="pageIndex" value="<%=pageIndex %>"/>
                    <input type="submit" />
                </form>
                 
            </td>
        </tr>
    </table>
    
    
    
  </body>
</html>