Java+Swing: 从数据库中查询数据并显示在表格中 整理11

分析：要想从数据库中查询数据并分页展示到表格中，我觉得应该按照这个思路：首先就是发起请求，此时需要向数据库中传递三个参数：当前页码（pageNum）、每一页的数量（pageSize）、搜索的关键词（searchKey）、控制从第几页开始（startPageNum）

然后就是接受返回的结果：数据（data）、 总数据条数（totalCount）

 

1. 首先在MainView中定义两个变量，给出初始值

    // 分页：定义两个变量
    private int pageNum = 1;  // 当前是第几页
    private int pageSize = 10; // 一页显示多少条数据

2. 创建请求的类对象

package com.resquest;

import lombok.Data;

/**
 * @Author：xiexu
 * @Date：2023/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;

/**
 * @Author：xiexu
 * @Date：2023/12/12 13:07
 */
@Data
public class TableDTO {
    private Vector<Vector<Object>> 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;

/**
 * @Author：xiexu
 * @Date：2023/12/11 10:40
 */
public class StudentServiceImpl implements StudentService {
    @Override
    public 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();  // 执行查询返回结果集

            // 查询记录
            Vector<Vector<Object>> 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 Vector<Vector<Object>> fillTableData(ResultSet resultSet) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (resultSet.next()) {
            // 遍历查询的每一条记录
            Vector<Object> 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中 

// 查询记录
 Vector<Vector<Object>> queryDatas = fillTableData(resultSet);
 tableDTO.setData(queryDatas);

fillTableData函数：

private static Vector<Vector<Object>> fillTableData(ResultSet resultSet) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (resultSet.next()) {
            // 遍历查询的每一条记录
            Vector<Object> 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);
        Vector<Vector<Object>> data = tableDTO.getData();
        int totalCount = tableDTO.getTotalCount();

 5.运行结果

本篇博客中涉及的数据库连接的相关代码请参考：http://t.csdnimg.cn/eYNmw