Showing posts with label Hibernate. Show all posts

What is the difference between HQL and Criteria in Hibernate?

HQL (Hibernate Query Language)

  • HQL can be used to perform SELECT, INSERT, UPDATE, DELETE.
  • SQL injection possible if not used parameterized query.
  • SELECT STATEMENT: You've to manually write the long queries, take care of where and other syntax.

Criteria

  • Criteria can be used to perform only SELECT.
  • SQL injection is not possible with Criteria because hibernate will take care of it while generating SQL query.
  • SELECT STATEMENT: Criteria interface comes with handy methods and take care of where and other syntax.

These are the basic difference between HQL (Hibernate Query Language) and Criteria. Do comment if other important difference is there.

Check out the stackoverflow thread for performance of Hibernate Criteria vs HQL: which is faster?

Hibernate one to many mapping example [Annotation]

We are going to understand hibernate one-to-many relationship on following table structure...

country table holds one-to-many relationship with state table. Where relationship id resides in state table.

Hibernate One to Many mapping

Before we see the complete code, lets first understand how to define relationship in code.

Country.java, State.java

one to many hibernate

Source code (Country.java)
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table;

/**
 * @author javaQuery
 * @date 11th April, 2017
 * @Github: https://github.com/javaquery/Examples
 */
@Entity
@Table(name = "country")
public class Country implements Serializable {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "country" /*, fetch = FetchType.LAZY*/)
    private Set<State> states = new HashSet<State>();
    
    //getter-setter
}

Source code (State.java)
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

/**
 * @author javaQuery
 * @date 15th June, 2017
 * @Github: https://github.com/javaquery/Examples
 */
@Entity
@Table(name = "state")
public class State implements Serializable{

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "country_id")
    private Country country;

    @Column(name = "name")
    private String name;
    
    //getter-setter
}

Source code (OneToManyMappingExample.java)
import com.javaquery.bean.Country;
import com.javaquery.bean.State;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

/**
 * Hibernate one to many example.
 * 
 * @author javaQuery
 * @date 15th June, 2017
 * @Github: https://github.com/javaquery/Examples
 */
public class OneToManyMappingExample {

    public static void main(String[] args) {
        try {
            /* Create hibernate configuration. */
            Configuration configuration = new Configuration();
            configuration.configure("com\\javaquery\\database\\hibernate\\hibernate.cfg.xml");

            /* Open session and begin database transaction for database operation. */
            SessionFactory sessionFactory = configuration.buildSessionFactory();
            Session session = sessionFactory.openSession();

            Country country = session.load(Country.class, 1L);
            if(!country.getStates().isEmpty()){
                for (State state: country.getStates()) {
                    System.out.println(state.getName());
                }
            }else{
                System.out.println("No states found!");
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Output
Hibernate: 
    select
        country0_.id as id1_1_0_,
        country0_.name as name2_1_0_ 
    from
        country country0_ 
    where
        country0_.id=?
Hibernate: 
    select
        states0_.country_id as country_3_3_0_,
        states0_.id as id1_3_0_,
        states0_.name as name2_3_1_ 
    from
        state states0_ 
    where
        states0_.country_id=?
  
Banglore
Gujarat
Mumbai

Hibernate one to one mapping example [Annotation]

We all understand one-to-one relation in database but when it comes to Hibernate I always stuck at which annotation to use and where should I place it?

We are going to understand the hibernate one-to-one relationship on following table structure.
- country table holds the one-to-one relationship with languages table. Where relationship id resides in country table.
- country table holds the one-to-one relationship with capital table. Where relationship id resides in capital table.


Before we see the complete code, lets first understand how to define relationship in code.

Country.java (country - languages)


Capital.java (country - capital)


Source code (Country.java)
import java.io.Serializable;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;

/**
 * @author javaQuery
 * @date 11th April, 2017
 * @Github: https://github.com/javaquery/Examples
 */
@Entity
@Table(name = "country")
public class Country implements Serializable {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @OneToOne(cascade = CascadeType.ALL, mappedBy = "country")
    private Capital capital;

    @OneToOne
    @JoinColumn(name = "primary_language_id", referencedColumnName = "id")
    private Language language;
 
    //getter-setter
}

Source code (Language.java)
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author javaQuery
 * @date 1th April, 2017
 * @Github: https://github.com/javaquery/Examples
 */
@Entity
@Table(name = "languages")
public class Language implements Serializable{
    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;
    
    @Column(name = "language")
    private String language;
 
    //getter-setter
}

Source code (Capital.java)
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;

/**
 * @author javaQuery
 * @date 11th April, 2017
 * @Github: https://github.com/javaquery/Examples
 */
@Entity
@Table(name = "capital")
public class Capital implements Serializable{
    
    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;
    
    @Column(name = "name")
    private String name;

    
    @OneToOne
    @JoinColumn(referencedColumnName = "id", name = "country_id")
    private Country country;
 
    //getter-setter
}

Source code (OneToOneMappingExample.java)
import com.javaquery.bean.Country;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

/**
 * @author javaQuery
 * @date 11th April, 2017
 * @Github: https://github.com/javaquery/Examples
 */
public class OneToOneMappingExample {

    public static void main(String[] args) {
        try {
            /* Create hibernate configuration. */
            Configuration configuration = new Configuration();
            configuration.configure("com\\javaquery\\database\\hibernate\\hibernate.cfg.xml");

            /* Open session and begin database transaction for database operation. */
            SessionFactory sessionFactory = configuration.buildSessionFactory();
            Session session = sessionFactory.openSession();
            
            Country country = session.load(Country.class, 1L);
            System.out.println(country);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Output
Hibernate: 
    select
        country0_.id as id1_1_0_,
        country0_.primary_language_id as primary_3_1_0_,
        country0_.name as name2_1_0_,
        language1_.id as id1_2_1_,
        language1_.language as language2_2_1_,
        capital2_.id as id1_0_2_,
        capital2_.country_id as country_3_0_2_,
        capital2_.name as name2_0_2_ 
    from
        country country0_ 
    left outer join
        languages language1_ 
            on country0_.primary_language_id=language1_.id 
    left outer join
        capital capital2_ 
            on country0_.id=capital2_.country_id 
    where
        country0_.id=?

Country{id=1, name=India, capital=Capital{id=2, name=Delhi}, language=Language{id=2, language=Hindi}}

What is SQL Injection and how to avoid it in Java?

SQL Injection
Its a technique where attacker try to alter(modify/change) your SQL query using input parameters.
SQL injection may leads to unexpected transaction (i.e select, update, delete, etc...). We'll see the basic SQL injection examples and later on see how to prevent it using Prepared Statement, Hibernate Criteria and HQL.

Source code (SQLInjection.java)
import java.util.ArrayList;
import java.util.List;

/**
 * Example of SQL injection.
 * @author javaQuery
 * @date 8th November, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class SQLInjection {

    public static void main(String[] args) {
        /* You are getting parameter value from web page or other user input */
        String parameter = "12"; // normal condition
        new SQLInjection().getUser(parameter);

        /**
         * SQL injection using parameter value. 
         * - If user can change parameter in url, use some script, etc...
         */
        parameter = "12 or 1 = 1";
        new SQLInjection().getUser(parameter);
    }

    /**
     * Get user from database.
     * @param id
     * @return 
     */
    public List<Object> getUser(String id) {
        List<Object> result = new ArrayList<Object>();

        String sql = "SELECT * FROM users WHERE id = " + id + ";";
        System.out.println("SQL Query: " + sql);

        /* prepare connection and execute query */
        return result;
    }
}
Output
In following queries, 1st query is valid and return result as expected but when 2nd query is executed it'll select all users from database and that may leads to unexpected behavior of your system.
SQL Query: SELECT * FROM users WHERE id = 12;
SQL Query: SELECT * FROM users WHERE id = 12 or 1 = 1;
In this example I used user table and this table contains very few records 1k, 10k, etc... but
What if you are selecting data from table which contains millions of records? - Answer is SYSTEM CRASH

Other ways of SQL injection
Consider you are getting value of username and password from parameter into param_username and param_password.
String param_username = "\" or \"\"=\"";
String param_password = "\" or \"\"=\"";

//SQL Injection:
String sql = "SELECT * FROM users WHERE username = \"" + param_username + "\" AND password = \"" + param_password +"\"";
System.out.println(sql);
//OUTPUT: SELECT * FROM users WHERE username = "" or ""="" AND password = "" or ""=""

============================================
String param_userid = "123; DROP TABLE messages;";

//SQL Injection:
String sql = "SELECT * FROM users WHERE id = " + param_userid;
System.out.println(sql);
//OUTPUT: SELECT * FROM users WHERE id = 123; DROP TABLE messages;

First and foremost way: Handle Datatypes
For the sake of simplicity developers don't handle data types at coding. In above code I used String as input parameter in method getUser but should've use Integer/Long. If I used Integer or Long then I've to convert String => 12 or 1 = 1 to Integer/Long => Not Valid Number. It'll prevent SQL Injection.


Avoid SQL Injection using Prepared Statement
Prepared Statement doesn't append values in your SQL query rather it provide SQL query and parameter values separately to database. Database will take care of every parameter value for escape character, special character and every other precaution needed.

Source code (PreparedStatementExample.java)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Example of SQL injection.
 * @author javaQuery
 * @date 8th November, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class PreparedStatementExample {
    public static void main(String[] args) {
        new PreparedStatementExample().getUser("12");
    }
    
    /**
     * Get user from database.
     * @param id
     * @return 
     */
    public List<Object> getUser(String id) {
        List<Object> result = new ArrayList<Object>();

        String sql = "SELECT * FROM users where id = ?;";

        /* prepare connection and execute query */
        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
            PreparedStatement prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setInt(1,Integer.parseInt(id)); // index of ? is '1', perform null/number check for 'id'
            //execute prepared statement
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return result;
    } 
}
Output
With PreparedStatement only following query can be generated.
SELECT * FROM users where id = 12;
prepareStatement.setInt: 12 or 1 = 1 is passed as value then Integer.parseInt will throw java.lang.NumberFormatException: For input string: "12 or 1=1".
prepareStatement.setString: What happen if String is used for Number data type in MySQL?


Avoid SQL Injection using Hibernate Criteria
Hibernate Criteria internally uses Prepared Statement to execute query.

Source code
String param_id = "12";

Criteria criteria = session.createCriteria(User.class);
/**
 * 'param_id' provided as String but 'id' declared as Integer/Long in User.java
 * So it'll throw exception(java.lang.String cannot be cast to java.lang.Integer) for invalid data type. (SQL injection handled)
 */
criteria.add(Restrictions.eq("id", param_id));
User user = criteria.uniqueResult();

==============================

// valid query
Integer param_id = 12;

Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.eq("id", param_id));
User user = criteria.uniqueResult();

Avoid SQL Injection using HQL
Its same as Hibernate Criteria.

Source code
String param_id = "12";

Query query = session.createQuery("FROM User WHERE id = :param_id");
/**
 * 'param_id' provided as String but 'id' declared as Integer/Long in User.java
 * So it'll throw exception(java.lang.String cannot be cast to java.lang.Integer) for invalid data type. (SQL injection handled)
 */
query.setParameter("param_id", param_id);
query.list();

==============================

// valid query
Integer param_id = 12;

Query query = session.createQuery("FROM User WHERE id = :param_id");
query.setParameter("param_id", param_id);
query.list();

How to get valued query from Hibernate Criteria(Not Logger)?


As per my knowledge there is no library available that allows you get Criteria query with its real values. So I came up with solution that allows you to do that.

Hibernate Assist, Its an open source Hibernate Criteria analysis tool. It has many features one of them is to get Criteria query with its value.

Download Librarywww.javaquery.com/p/hibernateassist.html

Source Code
Criteria criteria = objSession.createCriteria(User.class);
criteria.createAlias("Messages", "Messages");
criteria.createAlias("CreditCard", "CreditCard");
criteria.add(Restrictions.eq("Email", "vicky.thakor@javaquery.com"));
List<User> listUser = criteria.list();

HibernateAssist objHibernateAssist = new HibernateAssist(objSession);
objHibernateAssist.setCriteria(criteria);
String strQuery = objHibernateAssist.getValuedCriteriaQuery();
System.out.println(strQuery);

Output
SELECT this_.id                        AS id0_2_, 
       this_.username                  AS username0_2_, 
       this_.password                  AS password0_2_, 
       this_.email                     AS email0_2_, 
       messages1_.id                   AS id1_0_, 
       messages1_.user_id              AS user2_1_0_, 
       messages1_.message_text         AS message3_1_0_, 
       creditcard2_.id                 AS id2_1_, 
       creditcard2_.user_id            AS user2_2_1_, 
       creditcard2_.credit_card_number AS credit3_2_1_ 
FROM   user_master this_ 
       INNER JOIN message messages1_ 
               ON this_.id = messages1_.user_id 
       INNER JOIN creditcard creditcard2_ 
               ON this_.id = creditcard2_.user_id 
WHERE  this_.email = 'vicky.thakor@javaquery.com' 
Note: I tried to manage almost all cases however if you find for your Criteria its not working please comment your issues.

Hibernate Disjunction with Example

Hibernate Disjunction with Example

Hibernate Disjunction, is used to add multiple condition in SQL query separated by OR clause within brackets. To generate following query using Hibernate Criteria we need to use Disjunction.

Query
select
 this_.id as id0_0_,
 this_.username as username0_0_,
 this_.email as email0_0_ 
from
 user_master this_ 
where
(
 this_.username=? 
 or this_.username=?
)

Source Code
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Restrictions;

/**
 * Hibernate Conjunction with Example
 * @author javaQuery
 */
public class HibernateDisjunctionExample {

    public static void main(String[] args) {
        /* Create hibernate configuration. */
        Configuration objConfiguration = new Configuration();
        objConfiguration.configure("com\\hibernateassist\\hbm\\hibernate.cfg.xml");

        /* Open session and begin database transaction for database operation. */
        SessionFactory objSessionFactory = objConfiguration.buildSessionFactory();
        Session session = objSessionFactory.openSession();
        
        /* Create criteria */
        Criteria criteria = session.createCriteria(User.class);
        
        /* Create object of Disjunction */
        Disjunction objDisjunction = Restrictions.disjunction();
        /* Add multiple condition separated by OR clause within brackets. */
        objDisjunction.add(Restrictions.eq("Username", "vicky"));
        objDisjunction.add(Restrictions.eq("Username", "thakor"));
        
        /* Attach Disjunction in Criteria */
        criteria.add(objDisjunction);
        
        /* Execute criteria */
        criteria.list();
    }
}

Hibernate Conjunction with Example

Hibernate Conjunction

Hibernate Conjunction, is used to add multiple condition in SQL query separated by AND clause  within brackets. To generate following query using Hibernate Criteria we need to use Conjunction.

Query
select
 this_.id as id0_0_,
 this_.username as username0_0_,
 this_.email as email0_0_ 
from
 user_master this_ 
where
(  
 this_.username=? 
 and this_.username=? 
)

Source Code
Following code will generate above given query.
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Restrictions;

/**
 * Hibernate Conjunction with Example
 * @author javaQuery
 */
public class HibernateConjunctionExample {

    public static void main(String[] args) {
        /* Create hibernate configuration. */
        Configuration objConfiguration = new Configuration();
        objConfiguration.configure("hibernate.cfg.xml");

        /* Open session and begin database transaction for database operation. */
        SessionFactory objSessionFactory = objConfiguration.buildSessionFactory();
        Session session = objSessionFactory.openSession();
        
        /* Create criteria */
        Criteria criteria = session.createCriteria(User.class);
        
        /* Create object of Conjunction */
        Conjunction objConjunction = Restrictions.conjunction();
        /* Add multiple condition separated by AND clause within brackets. */
        objConjunction.add(Restrictions.eq("Username", "vicky"));
        objConjunction.add(Restrictions.eq("Username", "thakor"));
        
        /* Attach Conjunction in Criteria */
        criteria.add(objConjunction);
        
        /* Execute criteria */
        criteria.list();
    }
}

How Hibernate interpret Order of JOIN on a Table?

Hibernate Logo


Hibernate is one of the greatest framework to interact with database but every framework has its own advantages and disadvantages. Haphazard use of Hibernate may slower your application and you'll face major performance issues.

"Hibernate ain't important than database, you should care How Hibernate execute queries against database"

I've prepared sample mapping file to join two table "vehicle_master" and "profile_master" (It may not make sense but this is just an example so just ignore it).
/* user.hbm.xml */
<?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>
  <class name="javaQuery.hibernate.User" table="user_master">
    <id name="id" type="int">
      <column name="id"/>
      <generator class="assigned"/>
    </id>
    <property name="Username" type="java.lang.String">
      <column name="user_name"/>
    </property>
    <property name="Email" type="java.lang.String">
      <column name="email"/>
    </property>
    <list cascade="refresh" name="listVehicle" table="user_vehicle_map">
      <key column="user_id"/>
      <index column="idx" type="integer"/>
      <many-to-many class="javaQuery.hibernate.Vehicle" column="vehicle_id"/>
    </list>
    <many-to-one name="Profile" class="javaQuery.hibernate.Profile" column="profile_id" unique="true" not-null="true" />
  </class>
</hibernate-mapping>

Execute following criteria to get SQL query and lets see "In which order Hibernate interpret your join".
/* Simple Criteria to load user with its vehicles and profile */
Criteria criteria = session.createCriteria(User.class);
criteria.setFetchMode("listVehicle", FetchMode.JOIN);
criteria.setFetchMode("Profile", FetchMode.JOIN);
criteria.list();

Above criteria will generate following SQL as we know it.
SELECT *
FROM   user_master this_
LEFT OUTER JOIN user_vehicle_map listvehicl2_
            ON this_.id = listvehicl2_.user_id
LEFT OUTER JOIN vehicle_master vehicle3_
            ON listvehicl2_.vehicle_id = vehicle3_.id
INNER JOIN profile_master profile4_
       ON this_.profile_id = profile4_.id

Myth
Developer thinks that JOIN on vehicle table took first place in SQL query because we have vehicle table at the first position in Criteria but have you ever tried changing position in Criteria? Lets see what happen if we change position in Criteria. After executing following code you'll get the same SQL query generated by first code.
/* Position of JOIN changed in criteria */
Criteria criteria = session.createCriteria(User.class);
criteria.setFetchMode("Profile", FetchMode.JOIN);
criteria.setFetchMode("listVehicle", FetchMode.JOIN);        
criteria.list();

Why didn't Hibernate change order of JOIN?
Well so far you are living with wrong assumption about How order of JOIN take place in hibernate. Its not the Criteria used by Hibernate to interpret order of JOIN on table. Its the Hibernate Mapping (hbm.xml) file. So if you want to change order of JOIN on table, you'll have to change order in your Hibernate Mapping (hbm.xml) file.

Order changed in Hibernate Mapping file. Compare first and second user.hbm.xml file for Vehicle and Profile.
/* user.hbm.xml */
<?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>
  <class name="javaQuery.hibernate.User" table="user_master">
    <id name="id" type="int">
      <column name="id"/>
      <generator class="assigned"/>
    </id>
    <property name="Username" type="java.lang.String">
      <column name="user_name"/>
    </property>
    <property name="Email" type="java.lang.String">
      <column name="email"/>
    </property>
    <many-to-one name="Profile" class="javaQuery.hibernate.Profile" column="profile_id" unique="true" not-null="true" />
    <list cascade="refresh" name="listVehicle" table="user_vehicle_map">
      <key column="user_id"/>
      <index column="idx" type="integer"/>
      <many-to-many class="javaQuery.hibernate.Vehicle" column="vehicle_id"/>
    </list>
  </class>
</hibernate-mapping>

Now this hibernate mapping file will generate following SQL query not matter what order you've in your Criteria.
SELECT *
FROM   user_master this_
INNER JOIN profile_master profile2_
       ON this_.profile_id = profile2_.id
LEFT OUTER JOIN user_vehicle_map listvehicl3_
            ON this_.id = listvehicl3_.user_id
LEFT OUTER JOIN vehicle_master vehicle4_
            ON listvehicl3_.vehicle_id = vehicle4_.id 

Why ORDER of JOIN is so important?
You'll get same result in both the queries. However JOIN on table in wrong order may cause performance issue at Database. After all Database have to filter your data based on your joins. This is sample query and will not create major impact because of order of JOIN but in real time application we have lots of joins on table and at that time ORDDER of JOIN does matter. Contact your Database Administrator to understand this performance issue for JOINs.

When Hibernate uses INNER JOIN?

Hibernate Logo

After years of experience working around Hibernate there are very few people actually knows "When Hibernate Uses INNER JOIN". I'm one of 'em till today. Today I drilled further and found 3 cases where Hibernate uses INNER JOIN.

1. not-null attribute
When you specify attribute not-null = "true" in hbm.xml for particular column which used for table joining, It will use INNER JOIN no matter you specified FetchMode.JOIN in criteria.
/* Having not-null = "true" in hbm file, It'll override following FetchMode.JOIN */
criteria.setFetchMode("user", FetchMode.JOIN);

2. createAlias
When you create an alias of table, Hibernate will automatically uses INNER JOIN. Its default setting of hibernate.
criteria.createAlias("user", "user");

3. CriteriaSpecification.INNER_JOIN
Well you can ignore CriteriaSpecification.INNER_JOIN, as it can be used with createAlias. And when you use createAlias hibernate will automatically use INNER JOIN but this is just for the sake of your knowledge.
criteria.createAlias("user", "user", CriteriaSpecification.INNER_JOIN);

Note: If I'm missing any other way let's all know about it. Use comment box below.

Hibernate and Database (Microsoft SQL Server) Indexes

Hibernate logo

Hibernate
Hibernate is an Object Relational Mapping (ORM) library used in java. This framework used to map relational database into Object Oriented domain model. This framework can handle small and enterprise level application both. Hibernate uses Prepared Statement (pre-compiled query) for data retrieval until unless you write hard coded native queries.


Database Index
A database index is a data structure that improves the speed of data retrieval operations on a database table at the minimal cost of additional writes and little more additional space to maintain a restricted set of selected data.


Hibernate and Microsoft SQL Server Index
Today we will intercept the interaction of Hibernate and SQL Server Index. We will see How actually hibernate queries stored in database.

In the following example we have table user_master. It has column user_name with datatype varchar(100). We will create an Index on column user_name and test it against Hibernate.

Step 1: Create an Index on column user_name.
Note: Index will be used only when table holds huge number of records minimum 1500-2000 records.
CREATE NONCLUSTERED INDEX [IXNc_user_master__user_name] ON [dbo].[user_master] 
(
 [user_name] ASC
)WITH (PAD_INDEX  = ON, FILLFACTOR = 80)

Step 2: Executing simple select query in SQL Server Studio.
/* Press Ctrl + M or enable execution plan in SQL Studio. */
/* After execution of query you can see the execution plan, that shows Index Seek(Search in index) of 'IXNc_user_master__user_name' */
SELECT * FROM user_master WHERE user_name = 'vicky_thakor'

Step 3: We will verify that index used for seek (search) or scan. Seek cost is very low but scan cost lot more than regular. Execute following query that shows SEEK and SCAN count on Index and save/remember the details for reference. (*Change database name and table name.)
SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName,
  si.name AS IndexName, sc.Name AS ColumnName, sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si 
 ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic 
 ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc 
 ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('database_name') AND sis.OBJECT_ID = OBJECT_ID('user_master');

Step 4: Now clear all available Prepared Statements from SQL Server cache.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Step 5: Execute same query using Hibernate Criteria
Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.eq("Username", "vicky_thakor"));        
criteria.list();

Step 6: Execute following query to check what query Hibernate generated to fetch the data.
SELECT usecounts, text 
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

/* Hibernate generated following query */
(@P0 nvarchar(4000))select this_.uid as uid0_0_, this_.Firstname as Firstname0_0_, this_.Lastname as Lastname0_0_, this_.user_name as user4_0_0_ from user_master this_ where this_.user_name= @P0

Hibernate and Microsoft SQL Server database indices

Important: We have column user_name with datatype varchar but hibernate fetching data using nvarchar datatype.

Step 7: Execute Step 3 to check index is used with SEEK or SCAN. You will find that index is scanned and that cost a lot.


Cross-check
Lets check how database act with different datatype for index. You are requested to turn on execution plan setting in Microsoft SQL Server [Read more: Execution Plan in MSSQL Server]. We will use Prepared Statement at database level with both datatype.
/* Execute following query which leads to Index Scan (Check attached image) */
EXECUTE sp_executesql
@statement = N'SELECT * FROM user_master WHERE user_name = @P0',
@parameters = N'@P0 nvarchar(4000)',
@P0 = 'vicky_thakor'

/* Execute following query which leads to Index Seek (Check attached image) */
EXECUTE sp_executesql
@statement = N'SELECT * FROM user_master WHERE user_name = @P0',
@parameters = N'@P0 varchar(4000)',
@P0 = 'vicky_thakor'

Hibernate and Microsoft SQL Server database indices

Issue
Hibernate used nvarchar to support Unicode characters and we have column with varchar datatype. Database always upgrade lower datatype to higher datatype to eliminate data lose. Datatype conversion leads to index scan rather index seek.


Solution

You are suggested to change your datatype from varchar to nvarchar. If you are not interested in datatype change I have another solution for it.

Add parameter called sendStringParametersAsUnicode in your connection string. It will force hibernate to use varchar instead nvarchar.
<property name="hibernate.connection.url">jdbc:jtds:sqlserver://127.0.0.1:1433/javaQuery;sendStringParametersAsUnicode=false</property>

Conclusion
Its not only the hibernate use nvarchar, You should check all other framework you are working with. Indexes are meant to increase the performance but this intermediate framework and poor database knowledge leads to low performance of application.  

java.sql.SQLException: Incorrect syntax near '@P1'.

Scenario: 
We were calling MSSQL Stored Procedure using hibernate. My colleague called me to check out error and even I was clueless. It was working fine at database level. And while checking out calling code it seems alright but it thrown an error as follow.

org.hibernate.exception.SQLGrammarException: could not execute query
  at Incorrect syntax near '@P1'..()
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
  at could not execute query.(Native Method)
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
  at org.hibernate.loader.Loader.doList(Loader.java:2223)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
  ... 53 more
Caused by: java.sql.SQLException: Incorrect syntax near '@P1'.
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  ... 50 more

Solution:
My colleague forgot to put comma , after each parameter. Follow below queries one with error and other without.

EXEC MY_STORED_PROC :PARAM1 :PARAM2 /* Query that throwing error because of missing comma. */
EXEC MY_STORED_PROC :PARAM1, :PARAM2 /* Query that works fine. */

Date [java.util.Date] conversion from Java to SQL

Datatype Conversion is silent part of any project, yet very important. You'd like to read importance of Datatype Conversion [http://en.wikipedia.org/wiki/Cluster_(spacecraft)]

Case Scenario:
  • Pass current date (java.util.Date) to database stored procedure in varchar / string format.
  • Convert string date to datetime at database level. 
  • Process our logic and return date in string format.
  • Get date in string format at java.
  • Convert string date in java to java.util.Date

java.util.Date > String > datetime(MSSQL Database) > String > java.util.Date

Step 1: We'll create sample stored procedure to test our conversion.
IF (OBJECT_ID('SP_DateTime') IS NOT NULL)
  DROP PROCEDURE SP_DateTime
GO
CREATE PROCEDURE SP_DateTime @DateTimeString VARCHAR(100)
AS
BEGIN
 /**
  * @author javaQuery
  * @test EXEC SP_DateTime '2012-12-15 16:30'
  */
 DECLARE @v_Datetime DATETIME;
 SET @v_Datetime = CONVERT(varchar,@DateTimeString, 120);
 /* Compare dates and other business logic */
 SELECT CAST(@v_Datetime AS VARCHAR(100));
END

Step 2. Calling above stored procedure from Java (Hibernate: How To Call Stored Procedure In Hibernate).
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.hibernate.*;
import org.hibernate.cfg.Configuration;

public class SP_DATETIME {

    public static void main(String[] args) {
        /* Create hibernate configuration. */
        Configuration c = new Configuration();
        c.configure("hibernate.cfg.xml");

        /* Open session and begin database transaction for database operation. */
        SessionFactory sf = c.buildSessionFactory();
        Session session = sf.openSession();

        /* Call Stored Procedure */
        Query callStoredProcedure_MSSQL = session.createSQLQuery("EXEC SP_DateTime :param1");
        callStoredProcedure_MSSQL.setString("param1", String.valueOf(new Timestamp(new Date().getTime())));

        /* Get return value */
        List customResult = callStoredProcedure_MSSQL.list();
        if (customResult != null && !customResult.isEmpty()) {
            SimpleDateFormat formatter = new SimpleDateFormat("MMM dd yyyy hh:mma");
            try {
                Date d = formatter.parse(customResult.get(0));
                System.out.println(d.toString());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        session.close();
    }
}
For other date format check http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Question: Why we choose string rather than timestamp?
Answer: Consider if you have common class that pass call to stored procedure. You don't know which datatype will come as an argument to your method. We'll set every parameter as string.

Question: Why we are returning date as string from Stored Procedure.
Answer: When we get NULL from Database hibernate will try to convert NULL to date and will throw an exception.

How To Call Stored Procedure In Hibernate

What is STORED PROCEDURE?
A stored procedure is a pre-compiled SQL subroutine used to perform multiple procedural operations.

"A stored procedure is a subroutine available to applications that access a relational database system" - WikiPedia
"A stored procedure is a group of Transact-SQL statements compiled into a single execution plan" - Microsoft

What will we cover in this article?
  • Sample MSSQL and MYSQL stored procedure.
  • How to call MSSQL and MYSQL stored procedure.
  • What should be taken care while executing stored procedure in Hibernate?

MSSQL Sample Stored Procedure
IF (OBJECT_ID('SP_MSSQL_HIBERNATE') IS NOT NULL)
  DROP PROCEDURE SP_MSSQL_HIBERNATE
GO
CREATE PROCEDURE SP_MSSQL_HIBERNATE
@PARAM1 INT,
@PARAM2 INT,
@PARAM3 VARCHAR(50)
AS 
BEGIN
 BEGIN TRANSACTION
 BEGIN TRY
    /* 
     * Uncomment below code to get custom row in hibernate.
     * ------------------------------------------------
     * DECLARE @X, @Y INT;
     * DECLARE @RESULT INT;
     * SET @RESULT = @X + @Y;
     * SELECT @RESULT AS RESULT, 'javaQuery' AS STRING_RESULT;
     */
 
    /* Your custom operation */ 
    UPDATE user_master SET Firstname = @PARAM3 WHERE UID = 1;
           
    /* Insert record */
    INSERT INTO user_master (Firstname, Lastname) VALUES('Jennifer ', 'Lawrence');
           
    /* Returns user object (row) */
    SELECT * FROM user_master WHERE UID = 1;

 COMMIT TRANSACTION;
 END TRY
 BEGIN CATCH
          ROLLBACK TRANSACTION;
          PRINT @@ERROR
 END CATCH;
END
MYSQL Sample Stored Procedure
DROP PROCEDURE IF EXISTS SP_MYSQL_HIBERNATE;
DELIMITER $
CREATE PROCEDURE SP_MYSQL_HIBERNATE(IN PARAM1 INT, IN PARAM2 INT, IN PARAM3 VARCHAR(100))
BEGIN
 /*
  * Uncomment below code to get custom row in hibernate.
  * ------------------------------------------------
  * DECLARE X, Y INT DEFAULT 10;
  * DECLARE RESULT INT;
  * SET RESULT = X + Y;
  * SELECT RESULT AS RESULT, 'javaQuery' AS STRING_RESULT; 
  */

  /* Your custom operation */ 
  UPDATE user_master SET Firstname = PARAM3 WHERE UID = 1;
        
  /* Insert record */
  INSERT INTO user_master (Firstname, Lastname) VALUES('Jennifer ', 'Lawrence');

  /* Returns user object (row) */
  SELECT * FROM user_master WHERE UID = 1;
END $
DELIMITER ;

How to call MSSQL and MYSQL stored procedure in Hibernate?
/**
 * Create hibernate configuration.
 */
Configuration c = new Configuration();
c.configure("hibernate.cfg.xml");

/**
 * Open session and begin database transaction for database operation.
 */
SessionFactory sf = c.buildSessionFactory();
Session session = sf.openSession();
Transaction t = session.beginTransaction();

/**
 * Create SQL Query for Stored Procedure and pass required parameters.
 * MSSQL : EXEC Name_Of_Stored_Procedure :param1, :param2
 * MYSQL : CALL Name_Of_Stored_Procedure :param1, :param2
 * 
 * `.addEntity(User.class)` will map output result as per User bean.
 */

/**************************************************/
/* Call MSSQL Stored Procedure and MAP it to bean */
/* Un-comment the code                            */
/**************************************************/
/*Query callStoredProcedure_MSSQL = session.createSQLQuery("EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3").addEntity(User.class);
callStoredProcedure_MSSQL.setInteger("param1", 10);
callStoredProcedure_MSSQL.setInteger("param2", 10);
callStoredProcedure_MSSQL.setString("param3", "javaQuery");*/

/* callStoredProcedure_MSSQL.list() will execute stored procedure and return the value */
/*List userList = callStoredProcedure_MSSQL.list();
if (userList != null && !userList.isEmpty()) {
 for(User user : userList){
  System.out.println("Firstname:"+user.getFirstname());
 }
}*/


/**************************************************/
/* Call MYSQL Stored Procedure and MAP it to bean */
/**************************************************/
Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)").addEntity(User.class);
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "javaQuery");

/* callStoredProcedure_MSSQL.list() will execute stored procedure and return the value */
List userList = callStoredProcedure_MYSQL.list();
if (userList != null && !userList.isEmpty()) {
 for(User user : userList){
  System.out.println("Firstname:"+user.getFirstname());
 }
}

/******************************************************************/
/* Process custom result of Stored Procedure                      */
/* Un-comment the code, This will be the same for MSSQL and MYSQL */
/******************************************************************/
/*Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)");
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "javaQuery");*/

/* callStoredProcedure_MYSQL.list() will execute stored procedure and return the value */
/*List customResult = callStoredProcedure_MYSQL.list();
if (customResult != null && !customResult.isEmpty()) {
 Object[] obj = customResult.get(0);
 System.out.println(obj[0]);
 System.out.println(obj[1]);            
}*/

/* Commit the transaction and close session. */
t.commit();
session.close();
Above code contains 3 portion for execution. Un-comment your required code and test it.

  1. How to Execute MSSQL Stored Procedure in Hibernate (code is commented)
  2. How to Call MYSQL Stored Procedure in Hibernate
  3. How to Process custom result of Stored Procedure in Hibernate (code is commented)

What should be taken care while executing stored procedure in Hibernate?

  • If you have single Insert, Update or Delete operation in your stored procedure then you have to beginTransactin() and commit() it in order to take effect.
  • Hibernate will only select first result of stored procedure. e.g: If you write two select statement then first result will be mapped for hibernate in case of bean and without bean it'll only return first result as list of object.
  • If you are calling MSSQL stored procedure then SQL query must be without Parentheses ( and ).
    e.g EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3
  • If you are calling MYSQL stored procedure then SQL query must be with Parentheses ( and ).
    e.g CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)

java.sql.SQLException: Incorrect syntax near '@P0'.

I'm creating sample code for one of my article of hibernate and database. I got exception while calling Stored Procedure from java in hibernate.

So I googled and after trying different option found the solution and thought let me post it in proper way so you guys don't need to waste your time to check other options.

EXCEPTION
Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.
 at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
 at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)
 at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2334)
 at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:643)
 at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:506)
 at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:979)
 at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
 at org.hibernate.loader.Loader.doQuery(Loader.java:662)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
 at org.hibernate.loader.Loader.doList(Loader.java:2211)
 ... 7 more
CODE WITH ERROR
Query callStoredProcedure = session.createSQLQuery("EXEC SP_MSSQL_HIBERNATE (:param1, :param2, :param3)").addEntity(User.class);
callStoredProcedure.setInteger("param1", 10);
callStoredProcedure.setInteger("param2", 10);
callStoredProcedure.setString("param3", "javaQuery");
      
List userList = callStoredProcedure.list();
SOLUTION
  • If you are calling MSSQL stored procedure then SQL query must be without Parentheses ( and ).
    e.g EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3
  • If you are calling MYSQL stored procedure then SQL query must be with Parentheses ( and ).
    e.g CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)


How to create Hibernate query log?


What is Hibernate?
Hibernate is framework that provides object oriented interaction with database. Now a days  it is used widely to handle large database.

If you are working behind large application which uses hibernate for all of its transaction, I think you might need to configure query log and tune up your hibernate criteria.

Use of hibernate with proper criteria/queries is like a life boat for application but amateur use of hibernate is like hole in life boat.

Problem: JOIN is one creates massive overload in hibernate criteria. To get your desired output you always use JOIN in your criteria. You should be careful while using JOIN in any of your hibernate criteria or queries (HQL).  Single JOIN will load tons of database records and create overhead on your application.

Why you need to configure query log?
For development purpose it will be very useful to track down each query execution through hibernate. I'm not talking about hibernate.show_sql property. It'll only show you query with ? but won't print actual input and output of that query.

We'll configure log4j to get input query parameter and output values. You should turn off this mechanism at production server of will create IO overhead on server.

Step 1: Creating log4j.properties file. I already created one for you all you need to do is place it in your project.
log4j.logger.org.hibernate=INFO
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
log4j.logger.org.hibernate.hql.ast.AST=info
log4j.logger.org.hibernate.tool.hbm2ddl=warn
log4j.logger.org.hibernate.hql=debug
log4j.logger.org.hibernate.cache=info
log4j.logger.org.hibernate.jdbc=debug
log4j.rootLogger = DEBUG, FILE

# Define the file appender
log4j.appender.FILE=org.apache.log4j.DailyRollingFileAppender
# Set the DatePattern
log4j.appender.FILE.DatePattern='.'yyyy-MM-dd-HH-mm

# Set the append to false, should not overwrite
log4j.appender.FILE.Append=true

# Set the name of the file.
# It'll be created every minute with different filename(appnded with yyyy-MM-dd-HH-mm) and placed under log4j folder.
log4j.appender.FILE.File=log4j/QueryLog.log

# Define the layout for file appender
log4j.appender.FILE.layout=org.apache.log4j.PatternLayout
log4j.appender.FILE.layout.conversionPattern= %d{HH:mm:ss} %-5p %c - %m%n
Step 2: Configure this log4j.properties file before you perform any database transaction (Select, Insert, Update, Delete). Note: Download log4j [http://www.findjar.com/jar/log4j/jars/log4j-1.2.15.jar.html]
import org.apache.log4j.PropertyConfigurator; // import required
/** 
  * In below code I placed log4j.properties as string. 
  * Its file path, I placed log4j.properties in root directory/default package. You need to change path. 
  * If you are working with web application place it in war folder for below code to work.
  */
PropertyConfigurator.configure("log4j.properties");
Output:
17:43:45 DEBUG org.hibernate.SQL - select this_.UID as UID0_0_, this_.FIRSTNAME as FIRSTNAME0_0_, this_.Lastname as Lastname0_0_ from user_master this_ where this_.UID=?
17:43:45 TRACE org.hibernate.type.IntegerType - binding '1' to parameter: 1
17:43:45 DEBUG org.hibernate.jdbc.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
17:43:45 TRACE org.hibernate.type.IntegerType - returning '1' as column: UID0_0_
17:43:45 TRACE org.hibernate.type.StringType - returning 'Vicky' as column: FIRSTNAME0_0_
17:43:45 TRACE org.hibernate.type.StringType - returning 'Thakor' as column: Lastname0_0_
As you can see it shows what is value of input parameter where this_.UID=? with binding '1' to parameter: 1 and return values of fired query.

Conclusion: We have to be very careful about what we are trying to fetch from database and what will be fetched by frameworks. So configure it in your application and tune it up!