实现可以对特定的表查询出列，根据不同列名的内容输入内容进行模糊查询

Elik-hb

于 2024-04-28 00:30:32 发布

阅读量259

点赞数 6

分类专栏：其他文章标签： ajax 前端 vue.js servlet

本文链接：https://blog.csdn.net/qq_65099052/article/details/138261841

版权

其他专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章讲述了如何使用Java编写Servlet，通过JDBC连接MySQL数据库，实现模糊查询列名和用户表数据的分页功能，并利用Vue.js进行前端交互。展示了如何处理GET请求，使用AJAX获取数据并在Vue组件中动态展示和筛选结果。

摘要由CSDN通过智能技术生成

演示

我们以用户名Kon来举例

可以看出我们实现了模糊查询

实现代码

第一个是查询列名有哪些

package servlet;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/ColumnOptionsServlet")
public class ColumnOptionsServlet extends HttpServlet {
    private static final String URL = "jdbc:mysql://localhost:3306/people";
    private static final String USER = "root";
    private static final String PASSWORD = "nmnm1239";

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        response.setContentType("application/json");
        PrintWriter out = response.getWriter();

        JSONArray columnOptions = new JSONArray(); // 存储列名的 JSON 数组

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return;
        }

        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet rs = metaData.getColumns(null, null, "user", null); // 这里的 "user" 是数据库表的名称，根据你的实际表名进行修改
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                columnOptions.add(columnName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }

        JSONObject result = new JSONObject();
        result.put("data", columnOptions);

        out.println(result.toJSONString());
        out.close();
    }
}

我们写的jsp进行测试是否成功

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Column Options</title>
    <script src="https://cdn.jsdelivr.net/npm/vue@2.6.14/dist/vue.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
</head>
<body>
    <div id="app">
        <select v-model="selectedColumn">
            <option v-for="column in columns" :key="column">{{ column }}</option>
        </select>
    </div>

    <script>
        new Vue({
            el: '#app',
            data: {
                columns: [], // 存储列名的数组
                selectedColumn: '' // 选中的列名
            },
            mounted() {
                this.fetchColumnOptions(); // 在组件挂载后立即调用方法获取列名列表
            },
            methods: {
                fetchColumnOptions() {
                    axios.get('/test/ColumnOptionsServlet') // 发送请求获取列名列表
                        .then(response => {
                            this.columns = response.data.data; // 将返回的列名数组赋值给 data 中的 columns
                        })
                        .catch(error => {
                            console.error('请求失败:', error);
                        });
                }
            }
        });
    </script>
</body>
</html>

这个是我们的查询servlet

package servlet;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import pojo.User;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/PaginationServlet")
public class UserPageServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private static final String URL = "jdbc:mysql://localhost:3306/people";
    private static final String USER = "root";
    private static final String PASSWORD = "nmnm1239";
    private static final int PAGE_SIZE = 10; // 每页显示10条记录

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        int currentPage = 1;
        int pageSize = 10; // 默认每页显示10条记录
        String searchKeyword = request.getParameter("searchKeyword");
        String searchColumn = request.getParameter("searchColumn");

        if (request.getParameter("page") != null) {
            currentPage = Integer.parseInt(request.getParameter("page"));
        }
        if (request.getParameter("pageSize") != null) {
            pageSize = Integer.parseInt(request.getParameter("pageSize"));
        }

        List<User> users = new ArrayList<>();
        int totalRecords = 0;
        int totalPages = 0;

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String countQuery = "SELECT COUNT(*) FROM user";
            // 如果有搜索关键词，则构建带搜索条件的 SQL 查询语句
            if (searchKeyword != null && !searchKeyword.isEmpty() && searchColumn != null && !searchColumn.isEmpty()) {
                countQuery += " WHERE " + searchColumn + " LIKE ?";
            }
            try (PreparedStatement countStmt = connection.prepareStatement(countQuery)) {
                // 如果有搜索关键词，则设置查询参数
                if (searchKeyword != null && !searchKeyword.isEmpty() && searchColumn != null && !searchColumn.isEmpty()) {
                    countStmt.setString(1, "%" + searchKeyword + "%");
                }
                ResultSet rs = countStmt.executeQuery();
                if (rs.next()) {
                    totalRecords = rs.getInt(1);
                }
            }

            totalPages = (int) Math.ceil((double) totalRecords / pageSize);

            String query = "SELECT * FROM user";
            // 如果有搜索关键词，则构建带搜索条件的 SQL 查询语句
            if (searchKeyword != null && !searchKeyword.isEmpty() && searchColumn != null && !searchColumn.isEmpty()) {
                query += " WHERE " + searchColumn + " LIKE ?";
            }
            query += " LIMIT ? OFFSET ?";
            try (PreparedStatement stmt = connection.prepareStatement(query)) {
                int paramIndex = 1;
                // 如果有搜索关键词，则设置查询参数
                if (searchKeyword != null && !searchKeyword.isEmpty() && searchColumn != null && !searchColumn.isEmpty()) {
                    stmt.setString(paramIndex++, "%" + searchKeyword + "%");
                }
                stmt.setInt(paramIndex++, pageSize);
                stmt.setInt(paramIndex++, (currentPage - 1) * pageSize);
                ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUsername(rs.getString("username"));
                    user.setEmail(rs.getString("email"));
                    users.add(user);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        JSONObject responseData = new JSONObject();
        responseData.put("totalPages", totalPages);
        responseData.put("users", users);
        responseData.put("totalRecords", totalRecords); // 添加总记录数

        response.setContentType("application/json");
        PrintWriter out = response.getWriter();
        out.println(responseData);
        out.close();
    }
}

对应的jsp查询代码

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>分页示例</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        .search-box {
            float: right;
        }
    </style>
</head>
<body>
    <div id="app" class="container mt-5">
        <div class="row mb-3">
            <div class="col-3">
                <select class="form-select" v-model="searchColumn">
                    <option v-for="column in columnOptions" :value="column">{{ column }}</option>
                </select>
            </div>
            <div class="col-3">
                <input type="text" class="form-control" v-model="searchKeyword" placeholder="搜索内容">
            </div>
            <div class="col-2">
                <button class="btn btn-primary" @click="search">搜索</button>
            </div>
            <div class="col-4">
                <select class="form-select search-box" v-model="pageSize" @change="fetchData">
                    <option v-for="option in pageSizeOptions" :value="option">{{ option }}</option>
                </select>
            </div>
        </div>

        <!-- 数据表格 -->
        <table class="table table-striped">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>用户名</th>
                    <th>邮箱</th>
                </tr>
            </thead>
            <tbody>
                <tr v-for="user in users" :key="user.id">
                    <td>{{ user.id }}</td>
                    <td>{{ user.username }}</td>
                    <td>{{ user.email }}</td>
                </tr>
            </tbody>
        </table>

        <!-- 数据信息 -->
        <div class="row">
            <div class="col-6">
                <p>Showing {{ (currentPage - 1) * pageSize + 1 }} to {{ Math.min(currentPage * pageSize, totalRecords) }} of {{ totalRecords }} entries</p>
            </div>
            <div class="col-6">
                <nav aria-label="Page navigation" class="float-end">
                    <ul class="pagination">
                        <li class="page-item" :class="{ disabled: currentPage === 1 }">
                            <a class="page-link" href="#" @click.prevent="prevPage">上一页</a>
                        </li>
                        <li class="page-item" v-for="page in totalPages" :key="page" :class="{ active: currentPage === page }">
                            <a class="page-link" href="#" @click.prevent="goToPage(page)">{{ page }}</a>
                        </li>
                        <li class="page-item" :class="{ disabled: currentPage === totalPages }">
                            <a class="page-link" href="#" @click.prevent="nextPage">下一页</a>
                        </li>
                    </ul>
                </nav>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/vue@2.6.14/dist/vue.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        new Vue({
            el: '#app',
            data: {
                currentPage: 1,
                totalPages: 0,
                pageSize: 10,
                pageSizeOptions: [5, 10, 20, 30, 50],
                users: [],
                totalRecords: 0,
                searchKeyword: '',
                searchColumn: '', // 默认搜索列为空
                columnOptions: [] // 存储列名的数组
            },
            methods: {
                fetchColumnOptions() {
                    axios.get('/test/ColumnOptionsServlet')
                        .then(response => {
                            this.columnOptions = response.data.data; // 将返回的列名数组赋值给 data 中的 columnOptions
                            // 默认选中第一列
                            if (this.columnOptions.length > 0) {
                                this.searchColumn = this.columnOptions[0];
                            }
                        })
                        .catch(error => {
                            console.error('请求失败:', error);
                        });
                },
                fetchData() {
                    axios.get('/test/PaginationServlet', {
                        params: {
                            page: this.currentPage,
                            pageSize: this.pageSize,
                            searchKeyword: this.searchKeyword,
                            searchColumn: this.searchColumn
                        }
                    })
                    .then(response => {
                        this.totalPages = response.data.totalPages;
                        this.users = response.data.users;
                        this.totalRecords = response.data.totalRecords;
                    })
                    .catch(error => {
                        console.error('请求失败:', error);
                    });
                },
                goToPage(page) {
                    if (page >= 1 && page <= this.totalPages) {
                        this.currentPage = page;
                        this.fetchData();
                    }
                },
                prevPage() {
                    if (this.currentPage > 1) {
                        this.currentPage--;
                        this.fetchData();
                    }
                },
                nextPage() {
                    if (this.currentPage < this.totalPages) {
                        this.currentPage++;
                        this.fetchData();
                    }
                },
                search() {
                    this.currentPage = 1;
                    this.fetchData();
                }
            },
            created() {
                this.fetchColumnOptions(); // 获取列名选项
                this.fetchData(); // 初始化数据
            }
        });
    </script>
</body>
</html>

Elik-hb

关注

6
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
实现可以对特定的表查询出列，根据不同列名的内容输入内容进行模糊查询

这个是我们的查询servlet。我们写的jsp进行测试是否成功。可以看出我们实现了模糊查询。我们以用户名Kon来举例。第一个是查询列名有哪些。对应的jsp查询代码。
复制链接

扫一扫