CODEWITHSUNDEEP
javasql-serverhibernate
JimmyD
JimmyD

Reputation: 2759

Hibernate throws converting data type exception

I'm converting all our sql queries to use Hibernate parameters. Now when I add a parameter in my query and map it with an variable it always give me the error:

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

It doesn't make any sense because I added a string parameter.

The query looks like:

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));

When I return to my original working query it looks like:

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");

What can create this exception when I use named parameters? I'm using Hibernate 5.1 and for the database MS SQL server.

Upvotes: 0

Views: 683

Answers (1)

JimmyD
JimmyD

Reputation: 2759

I changed my query to not cast to a date and sets the parameters with a correct type:

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()));

Upvotes: 1

Related Questions