In the previous articles, we have seen two different ways to extract data from the database such as using HQL and using HCQL. In this article, we will see another way which is a traditional approach where we can also use our SQL queries in Hibernate.
What is Hibernate native SQL?
Hibernate is developed on ORM(Object Relation Mapping) tool means we can write database independent queries. For example, consider you are writing a query and your database vendor is changed so you don’t need to change the Queries in Hibernate. But if we are using native SQL queries we have to change the query if the database vendor is changed. So what’s the use of this?
There are some features of the database that we cannot access it using Hibernate so Native SQL queries in Hibernate are used when we want to access some specific feature of the database. The feature such as CONNECT keyword in ORACLE or query Hints and, etc.
How to Create Native SQL Query?
In Hibernate, we will use Session.createSQLQuery(String sqlQuery) to create the object of SQL Query and execute it. The SQLQuery Interface controlled the creation of native SQL queries. Let us see how we can use the APIs for querying.
Using Hibernate Native Scalar Queries
The most basic SQL query is Hibernate Native Scalar Query. In this, it fetches the list of values(which is raw data) from one or more database tables. Let us see the syntax of native Scalar(Values) queries:
session.createSQLQuery("SELECT * FROM VEHICLE").list();
Here, this will return a list of arrays of objects with each value from the table Vehicle. Hibernate internally uses ResultSetMetaData to gather the order and types of returned Scalar data/values. If we want to be more specific about what this query is returning we can add the addScalar() method with column name & type of column.
session.createSQLQuery("SELECT * FROM VEHICLE")
.addScalar("VEHILE_ID", Hibernate.LONG)
.addScalar("VEHICLE_NAME", Hibernate.STRING)
Using Entities Queries
In this, it returns the object of an entity instead of scalar values. It uses addEntity() method to get the Entity Object. Let us see the syntax of Entities queries.
session.createSQLQuery("SELECT * FROM VEHICLE").addEntity(Vehicle.class);
Here, we have two things first is the SQL query string and next is the entity returned by the query. This will return the object of an entity class “Vehicle”.
Using named Parameter
Sometimes there is a condition when we want to fetch the data on the basis of parameter provided by the user we use named parameter
String sql = "SELECT * FROM VEHICLE WHERE name = :vehicle_name";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Vehicle.class);
query.setParameter("vehicle_name", "ABC");
List results = query.list();
Example of Native SQL Queries in Hibernate
In this example, we will fetch the data using the native SQL query.
Let us first create a configuration file-hibernate. cfg.xml (Check this article for Configuration File How to configure hibernate in Java application).
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/Vehicledb</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">khan</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.show_sql">true</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<mapping class="com.abc.Vehicle" />
</session-factory>
</hibernate-configuration>
Now, create a Java class to set and get the data. We are creating annotated-based classes so we won’t need a mapping file now. (for annotation you can see the previous article How to use Annotation in Hibernate?).
Vehicle.java
In this class Just write the @Entity and @Table followed by table name.
package com.abc;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="vehicle")
public class Vehicle {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int id;
private String vehicleName;
private long vehiclePrice;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getVehicleName() {
return vehicleName;
}
public void setVehicleName(String vehicleName) {
this.vehicleName = vehicleName;
}
public long getVehiclePrice() {
return vehiclePrice;
}
public void setVehiclePrice(long vehiclePrice) {
this.vehiclePrice = vehiclePrice;
}
}
Now, create the main class Test.java
In this class, we have used the SQLQuery Interface method createSQLQuery() method to execute the query. The query returns the Object in List form so we have to use List Object from java. util package and we have displayed the data using for loop. (Let’s consider we have some data in our created table).
package com.abc;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class Test {
public static void main(String[] args) {
SessionFactory factory = new Configuration().configure().buildSessionFactory();
Session session = factory.openSession();
session.beginTransaction();
/* Let's see the SQL NATIVE query*/
Query q=session.createSQLQuery("SELECT ID,VehicleName,VehiclePrice from vehicle");
List<Vehicle[]> list=q.list();
for(Object[] v:list)
{
System.out.println(v[0]+ " " +v[1]+ " "+v[2]);
}
session.getTransaction().commit();
// System.out.println("table craeted");
}
}
OUTPUT
Hibernate: SELECT ID,VehicleName,VehiclePrice from vehicle 1 Abc 4526 2 Pqr 4869 3 xyz 2369
Thus, we have seen How to use native SQL queries in Hibernate. In the next article of this tutorial, we will see NamedQuery in Hibernate.
Note: The point here is that we should avoid using it in Hibernate because we lose the benefits of Hibernate Framework. We should go for Native SQL query when there is some specific functionality is to be done related to the database.
