带有字符串参数的Hibernate查询不使用撇号

Question

我们使用SQL Server数据库。当试图使用27GB内存在具有100M条记录的表上运行查询时，Hibernate会将查询（无论是findById还是findByFieldName转换为以下内容：

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=?

现在的情况是，该查询持续约7秒。数据库中的两个字段都是字符串类型。

我们能够在DBeaver中重现这种行为：

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

此查询也将持续7秒。但是，如果按以下方式进行查询，只需0.7秒：

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

此行为在多个应用程序/环境/数据库、具有数百万行的表、Hibernate在使用带有String参数的findById或findByFieldName时自动创建的查询上重复。

我试着在谷歌上搜索这个问题，但找不到任何有用的东西。是否可以在应用程序中设置任何属性。yml文件强制在字符串参数上使用撇号？

我尝试了以下方法：

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

但是查询按原样运行，它不会用函数参数中的值替换查询参数。我尝试使用？1和？2，或者转义撇号，都不起作用。

我还使用实体管理器和会话尝试了一些变通方法。

第一种解决方法，使用实体管理器，在不到1秒的时间内完成。问题是我必须在查询中手动添加参数。在这种情况下，这不是问题，因为这是一个作业，我们保证这里不会有任何SQL注入，但是由于这个确切的原因，不推荐这种方法，我们将无法在API上使用它。

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

建议改用的第二种方法，使用会话，也可以工作，但查询再次需要7秒才能运行：

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

但所有这些都是不希望的，因为它在我们的查询中添加了很多额外的代码和额外的处理，所以我仍然希望我们可以设置一些hibernate属性，以便它自己添加撇号。

编辑：我用演员尝试了延斯的建议：

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

此查询运行速度很快，但没有返回任何结果。我认为这可能是因为删除了前导零，所以我尝试了：

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

这终于起作用了，但这意味着要手动编写查询，这使我可以在存储库中使用@query，而不是使用实体管理器，实体管理器是一种升级（如果它实际上在Hibernate中工作，则在周一之前无法测试），但仍然意味着要像这样编写每个敏感查询，而不是简单地使用由函数名组成的查询：(

编辑2：我将在这里回答一些小工具的问题

在您的SbtInventory实体中的CusterNumber的Hibernate映射中有任何自定义吗？您能发布映射吗？

@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;
}

你在冬眠时用什么方言？

数据库连接的完整配置属性：

  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

在数据库中使用char而不是varchar有什么特殊原因吗？

这是一个我没有确切答案的问题。这就是数据库的设计方式。客户编号是另一个表的外键，其中主键创建为Char。这些是一些旧表，为了保持这一标准，它们继续使用char作为遗留字段。

编辑3：

    @Query(value = "SELECT REP_BAL_QTY\r\n" + "FROM ODH_INV.SBT_INVEN\r\n"
            + "WHERE CUST_NBR = RIGHT(CONCAT('0000000000', CAST(:customerNumber AS varchar)), 10) "
            + "AND EAN_UPC = RIGHT(CONCAT('0000000000000', CAST(:eanUpc AS varchar)), 13)", nativeQuery = true)
    Optional<Integer> findReportedBalanceQuantityByCustomerNumberAndEanUpc(String customerNumber, String eanUpc);

因此，这在存储库中运行良好，但在本例中，我公开的是一个API，我只需要结果中的REP\u BAL\u QTY列，所以我必须对返回的页面的每一行运行此查询。在我将其映射到实体之前：

    @MapsId
    @OneToOne
    @JoinColumns({
        @JoinColumn(name = "EAN_UPC", referencedColumnName = "EAN_UPC"),
        @JoinColumn(name = "CUST_NBR", referencedColumnName = "CUST_NBR")
    })
    @NotFound(action = NotFoundAction.IGNORE)
    private SbtInventory sbtInventory;

这是在加载实体时调用的，进程有点快，我认为（除了显而易见的，避免样板代码）使用实体中的连接，进程带来10行的页面持续时间：
SbtInventoryController.get数据花费了84113 ms
这是连接的执行查询之一：

select sbtinvento0_.CUST_NBR as CUST_NBR1_19_0_, sbtinvento0_.EAN_UPC as EAN_UPC2_19_0_, sbtinvento0_.REP_BAL_QTY as REP_BAL_3_19_0_ from ODH_INV.SBT_INVEN sbtinvento0_ where sbtinvento0_.CUST_NBR=? and sbtinvento0_.EAN_UPC=?

使用查询，该过程持续：
SbtInventoryController。getData耗时14063毫秒，因此性能大幅提升。缺点是，通过使用查询，我必须删除包含REP\u BAL\u QTY的列上的排序：(




Answer 1

一个月后，我们终于找到了问题的原因。

在与DBA交谈后，我们收到了查询的评估计划，其中我们注意到其中一些查询在谓词中具有CONVERT_隐式函数调用：

CONVERT_IMPLICIT(nchar(18),[dbo].[PRICING].[MATL_NBR] as [pricing0_].[MATL_NBR],0)=[@P1]

在这个查询中，CONVERT_隐式调用导致它在150ms内运行，并且没有利用应用于表的索引。

搜索这个CONVERT_隐式函数时，我发现这篇文章提到了我推导的内容，查询将使用更多的资源，并且不会选择适当的索引：

https://blog.sqlauthority.com/2018/06/11/sql-server-how-to-fix-convert_implicit-warnings/

在搜索如何禁用此行为时，我发现了几个月前由Vlad Mihalcea撰写的以下文章，我想为解决方案提供一些支持：

https://vladmihalcea.com/sql-server-jdbc-sendstringparametersasunicode/

这篇文章指定在JDBC驱动程序上设置一个默认属性sendStringParametersAsUnicode，应该始终禁用它，因为它会极大地影响查询的性能。这篇文章还提到微软也提出了同样的建议：

https://docs.microsoft.com/en-us/sql/connect/jdbc/improving-performance-and-reliability-with-the-jdbc-driver?view=sql-服务器-ver15

When working with CHAR, VARCHAR, and LONGVARCHAR data, users can set the connection property sendStringParametersAsUnicode to false for optimal performance gain.

因此，解决方案非常简单。只需附加sendStringParametersAsUnicode=false；到数据库连接字符串。您应该始终将此属性设置为false，以防止查询出现性能问题。

dbc:sqlserver://localhost;instance=SQLEXPRESS;databaseName=high_performance_sql;sendStringParametersAsUnicode=false;