How to set SQL Server options (ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS) using Hibernate

Hibernate ORM
1

Hello guys,

We are running our application over JBoss application server and the Hibernate ORM handles the connection to an MSSQL database.

As of now, we are running into a lot of performance issues and deadlocks and the DBA is pushing things into Hibernate. Namely, he stated that some flags should be turned ON/OFF.

So, my question is, do we have some way to specify on Hibernate configs those queries statuses?

  • ANSI_NULLS,
  • ANSI_PADDING,
  • ANSI_WARNINGS,
  • ARITHABORT,
  • CONCAT_NULL_YIELDS_NULL,
  • NUMERIC_ROUNDABORT,
  • QUOTED_IDENTIFIER

Can these flags be set on some properties file ?

2

Check out these performance tuning tips if you want to get the most out of Hibernate.

Namely, he stated that some flags should be turned ON/OFF.

That has nothing to do with Hibernate, but how you configure the SQL Server connection. You would have bumped into the same issue with plain JDBC.

So, my question is, do we have some way to specify on Hibernate configs those queries statuses?

You can set them via the JDBC connection URL InitializationString parameter:

 jdbc:sqlserver://localhost;instance=SQLEXPRESS;DatabaseName=my_db;InitializationString=(set ANSI_NULLS off;set QUOTED_IDENTIFIER on);
1 Like
3

Thank you vlad, it was exactly something like this I was searching for.