java - Hibernate抛出转换数据类型异常

Question

我正在将所有 sql 查询转换为使用 Hibernate 参数。现在，当我在查询中添加参数并将其映射到变量时，它总是给出错误:

ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Error converting data type nvarchar to bigint.

这没有任何意义，因为我添加了一个字符串参数。

查询如下:

Query query = session.createSQLQuery("SELECT Sum([qtyrequired]) as pcs, o.materialrequireddate, itemid  "
                + "FROM   [materialManagement].[pcsorderrequirement] r  JOIN materialmanagement.pcsorders o  "
                + "ON o.id = r.pcsorderid  WHERE  itemid IN ( :items )   "
                + "AND Cast(o.materialrequireddate AS DATE) >= Cast(Getdate() AS DATE)  "
                + "AND o.materialrequireddate <= Cast( :endDate AS DATE)  "
                + "GROUP  BY o.materialrequireddate, itemid")
                .setString("items", items).setString("endDate", endDate.format(DateTimeFormatter.ISO_LOCAL_DATE));

当我返回到原来的工作查询时，它看起来像:

Query query = session.createSQLQuery("SELECT Sum([qtyrequired]) as pcs, o.materialrequireddate, itemid  "
                + "FROM   [materialManagement].[pcsorderrequirement] r  " + "JOIN materialmanagement.pcsorders o  "
                + "ON o.id = r.pcsorderid  " + "WHERE  itemid IN (" + items + ")   "
                + "AND Cast(o.materialrequireddate AS DATE) >= Cast(Getdate() AS DATE)  "
                + "AND o.materialrequireddate <= Cast('" + endDate + "' AS DATE)  "
                + "GROUP  BY o.materialrequireddate, itemid");

当我使用命名参数时，什么会导致此异常？我使用 Hibernate 5.1 并使用 MS SQL 服务器作为数据库。

Answer 1

我将查询更改为不转换为日期，并使用正确的类型设置参数:

Query query = session.createSQLQuery("SELECT Sum([qtyrequired]) as pcs, o.materialrequireddate, itemid  "
                + "FROM   [materialManagement].[pcsorderrequirement] r  JOIN materialmanagement.pcsorders o "
                + "ON o.id = r.pcsorderid  WHERE  itemid IN ( :items ) "
                + "AND Cast(o.materialrequireddate AS DATE) >= Cast(Getdate() AS DATE)  "
                + "AND o.materialrequireddate <= :endDate "
                + "GROUP  BY o.materialrequireddate, itemid").setParameterList("items", items.stream().map(Item::getId).collect(Collectors.toList()))
                .setDate("endDate", Date.from(endDate.atStartOfDay(ZoneId.systemDefault()).toInstant()));