做衣服的教程网站,长沙建站优化,公司网站表达的内容,网站建设后期需要后期做的分析#xff1a;要想从数据库中查询数据并分页展示到表格中#xff0c;我觉得应该按照这个思路#xff1a;首先就是发起请求#xff0c;此时需要向数据库中传递三个参数#xff1a;当前页码#xff08;pageNum#xff09;、每一页的数量#xff08;pageSize#xff09… 分析要想从数据库中查询数据并分页展示到表格中我觉得应该按照这个思路首先就是发起请求此时需要向数据库中传递三个参数当前页码pageNum、每一页的数量pageSize、搜索的关键词searchKey、控制从第几页开始startPageNum 然后就是接受返回的结果数据data、 总数据条数totalCount 1. 首先在MainView中定义两个变量给出初始值 // 分页定义两个变量private int pageNum 1; // 当前是第几页private int pageSize 10; // 一页显示多少条数据
2. 创建请求的类对象
package com.resquest;import lombok.Data;/*** Authorxiexu* Date2023/12/12 12:59*/
Data
public class StudentRequest {private int pageNum;private int pageSize;private String searchKey; // 搜索关键字private int startPageNum; // 控制从第几页开始public int getStartPageNum() {return (pageNum - 1) * pageSize;}public void setStartPageNum(int startPageNum) {this.startPageNum startPageNum;}
}3. 创建接受返回结果的类对象
package com.response;import lombok.Data;import java.util.Vector;/*** Authorxiexu* Date2023/12/12 13:07*/
Data
public class TableDTO {private VectorVectorObject data;private int totalCount;
}4. 定义接口 返回的结果是TableDTO类型 public interface StudentService {TableDTO queryStudent(StudentRequest studentRequest);
}
5. 实现接口
package com.service.Impl;import com.response.TableDTO;
import com.resquest.StudentRequest;
import com.service.StudentService;
import com.utils.DBUtil;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;/*** Authorxiexu* Date2023/12/11 10:40*/
public class StudentServiceImpl implements StudentService {Overridepublic TableDTO queryStudent(StudentRequest studentRequest){StringBuilder sql new StringBuilder();sql.append(select * from detail );if (studentRequest.getSearchKey() ! null !.equals(studentRequest.getSearchKey().trim())) {sql.append( where name like % studentRequest.getSearchKey() %);}sql.append(order by id desc limit ).append(studentRequest.getStartPageNum()).append(,).append(studentRequest.getPageSize());// 执行Connection connection null;PreparedStatement preparedStatement null;ResultSet resultSet null;TableDTO tableDTO new TableDTO(); // 返回的数据try {connection DBUtil.getConnection();preparedStatement connection.prepareStatement(sql.toString());resultSet preparedStatement.executeQuery(); // 执行查询返回结果集// 查询记录VectorVectorObject queryDatas fillTableData(resultSet);tableDTO.setData(queryDatas);sql.setLength(0);sql.append(select count(*) from detail );if (studentRequest.getSearchKey() ! null !.equals(studentRequest.getSearchKey().trim())) {sql.append( where name like % studentRequest.getSearchKey() %);}preparedStatement connection.prepareStatement(sql.toString());resultSet preparedStatement.executeQuery();while (resultSet.next()) {int count resultSet.getInt(1);tableDTO.setTotalCount(count);}return tableDTO;}catch (Exception e) {e.printStackTrace();} finally {DBUtil.closeRS(resultSet);DBUtil.closePS(preparedStatement);DBUtil.closeConnection(connection);}return null;}private static VectorVectorObject fillTableData(ResultSet resultSet) throws SQLException {VectorVectorObject data new Vector();while (resultSet.next()) {// 遍历查询的每一条记录VectorObject oneRecord new Vector();int id resultSet.getInt(id);String name resultSet.getString(name);String no resultSet.getString(no);String homeTown resultSet.getString(homeTown);int chinese resultSet.getInt(chinese);int math resultSet.getInt(math);int english resultSet.getInt(english);int total resultSet.getInt(total);oneRecord.addElement(id);oneRecord.addElement(name);oneRecord.addElement(no);oneRecord.addElement(homeTown);oneRecord.addElement(chinese);oneRecord.addElement(math);oneRecord.addElement(english);oneRecord.addElement(total);data.addElement(oneRecord);}return data;}
}上述代码详细解释 1. 下方代码主要是编写sql语句的 使用StringBuilder()动态构建字符串。 首先判断searchKey是否为空或是否为空字符串false的话就直接下方的sql语句如果为true的话需要按照name进行模糊查询然后再拼接下方的sql语句 StringBuilder sql new StringBuilder();sql.append(select * from detail );if (studentRequest.getSearchKey() ! null !.equals(studentRequest.getSearchKey().trim())) {sql.append( where name like % studentRequest.getSearchKey() %);}sql.append(order by id desc limit ).append(studentRequest.getStartPageNum()).append(,).append(studentRequest.getPageSize()); 2. 查询数据并注入到tableDTO中 // 查询记录VectorVectorObject queryDatas fillTableData(resultSet);tableDTO.setData(queryDatas);
fillTableData函数
private static VectorVectorObject fillTableData(ResultSet resultSet) throws SQLException {VectorVectorObject data new Vector();while (resultSet.next()) {// 遍历查询的每一条记录VectorObject oneRecord new Vector();int id resultSet.getInt(id);String name resultSet.getString(name);String no resultSet.getString(no);String homeTown resultSet.getString(homeTown);int chinese resultSet.getInt(chinese);int math resultSet.getInt(math);int english resultSet.getInt(english);int total resultSet.getInt(total);oneRecord.addElement(id);oneRecord.addElement(name);oneRecord.addElement(no);oneRecord.addElement(homeTown);oneRecord.addElement(chinese);oneRecord.addElement(math);oneRecord.addElement(english);oneRecord.addElement(total);data.addElement(oneRecord);}return data;} 3. 查询数据数量 sql.setLength(0); // 首先将之前的sql语句置为空sql.append(select count(*) from detail );if (studentRequest.getSearchKey() ! null !.equals(studentRequest.getSearchKey().trim())) {sql.append( where name like % studentRequest.getSearchKey() %);}preparedStatement connection.prepareStatement(sql.toString());resultSet preparedStatement.executeQuery();while (resultSet.next()) {int count resultSet.getInt(1);tableDTO.setTotalCount(count);} 4. MainView中使用 StudentServiceImpl studentService new StudentServiceImpl();StudentRequest studentRequest new StudentRequest();studentRequest.setPageNum(pageNum);studentRequest.setPageSize(pageSize);studentRequest.setSearchKey(searchTxt.getText().trim());TableDTO tableDTO studentService.queryStudent(studentRequest);VectorVectorObject data tableDTO.getData();int totalCount tableDTO.getTotalCount(); 5.运行结果 本篇博客中涉及的数据库连接的相关代码请参考http://t.csdnimg.cn/eYNmw
