Search code examples
javasql-serverhibernateormhibernate.cfg.xml

how to configure hibernate config file for sql server

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>
Solution

  • 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