Native SQL queries to call database in hibernate

Click here to download eclipse supported ZIP file


We looked into Hibernate Query Language and Hibernate Criteria in recent posts, today we will look into Hibernate Native SQL query with examples.

Hibernate provide option to execute native SQL queries through the use of SQLQuery object. This is very handy when we want to execute database specific queries that are not supported by Hibernate API such as query hints or the CONNECT keyword in Oracle Database.

For normal scenarios, it is however not the recommended approach because we loose benefits related to hibernate association and hibernate first level caching.

I will use MySQL database and same tables and data setup as used in HQL example, so you should check out that first to understand the tables and corresponding model classes mapping.

You can use native SQL to express database queries if you want to utilize database-specific features such as query hints or the CONNECT keyword in Oracle. Hibernate 3.x allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.

Your application will create a native SQL query from the session with the createSQLQuery() method on the Session interface.:

public SQLQuery createSQLQuery(String sqlString) throws HibernateException

After you pass a string containing the SQL query to the createSQLQuery() method, you can associate the SQL result with either an existing Hibernate entity, a join, or a scalar result using addEntity(), addJoin(), and addScalar() methods respectively.

Named SQL queries:

The following is the syntax to get entity objects from a native sql query via addEntity() and using named SQL query.

String sql = "SELECT * FROM EMPLOYEE WHERE id = :employee_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(EmployeeBean.class);
query.setParameter("employee_id", 10);
List results = query.list();
Using a SQLQuery

Execution of native SQL queries is controlled via the SQLQuery interface, which is obtained by calling Session.createSQLQuery(). The following sections describe how to use this API for querying.

The most basic SQL query is to get a list of scalars (values).

sess.createSQLQuery("SELECT * FROM CATS").list();
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();

These will return a List of Object arrays (Object[]) with scalar values for each column in the CATS table. Hibernate will use ResultSetMetadata to deduce the actual order and types of the returned scalar values.

To avoid the overhead of using ResultSetMetadata, or simply to be more explicit in what is returned, one can use addScalar():

sess.createSQLQuery("SELECT * FROM CATS")
 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME", Hibernate.STRING)
 .addScalar("BIRTHDATE", Hibernate.DATE)

This query specified:

This will return Object arrays, but now it will not use ResultSetMetadata but will instead explicitly get the ID, NAME and BIRTHDATE column as respectively a Long, String and a Short from the underlying resultset. This also means that only these three columns will be returned, even though the query is using * and could return more than the three listed columns.

It is possible to leave out the type information for all or some of the scalars.

sess.createSQLQuery("SELECT * FROM CATS")
 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME")
 .addScalar("BIRTHDATE")

This is essentially the same query as before, but now ResultSetMetaData is used to determine the type of NAME and BIRTHDATE, where as the type of ID is explicitly specified.

How the java.sql.Types returned from ResultSetMetaData is mapped to Hibernate types is controlled by the Dialect. If a specific type is not mapped, or does not result in the expected type, it is possible to customize it via calls to registerHibernateType in the Dialect.

The above queries were all about returning scalar values, basically returning the "raw" values from the resultset. The following shows how to get entity objects from a native sql query via addEntity().

sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class);
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);

This query specified:

Assuming that Cat is mapped as a class with the columns ID, NAME and BIRTHDATE the above queries will both return a List where each element is a Cat entity.

If the entity is mapped with a many-to-one to another entity it is required to also return this when performing the native query, otherwise a database specific "column not found" error will occur. The additional columns will automatically be returned when using the * notation, but we prefer to be explicit as in the following example for a many-to-one to a Dog:

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class);

This will allow cat.getDog() to function properly.

It is possible to eagerly join in the Dog to avoid the possible extra roundtrip for initializing the proxy. This is done via the addJoin() method, which allows you to join in an association or collection.

sess.createSQLQuery("SELECT c.ID, NAME, BIRTHDATE, DOG_ID, D_ID, D_NAME FROM CATS c, DOGS d WHERE c.DOG_ID = d.D_ID")
 .addEntity("cat", Cat.class)
 .addJoin("cat.dog");

In this example, the returned Cat's will have their dog property fully initialized without any extra roundtrip to the database. Notice that you added an alias name ("cat") to be able to specify the target property path of the join. It is possible to do the same eager joining for collections, e.g. if the Cat had a one-to-many to Dog instead.

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, D_ID, D_NAME, CAT_ID FROM CATS c, DOGS d WHERE c.ID = d.CAT_ID")
 .addEntity("cat", Cat.class)
 .addJoin("cat.dogs");

At this stage you are reaching the limits of what is possible with native queries, without starting to enhance the sql queries to make them usable in Hibernate. Problems can arise when returning multiple entities of the same type or when the default alias/column names are not enough.

Until now, the result set column names are assumed to be the same as the column names specified in the mapping document. This can be problematic for SQL queries that join multiple tables, since the same column names can appear in more than one table.

Column alias injection is needed in the following query (which most likely will fail):

sess.createSQLQuery("SELECT c.*, m.*  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")
 .addEntity("cat", Cat.class)
 .addEntity("mother", Cat.class)

The query was intended to return two Cat instances per row: a cat and its mother. The query will, however, fail because there is a conflict of names; the instances are mapped to the same column names. Also, on some databases the returned column aliases will most likely be on the form "c.ID", "c.NAME", etc. which are not equal to the columns specified in the mappings ("ID" and "NAME").

The following form is not vulnerable to column name duplication:

sess.createSQLQuery("SELECT {cat.*}, {mother.*}  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")
 .addEntity("cat", Cat.class)
 .addEntity("mother", Cat.class)

This query specified:

The {cat.*} and {mother.*} notation used above is a shorthand for "all properties". Alternatively, you can list the columns explicitly, but even in this case Hibernate injects the SQL column aliases for each property. The placeholder for a column alias is just the property name qualified by the table alias. In the following example, you retrieve Cats and their mothers from a different table (cat_log) to the one declared in the mapping metadata. You can even use the property aliases in the where clause.

String sql = "SELECT ID as {c.id}, NAME as {c.name}, " + 
         "BIRTHDATE as {c.birthDate}, MOTHER_ID as {c.mother}, {mother.*} " +
         "FROM CAT_LOG c, CAT_LOG m WHERE {c.mother} = c.ID";

List loggedCats = sess.createSQLQuery(sql)
        .addEntity("cat", Cat.class)
        .addEntity("mother", Cat.class).list()

In most cases the above alias injection is needed. For queries relating to more complex mappings, like composite properties, inheritance discriminators, collections etc., you can use specific aliases that allow Hibernate to inject the proper aliases.

The following table shows the different ways you can use the alias injection. Please note that the alias names in the result are simply examples; each alias will have a unique and probably different name when used.


Here is the example code to test Native SQL functionality :



This is hibernate.cfg.xml file for connecting the postgreSQL database.


 
<?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">oracle.jdbc.driver.OracleDriver</property> 
		hibernate word is optional <property name="connection.url">jdbc:oracle:thin:@localhost:1521:ORCL</property> 
		<property name="hibernate.connection.username">kcv</property> <property name="hibernate.connection.password">kcv</property> 
		<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property> 
		<property name="show_sql">true</property> <mapping resource="Employee.hbm.xml"/> 
		</session-factory> -->
   <session-factory>
      <property name="hibernate.current_session_context_class">thread</property>
      <property name="hbm2ddl.auto">create</property>
      <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
      <property name="connection.url">jdbc:postgresql://localhost:5432/hibernate</property>
      <property name="connection.username">postgres</property>
      <property name="connection.password">password</property>
      <property name="connection.driver_class">org.postgresql.Driver</property>
      <property name="show_sql">true</property>
      <property name="format_sql">true</property>
      <property name="use_sql_comments">true</property>
      		<mapping resource="EmployeeBean.hbm.xml" />
   </session-factory>
</hibernate-configuration>

This is EmployeeBean.hbm.xml mapping file for map the Entity(POJO) class to columns.


 
<?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="com.cv.nativesql.model">
	<class name="EmployeeBean" table="EmployeeBean_Native_SQL">
		<id name="id" type="long">
			<column name="EID" precision="10" scale="0" />
			<!-- <generator class="assigned" /> -->
		</id>
		<property name="firstName" type="string">
			<column name="FIRSTNAME" length="29" />
		</property>
		<property name="lastName" type="string">
			<column name="LASTNAME" length="29" />
		</property>
		<property name="email" type="string">
			<column name="EMAIL" length="39" />
		</property>
	</class>

	<sql-query name="selectByEid">
		<return class="EmployeeBean" /> <![CDATA[ 
		select * from EmployeeBean_Native_SQL where eid>=:p1 and eid<=:p2]]>
	</sql-query>
	<sql-query name="selectByEmail">
		<return-scalar column="eid" type="int" />
		<return-scalar column="firstname" type="string" /> <![CDATA[ select eid,firstName 
		from EmployeeBean_Native_SQL where email like :p1]]>
	</sql-query>
	<sql-query name="deleteByMaxId">  
		<![CDATA[ delete from EmployeeBean_Native_SQL where eid=(select max(eid) 
		from EmployeeBean_Native_SQL)]]>
	</sql-query>

</hibernate-mapping>




This is EmployeeBean.java Entity(POJO) class having the fields needs to be configured in mapping file.



 

    
package com.cv.hibernate.nativesql.model;



/**

 * @author Chandra Vardhan

 */

public class EmployeeBean {

  private long id;

  private String firstName;

  private String lastName;

  private String email;



  public long getId() {

    return id;

  }



  public void setId(long id) {

    this.id = id;

  }



  public String getFirstName() {

    return firstName;

  }



  public void setFirstName(String firstName) {

    this.firstName = firstName;

  }



  public String getLastName() {

    return lastName;

  }



  public void setLastName(String lastName) {

    this.lastName = lastName;

  }



  public String getEmail() {

    return email;

  }



  public void setEmail(String email) {

    this.email = email;

  }



}


This is MyBean.java Entity(POJO) class having the fields needs to be configured in mapping file.



 

    
package com.cv.hibernate.nativesql.model;



public class MyBean {

  

  private long result;



  public long getResult() {

    return result;

  }



  public void setResult(long result) {

    this.result = result;

  }



}


This is NativeSQLDeleteByID.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import org.apache.log4j.Logger;

import org.hibernate.Query;

import org.hibernate.Session;



import com.cv.hibernate.nativesql.util.HibernateUtil;



public class NativeSQLDeleteByID {



  /**

   * @param args

   */

  private final static Logger logger = Logger.getLogger(NativeSQLDeleteByID.class);



  public static void main(String[] args) {



    HibernateUtil.saveOrUpdate();



    Session session = HibernateUtil.getSession();



    Query query = session.getNamedQuery("deleteByMaxId");



    int res = query.executeUpdate();



    if (res > 0) {



      logger.info("Return type of the class is : " + res);



    } else {

      logger.info("Record(s) not available for the specified data...");

    }



    HibernateUtil.closeSession();

  }



}


This is NativeSQLIterateNotSupport.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import java.util.Iterator;



import org.apache.log4j.Logger;

import org.hibernate.SQLQuery;

import org.hibernate.Session;

import org.hibernate.type.FloatType;

import org.hibernate.type.IntegerType;

import org.hibernate.type.LongType;

import org.hibernate.type.ShortType;



import com.cv.hibernate.nativesql.util.HibernateUtil;



/**

 * @author Chandra Vardhan

 */

public class NativeSQLIterateNotSupport {

  private final static Logger logger = Logger

      .getLogger(NativeSQLIterateNotSupport.class);



  public static void main(String args[]) {

    // Am creating the records by this statement.

    HibernateUtil.saveOrUpdate();

    Session session = HibernateUtil.getSession();

    try {

      SQLQuery q1 = session

          .createSQLQuery("select count(*) as cnt,max(eid) as max,min(eid) as min,avg(eid) as avg from EmployeeBean_Native_SQL where eid>=:p1 and eid<=:p2");



      q1.setInteger("p1", 101);

      q1.setInteger("p2", 110);



      q1.addScalar("cnt", IntegerType.INSTANCE);

      q1.addScalar("max", LongType.INSTANCE);

      q1.addScalar("min", ShortType.INSTANCE);

      q1.addScalar("avg", FloatType.INSTANCE);

      // Exception in thread "main"

      // java.lang.UnsupportedOperationException:

      // SQL queries do not currently support iteration

      // This operation is not supported by hibernate native SQL

      Iterator itr = q1.iterate();

      while (itr.hasNext()) {

        Object element = itr.next();

        System.out.print(element + " ");

      }

    } catch (UnsupportedOperationException uoe) {

      logger.info("-------------------------------ERROR-------------------------------");

      System.err

          .println("Hibernate Native SQL queries do not currently support iteration");

    } catch (Exception e) {

      System.err.println(e);

    }



    HibernateUtil.closeSession();

  }

}


This is NativeSQLQueryInsert.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import org.apache.log4j.Logger;

import org.hibernate.SQLQuery;

import org.hibernate.Session;



import com.cv.hibernate.nativesql.util.HibernateUtil;



public class NativeSQLQueryInsert {



  /**

   * @param args

   */

  private final static Logger logger = Logger.getLogger(NativeSQLQueryInsert.class);



  public static void main(String[] args) {



    Session session = HibernateUtil.getSession();



    SQLQuery query = session.createSQLQuery("insert into EmployeeBean_Native_SQL values(?,?,?,?)");

    int count = 0;

    for (int i = 110; i < 120; i++) {

      query.setInteger(0, i);

      query.setString(1, "chote");

      query.setString(2, "kodam");

      query.setString(3, "meetkodam@gmail.com");



      int res = query.executeUpdate();

      if (res > 0) {

        ++count;

      }

    }



    logger.info("No of records inserted" + count);

    HibernateUtil.closeSession();

  }



}


This is NativeSQLSelectByEid.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import java.util.List;



import org.apache.log4j.Logger;

import org.hibernate.Query;

import org.hibernate.Session;



import com.cv.hibernate.nativesql.model.EmployeeBean;

import com.cv.hibernate.nativesql.util.HibernateUtil;



public class NativeSQLSelectByEid {



  /**

   * @param args

   */

  private final static Logger logger = Logger

      .getLogger(NativeSQLSelectByEid.class);



  public static void main(String[] args) {

    // Am creating the records by this statement.

    HibernateUtil.saveOrUpdate();

    Session session = HibernateUtil.getSession();

    Query query = session.getNamedQuery("selectByEid");

    query.setInteger("p1", 100);

    query.setInteger("p2", 105);

    List<EmployeeBean> list = query.list();



    if (list != null && !list.isEmpty()) {

      logger.info("return type of the class : " + list.get(0).getClass());

      for (int i = 0; i < list.size(); ++i) {

        EmployeeBean eb =list.get(i);



        logger.info(eb.getId() + " " + eb.getFirstName() + " "

            + eb.getLastName() + " " + eb.getEmail());

      }



    } else {

      logger.info("No Records found for the specified input...");

    }

  }



}


This is NativeSQLSelectByEmail.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import java.util.List;



import org.apache.log4j.Logger;

import org.hibernate.Query;

import org.hibernate.Session;



import com.cv.hibernate.nativesql.util.HibernateUtil;



public class NativeSQLSelectByEmail {



  /**

   * @param args

   */

  private final static Logger logger = Logger

      .getLogger(NativeSQLSelectByEmail.class);



  public static void main(String[] args) {

    // Am creating the records by this statement.

    HibernateUtil.saveOrUpdate();

    Session session = HibernateUtil.getSession();

    Query q1 = session.getNamedQuery("selectByEmail");

    q1.setString("p1", "%gmail.com");

    q1.setFirstResult(1);

    q1.setMaxResults(2);// pagination concept working here....

    List list = q1.list();

    if (list != null && !list.isEmpty()) {

      logger.info("Return type of the class is : "

          + list.get(0).getClass());



      for (int i = 0; i < list.size(); ++i) {

        Object[] row = (Object[]) list.get(i);



        String result = null;

        for (int k = 0; k < row.length; k++) {

          if (result == null) {

            result = row[k].toString() + " ";

          } else {

            result += row[k].toString() + " ";

          }

        }

        logger.info(result);

      }

    } else {

      logger.info("No Records found for the specified input...");

    }

  }



}


This is NativeSQLSelectByIdANDEmail.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import java.util.List;



import org.apache.log4j.Logger;

import org.hibernate.SQLQuery;

import org.hibernate.Session;

import org.hibernate.type.IntegerType;

import org.hibernate.type.StringType;



import com.cv.hibernate.nativesql.util.HibernateUtil;



/**

 * @author Chandra Vardhan

 */

public class NativeSQLSelectByIdANDEmail {



  /**

   * @param args

   */

  private final static Logger logger = Logger

      .getLogger(NativeSQLSelectByIdANDEmail.class);



  public static void main(String[] args) {



    // Am creating the records by this statement.

    HibernateUtil.saveOrUpdate();

    Session session = HibernateUtil.getSession();



    SQLQuery q1 = session

        .createSQLQuery("select eid,firstname,email from EmployeeBean_Native_SQL where eid=? and email like ?");



    q1.setInteger(0, 102);

    q1.setString(1, "%gmail.com");



    // To ensure the type safety

    q1.addScalar("eid", IntegerType.INSTANCE);

    q1.addScalar("firstname", StringType.INSTANCE);

    q1.addScalar("email", StringType.INSTANCE);



    List list = q1.list();

    if (list != null && !list.isEmpty()) {

      logger.info("Return type of the class is : "

          + list.get(0).getClass());

      for (int i = 0; i < list.size(); ++i) {

        Object[] row = (Object[]) list.get(i);



        String result = null;

        for (int k = 0; k < row.length; k++) {

          if (result == null) {

            result = row[k].toString() + " ";

          } else {

            result += row[k].toString() + " ";

          }

        }

        logger.info(result);

      }

    } else {

      logger.info("No Records found for the specified input...");

    }

    HibernateUtil.closeSession();

  }



}


This is NativeSQLSelectCount.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import java.util.List;



import org.apache.log4j.Logger;

import org.hibernate.SQLQuery;

import org.hibernate.Session;

import org.hibernate.type.IntegerType;



import com.cv.hibernate.nativesql.util.HibernateUtil;



/**

 * @author Chandra Vardhan

 */

public class NativeSQLSelectCount {

  private final static Logger logger = Logger

      .getLogger(NativeSQLSelectCount.class);



  public static void main(String args[]) throws Exception {



    // Am creating the records by this statement.

    HibernateUtil.saveOrUpdate();

    Session session = HibernateUtil.getSession();



    SQLQuery q1 = session

        .createSQLQuery("select count(*) as cnt from EmployeeBean_Native_SQL where eid>=:p1 and eid<=:p2");



    q1.setInteger("p1", 103);

    q1.setInteger("p2", 110);



    q1.addScalar("cnt", IntegerType.INSTANCE);



    List list = q1.list();



    if (list != null && !list.isEmpty()) {

      logger.info("Return type of the class is : "

          + list.get(0).getClass());

      int cnt = ((Integer) list.get(0)).intValue();

      logger.info("count of records : " + cnt);

    } else {

      logger.info("No Records found for the specified input...");

    }

    HibernateUtil.closeSession();

  }

}


This is NativeSQLSelectCountMaxMinFind.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import java.util.List;



import org.apache.log4j.Logger;

import org.hibernate.SQLQuery;

import org.hibernate.Session;

import org.hibernate.type.FloatType;

import org.hibernate.type.IntegerType;

import org.hibernate.type.LongType;

import org.hibernate.type.ShortType;



import com.cv.hibernate.nativesql.util.HibernateUtil;



/**

 * @author Chandra Vardhan

 */

public class NativeSQLSelectCountMaxMinFind {



  /**

   * @param args

   */

  private final static Logger logger = Logger

      .getLogger(NativeSQLSelectCountMaxMinFind.class);



  public static void main(String[] args) {

    // Am creating the records by this statement.

    HibernateUtil.saveOrUpdate();

    Session session = HibernateUtil.getSession();



    SQLQuery q1 = session

        .createSQLQuery("select count(*) as cnt,max(eid) as max,min(eid) as min,avg(eid) as avg from EmployeeBean_Native_SQL where eid>=:p1 and eid<=:p2");



    q1.setInteger("p1", 100);

    q1.setInteger("p2", 110);



    q1.addScalar("cnt", IntegerType.INSTANCE);

    q1.addScalar("max", LongType.INSTANCE);

    q1.addScalar("min", ShortType.INSTANCE);

    q1.addScalar("avg", FloatType.INSTANCE);



    // Iterator it=q1.iterate(); //Not supported by SQL...



    List list = q1.list();

    if (list != null && !list.isEmpty()) {

      logger.info("return type of the class : " + list.get(0).getClass());

      Object[] obj = (Object[]) list.get(0);



      int cnt = ((Integer) obj[0]).intValue();

      long max = ((Long) obj[1]).longValue();

      short min = ((Short) obj[2]).shortValue();

      float avg = ((Float) obj[3]).floatValue();



      logger.info("cnt of records :" + cnt);

      logger.info("max of records :" + max);

      logger.info("min of records :" + min);

      logger.info("avg of records :" + avg);

    } else {

      logger.info("No Records found for the specified input...");

    }

    // This way is without adding the scalar



    List list2 = q1.list();

    if (list2 != null && !list2.isEmpty()) {

      logger.info("Return type of the class is : "

          + list2.get(0).getClass());



      for (int i = 0; i < list2.size(); ++i) {

        Object[] row = (Object[]) list2.get(i);



        String result = null;

        for (int k = 0; k < row.length; k++) {

          if (result == null) {

            result = row[k].toString() + " ";

          } else {

            result += row[k].toString() + " ";

          }

        }

        logger.info(result);

      }

    } else {

      logger.info("No Records found for the specified input...");

    }

    

    HibernateUtil.closeSession();

  }



}


This is NativeSQLSelectMaxFind.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import java.util.List;



import org.apache.log4j.Logger;

import org.hibernate.SQLQuery;

import org.hibernate.Session;

import org.hibernate.type.FloatType;

import org.hibernate.type.IntegerType;

import org.hibernate.type.LongType;

import org.hibernate.type.ShortType;



import com.cv.hibernate.nativesql.util.HibernateUtil;



/**

 * @author Chandra Vardhan

 */

public class NativeSQLSelectMaxFind {



  /**

   * @param args

   */

  private final static Logger logger = Logger.getLogger(NativeSQLSelectMaxFind.class);



  public static void main(String[] args) {



    Session ses = HibernateUtil.getSession();



    SQLQuery q1 = ses.createSQLQuery(

        "select count(*) as cnt,max(eid) as max,min(eid) as min,avg(eid) as avg from EmployeeBean_Native_SQL where eid>=:p1 and eid<=:p2");



    q1.setInteger("p1", 100);

    q1.setInteger("p2", 110);



    q1.addScalar("cnt", IntegerType.INSTANCE);

    q1.addScalar("max", LongType.INSTANCE);

    q1.addScalar("min", ShortType.INSTANCE);

    q1.addScalar("avg", FloatType.INSTANCE);



    // Iterator it=q1.iterate(); //Not supported by SQL...



    List l = q1.list();

    Object[] obj = (Object[]) l.get(0);



    int cnt = ((Integer) obj[0]).intValue();

    long max = ((Long) obj[1]).longValue();

    short min = ((Short) obj[2]).shortValue();

    float avg = ((Float) obj[3]).floatValue();



    logger.info("cnt of records :" + cnt);

    logger.info("max of records :" + max);

    logger.info("min of records :" + min);

    logger.info("avg of records :" + avg);



    // This way is without adding the scalar



    List l2 = q1.list();



    logger.info("return type of the class is : " + l2.get(0).getClass());



    for (int i = 0; i < l2.size(); ++i) {

      Object[] row2 = (Object[]) l2.get(i);

      for (int k = 0; k < row2.length; ++k) {

        System.out.print(row2[k].toString() + " ");

      }



    }



  }



}


This is NativeSQLSelectQuery.java main class having the application business logic.



 

    
package com.cv.hibernate.nativesql;



import java.util.List;



import org.apache.log4j.Logger;

import org.hibernate.SQLQuery;

import org.hibernate.Session;



import com.cv.hibernate.nativesql.model.EmployeeBean;

import com.cv.hibernate.nativesql.util.HibernateUtil;



/**

 * @author Chandra Vardhan

 */

public class NativeSQLSelectQuery {

  private final static Logger logger = Logger

      .getLogger(NativeSQLSelectQuery.class);



  public static void main(String args[]) throws Exception {

    // Am creating the records by this statement.

    HibernateUtil.saveOrUpdate();

    Session session = HibernateUtil.getSession();

    SQLQuery query = session

        .createSQLQuery("select * from EmployeeBean_Native_SQL");

    query.addEntity(EmployeeBean.class);

    List<EmployeeBean> list = query.list();

    if (list != null && !list.isEmpty()) {

      logger.info("return type of the class : " + list.get(0).getClass());

      for (int i = 0; i < list.size(); ++i) {

        EmployeeBean eb = list.get(i);

        logger.info(eb.getId() + " " + eb.getFirstName() + " "

            + eb.getLastName() + " " + eb.getEmail());



      }

    } else {

      logger.info("No Records found for the specified input...");

    }



    HibernateUtil.closeSession();

  }

}


This is HibernateUtil.java utility class for getting the database connection.



 

    
package com.cv.hibernate.nativesql.util;



import org.apache.log4j.Logger;

import org.hibernate.HibernateException;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.boot.registry.StandardServiceRegistryBuilder;

import org.hibernate.cfg.Configuration;



import com.cv.hibernate.nativesql.model.EmployeeBean;



/**

 * @author Chandra Vardhan

 */

/**

 * Configures and provides access to Hibernate sessions, tied to the current

 * thread of execution. Follows the Thread Local Session pattern, see

 * {@link http://hibernate.org/42.html }.

 */

public class HibernateUtil {

  private final static Logger logger = Logger.getLogger(HibernateUtil.class);



  /**

   * Location of hibernate.cfg.xml file. Location should be on the classpath

   * as Hibernate uses #resourceAsStream style lookup for its configuration

   * file. The default classpath location of the hibernate config file is in

   * the default package. Use #setConfigFile() to update the location of the

   * configuration file for the current session.

   */

  private static String CONFIG_FILE_LOCATION = "hibernate.cfg.xml";

  private static final ThreadLocal<Session> threadLocal = new ThreadLocal<Session>();

  private static Configuration configuration = new Configuration();

  private static SessionFactory sessionFactory;



  static {

    try {

      configuration = configuration.configure(CONFIG_FILE_LOCATION);

      StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()

          .applySettings(configuration.getProperties());

      sessionFactory = configuration.buildSessionFactory(builder.build());

      logger.info("%%%% Connection successful %%%%");

    } catch (Exception e) {

      logger.error("%%%% Error Creating SessionFactory %%%%");

      e.printStackTrace();

    }

  }



  private HibernateUtil() {

  }



  /**

   * Returns the ThreadLocal Session instance. Lazy initialize the

   * <code>SessionFactory</code> if needed.

   * 

   * @return Session

   * @throws HibernateException

   */

  public static Session getSession() throws HibernateException {

    Session session = (Session) threadLocal.get();



    if (session == null || !session.isOpen()) {

      if (sessionFactory == null) {

        rebuildSessionFactory();

      }

      session = (sessionFactory != null) ? sessionFactory.openSession()

          : null;

      threadLocal.set(session);

    }



    return session;

  }



  /**

   * Rebuild hibernate session factory

   * 

   */

  public static void rebuildSessionFactory() {

    try {

      configuration = configuration.configure(CONFIG_FILE_LOCATION);

      StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()

          .applySettings(configuration.getProperties());

      sessionFactory = configuration.buildSessionFactory(builder.build());

      logger.info("%%%% Connection successful %%%%");

    } catch (Exception e) {

      logger.error("%%%% Error Creating SessionFactory %%%%");

      e.printStackTrace();

    }

  }



  /**

   * Close the single hibernate session instance.

   * 

   * @throws HibernateException

   */

  public static void closeSession() throws HibernateException {

    Session session = (Session) threadLocal.get();

    threadLocal.set(null);



    if (session != null) {

      session.close();

      System.exit(0);

    }

  }



  /**

   * return session factory

   * 

   */

  public static SessionFactory getSessionFactory() {

    return sessionFactory;

  }



  /**

   * return hibernate configuration

   * 

   */

  public static Configuration getConfiguration() {

    return configuration;

  }



  public static void saveOrUpdate() {

    Session session = getSession();

    for (int i = 100; i <= 105; i++) {

      EmployeeBean eb = new EmployeeBean();

      eb.setId(new Long(i));

      eb.setFirstName("chandra");

      eb.setLastName("vardhan");

      eb.setEmail("meetkodam@gmail.com");

      session.beginTransaction();

      session.saveOrUpdate(eb);

      session.getTransaction().commit();

    }

    logger.info("Saved successful...");

    session.close();

  }

}


This is log4j.properties file having the entries for logging the information into the console/file.




#By default enabling Console appender
# Root logger option
log4j.rootLogger=INFO, stdout

# Redirect log messages to console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%-5p [%c]:%L -->> %m%n

# Redirect log messages to a log file
#log4j.appender.file=org.apache.log4j.RollingFileAppender
#log4j.appender.file.File=C:\\servlet-application.log
#log4j.appender.file.MaxFileSize=5MB
#log4j.appender.file.MaxBackupIndex=10
#log4j.appender.file.layout=org.apache.log4j.PatternLayout
#log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n


This is pom.xml file having the entries of dependency jars and information to build the application .



	
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.cv.natvesql</groupId>
	<artifactId>NativeSQL</artifactId>
	<version>1.0</version>

	<dependencies>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-entitymanager</artifactId>
			<version>4.3.5.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-c3p0</artifactId>
			<version>4.3.5.Final</version>
		</dependency>

		<dependency>
			<artifactId>hibernate-core</artifactId>
			<groupId>org.hibernate</groupId>
			<version>4.3.5.Final</version>
		</dependency>

		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-validator</artifactId>
			<version>4.2.0.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate.common</groupId>
			<artifactId>hibernate-commons-annotations</artifactId>
			<version>4.0.4.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate.javax.persistence</groupId>
			<artifactId>hibernate-jpa-2.0-api</artifactId>
			<version>1.0.1.Final</version>
		</dependency>

		<dependency>
			<groupId>javax.validation</groupId>
			<artifactId>validation-api</artifactId>
			<version>1.0.0.GA</version>
			provided
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.6.4</version>
		</dependency>
		<dependency>
			<groupId>org.jboss.logging</groupId>
			<artifactId>jboss-logging</artifactId>
			<version>3.1.0.CR2</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.6.4</version>
		</dependency>
		<dependency>
			<groupId>postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<version>9.1-901.jdbc4</version>
		</dependency>
		
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-simple</artifactId>
			<version>1.7.5</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.35</version>
		</dependency>

	</dependencies>
	<build>

		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.3</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>
Posted by
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)