We use a SQL Server Database. When attempting to run a query on a table with 100M records, using 27GB of memory, Hibernate will translate the query, whether it's a findById or a findByFieldName into something like this:

select sbtinvento0_.cust_nbr as cust_nbr1_10_0_, sbtinvento0_.ean_upc as ean_upc2_10_0_, sbtinvento0_.rep_bal_qty as rep_bal_3_10_0_ from odh_inv.sbt_inven sbtinvento0_ where sbtinvento0_.cust_nbr=? and sbtinvento0_.ean_upc=? 

What happens now is that this query lasts about 7 seconds. Both fields in the database are of type string.

We were able to reproduce this behavior in DBeaver:

SELECT CUST_NBR, EAN_UPC, REP_BAL_QTY FROM ODH_INV.SBT_INVEN WHERE CUST_NBR = 0000100974 AND EAN_UPC = '0042823091698'; 

This query will also last 7 seconds. But, if the query is done in the following way, it only takes 0.7 seconds:

SELECT CUST_NBR, EAN_UPC, REP_BAL_QTY FROM CommonSQLDevPrj.ODH_INV.SBT_INVEN WHERE CUST_NBR = '0000100974' AND EAN_UPC = '0042823091698'; 

This behavior is repeated across multiple applications/environments/databases, on tables with millions of lines, on queries automatically created by Hibernate when using findById or findByFieldName with String parameters.

I tried googling this issue but I couldn't manage to find anything helpful. Is there any property that can be set in the application.yml file to force apostrophes to be used on String parameters?

I tried the following method:

@Query("SELECT si FROM SbtInventory si WHERE sbtInventoryId.customerNumber = ':customerNumber' and sbtInventoryId.eanUpc = ':eanUpc'") Optional<SbtInventory> findByCustomerNumberAndEanUpc(String customerNumber, String eanUpc); 

But the query runs as is, and it doesn't substitute the query parameters with the values from the function parameters. I tried using ?1 and ?2, or escaping the apostrophe, none of them worked.

I also tried some workarounds using Entity Manager and Session.

First workaround, using Entity Manager, does work, in under 1 second. The issue is that I have to add the parameters manually in the query. In this case it's not an issue, as this is a job and we have a guarantee that there won't be any SQL Injection here, but this method is not recommended for this exact reason, and we won't be able to use it on APIs.

 private final EntityManager em; public Optional<SbtInventory> get(String customerNumber, String eanUpc) { @SuppressWarnings("unchecked") List<SbtInventory> sbtInventory = em .createQuery("SELECT si FROM SbtInventory si WHERE sbtInventoryId.customerNumber = '" + customerNumber + "' and sbtInventoryId.eanUpc = '" + eanUpc + "'") .getResultList(); if (sbtInventory.size() == 0) { return Optional.empty(); } else { return Optional.of(sbtInventory.get(0)); } } 

The second method that is recommended instead, using Session, also works, but the query takes again 7 seconds to run:

 private final EntityManager em; @Transactional public Optional<SbtInventory> get(String customerNumber, String eanUpc) { Session session = null; if (em == null || (session = em.unwrap(Session.class)) == null) { throw new NullPointerException(); } @SuppressWarnings("unchecked") List<SbtInventory> sbtInventory = session.createQuery( "SELECT si FROM SbtInventory si WHERE sbtInventoryId.customerNumber = :customerNumber and sbtInventoryId.eanUpc = :eanUpc") .setParameter("customerNumber", customerNumber, StandardBasicTypes.STRING) .setParameter("eanUpc", eanUpc, StandardBasicTypes.STRING).getResultList(); if (sbtInventory.size() == 0) { return Optional.empty(); } else { return Optional.of(sbtInventory.get(0)); } } 

But all of this is undesired, as it adds a lot of extra code and extra handling on our queries, so I'm still hoping there's some hibernate property we can set so it adds apostrophes by itself.

EDIT: I tried Jens suggestion with cast:

SELECT CUST_NBR, EAN_UPC, REP_BAL_QTY FROM ODH_INV.SBT_INVEN WHERE CUST_NBR = CAST(0000100974, varchar) AND EAN_UPC = '0042823091698'; 

This query runs fast, but brings back no result. I thought it might be because of removing leading zeroes, so I tried:

SELECT CUST_NBR, EAN_UPC, REP_BAL_QTY FROM ODH_INV.SBT_INVEN WHERE CUST_NBR = RIGHT(CONCAT('0000000000', CAST(0000100974, varchar)), 10) AND EAN_UPC = '0042823091698'; 

This is finally working, but it would imply to manually write the query, which gets me to @Query in a Repository instead of using entity manager, which is an upgrade ( can't test it till Monday if it's actually working from Hibernate ), but still implies writing every sensitive query like this, instead of simply using queries formed by function name :(

EDIT 2: I'm going to reply to some of gadget's questions here

Any customization in the hibernate mapping of customerNumber in your SbtInventory entity? Could you please post the mapping?

@Getter @Setter @AllArgsConstructor @NoArgsConstructor @ToString @Entity @Builder @Table(name = "SBT_INVEN", schema = "ODH_INV") public class SbtInventory { @EmbeddedId private SbtInventoryIdentity sbtInventoryId; @Column(name = "REP_BAL_QTY") private Integer reportedBalanceQuantity; } @Getter @Setter @AllArgsConstructor @NoArgsConstructor @ToString @EqualsAndHashCode(callSuper = false) @Embeddable public class SbtInventoryIdentity implements Serializable { /** * */ private static final long serialVersionUID = -892835625356278964L; @Column(name = "CUST_NBR") private String customerNumber; @Column(name = "EAN_UPC") private String eanUpc; } 

Also what dialect are you using for hibernate?

Full config properties for the database connection:

 datasource: url: jdbc:sqlserver://db-url:db-port;databaseName=db-name username: user password: pass driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver hikari: data-source-properties: cachePrepStmts: true prepStmtCacheSize: 250 prepStmtCacheSqlLimit: 2048 useServerPrepStmts: false maximum-pool-size: 50 jpa: database-platform: org.hibernate.dialect.SQLServer2008Dialect database: SQL_SERVER show-sql: true hibernate: ddl-auto: none properties: hibernate.dialect: org.hibernate.dialect.SQLServer2008Dialect hibernate.jdbc.batch_size: 20 hibernate.cache.use_second_level_cache: false hibernate.enable_lazy_load_no_trans: true 

Any particular reason for using char instead of varchar in the db?

That's a question I don't have the exact answer to. This is how the database was designed. Customer number is a foreign key to another table, where the primary key was created as Char. These are some old tables and in order to keep that standard, they keep going with char for legacy fields.