Spring MVC+H2数据库使用java.sql.Date保存订单时DAO层异常求助

Alright, let's break down the date-related exception you're hitting when saving orders with Spring MVC and H2. Based on your code snippet and common pitfalls with java.sql.Date and JDBC template, here's how to diagnose and fix this:

Common Causes & Fixes #

1. Incomplete/Incorrect Date Parameter Passing #

First off, your code snippet cuts off at order.getOrderDat...—make sure you're actually fetching the date with order.getOrderDate() and that this method returns a non-null java.sql.Date object. A null value here will throw a type mismatch exception if your H2 ORDER_DATE column is marked as NOT NULL.

2. Explicitly Specify Date Type for Parameter Binding #

Sometimes the default type inference in MapSqlParameterSource can misinterpret java.sql.Date with H2's date types. Fix this by explicitly defining the SQL type when adding the parameter:

import java.sql.Types;
// ...
namedParameters.addValue("orderDate", order.getOrderDate(), Types.DATE);

3. Verify SQL Statement & Database Column Alignment #

Double-check your INSERT SQL to ensure the :orderDate parameter maps to the correct column in your H2 table:

• Confirm the database column name matches (e.g., if your table uses ORDER_DATE, don't write order_date in the SQL unless H2 is configured for case-insensitive names).
• Ensure the column type is DATE (not TIMESTAMP or DATETIME). If you need timestamp precision, switch to java.sql.Timestamp instead.

Example valid H2 table schema:

CREATE TABLE ORDERS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    PETROL_QTY DOUBLE NOT NULL,
    PRICE DOUBLE NOT NULL,
    ORDER_DATE DATE NOT NULL
);

Corresponding INSERT statement:

INSERT INTO ORDERS (PETROL_QTY, PRICE, ORDER_DATE) 
VALUES (:petrolQty, :price, :orderDate)

4. Validate Date Conversion Logic #

If you're converting from a frontend date string or Java 8+ date type (like LocalDate) to java.sql.Date, make sure the conversion is correct:

// Converting LocalDate to java.sql.Date (common for modern apps)
LocalDate localOrderDate = LocalDate.now(); // or from input
java.sql.Date sqlOrderDate = java.sql.Date.valueOf(localOrderDate);
order.setOrderDate(sqlOrderDate);

5. Check the Full Exception Stack Trace #

If the above fixes don't work, share the full exception message (e.g., DataIntegrityViolationException, SQLSyntaxErrorException). It'll tell you exactly what's wrong—whether it's a null constraint violation, type mismatch, or invalid column name.

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