如何从DAO获取值传递到JSP？数据入库重复提示与DAO优化

Hey, let's tackle your three questions one by one with practical code examples and straightforward explanations:

1. Show "Record already exist" in JSP when duplicate is detected #

The core idea is to check for existing records first, then pass the error message from your backend to the JSP via the request scope. Here's a step-by-step breakdown:

Step 1: Add an existence check in your DAO #

Create a method to verify if the record already exists using a unique identifier:

public boolean isRecordExists(String uniqueFieldValue) throws SQLException {
    String sql = "SELECT COUNT(*) FROM your_table WHERE unique_column = ?";
    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
        stmt.setString(1, uniqueFieldValue);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            return rs.getInt(1) > 0;
        }
        return false;
    }
}

Step 2: Handle the check in your Servlet #

In your Servlet (the middle layer between DAO and JSP), call the existence method and set the error message if a duplicate is found:

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String uniqueValue = request.getParameter("uniqueField");
    YourDAO dao = new YourDAO();
    
    try {
        if (dao.isRecordExists(uniqueValue)) {
            // Pass error message to JSP
            request.setAttribute("errorMsg", "Record already exist");
            // Forward back to the input form page
            request.getRequestDispatcher("/record-input.jsp").forward(request, response);
            return;
        }
        // No duplicate? Proceed with insert
        dao.saveRecord(yourRecordData);
        request.setAttribute("successMsg", "Record added successfully");
        request.getRequestDispatcher("/record-input.jsp").forward(request, response);
    } catch (SQLException e) {
        e.printStackTrace();
        request.setAttribute("errorMsg", "Database error occurred");
        request.getRequestDispatcher("/record-input.jsp").forward(request, response);
    }
}

Step 3: Display the message in JSP #

Use EL (Expression Language) and JSTL to show the message on your page:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!DOCTYPE html>
<html>
<head>
    <title>Add New Record</title>
</head>
<body>
    <!-- Show error message if present -->
    <c:if test="${not empty errorMsg}">
        <p style="color: crimson;">${errorMsg}</p>
    </c:if>
    
    <!-- Show success message if present -->
    <c:if test="${not empty successMsg}">
        <p style="color: forestgreen;">${successMsg}</p>
    </c:if>
    
    <!-- Your input form -->
    <form method="post" action="RecordServlet">
        <label>Unique Value:</label>
        <input type="text" name="uniqueField" required>
        <!-- Other form fields -->
        <button type="submit">Add Record</button>
    </form>
</body>
</html>

2. Merge query and insert into a single PreparedStatement #

Instead of running two separate statements (check then insert), you can use database-specific syntax to handle duplicates in one query. This is more efficient and avoids race conditions (where another thread inserts the same record between your check and insert).

Example for MySQL (using ON DUPLICATE KEY UPDATE) #

Assuming you have a unique key on the column you're checking:

public int saveOrCheckDuplicate(YourRecord record) throws SQLException {
    // This SQL inserts if no duplicate, does a no-op update if duplicate exists
    String sql = "INSERT INTO your_table (unique_col, col2, col3) VALUES (?, ?, ?) " +
                 "ON DUPLICATE KEY UPDATE unique_col = unique_col";
    
    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
        stmt.setString(1, record.getUniqueValue());
        stmt.setString(2, record.getCol2());
        stmt.setString(3, record.getCol3());
        
        int affectedRows = stmt.executeUpdate();
        // In MySQL:
        // - 1 = record inserted successfully
        // - 2 = duplicate found (update executed)
        return affectedRows;
    }
}

Then in your Servlet, check the return value to trigger the error message:

int result = dao.saveOrCheckDuplicate(record);
if (result == 2) {
    request.setAttribute("errorMsg", "Record already exist");
} else {
    request.setAttribute("successMsg", "Record added");
}

Example for Oracle/SQL Server (using MERGE) #

For Oracle or SQL Server, use the MERGE statement:

public boolean saveOrCheckDuplicate(YourRecord record) throws SQLException {
    String sql = "MERGE INTO your_table t " +
                 "USING (SELECT ? AS unique_col, ? AS col2, ? AS col3 FROM dual) s " +
                 "ON (t.unique_col = s.unique_col) " +
                 "WHEN NOT MATCHED THEN INSERT (unique_col, col2, col3) VALUES (s.unique_col, s.col2, s.col3)";
    
    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
        stmt.setString(1, record.getUniqueValue());
        stmt.setString(2, record.getCol2());
        stmt.setString(3, record.getCol3());
        
        int affectedRows = stmt.executeUpdate();
        return affectedRows > 0; // True if inserted, false if duplicate existed
    }
}

3. Pass data from DAO to JSP #

Just like passing error messages, store data from DAO in the request or session scope, then access it in JSP using EL and JSTL.

Step 1: Fetch data in DAO #

Create a method to retrieve records from the database:

public List<YourRecord> getAllRecords() throws SQLException {
    List<YourRecord> records = new ArrayList<>();
    String sql = "SELECT id, unique_col, col2 FROM your_table";
    
    try (PreparedStatement stmt = connection.prepareStatement(sql);
         ResultSet rs = stmt.executeQuery()) {
        
        while (rs.next()) {
            YourRecord record = new YourRecord();
            record.setId(rs.getInt("id"));
            record.setUniqueValue(rs.getString("unique_col"));
            record.setCol2(rs.getString("col2"));
            records.add(record);
        }
    }
    return records;
}

Step 2: Pass data from Servlet to JSP #

Store the fetched data in the request scope and forward to your JSP:

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    YourDAO dao = new YourDAO();
    try {
        List<YourRecord> records = dao.getAllRecords();
        request.setAttribute("records", records);
        request.getRequestDispatcher("/records-list.jsp").forward(request, response);
    } catch (SQLException e) {
        e.printStackTrace();
        request.setAttribute("errorMsg", "Failed to load records");
        request.getRequestDispatcher("/error-page.jsp").forward(request, response);
    }
}

Step 3: Display data in JSP #

Use JSTL's <c:forEach> to loop through the list and render records:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!DOCTYPE html>
<html>
<head>
    <title>All Records</title>
</head>
<body>
    <h1>Record List</h1>
    
    <c:if test="${not empty errorMsg}">
        <p style="color: crimson;">${errorMsg}</p>
    </c:if>
    
    <table border="1" cellpadding="8">
        <tr>
            <th>ID</th>
            <th>Unique Value</th>
            <th>Column 2</th>
        </tr>
        <c:forEach items="${records}" var="record">
            <tr>
                <td>${record.id}</td>
                <td>${record.uniqueValue}</td>
                <td>${record.col2}</td>
            </tr>
        </c:forEach>
    </table>
</body>
</html>

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