提供一个数据库的表，然后，分页显示表中所有信息，一页10个，此表130条信息。最后再以饼图显示男 女 未知 人数的情况。

发布时间：2023年12月19日

?

运行之后显示的效果：

如果是新项目，建立项目后，把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>&nbsp;&nbsp;&nbsp;&nbsp;
<a href="listpage.jsp?page=<%=pageUtil.getCurrIndex()-1%>">上一页</a>&nbsp;&nbsp;&nbsp;&nbsp;
<a href="listpage.jsp?page=<%=pageUtil.getCurrIndex()+1%>">下一页</a>&nbsp;&nbsp;&nbsp;&nbsp;
<a href="listpage.jsp?page=<%=pageUtil.getTotalPage()%>">最后页</a>&nbsp;&nbsp;&nbsp;&nbsp;
(<%=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();
	}

}

文章来源:https://blog.csdn.net/laocooon/article/details/135023964
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章