简单实现单列模糊查询

最新推荐文章于 2024-10-04 20:38:15 发布

Elik-hb

最新推荐文章于 2024-10-04 20:38:15 发布

阅读量167

点赞数 1

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

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

版权

其他专栏收录该内容

7 篇文章 0 订阅

订阅专栏

查询

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-6">
                <select class="form-select" v-model="pageSize" @change="fetchData">
                    <option v-for="option in pageSizeOptions" :value="option">{{ option }}</option>
                </select>
            </div>
            <div class="col-6">
                <input type="text" class="form-control search-box" placeholder="搜索" v-model="searchKeyword" @input="search">
            </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: ''
            },
            methods: {
                fetchData() {
                    axios.get('/test/SearchServlet', {
                        params: {
                            page: this.currentPage,
                            pageSize: this.pageSize,
                            searchKeyword: this.searchKeyword
                        }
                    })
                    .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.fetchData();
            }
        });
    </script>
</body>
</html>

servlet代码

package servlet;

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("/SearchServlet")
public class UserSerachServlet 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;

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        int currentPage = 1;
        int pageSize = 10;
        String searchKeyword = "";

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

        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 WHERE username LIKE ?";
            try (PreparedStatement countStmt = connection.prepareStatement(countQuery)) {
                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 WHERE username LIKE ? LIMIT ? OFFSET ?";
            try (PreparedStatement stmt = connection.prepareStatement(query)) {
                stmt.setString(1, "%" + searchKeyword + "%");
                stmt.setInt(2, pageSize);
                stmt.setInt(3, (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();
    }

}