运行之后显示的效果：
如果是新项目，建立项目后，把mysql驱动放到指定的目录下即：
WebContent\WEB-INF-lib
我用的驱动是 mysql-connector-j-8.0.33.jar
展示页
listpage.jsp
<%@page import="java.util.Map.Entry"%> <%@page import="week18.util.PageUtil"%> <%@page import="week18.biz.StuBizImpl"%> <%@page import="week18.entity.*"%> <%@page import="java.util.*"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% String page1=request.getParameter("page"); int pageNum = 1; if(page1==null) pageNum = 1; else pageNum = Integer.parseInt(page1); StuBizImpl sbi= new StuBizImpl(); int pageSize=20; int currIndex =pageNum; int totalCount=sbi.SelectCount(); PageUtil pageUtil = new PageUtil( pageSize, currIndex, totalCount); List<Stu> list=sbi.SelectByPage(pageUtil.getStart(), pageUtil.getPageSize()); %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>分页显示记录</title> <style type="text/css"> p{ text-align: center; color:red; } table{ width:100%; background-color: green; text-align: center; } .d1{ text-align: right; } </style> <script src="https://cdn.staticfile.org/Chart.js/3.9.1/chart.js"></script> </head> <body> <div> <canvas id="myChart" width="400" height="400"></canvas> </div> <p></p> <p></p> <p></p> <p></p> <p>人员名单相关信息</p> <table border="1"> <tr> <th>编号</th> <th>姓名</th> <th>性别</th> <th>成绩</th> </tr> <%for(Stu stu:list) {%> <tr> <td><%=stu.getId() %></td> <td><%=stu.getName() %></td> <td><%=stu.getSex() %></td> <td><%=stu.getScore() %></td> </tr> <%} %> </table> <div class="d1"> <a href="listpage.jsp?page=1">第一页</a> <a href="listpage.jsp?page=<%=pageUtil.getCurrIndex()-1%>">上一页</a> <a href="listpage.jsp?page=<%=pageUtil.getCurrIndex()+1%>">下一页</a> <a href="listpage.jsp?page=<%=pageUtil.getTotalPage()%>">最后页</a> (<%=pageUtil.getCurrIndex() %>/<%=pageUtil.getTotalPage() %>) </div> <% Map<String,Integer> map=sbi.SexConut(); String keyStr="",valStr=""; for(Entry<String,Integer> entry:map.entrySet()){ String key=entry.getKey(); Integer value=entry.getValue(); keyStr+="'"+key+"',"; valStr+=value+","; } %> <script> const ctx = document.getElementById('myChart'); const data = { labels: [ <%=keyStr%> ], datasets: [{ label: '不同性别占比情况', data: [<%=valStr%>], backgroundColor: [ 'rgb(255, 99, 132)', 'rgb(54, 162, 235)', 'rgb(255, 205, 86)' ], hoverOffset: 4 }] }; const config = { type: 'pie', data: data, options: { responsive: true, // 设置图表为响应式，根据屏幕窗口变化而变化 maintainAspectRatio: false,// 保持图表原有比例 scales: { yAxes: [{ ticks: { beginAtZero:true } }] } } }; const myChart = new Chart(ctx, config); </script> </body> </html>工具类
连接库的工具
package week18.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class BaseDao { private final static String driver = "com.mysql.cj.jdbc.Driver"; // 数据库驱动 private final static String url = "jdbc:mysql://mysql.sqlpub.com:3306/huangjin"; // url private final static String dbName = "laocooon"; // 数据库用户名 private final static String dbPass = "fc12f7a5215e8e0a"; // 数据库密码 private static Connection conn = null; private static PreparedStatement pstmt = null; private static ResultSet rs = null; public static Connection getConn() throws Exception{ Class.forName(driver); //注册驱动 return DriverManager .getConnection(url,dbName,dbPass); //获得数据库连接并返回 } public static void closeAll( Connection conn, PreparedStatement pstmt, ResultSet rs ) throws Exception { if(rs != null) rs.close(); if(pstmt != null) pstmt.close(); if(conn != null) conn.close(); } public static void closeAll( Connection conn, PreparedStatement pstmt) throws Exception { if(pstmt != null) pstmt.close(); if(conn != null) conn.close(); } //直接关静态成员的资源 public static void closeAll() throws Exception { closeAll(conn,pstmt,rs);// 关 17 18 19行的资源的 } //增删改 返回多少条发生了变化，如果0表示没变化 public static int executeUpdataSQL(String sql,String[] param)throws Exception { Connection conn = null; PreparedStatement pstmt = null; conn = getConn(); pstmt = conn.prepareStatement(sql); for( int i = 0; i < param.length; i++ ) pstmt.setString(i+1, param[i]); int num = pstmt.executeUpdate(); closeAll(conn,pstmt); return num; } //调用此代码，使用完 记录集的内容之后，通过 closeAll(); public static ResultSet executeQuerySQL(String sql,String[] param)throws Exception { closeAll(); conn = getConn(); pstmt = conn.prepareStatement(sql); for( int i = 0; i < param.length; i++ ) pstmt.setString(i+1, param[i]); rs = pstmt.executeQuery(); return rs; } }
分页工具
package week18.util; public class PageUtil { private int pageSize;//一页有多少条 private int currIndex;//当前是第几页 private int totalCount;//共有多少条记录 谁给我？ 逻辑层的 getTotalCount private int totalPage;//共有多少页 private int start;//显时时开始的位置 //构造方法时，需要提供什么参数 一页多少个，当前第几页，一共多少条 public PageUtil(int pageSize, int currIndex, int totalCount) { this.pageSize = pageSize; this.currIndex = currIndex; this.totalCount = totalCount; } public int getPageSize() { return pageSize; } public int getCurrIndex() { currIndex=currIndex<1?1:currIndex;//如果当前页小于1，则为1 currIndex=currIndex>getTotalPage()?getTotalPage():currIndex;//如果当前页大于总页数 return currIndex; } public int getTotalCount() { return totalCount; } public int getTotalPage() { return totalCount%pageSize==0?totalCount/pageSize:totalCount/pageSize+1; } public int getStart() { return (getCurrIndex()-1)*pageSize;//开始的位置=（当前页-1）*一页的数; } @Override public String toString() { return "PageUtil [pageSize=" + pageSize + ", currIndex=" + currIndex + ", totalCount=" + totalCount + ", totalPage=" + totalPage + ", start=" + start + ", toString()=" + super.toString() + "]"; } }
实体包的 VO类
package week18.entity; public class Stu { private int id; private String name,sex; private int score; public Stu() { } public Stu(int id, String name, String sex, int score) { super(); this.id = id; this.name = name; this.sex = sex; this.score = score; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public int getScore() { return score; } public void setScore(int score) { this.score = score; } @Override public String toString() { return "Stu [id=" + id + ", name=" + name + ", sex=" + sex + ", score=" + score + ", toString()=" + super.toString() + "]"; } }数据交换层的dao包
package week18.dao; import java.util.List; import java.util.Map; import week18.entity.Stu; public interface IStuDao { //总记录数 public int SelectCount() throws Exception; //n位置开始后的指定条记录 public List<Stu> SelectByPage(int start,int pageSize) throws Exception; //不同性别的人数 public Map<String,Integer> SexConut() throws Exception; }package week18.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import week18.entity.Stu; import week18.util.BaseDao; public class StuDaoImpl implements IStuDao { private Connection conn=null; private PreparedStatement pstmt=null; ResultSet rs=null; @Override public int SelectCount() throws Exception { String sql="SELECT COUNT(*) FROM stu"; conn=BaseDao.getConn(); pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); int count = 0; if(rs.next()) { count = rs.getInt(1); } BaseDao.closeAll(conn,pstmt,rs); return count; } @Override public List<Stu> SelectByPage(int start, int pageSize) throws Exception { List<Stu> list=new ArrayList<Stu>(); String sql="SELECT * FROM Stu LIMIT ?,?"; conn=BaseDao.getConn(); pstmt=conn.prepareStatement(sql); pstmt.setInt(1, start); pstmt.setInt(2, pageSize); rs=pstmt.executeQuery(); while(rs.next()) { Stu Stu = new Stu(); Stu.setId(rs.getInt(1)); Stu.setName(rs.getString(2)); if(rs.getString(3)!=null) Stu.setSex(rs.getString(3)); else Stu.setSex("未知"); Stu.setScore(rs.getInt(4)); list.add(Stu); } BaseDao.closeAll(conn,pstmt,rs); return list; } @Override public Map<String, Integer> SexConut() throws Exception { Map<String, Integer> map=new HashMap<String, Integer>(); String sql="SELECT COALESCE(sex, '未知') AS sex, COUNT(*) AS count FROM stu GROUP BY sex;"; conn=BaseDao.getConn(); pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); while(rs.next()) { map.put(rs.getString(1), rs.getInt(2)); } BaseDao.closeAll(conn,pstmt,rs); return map; } }业务逻辑层的 biz包
package week18.biz; import java.util.List; import java.util.Map; import week18.entity.Stu; public interface IStuBiz { //总记录数 public int SelectCount() throws Exception; //n位置开始后的指定条记录 public List<Stu> SelectByPage(int start,int pageSize) throws Exception; //不同性别的人数 public Map<String,Integer> SexConut() throws Exception; }package week18.biz; import java.util.List; import java.util.Map; import week18.dao.IStuDao; import week18.dao.StuDaoImpl; import week18.entity.Stu; public class StuBizImpl implements IStuBiz { IStuDao sd = null; public StuBizImpl() { sd=new StuDaoImpl(); } @Override public int SelectCount() throws Exception { // TODO Auto-generated method stub return sd.SelectCount(); } @Override public List<Stu> SelectByPage(int start, int pageSize) throws Exception { // TODO Auto-generated method stub return sd.SelectByPage(start, pageSize); } @Override public Map<String, Integer> SexConut() throws Exception { // TODO Auto-generated method stub return sd.SexConut(); } }
