JSP实现数据库分页查询

- 日理万妓 2022-05-21 09:19 225阅读 0赞

# 1 数据库建表 #

在MySQL数据库中创建名为javaee的数据库，数据库中建一个名为news\_inf的表，表有两列一列为news\_id、一列为news\_title

数据库javaee.sql文件如下

# --------------------------------------------------------
    # Host:                         localhost
    # Server version:               5.5.58
    # Server OS:                    Win32
    # HeidiSQL version:             6.0.0.3832
    # Date/time:                    2018-07-04 14:17:59
    # --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    # Dumping database structure for javaee
    CREATE DATABASE IF NOT EXISTS `javaee` /*!40100 DEFAULT CHARACTER SET utf8 */;
    USE `javaee`;
    
    
    # Dumping structure for table javaee.news_inf
    CREATE TABLE IF NOT EXISTS `news_inf` ( `news_id` int(11) NOT NULL AUTO_INCREMENT, `news_title` varchar(255) DEFAULT NULL, PRIMARY KEY (`news_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
    
    # Dumping data for table javaee.news_inf: ~10 rows (approximately)
    /*!40000 ALTER TABLE `news_inf` DISABLE KEYS */;
    INSERT INTO `news_inf` (`news_id`, `news_title`) VALUES (1, 'first'), (2, 'second'), (3, 'third'), (4, 'fouth'), (5, 'fifth'), (6, 'sixth'), (7, 'seven'), (8, 'eight'), (9, 'nine'), (10, 'tenth');
    /*!40000 ALTER TABLE `news_inf` ENABLE KEYS */;
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

可以使用HeidiSQL将sql文件导入数据库。

# 2 新建javaWeb项目 #

在eclipse中新建javaWeb项目，并命名为SpiltPageDB

## 1.1建立访问数据库的bean文件 ##

在java Resources目录下的src包下新建一个Package命名为bean，在bean包下新建java类命名为SpiltPage.java，类文件内如如下

package bean;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Vector;
    
    public class SpiltPage {  
        //定义数据库连接对象和结果集对象
        private Connection con = null;
        private Statement stmt = null;
        private ResultSet rs = null;
        private ResultSetMetaData rsmd = null;
        //SQL查询语句
        private String sqlStr;
        //总记录数目
        private int rowCount = 0;
        //所分的逻辑页数
        private int pageCount = 0;
        //每页显示的记录数目
        private int pageSize = 0;
        //设置参数值
        public void setCon(Connection con) {
            this.con = con;
            if(null==this.con) {
                System.out.println("Failure to get a connection");
            }else {
                System.out.println("Success to get a connection");
            }
        }
    
        //初始化数据库表中的信息
        public void initialize(String sqlStr,int pageSize,int ipage) {
            int irows = pageSize*(ipage-1);
            this.sqlStr = sqlStr;
            this.pageSize = pageSize;
            try {
                stmt = this.con.createStatement();
                rs = stmt.executeQuery(this.sqlStr);
                if(null != rs) {
                    rs.last();
                    this.rowCount = rs.getRow();
                    rs.first();
                    this.pageCount = (this.rowCount-1)/this.pageSize+1;
                }
                this.sqlStr = sqlStr+" limit "+irows+","+pageSize;
                stmt = this.con.createStatement();
                rs = stmt.executeQuery(this.sqlStr);
                rsmd = rs.getMetaData();
            }catch(SQLException e) {
                System.out.println("zcn数据库异常："+e.toString());
            }
        }
    
        //将显示结果存到Vector
        public Vector getPage() {
            Vector vData = new Vector();
            try {
                if(null != rs) {
                    while(rs.next()) {
                        String[] sData = new String[2];
                        for(int j=0;j<rsmd.getColumnCount();j++) {
                            sData[j] = rs.getString(j+1);
                        }
                        vData.addElement(sData);
                    }
                }
                rs.close();
                stmt.close();
            }catch(SQLException e) {
                System.out.println("zcn数据库异常："+e.toString());
            }
            return vData;
        }
    
        //获得页面总数
        public int getPageCount() {
            return this.pageCount;
        }
    
        //获取数据表
        public int getRowCount() {
            return this.rowCount;
        }
    }

## 1.2建立显示用的jsp页面 ##

在webContent目录下新建words\_list\_javabean.jsp页面，页面内容如下

**注：需要把数据库用户名和密码改成自己电脑数据库的用户名和密码**

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
    <%@page import="java.sql.*" %>
    <%@page import="java.io.*" %>
    <%@page import="java.util.*" %>
    <%@page import="bean.*"%>
    <jsp:useBean id="pages" scope="page" class="bean.SpiltPage"/>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <%! //每页显示的记录数 int pageSize = 3; String sqlStr=""; //当前页 int showPage=1; //数据库用户名 String userName="root"; //数据库密码 String userPassword="123"; //数据库的URL,包括连接数据库所使用的编码格式 String url="jdbc:mysql://localhost:3306/javaee"; //定义连接对象 Connection dbcon; %>
    <% try{ //加载驱动程序 Class.forName("com.mysql.jdbc.Driver"); //获得数据库的连接对象 dbcon = DriverManager.getConnection(url,userName,userPassword); }catch(SQLException e){ //打印出异常信息 System.out.println(e.toString()); } //给pages中参数con赋值 pages.setCon(dbcon); sqlStr="select * from news_inf order by news_id"; //查询数据表，获得查询结果 String strPage = null; //获取跳转到的目的的页面 strPage = request.getParameter("showPage"); if(null == strPage){ showPage=1; }else{ try{ showPage=Integer.parseInt(strPage); }catch(NumberFormatException e){ showPage = 1; } if(showPage<1){ showPage=1; } } pages.initialize(sqlStr, pageSize, showPage); //获取要显示的数据集合 Vector vData = pages.getPage(); %>
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>分页显示</title>
    </head>
    <body bgcolor="#FFFFFF" text="#000000">
        <h1 align=center>留言簿</h1>
        <div align=center>
        <table border="1" cellspacing="0" cellpadding="0" width="80%">
            <tr>
            <th width="20%">编号</th>
            <th width="50%">标题</th>
            </tr>
            <% for(int i=0;i<vData.size();i++){ //显示数据 String[] sData=(String[])vData.get(i); %>
            <tr>
                <td><%=sData[0]%></td>
                <td align=left><%=sData[1]%></td>
            </tr>
            <%}%>
        </table>
        <form action="words_list_javabean.jsp" mehod="get" target="_self">
            共<font color=red><%=pages.getRowCount()%></font>条 
            <%=pageSize%>条/页 
            第<font color=red><%=showPage%></font>页/共
            <font color=red><%=pages.getPageCount()%></font>页 
            <a href="words_list_javabean.jsp?showPage=1" target="_self">[首页]</a> 
            <% //判断“上一页”链接是否要显示 if(showPage>1){ %>
                <a href="words_list_javabean.jsp?showPage=<%=showPage-1%>"target="_self">[上一页]</a> 
            <% }else{ %>  
                [上一页] 
            <% } //判断下一页链接是否显示 if(showPage<pages.getPageCount()){ %>
                <a href="words_list_javabean.jsp?showPage=<%=showPage+1%>"target="_self">[下一页]</a>
            <% }else{ %>
                [下一页] 
            <%} %>
            <a href="words_list_javabean.jsp?showPage=<%=pages.getPageCount()%>"target="_self">[尾页]</a> 
            转到
            <select name="showPage">
            <% for(int i=1;i<=pages.getPageCount();i++){ %>
                <option value="<%=i%>"<%if(showPage==i)out.println("selected");%>><%=i%></option>
            <%} %>  
            </select>
            页 
            <input type="submit" name="go" value="提交"/>
        </form>
        <% //关闭数据库连接 dbcon.close(); %>
        </div>
    </body>
    </html>

# 3 运行显示 #

至此就可以将web项目跑到Tomcat服务器上，并在浏览器上访问，访问的网址为：

http://localhost:8080/SpiltPageDB/words_list_javabean.jsp

**注：SpiltPageDB为你建立的web项目的名字**