如何在JPA原生查询中设置dd/MM/YYYY格式的日期参数

直接传dd/MM/yyyy格式的字符串给日期类型的查询字段，会触发两类问题：

• 数据库无法做隐式类型转换时，直接报类型不匹配错误
• 就算数据库能隐式转，默认转换规则不一定匹配dd/MM/yyyy格式，且字符串按字典序比较的结果和日期先后逻辑完全不一致，会返回错误的查询结果

方案1：Java侧解析为日期类型后绑定参数（推荐，跨数据库兼容） #

不要直接传字符串，先把日期字符串按固定格式解析为JDBC支持的日期类型，再通过setParameter传入，JPA会自动完成类型适配。
JDBC 4.2及以上版本（Java 8+，绝大多数主流环境都支持）直接用java.time包的日期类型即可，示例代码：

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import jakarta.persistence.Query;
// 旧版本JPA包路径为javax.persistence.Query

// 严格匹配传入字符串的日期格式
DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("dd/MM/yyyy");
LocalDate start = LocalDate.parse("01/01/2021", dateFormatter);
LocalDate end = LocalDate.parse("01/05/2021", dateFormatter);

Query query = em.createNativeQuery("select * from 你的业务表 where 日期字段 between ? and ?");
// 注意：JPA原生查询的参数占位符索引从1开始
query.setParameter(1, start);
query.setParameter(2, end);

// 执行查询获取结果
List resultList = query.getResultList();

如果是老版本运行环境不支持LocalDate传参，可以转成java.sql.Date类型：

import java.text.SimpleDateFormat;
import java.sql.Date;

SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
Date start = new Date(sdf.parse("01/01/2021").getTime());
Date end = new Date(sdf.parse("01/05/2021").getTime());
// 后续setParameter传参逻辑和上面一致

方案2：SQL侧调用数据库函数转换字符串 #

如果不想在Java侧做日期解析，可以在原生SQL里调用对应数据库的字符串转日期函数，这种方式可以直接传原字符串参数，注意不同数据库的函数写法有差异：

• MySQL、MariaDB：

  SELECT * FROM 你的业务表 WHERE 日期字段 BETWEEN STR_TO_DATE(?, '%d/%m/%Y') AND STR_TO_DATE(?, '%d/%m/%Y')
  

• Oracle、PostgreSQL：

  SELECT * FROM 你的业务表 WHERE 日期字段 BETWEEN TO_DATE(?, 'dd/mm/yyyy') AND TO_DATE(?, 'dd/mm/yyyy')
  

• 不要依赖数据库的隐式类型转换，隐式转换依赖数据库的默认日期格式配置，换部署环境、改配置都可能导致查询报错或结果错误
• 写日期格式模板时注意区分字段含义：MM代表月份、mm代表分钟，写反会导致日期解析结果完全错误
• 绑定参数时注意索引从1开始，不要从0计数，否则会报参数索引越界错误

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