Hibernate does not return any result

[umar ali karimi]

[color=blue]I've been trying to get a list of values from database using hibernate.
I'm using MS SQL Server XE 2005 and using Hibernate 3.2.2 to fetch DB rows. I've tried JDBC drivers provided by Microsoft as well as jTDS but all I get is an empty list.

Here is my hibernate configuration file.

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
 
 "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
 
<hibernate-configuration>
 
    <session-factory>
        <property name="hibernate.connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:jtds:sqlserver://localhost:1433;databseName=SQLTutorial;integratedSecurity=true;</property>
        <!--property name="hibernate.connection.username"></property>
        <property name="hibernate.connection.password"></property-->
        <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
        <property name="show_sql">true</property>
        <property name="transaction.factory_class">
            org.hibernate.transaction.JDBCTransactionFactory
        </property>
        <property name="hibernate.cache.provider_class">
            org.hibernate.cache.HashtableCacheProvider
        </property>
        <property name="current_session_context_class">thread</property>
        <property name="hibernate.hbm2ddl.auto">update</property>
 
        <mapping resource="hibernate\tutorials\Department.hbm.xml"/>
 
    </session-factory>
 
</hibernate-configuration>

Am I missing something here?

And my hbm mappings are

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping package="hibernate.tutorials">
    <class name="Department" table="Department">
        <id name="id" column="DEPT_ID" type="long">
            <generator class="native"/>            
        </id>
        <property name="departmentName" column="DEPT_NAME"/>
    </class>  
    <class name="Employee" table="EMPLOYEE">
        <id name="id" column="EMP_ID">
            <generator class="native"/>
        </id>
        <property name="firstName" column="FIRST_NAME"/>
        <property name="lastName" column="LAST_NAME"/>
        <!--one-to-one name="department" class="Department"/-->
    </class>
</hibernate-mapping>

The output that I always get is

[color=blue]Hibernate: select this_.EMP_ID as EMP1_1_0_, this_.FIRST_NAME as FIRST2_1_0_, this_.LAST_NAME as LAST3_1_0_ from EMPLOYEE this_[/color]

Also, I have successfully retrieved the result sets using plain JDBC

[Leandro Coutinho]

Is there no error? Are you using printStackTrace in the exceptions?

[umar ali karimi]

No there is no error.

But when I use MySQL, I can see expected results.

It seems there's a communication gap between Hibernate and SQL Server Express Edition(2005/2008).

I tried with both of them on Windows 7 x64 bit platform but in vain.

[Leandro Coutinho]

umar ali karimi wrote:No there is no error.

But when I use MySQL, I can see expected results.

It seems there's a communication gap between Hibernate and SQL Server Express Edition(2005/2008).

I tried with both of them on Windows 7 x64 bit platform but in vain.

So probably the error is in the hibernate.cfg.xml or in the driver.

Try this:
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>

[Paul Sturrock]

umar ali karimi wrote:
But when I use MySQL, I can see expected results.

Do you mean SQL Server?

Does the user you connect with have permission to select from Department?

[umar ali karimi]

hbm mappings for MS SQL server require additional properties which I just figured out with the help of Netbeans.

Thanks to Netbeans

The additional properties are schema and catalog

I modified the mappings as shown below and it gave me the expected results

   

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
        
<hibernate-mapping package="hibernate.tutorials">
    <class name="Department" table="Department" schema="dbo" catalog="SQLTutorial">
        <id name="id" column="DEPT_ID" type="long">
            <generator class="native"/>            
        </id>
        <property name="departmentName" column="DEPT_NAME"/>
    </class>  
    <class name="Employee" table="EMPLOYEE" schema="dbo" catalog="SQLTutorial">
        <id name="id" column="EMP_ID">
            <generator class="native"/>
        </id>
        <property name="firstName" column="FIRST_NAME"/>
        <property name="lastName" column="LAST_NAME"/>
        <many-to-one name="department">
        	<column name="DEPT_ID" />
        </many-to-one>
    </class>
</hibernate-mapping>

 

[umar ali karimi]

But there's still one question which is haunting me.

Why does not hibernate report any exception like "table or view" does not exist if it is not aware of the schema and catalog and simply returns an empty list.

Any thoughts?

[Paul Sturrock]

The additional properties are schema and catalog

Hibernate mappings for SQL Server do not require these. These are required because of the user you connect to your database as. If you use Windows Integrated authentication you do not connect to any particular schema so you need to tell SQL Server which one you are using. If you had conencted using SQL Server authentication you would not need these properties.

Why does not hibernate report any exception like "table or view" does not exist if it is not aware of the schema and catalog and simply returns an empty list. [

This is a detail of how SQL Server is implemented, its not Hibernate. Had it been Oracle and you connected with a user who could not see your department table you would get an error.