how to configure hibernate config file for sql server

Question

Here is the config file for MySQL:

<hibernate-configuration>
  <session-factory>
    <property name="hibernate.connection.driver_class">org.gjt.mm.mysql.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost/test</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">zgy01</property>
    <property name="hibernate.connection.pool_size">100</property>
    <property name="show_sql">false</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

    <!-- Mapping files -->
    <mapping resource="model.hbm.xml"/>

  </session-factory>
</hibernate-configuration>

What to specify for SQL Server 2005? I did it like this:

<hibernate-configuration>
  <session-factory>
    <property name="hibernate.connection.driver_class">org.gjt.mm.mysql.Driver</property>
    <property name="hibernate.connection.url">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
    <property name="hibernate.connection.username">sa</property>
    <property name="hibernate.connection.password">lal</property>
    <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>

    <property name="hibernate.connection.pool_size">100</property>        
    <property name="show_sql">false</property>

    <!-- Mapping files -->
    <mapping resource="model.hbm.xml"/>

  </session-factory>
</hibernate-configuration>

My question more precisely is how to specify the database that I have to connect to?

In MySQL I used to do like this:

<property name="hibernate.connection.url">jdbc:mysql://localhost/test</property> 

Answer 1

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

Answer 2

Keep the jar files under web-inf lib incase you included jar and it is not able to identify .

It worked in my case where everything was ok but it was not able to load the driver class.

Answer 3

Finally this is for Hibernate 5 in Tomcat.

Compiled all the answers from the above and added my tips which works like a charm for Hibernate 5 and SQL Server 2014.

<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">
   org.hibernate.dialect.SQLServerDialect
</property>
<property name="hibernate.connection.driver_class">
   com.microsoft.sqlserver.jdbc.SQLServerDriver
</property>
<property name="hibernate.connection.url">  
jdbc:sqlserver://localhost\ServerInstanceOrServerName:1433;databaseName=DATABASE_NAME 
</property>
<property name="hibernate.default_schema">theSchemaNameUsuallydbo</property>
<property name="hibernate.connection.username">
   YourUsername
</property>
<property name="hibernate.connection.password">
   YourPasswordForMSSQL
</property>

Answer 4

The connection URL should look like this for SQL Server:

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

Examples:

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

Answer 5

We also need to mention default schema for SQSERVER: dbo

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

Tested with hibernate 4

Answer 6

Don't forget to enable tcp/ip connections in SQL SERVER Configuration tools