Wednesday, January 12, 2011

How to : test hibernate data access with SQL Server 2008

there are some prerequisites to test the hibernate data access with SQL server.

I will provide some snippets as following,

Create a java project, add the jars to the builder path. 101 with Hibernate, first create one POJO ( a basic entity bean with “attributes”), I will create a basic Class called TODO with two attributes, ID and Title.

package Domain;

public class TODO {

    private int ID;

    private String Title;

    public void setID(int iD) {
        ID = iD;
    }

    public int getID() {
        return ID;
    }

    public void setTitle(String title) {
        Title = title;
    }

    public String getTitle() {
        return Title;
    }
}

Then Add one XML mapping for this Class. (the file will used by hibernate to interpret the field-column mapping, ID generation rules.) , always using the naming rules as [ENtity].HBM.XML which is optional.
for the entitye TODO, will be mapped to a table called TODOs, the ID generation rule is implemented by SQL server( identity column)

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="Domain">

    <class table="TODOs" name="TODO">
        <id name="ID" column="ID">
            <generator class="native">
            </generator>
        </id>

        <property name="Title"></property>

    </class>
</hibernate-mapping>

 
then create one file HIbernate.cfg.xml ( the driver used to communicate with sql server. )

<?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>        <!-- Database connection settings -->
        <property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
        <property name="connection.url">jdbc:sqlserver://localhost;databaseName=test;integratedSecurity=true; </property>
        <property name="connection.username">not required</property>
        <property name="connection.password"></property>        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.SQLServer2008Dialect</property>        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>        <!-- Disable the second-level cache -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create-drop</property>
        <mapping resource="test/TODO.hbm.xml" />
    </session-factory>
</hibernate-configuration>

Then, read /write to and from DB using the hibernate API.

package test;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class Main {

    /**
     * @param args
     * @throws ClassNotFoundException
     */
    public static void main(String[] args) {

        SessionFactory factory = new Configuration().configure(
                "test/hibernate.cfg.xml").buildSessionFactory();
        Session session = factory.getCurrentSession();
        Transaction trans = session.beginTransaction();

        Domain.TODO newobj = new Domain.TODO();
        newobj.setTitle("FirstTODO");
        session.save(newobj);

        Domain.TODO newobj2 = new Domain.TODO();
        newobj2.setTitle("SecondTODO");
        session.save(newobj2);
        // list all objects

        java.util.List lists = session.createQuery("from TODO").list();
        System.out.println(lists.size());
        for (int i = 0; i < lists.size(); i++) {
            System.out.println(((Domain.TODO) lists.get(i)).getTitle());
        }

        trans.commit();

    }

}

When you run the program, it will create two records and save it to DB. here is the sql used captured by SQL profiler.

image

If you get the error when you run the app,

Jan 12, 2011 2:19:19 PM com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>
WARNING: Failed to load the sqljdbc_auth.dll cause :- no sqljdbc_auth in java.library.path

that means the JDBC driver need loaded some DLL in order to work with SQL server. just copy this dll [located in the driver auth folder] to the class path folder. or just c:\windows folder.
this only applies if we use the Windows authentication against sql server instead of sql authentication.

<property name="connection.url">jdbc:sqlserver://localhost;databaseName=test;integratedSecurity=false; </property>





 

2 comments:

Timon Ernst said...

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)

feb 20, 2013 3:24:42 AM org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: HHH000230: Schema export complete
feb 20, 2013 3:24:57 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: 08S01
feb 20, 2013 3:24:57 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
Exception in thread "main" org.hibernate.exception.JDBCConnectionException: Could not open connection
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:131)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)



Any idea as to why i can get no connection going ?
MsSql Server 2008 server is running with tcp/ip enabled on client en server protocols

Harshad Nasit said...

Please enable TCP port from
MSSQL Configuration tools

 
Locations of visitors to this page