how to configure hibernate config file for sql server

Properties that are database specific are:

• hibernate.connection.driver_class: JDBC driver class
• hibernate.connection.url: JDBC URL
• hibernate.connection.username: database user
• hibernate.connection.password: database password
• hibernate.dialect: The class name of a Hibernate org.hibernate.dialect.Dialect which allows Hibernate to generate SQL optimized for a particular relational database.

To change the database, you must:

1. Provide an appropriate JDBC driver for the database on the class path,
2. Change the JDBC properties (driver, url, user, password)
3. Change the Dialect used by Hibernate to talk to the database

There are two drivers to connect to SQL Server; the open source jTDS and the Microsoft one. The driver class and the JDBC URL depend on which one you use.

With the jTDS driver

The driver class name is net.sourceforge.jtds.jdbc.Driver.

The URL format for sqlserver is:

 jdbc:jtds:sqlserver://<server>[:<port>][/<database>][;<property>=<value>[;...]]

So the Hibernate configuration would look like (note that you can skip the hibernate. prefix in the properties):

<hibernate-configuration>  <session-factory>    <property name="connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>    <property name="connection.url">jdbc:jtds:sqlserver://<server>[:<port>][/<database>]</property>    <property name="connection.username">sa</property>    <property name="connection.password">lal</property>    <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>    ...  </session-factory></hibernate-configuration>

With Microsoft SQL Server JDBC 3.0:

The driver class name is com.microsoft.sqlserver.jdbc.SQLServerDriver.

The URL format is:

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

So the Hibernate configuration would look like:

<hibernate-configuration>  <session-factory>    <property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>    <property name="connection.url">jdbc:sqlserver://[serverName[\instanceName][:portNumber]];databaseName=<databaseName></property>    <property name="connection.username">sa</property>    <property name="connection.password">lal</property>    <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>    ...  </session-factory></hibernate-configuration>

References

• Hibernate Core Reference Documentation
  • 3.3. JDBC connections
  • 3.4. Optional configuration properties
• jTDS Documentation
• Microsoft SQL Server JDBC Driver 3.0 Documentation
• Microsoft SQL Server JDBC Driver 2.0
• Support Matrix for Microsoft SQL Server JDBC Driver

The connection URL should look like this for SQL Server:

jdbc:sqlserver://serverName[\instanceName][:port][;databaseName=your_db_name]

Examples:

jdbc:sqlserver://localhostjdbc:sqlserver://127.0.0.1\INGESQL:1433;databaseName=datatest...

We also need to mention default schema for SQSERVER: dbo

<property name="hibernate.default_schema">dbo</property>

Tested with hibernate 4