1效果展示
2jdbc连接mysql数据库也可以连接Oracle数据库
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class Base<e> {
public static String class1 = "com.mysql.jdbc.Driver";
public static String url = "jdbc:mysql://localhost:3306/xuexi?characterEncoding=utf8";
public static String username = "root";
public static String userpwd = "123456";
private static Connection conn = null;
private static Statement stmt = null;
protected static ResultSet rs= null;
private String flag="1";
//用于事务控制
public Base(Connection conn){
this.conn=conn;
flag="2";
}
//用于非事务控制
public Base(){
try {
lianjie();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
flag="3";
}
static {
try{
Class.forName(class1);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
public void lianjie() throws SQLException {
conn = DriverManager.getConnection(url,username,userpwd);
System.out.println("====连接成功====");
}
public void chuangjian() throws SQLException {
stmt = conn.createStatement();
System.out.println("====创捷成功====");
}
// 更新
public void zhexing(String sql) throws SQLException {
// lianjie();
chuangjian();
System.out.println("=====sql===="+sql);
stmt.executeUpdate(sql);
System.out.println("=====sql===="+sql);
qingchu();
}
public List<e> chaxun(String sql) throws SQLException {
// lianjie();
chuangjian();
rs = stmt.executeQuery(sql);
System.out.println("===============chaxun111=========="+sql);
List<e> list = new ArrayList<e>();
System.out.println("===============chaxun222==========");
yunxing(list);
System.out.println(list.size());
System.out.println("===============yunxing==========");
qingchu();
System.out.println("======查询我完毕=====");
System.out.println("======listsize111111====="+list.size());
return list;
}
public void qingchu (){
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
rs=null;
}
if (stmt!=null) {
try {
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
stmt=null;
}
if(flag=="3") {
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
conn=null;
}
}
}
public void yunxing(List<e> list) throws SQLException {
}
}
当然这里的yunxing（）可以用反射机制来写；
2.1生成bean和对应的dao层
student的bean
package bean;
import java.sql.SQLException;
import java.util.List;
public class stubean {
String stu_id;
String stu_name;
String stu_sex;
String stu_score;
String stu_addr;
String stu_class;
String stu_beizhu;
String stu_major1;
String stu_age;
public String getStu_id() {
return stu_id;
}
public void setStu_id(String stu_id) {
this.stu_id = stu_id;
}
public String getStu_name() {
return stu_name;
}
public void setStu_name(String stu_name) {
this.stu_name = stu_name;
}
public String getStu_sex() {
return stu_sex;
}
public void setStu_sex(String stu_sex) {
this.stu_sex = stu_sex;
}
public String getStu_score() {
return stu_score;
}
public void setStu_score(String stu_score) {
this.stu_score = stu_score;
}
public String getStu_addr() {
return stu_addr;
}
public void setStu_addr(String stu_addr) {
this.stu_addr = stu_addr;
}
public String getStu_class() {
return stu_class;
}
public void setStu_class(String stu_class) {
this.stu_class = stu_class;
}
public String getStu_beizhu() {
return stu_beizhu;
}
public void setStu_beizhu(String stu_beizhu) {
this.stu_beizhu = stu_beizhu;
}
public String getStu_major1() {
return stu_major1;
}
public void setStu_major1(String stu_major1) {
this.stu_major1 = stu_major1;
}
public String getStu_age() {
return stu_age;
}
public void setStu_age(String stu_age) {
this.stu_age = stu_age;
}
@Override
public String toString() {
return "stubean [stu_id=" + stu_id + ", stu_name=" + stu_name + ", stu_sex=" + stu_sex + ", stu_score="
+ stu_score + ", stu_addr=" + stu_addr + ", stu_class=" + stu_class + ", stu_beizhu=" + stu_beizhu
+ ", stu_major1=" + stu_major1 + ", stu_age=" + stu_age + "]";
}
public stubean(String stu_id, String stu_name, String stu_sex, String stu_score, String stu_addr,
String stu_class, String stu_beizhu, String stu_major1, String stu_age) {
super();
this.stu_id = stu_id;
this.stu_name = stu_name;
this.stu_sex = stu_sex;
this.stu_score = stu_score;
this.stu_addr = stu_addr;
this.stu_class = stu_class;
this.stu_beizhu = stu_beizhu;
this.stu_major1 = stu_major1;
this.stu_age = stu_age;
}
}对应的dao层
package dao;
import java.sql.SQLException;
import java.util.List;
import bean.stubean;
public class stuDAO extends Base<stubean>{
//查询全部
public List<stubean> fandalldao() throws SQLException{
String sql = "select * from student2";
return super.chaxun(sql);
}
public void yunxing(List<stubean> list) {
try{
while (rs.next()) {
String stu_id = rs.getString("stu_id");
String stu_name = rs.getString("stu_name");
String stu_sex = rs.getString("stu_sex");
String stu_score = rs.getString("stu_score");
String stu_addr = rs.getString("stu_addr");
String stu_class = rs.getString("stu_class");
String stu_beizhu = rs.getString("stu_beizhu");
String stu_major1 = rs.getString("stu_major1");
String stu_age = rs.getString("stu_age");
stubean stu = new stubean(stu_id, stu_name, stu_sex, stu_score, stu_addr, stu_class, stu_beizhu, stu_major1, stu_age);
list.add(stu);
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
3对应的service
package service;
import java.sql.SQLException;
import java.util.List;
import bean.stubean;
import dao.stuDAO;
public class stuservice {
// 查询
public List<stubean> fandall() throws SQLException{
return new stuDAO().fandalldao();
}
public List<stubean> fandlimit( String page , String limit ) throws SQLException{
int startRec=(Integer.valueOf(page)-1)*Integer.valueOf(limit);
String sql="select * from student2 limit "+startRec+","+limit+"";
return new stuDAO().chaxun(sql);
}
}
4servlet
4.1传到前端jsp中显示出来的数据
这里要主要传输的格式wei：
{
"code": 0,
"msg": "",
"count": 1000,
"data": [{}, {}]
}
package servlet;
import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 org.json.JSONObject;
import bean.stubean;
import service.stuservice;
/**
* Servlet implementation class fandallservlet
*/
@WebServlet("/fandallservlet.do")
public class fandallservlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public fandallservlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
System.out.println("========1=====");
String page=request.getParameter("page");
String limit=request.getParameter("limit");
List<stubean> list = null;
List<stubean> list1 = null;
try {
list = new stuservice().fandall();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
/**
* {
"code": 0,
"msg": "",
"count": 1000,
"data": [{}, {}]
}
*/
try {
list1 = new stuservice().fandlimit(page, limit);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Map<String, Object> map = new HashMap<String, Object>();
map.put( "code", 0);
map.put("msg","哈哈哈");
map.put( "count",list.size());
StringBuffer str = new StringBuffer();
str.append("[");
for(int i = 0 ; i<list1.size() ; i++) {
if(i!=0) {
str.append(",");
}
/*str.append("{");
str.append("\"stu_id\"");
str.append(":");
str.append("\""+list.get(i).getStu_id()+"\"");
str.append(",");
str.append("{");
str.append("\"stu_id\"");
str.append(":");
str.append("\""+list.get(i).getStu_id()+"\"");
str.append(",");
*/
JSONObject jsonObject=new JSONObject(list1.get(i));
//System.out.println(jsonObject);
str.append(jsonObject.toString());
}
str.append("]");
System.out.println(map.toString());
JSONObject json1 = new JSONObject(map);
String re = json1.toString();
String re1 = re.subSequence(0, re.lastIndexOf("}")).toString();
String str1 = re1 +","+"\"data\""+":"+str.toString()+"}";
System.out.println(str1.toString());
response.getWriter().print(str1);
}
}
4.2数据分组生成excel表格和遍历目录生成对应的zip文件的servlet
package servlet;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
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 org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import bean.stubean;
import until.SnowFlakeUtils;
import until.compressXSL;
import service.stuservice;
/**
* Servlet implementation class yasuo
*/
@WebServlet("/yasuo.do")
public class yasuo extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public yasuo() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
// 查询数据
List<stubean> list = null;
try {
list = new stuservice().fandall();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("==listsize==="+list.size());
// 导入excle
/* Workbook workbook = WorkbookFactory.create(true); // 创建一个新的Excel工作簿
Sheet sheet = workbook.createSheet("Students"); // 创建一个名为"Students"的工作表
Row row = sheet.createRow(0);
Cell c1 = row.createCell(0);
c1.setCellValue("stu_id");
Cell c2 = row.createCell(0);
c2.setCellValue("stu_name");
Cell c3 = row.createCell(0);
c3.setCellValue("stu_sex");
Cell c4 = row.createCell(0);
c4.setCellValue("stu_score");
Cell c5 = row.createCell(0);
c5.setCellValue("stu_addr");
Cell c6 = row.createCell(0);
c6.setCellValue("stu_class");
Cell c7 = row.createCell(0);
c7.setCellValue("stu_beizhu");
Cell c8 = row.createCell(0);
c8.setCellValue("stu_major1");
Cell c9 = row.createCell(0);
c9.setCellValue("sstu_age");
int rowNumber = 1;
for(int i = 0; i<list.size();i++) {
Row row1 = sheet.createRow(rowNumber++);
Cell cell1 = row1.createCell(0);
cell1.setCellValue(list.get(i).getStu_id()); // 假设学生对象有一个getId()方法获取学生ID，将其写入第一列
Cell cell2 = row1.createCell(1);
cell2.setCellValue(list.get(i).getStu_name());
Cell cell3 = row1.createCell(1);
cell3.setCellValue(list.get(i).getStu_sex());
Cell cell4 = row1.createCell(1);
cell4.setCellValue(list.get(i).getStu_score());
Cell cell5 = row1.createCell(1);
cell5.setCellValue(list.get(i).getStu_addr());
Cell cell6 = row1.createCell(1);
cell6.setCellValue(list.get(i).getStu_class());
Cell cell7 = row1.createCell(1);
cell7.setCellValue(list.get(i).getStu_beizhu());
Cell cell8 = row1.createCell(1);
cell8.setCellValue(list.get(i).getStu_major1());
Cell cell9 = row1.createCell(1);
cell9.setCellValue(list.get(i).getStu_age());
}
*/
int size = 0;
int count = 10;
if(list.size()%10==0) {
size = list.size()/10;
}
if(list.size()%10!=0) {
size = list.size()/10+1;
}
for(int w = 0;w<size;w++) {
Workbook workbook = WorkbookFactory.create(true); // 创建一个新的Excel工作簿
Sheet sheet = workbook.createSheet("Students"); // 创建一个名为"Students"的工作表
Row headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("stu_id");
headerRow.createCell(1).setCellValue("stu_name");
headerRow.createCell(2).setCellValue("stu_sex");
headerRow.createCell(3).setCellValue("stu_score");
headerRow.createCell(4).setCellValue("stu_addr");
headerRow.createCell(5).setCellValue("stu_class");
headerRow.createCell(6).setCellValue("stu_beizhu");
headerRow.createCell(7).setCellValue("stu_major1");
headerRow.createCell(8).setCellValue("stu_age");
int rowNumber = 1;
for (int t =w*10 ;t<(w+1)*10;t++) {
if(t==list.size()) {
break;
}
Row dataRow = sheet.createRow(rowNumber++);
dataRow.createCell(0).setCellValue(list.get(t).getStu_id()); // 学生ID
dataRow.createCell(1).setCellValue(list.get(t).getStu_name()); // 学生姓名
dataRow.createCell(2).setCellValue(list.get(t).getStu_sex()); // 学生性别
dataRow.createCell(3).setCellValue(list.get(t).getStu_score()); // 学生成绩
dataRow.createCell(4).setCellValue(list.get(t).getStu_addr()); // 学生地址
dataRow.createCell(5).setCellValue(list.get(t).getStu_class()); // 学生班级
dataRow.createCell(6).setCellValue(list.get(t).getStu_beizhu()); // 学生备注
dataRow.createCell(7).setCellValue(list.get(t).getStu_major1()); // 学生专业
dataRow.createCell(8).setCellValue(list.get(t).getStu_age()); // 学生年龄
}
File file1= new File("D:\\zhou");
if(!file1.exists()){
file1.mkdir();//如果目录不存在就创建
}
System.out.println("111111111111");
try (FileOutputStream fos = new FileOutputStream(file1+"\\"+""+w+""+".xls")) {
workbook.write(fos);
System.out.println("数据已成功写入Excel文件！");
} catch (IOException e) {
e.printStackTrace();
}
}
// 压缩
String sourceFilePath = "D:\\zhou";
String zipFilePath = "D:\\zhoujiajun1"+"\\"+""+SnowFlakeUtils.nextId()+""+".zip";
compressXSL.compressFiles(sourceFilePath, zipFilePath);
response.getWriter().print("2");
}
}
这里要注意的是使用了阿帕奇的poi：
Workbook workbook = WorkbookFactory.create(true); ：创建一个新的Excel工作簿
Sheet sheet = workbook.createSheet("Students"); ：创建一个名为"Students"的工作表，即为创建一个sheet页。
Row headerRow = sheet.createRow(0);：创建一行，注意是从零开始的；
headerRow.createCell(0).setCellValue("stu_id");：这个就是再一行里创建一个小的cell，我的理解是创建对应的一列 或者是一个小格子；并使用set赋值；
对应的取值就是反过来，但是要判断类型；
5前端jsp页面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Quick Start - Layui</title>
<link href="layui/css/layui.css" rel="stylesheet">
</head>
<body>
<button id="tasuoshang" onclick="yasuo()">导入excel中并压缩文件</button>
<div id="qq" style="display: none">
excle导入压缩中........
<span id="addr"></span>
</div>
<table class="layui-hide" id="ID-table-demo-data" lay-filter="test"></table>
<script type="text/html" id="ID-table-demo-templet-switch">
<!-- 这里的 checked 的状态值判断仅作为演示 -->
{{d.id}}
<input type="checkbox" name="status" value="{{= d.id }}" title="热|" lay-skin="switch" lay-filter="demo-templet-status" {{= d.id == 10001 ? "checked" : "" }}>
</script>
<script type="text/html" id="ID-table-demo-templet-switch1">
<button type="button" class="layui-btn layui-btn-sm layui-btn-normal" lay-event="delete">
<i class="layui-icon layui-icon-delete"></i>
</button>
</script>
<script src="layui/layui.js"></script>
<script type="text/javascript">
layui.use(['table'],function(){
var layer = layui.layer;
var carousel = layui.carousel;
var $ = layui.$;
var table = layui.table;
// 已知数据渲染
var inst = table.render({
elem: '#ID-table-demo-data',
toolbar: true,
url:"fandallservlet.do",
defaultToolbar: ['filter','print','exports'],
cols: [[ //标题栏
{field: 'stu_id', title: '编号', width:'10%', sort: true},
{field: 'stu_name', title: '姓名', width: '10%'},
{field: 'stu_sex', title: '性别', minWidth: '10%'},
{field: 'stu_score', title: '分数', width: '10%'},
{field: 'stu_addr', title: '地址', width: '10%'},
{field: 'stu_class', title: '班级', width: '10%'},
{field: 'stu_beizhu', title: '备注', width: '10%'},
{field: 'stu_major1', title: '专业', width: '10%'},
{field: 'stu_age', title: '年龄', width: '10%'},
{ title: '操作', width: "10%", templet:"#ID-table-demo-templet-switch1"}
]],
even: true,
//skin: 'line', // 表格风格
//even: true,
page: true, // 是否显示分页
limits: [5, 10, 15],
limit: 5 // 每页默认显示的数量
});
/* table.on('tool(test)', function(obj){
var data = obj.data; // 获得当前行数据
// console.log(obj)
obj.click(function(){
console.log(data);
})
}); */
table.on('tool(test)', function(obj) {
var data = obj.data; // 获得当前行数据
console.log(data);
var layEvent = obj.event; // 获得lay-event值
if (layEvent === 'delete') {
// 点击了edit按钮
// 执行删除操作
layer.confirm('确认删除吗？', function(index){
// 发送删除请求
$.ajax({
url: 'deleteData.do',
type: 'post',
data: {id: data.stu_id}, // 根据需要传递的参数进行修改
success: function(res) {
// 删除成功的处理
if (res.code != 0) {
layer.msg('删除成功');
// 刷新表格等操作
table.reload('ID-table-demo-data');
} else {
layer.msg('删除失败');
}
},
error: function() {
layer.msg('删除失败');
}
});
layer.close(index);
});
}
});
table.on('toolbar(test)', function(obj){
var id = obj.config.id;
var checkStatus = table.checkStatus(id);
var othis = lay(this);
switch(obj.event){
case 'getCheckData':
var data = checkStatus.data;
layer.alert(layui.util.escape(JSON.stringify(data)));
break;
case 'getData':
var getData = table.getData(id);
console.log(getData);
layer.alert(layui.util.escape(JSON.stringify(getData)));
break;
case 'LAYTABLE_TIPS':
layer.alert('自定义工具栏图标按钮');
break;
};
});
table.on('colTool(test)', function(obj){
var event = obj.event;
console.log(obj);
if(event === 'email-tips'){
layer.alert(layui.util.escape(JSON.stringify(obj.col)), {
title: '当前列属性配置项'
});
}
});
})
</script>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type="text/javascript">
function yasuo(){
$("#qq").css("display","block");
$.get("yasuo.do",{"hhh":"1"},function(msg){
if(msg==2){
$("#qq").css("display","none");
}
})
}
</script>
</body>
</html>这里使用layui的数据表格模式，用来展现数据和实现分页功能，通过向后端servlet传入"page"，"limit"俩个参数，后端servlet使用limit关键字对查询结果做限制从而实现分页操作。
最后，今天的分享就到这里了。如果有需要可以联系作者获取相关的代码和相应的依赖包jar包。
591
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
