利用servlet+jsp+javabean进行分页查询

Bertha 。 2022-10-02 12:44 293阅读 0赞

### 利用servlet+jsp+javabean进行分页查询 ###

*  分页项目
 *   *  文件结构
     *  文件说明
     *  mysql的limit查询
     *  源代码下载地址
 *  JDBC
 *  javaBean的Companyinfo
 *  Dao层
 *  pageBean
 *  servlet的page
 *  运行截图
 *  eclipse常用的快捷键

# 分页项目 #

## 文件结构 ##

![文件结构][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw_size_16_color_FFFFFF_t_70]

## 文件说明 ##

Companyinfo：是一个JavaBean类，需要显示的内容
    pageBean：描述分页信息的内容
    CompanyinfoDao：Companyinfo的Dao层用来数据库访问的一个接口
    DB：连接数据库，获取数据库连接对象
    page：servlet与前台进行交换，在运行该项目的时候在浏览器的的地址栏访问
    http://localhost:8080/page/page
    show.jsp：用来显示从数据库获取的记录，并可以进行上下页进行查看
    lib：导入该项目所需要的jar

## mysql的limit查询 ##

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset  
    
    说明：
    LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个
    或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指
    定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记
    录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句
    法： LIMIT # OFFSET #。

## 源代码下载地址 ##

[利用servlet+jsp+javabean进行分页查询][servlet_jsp_javabean]

# JDBC #

package jdbc;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    public class DB { 
    	private final static String driver="com.mysql.jdbc.Driver";
    	private final static String url = "jdbc:mysql://
        127.0.0.1:3306/job?useSSL=false";
    	private final static String user = "root";
    	private final static String password  ="";
    	public static Connection getConn() { 
    		Connection connection = null;
    		try { 
    			Class.forName(driver);
    			connection=DriverManager.getConnection(url, user, 
                password);
    		} catch (ClassNotFoundException e) { 
    			System.out.println("数据库驱动加载失败");
    			e.printStackTrace();
    		}catch (SQLException e) { 
    			System.out.println("数据库连接失败");
    			e.printStackTrace();
    		}
    		return connection;
    	}
    	
    	public static void close (Connection connection ,Statement 
        statement , ResultSet resultSet) { 
    		try { 
    			if(connection!=null)connection.close();
    			if(statement!=null)statement.close();
    			if(resultSet!=null)resultSet.close();
    		} catch (SQLException e) { 
    			System.out.println("数据库关闭异常");
    			e.printStackTrace();
    		}
    	}
    	
    	//测试数据库连接是否成功
    	public static void main(String []args) { 
    		Connection connection=null;
    		connection = getConn();
    		System.out.println(connection);
    		close(connection, null, null);
    		
    	}
    }

# javaBean的Companyinfo #

package Bean;
    
    public class Companyinfo { 
    	private String c_name;
    	private String c_email;
    	private String c_serve;
    
    	@Override
    	public String toString() { 
    		return "Companyinfo [c_name=" + c_name + ", c_email=" + c_email + ", c_serve=" + c_serve + "]";
    	}
    
    	public Companyinfo(String c_name, String c_email, String c_serve) { 
    		super();
    		this.c_name = c_name;
    		this.c_email = c_email;
    		this.c_serve = c_serve;
    	}
    
    	public Companyinfo() { 
    		super();
    	}
    
    	public String getC_name() { 
    		return c_name;
    	}
    
    	public void setC_name(String c_name) { 
    		this.c_name = c_name;
    	}
    
    	public String getC_email() { 
    		return c_email;
    	}
    
    	public void setC_email(String c_email) { 
    		this.c_email = c_email;
    	}
    
    	public String getC_serve() { 
    		return c_serve;
    	}
    
    	public void setC_serve(String c_serve) { 
    		this.c_serve = c_serve;
    	}
    
    }

# Dao层 #

package dao;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.List;
    
    import Bean.Companyinfo;
    import jdbc.DB;
    
    public class CompanyinfoDao { 
    	
    	public int getCount(String sql) { 
    		int count=0;
    		Connection connection = DB.getConn();
    		Statement statement = null;
    		ResultSet resultSet = null;
    		try { 
    			statement=connection.createStatement();
    			resultSet=statement.executeQuery(sql);
    			while(resultSet.next()) { 
    				count=resultSet.getInt(1);
    			}
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}finally { 
    			DB.close(connection, statement, resultSet);
    		}
    		return count;
    	}
    	
    	public List<Companyinfo> getList(String sql){ 
    		List<Companyinfo> list = new ArrayList<>();
    		Connection connection = DB.getConn();
    		Statement statement = null;
    		ResultSet resultSet = null;
    		try { 
    			statement=connection.createStatement();
    			resultSet=statement.executeQuery(sql);
    			while(resultSet.next()) { 
    				Companyinfo companyinfo = new Companyinfo(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3));
    				list.add(companyinfo);
    			}
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}finally { 
    			DB.close(connection, statement, resultSet);
    		}
    		return list;
    	}
    	
    }

# pageBean #

package Bean;
    public class pageBean { 
    	private int everyPage; // 每页显示记录数
    	private int totalCount; // 总记录数
    	private int totalPage; // 总页数
        private int currentPage; //当前页
        private int beginIndex;			//查询起始点
    	public pageBean(int everyPage, int totalCount ,int currentPage) { 
    		super();
    		this.everyPage = everyPage;
    		this.totalCount = totalCount;
    		this.currentPage = currentPage;
    		if(totalCount != 0 &&totalCount % everyPage == 0) { 
    			totalPage = totalCount / everyPage;
    		} else { 
    			totalPage = totalCount / everyPage + 1;
    		}
    	}
    	public int getCurrentPage() { 
    		return currentPage;
    	}
    	public void setCurrentPage(int currentPage) { 
    		if(currentPage<1)currentPage=1;
    		this.currentPage = currentPage;
    		
    	}
    	public int getEveryPage() { 
    		return everyPage;
    	}
    	public void setEveryPage(int everyPage) { 
    		this.everyPage = everyPage;
    	}
    	public int getTotalCount() { 
    		return totalCount;
    	}
    	public void setTotalCount(int totalCount) { 
    		this.totalCount = totalCount;
    	}
    	
    	public int getTotalPage() { 
    		return totalPage;
    	}
    	public void setTotalPage(int totalPage) { 
    		this.totalPage = totalPage;
    	}
    	public int getBeginIndex() { 
    		setBeginIndex();
    		return beginIndex;
    	}
    	public void setBeginIndex() { 
    		this.beginIndex =(currentPage - 1) * everyPage;
    	}
    
    }

# servlet的page #

package servlet;
    import java.io.IOException;
    import java.util.List;
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import javax.servlet.http.HttpSession;
    import Bean.Companyinfo;
    import Bean.pageBean;
    import dao.CompanyinfoDao;
    @WebServlet("/page")
    public class page extends HttpServlet { 
    	private static final long serialVersionUID = 1L;
    	public static CompanyinfoDao companyinfoDao =  new CompanyinfoDao();
    	String sql;
    	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
    		HttpSession session = request.getSession();
    		pageBean pBean = (pageBean)session.getAttribute("page");
    		if(pBean==null) { 
    			sql = "select count(*) from companyinfo ";
    			int count =companyinfoDao.getCount(sql);
    			pBean  = new pageBean(3, count, 1);
    		}else { 
    			String cString = request.getParameter("currentPage");
    			if(cString==null)cString="1";
    			int currentPage = Integer.parseInt(cString);
    			pBean.setCurrentPage(currentPage);
    		}
    		String sql = "select c_name,c_email, c_serve from companyinfo limit "+pBean.getBeginIndex()+","+pBean.getEveryPage();
    		List<Companyinfo> list = companyinfoDao.getList(sql);
    		session.setAttribute("page", pBean);
    		request.setAttribute("list", list);
    		request.getRequestDispatcher("show.jsp").forward(request, response);
    	}
    	
    	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
    		doGet(request, response);
    	}
    
    }

\#前台 show.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
    <%@page import="Bean.*"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html lang="en">
    <head>
    	<meta charset="UTF-8">
    	<title>Document</title>
    </head>
    <style type="text/css"> table { border-collapse: collapse; border:2px solid black; } th,td { padding: .5em .75em; } th { border:1px solid grey; } td { border:1px dotted grey; } </style>
     <body>
            <table>
                <caption>彩排安排</caption>
                <thead>
                    
                    <tr>
                        <th scope="rowgroup">公司名称</th>
                        <th scope="col">公司email</th>
                        <th scope="col">公司服务</th>
                    </tr>
                </thead>
                <tbody>
                    
                   <c:forEach items="${requestScope.list}" var="companyinfo">
                   	<tr>
                   		<td>${companyinfo.c_name}</td>
                   		<td>${companyinfo.c_email}</td>
                   		<td>${companyinfo.c_serve}</td>
                   </tr>
                   </c:forEach>
                </tbody>
            </table>
            <a href="page?currentPage=1">首页</a>  
             <c:if test="${sessionScope.page.currentPage !=1}">
            	 <a href="page?currentPage=${sessionScope.page.currentPage-1}">上一页</a>  
             </c:if>
             <c:if test="${sessionScope.page.currentPage !=sessionScope.page.totalPage}">
            	 <a href="page?currentPage=${sessionScope.page.currentPage+1}">下一页</a>  
             </c:if>       
              <a href="page?currentPage=${sessionScope.page.totalPage}">尾页</a>
        </body>
    </html>

# 运行截图 #

![运行截图一][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw_size_16_color_FFFFFF_t_70 1]  
![运行截图2][2]

# eclipse常用的快捷键 #

<table> 
 <thead> 
  <tr> 
   <th>描述</th> 
   <th>快捷键</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>生成set和get方法</td> 
   <td>Alt+shift+s r</td> 
  </tr> 
  <tr> 
   <td>生成toString方法</td> 
   <td>Alt+shift+s s</td> 
  </tr> 
  <tr> 
   <td>生成object方法</td> 
   <td>Alt+shift+s o</td> 
  </tr> 
  <tr> 
   <td>格式化代码</td> 
   <td>Alt+shift+s f</td> 
  </tr> 
  <tr> 
   <td>保存所有代码</td> 
   <td>ctrl+shift+s</td> 
  </tr> 
  <tr> 
   <td>自动补全代码或者提示代码</td> 
   <td>alt+?或alt+/</td> 
  </tr> 
 </tbody> 
</table>

**注意：Alt+shift+s r 是指先按下組合按下Alt，shift，s 后按下r即可**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw_size_16_color_FFFFFF_t_70]: /images/20220112/1c5395991ad34074ac57257cdc7d62b9.png
[servlet_jsp_javabean]: https://download.csdn.net/download/qq_41146650/11247437
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw_size_16_color_FFFFFF_t_70 1]: /images/20220112/9b38f83d54fb453fb753da172b27832b.png
[2]: /images/20220112/ec40e3ae0ae349748a71c19e2e8aa9a8.png