How to : test hibernate data access with SQL Server 2008

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

• Hibernate Lib. http://www.hibernate.org/downloads
• SQL Server JDBC Driver 3.0 http://msdn.microsoft.com/en-us/sqlserver/aa937724
• some XML configurations ( hibernate.cfg.xml, POJO.hbm.xml)

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.



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>