如何在HTML中展示SQLite数据库users表的username列？

Got it, let's walk through a full end-to-end implementation to show all usernames from your SQLite database on your website using your tech stack (Java, JavaScript, HTML, SQL). Here's a step-by-step breakdown that covers every part you'll need:

1. Backend Setup (Java) #

First, we'll build a simple Java endpoint to connect to your SQLite database, run your SELECT username FROM users query, and return the data in a format the frontend can easily work with (JSON).

Step 1: Add SQLite JDBC Dependency #

To connect Java to SQLite, you'll need the sqlite-jdbc library. If you're using Maven, add this to your pom.xml:

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.45.2.0</version> <!-- Use the latest stable version -->
</dependency>

If you're not using Maven, download the JAR file directly and drop it into your project's lib folder.

Step 2: Create a Java Servlet (REST Endpoint) #

This servlet will handle GET requests, query the database, and send back a JSON array of usernames. I'll include both manual JSON formatting (no extra libraries) and a note on using Gson for cleaner code.

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 java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/getUsernames")
public class UsernameServlet extends HttpServlet {
    // Update this path to match where your SQLite database is stored
    private static final String DB_URL = "jdbc:sqlite:/path/to/your/database.db";

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8");
        PrintWriter out = response.getWriter();

        List<String> usernames = new ArrayList<>();

        // Try-with-resources ensures connections/streams are closed automatically
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT username FROM users")) {

            // Pull all usernames from the result set
            while (rs.next()) {
                usernames.add(rs.getString("username"));
            }

            // Manual JSON conversion (no extra libraries)
            StringBuilder jsonBuilder = new StringBuilder();
            jsonBuilder.append("[");
            for (int i = 0; i < usernames.size(); i++) {
                jsonBuilder.append("\"").append(usernames.get(i)).append("\"");
                if (i != usernames.size() - 1) {
                    jsonBuilder.append(",");
                }
            }
            jsonBuilder.append("]");

            out.print(jsonBuilder);
        } catch (Exception e) {
            e.printStackTrace();
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
            out.print("{\"error\": \"Failed to load usernames. Please try again later.\"}");
        } finally {
            out.close();
        }
    }
}

Note: For cleaner JSON handling, add the Gson library to your project and replace the manual JSON code with out.print(new Gson().toJson(usernames)); – it's less error-prone for larger datasets.

2. Frontend Setup (HTML + JavaScript) #

Next, we'll build an HTML page that uses JavaScript to fetch the usernames from the Java endpoint and display them in a user-friendly list.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Registered Users</title>
    <style>
        .container {
            max-width: 600px;
            margin: 2rem auto;
            padding: 0 1rem;
        }
        .user-list {
            list-style: none;
            padding: 0;
            border: 1px solid #eee;
            border-radius: 8px;
        }
        .user-item {
            padding: 1rem;
            border-bottom: 1px solid #eee;
        }
        .user-item:last-child {
            border-bottom: none;
        }
        .empty-message {
            padding: 2rem;
            text-align: center;
            color: #666;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Registered Users</h1>
        <ul id="userList" class="user-list"></ul>
    </div>

    <script>
        // Fetch usernames from the backend endpoint
        fetch('/getUsernames')
            .then(response => {
                if (!response.ok) {
                    throw new Error('Failed to connect to server');
                }
                return response.json();
            })
            .then(usernames => {
                const userListElement = document.getElementById('userList');
                userListElement.innerHTML = '';

                // Handle empty user list
                if (usernames.length === 0) {
                    const emptyItem = document.createElement('li');
                    emptyItem.className = 'empty-message';
                    emptyItem.textContent = 'No registered users found.';
                    userListElement.appendChild(emptyItem);
                    return;
                }

                // Add each username to the list
                usernames.forEach(username => {
                    const listItem = document.createElement('li');
                    listItem.className = 'user-item';
                    listItem.textContent = username;
                    userListElement.appendChild(listItem);
                });
            })
            .catch(error => {
                console.error('Error loading usernames:', error);
                const userListElement = document.getElementById('userList');
                userListElement.innerHTML = '<li class="empty-message">Failed to load users. Please try again later.</li>';
            });
    </script>
</body>
</html>

3. Deployment & Testing Tips #

• Database Path Check: Double-check the DB_URL in your Java servlet – if your SQLite file is in your project root, use a relative path like jdbc:sqlite:./users.db instead of an absolute path.
• Server Setup: Deploy your Java servlet and HTML page to a web server like Tomcat. Make sure the servlet's context path matches what you're using in the frontend fetch call.
• Production Improvements: For a production setup, consider adding:
  • Authentication/authorization if you don't want public access to the username list
  • A connection pool for database connections (instead of creating a new one per request)
  • More robust error logging instead of just printing to the console

4. How to Test #

1. Start your web server (e.g., Tomcat).
2. Navigate to http://localhost:8080/your-app-context/index.html (replace your-app-context with your project's context path).
3. You should see a list of usernames, or a message if there are no users in the database.

That's all you need! If you hit any snags (like database connection issues or CORS problems), feel free to dig into the error logs and tweak the code accordingly.

内容的提问来源于stack exchange，提问作者Thunderstruck