Java连接MariaDB按日期查询的正确方法及异常解决

问题背景 #

以下是用于按日期范围查询document_manager表的Java代码：

public List<DocumentManagerDTO> getDocumentManager(java.sql.Date fromDateInclusive, java.sql.Date toDateInclusive) {
        String query = "select * from document_manager where dataReferencia >= ? and dataReferencia <= ?";
        
        QueryRunner run = new QueryRunner();
        
        ResultSetHandler<List<DocumentManagerDTO>> h = new BeanListHandler<DocumentManagerDTO>(DocumentManagerDTO.class);

        List<DocumentManagerDTO> docsToRet = null;
        try {
            docsToRet = run.query(conn, query, h, fromDateInclusive, toDateInclusive);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return docsToRet;
    }

预期生成的SQL语句为：

select * from document_manager where dataReferencia >= '2023-12-04' and dataReferencia <= '2023-12-18'

该SQL在MySQL Workbench中可正常返回结果，但Java代码运行时抛出以下异常：

java.sql.SQLException: Out of range value for column 'dataReferencia' : value 2023-12-08 00:00:00 Query: select * from document_manager where dataReferencia >= ? and dataReferencia <= ? Parameters: [2023-12-06, 2023-12-20]
    at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:363)
    at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:350)
    at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:211)
    at infoasset.mining.connection.DBInterface.getDocumentManager(DBInterface.java:188)
    at infoasset.mining.fnet.ProcessXML.processXMLs(ProcessXML.java:100)
    at infoasset.mining.entry.EntryPointMining.main(EntryPointMining.java:24)

数据库表document_manager的结构如下：

CREATE TABLE `document_manager` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `descriptionDocument` varchar(500) DEFAULT NULL,
  `dataReferencia` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2739 DEFAULT CHARSET=latin1

问题原因 #

数据库中dataReferencia字段为datetime类型（包含日期和时间），而Java代码使用java.sql.Date作为参数（仅包含日期部分）。JDBC驱动在绑定参数时会自动为java.sql.Date补全时间为00:00:00，但MariaDB对参数类型与字段类型的匹配要求严格，旧版本驱动或默认配置下容易出现类型映射异常，导致抛出“值超出范围”的错误。

正确解决方法 #

方法1：改用java.sql.Timestamp作为参数类型 #

java.sql.Timestamp对应数据库的datetime类型，包含完整的日期和时间信息，能完美匹配字段类型。修改后的代码如下：

public List<DocumentManagerDTO> getDocumentManager(java.sql.Timestamp fromDateInclusive, java.sql.Timestamp toDateInclusive) {
    String query = "select * from document_manager where dataReferencia >= ? and dataReferencia <= ?";
    
    QueryRunner run = new QueryRunner();
    ResultSetHandler<List<DocumentManagerDTO>> h = new BeanListHandler<>(DocumentManagerDTO.class);

    List<DocumentManagerDTO> docsToRet = null;
    try {
        docsToRet = run.query(conn, query, h, fromDateInclusive, toDateInclusive);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    return docsToRet;
}

如果仅需基于日期查询，可将LocalDate转换为Timestamp：

// 示例：将LocalDate转为当天0点的Timestamp
LocalDate fromLocal = LocalDate.of(2023, 12, 6);
Timestamp fromTs = Timestamp.valueOf(fromLocal.atStartOfDay());

方法2：在SQL中显式转换参数类型 #

若不想修改Java方法的参数类型，可在SQL语句中显式将java.sql.Date参数转换为datetime类型，或调整查询范围以覆盖当天所有时间：

-- 方法2.1：显式转换参数类型
select * from document_manager 
where dataReferencia >= CAST(? AS DATETIME) 
and dataReferencia <= CAST(? AS DATETIME)

-- 方法2.2：调整结束时间为当天23:59:59，确保覆盖所有datetime记录
select * from document_manager 
where dataReferencia >= ? 
and dataReferencia <= DATE_ADD(?, INTERVAL 1 DAY - 1 SECOND)

方法3：优化JDBC驱动配置 #

• 确保使用最新版本的MariaDB JDBC驱动，避免旧版本的类型映射bug。
• 在数据库连接URL中添加以下参数，启用标准日期时间处理逻辑：

jdbc:mariadb://localhost:3306/your_database?useLegacyDatetimeCode=false&serverTimezone=UTC

useLegacyDatetimeCode=false会禁用旧的日期时间处理逻辑，采用JDBC 4.2标准的类型映射规则，减少类型不匹配的问题。

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