Execute query in hibernate java

Executing SQL Queries using Hibernate

So far, you have seen how to execute commands in Hibernate Query Language (HQL) which are slightly different from SQL commands. For those who are somehow uncomfortable with learning Hibernate Query Language to interact with the database, don’t worry, Hibernate has got a solution. Hibernate also provides a way to execute the SQL queries by using its method createNativeQuery() of Query class.

Today, we are going to show you how to execute the SQL commands like accessing multiple columns of a table and executing SQL methods like max(), min(), sum(), count() and avg() using Hibernate.

Note :

• In the upcoming example, We are going to create a Java class which will be mapped to a table in our database(Oracle Express Edition 10g). We are going to use some Hibernate Annotations, hence, there will be no need to create the mapping-resource with extension hbm.xml. For those who are not aware of Hibernate Annotations, please read Hibernate Annotations for reference.
• And yes, one more thing, we are going to create this program in an Integrated Development Environment (IDE), so you may use any IDE of your choice.

Creating an Entity class

We are going to create a general parent class that will contain all the Java code files representing our Model class(whose objects needs to be persisted).

This is a simple Java class whose objects needs to be persisted/saved, these objects are also known as Plain Old Java Objects(POJO) or Entity class. Some may even refer to such class whose objects needs to be persisted as the Model class. This class is an entity class, hence it will be preceded with the @Entity annotation plus a few other annotations used below.

package decodejava; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table (name ="USER_INFO") public class UserInfo < @Id @GeneratedValue @Column (name = "ID") private int id; @Column (name = "First_Name") private String firstName; @Column (name = "Salary") private Integer salary; public int getId() < return id; >public void setId(int id) < this.id = id; >public String getFirstName() < return firstName; >public void setFirstName(String firstName) < this.firstName = firstName; >public Integer getSalary() < return salary; >public void setSalary(Integer salary) < this.salary = salary; >>

Creating the class that calls the Hibernate API

The Hiber class creates a Configuration object, used to configure the Hibernate. This is the first object we use when using the Hibernate. This object is used to specify the location of a configuration file and mapping document used by Hibernate. Using Configuration object we can create a SessionFactory object, which is eventually used to create a Session object to perform the object persistence operations.

package decodejava; import java.util.List; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.cfg.Configuration; import org.hibernate.query.Query; //import javax.persistence.Parameter; //import javax.persistence.Query; public class Hiber < public static void main(String[] args) < SessionFactory sf= new Configuration().configure().buildSessionFactory(); Session session = sf.openSession(); //Creating the first object UserInfo ob1 = new UserInfo(); ob1.setFirstName("D"); ob1.setSalary(40000); //Creating the second object UserInfo ob2 = new UserInfo(); ob2.setFirstName("A"); ob2.setSalary(20000); //Creating the third object UserInfo ob3 = new UserInfo(); ob3.setFirstName("Z"); ob3.setSalary(35000); session.beginTransaction(); session.save(ob1); //Saving the first object session.save(ob2); //Saving the second object session.save(ob3); //Saving the third object session.getTransaction().commit(); session.close(); //Opening a new Session session = sf.openSession(); //Beginning the Transaction session.beginTransaction(); //Creating a Query by calling createNativeQuery() method to execute an SQL query Query query = session.createNativeQuery("select ID,First_Name from User_Info"); Listcollection = query1.getResultList(); System.out.println("Retrieving values in multiple columns with property 'id' and 'firstName' "); for(Object[] user: collection) < System.out.println("Id : " + user[0]); System.out.println("First Name : " + user[1]); System.out.println(); >//Creating a Query by calling createNativeQuery() method to execute SQL min() function query = session.createNativeQuery("select min(salary) from User_Info"); Object o1 = query.getSingleResult(); System.out.println("Minimum Salary :" +o1); System.out.println(); //Creating a Query by calling createNativeQuery() method to execute SQL max() function query = session.createNativeQuery("select max(salary) from User_Info"); o1 = query.getSingleResult(); System.out.println("Maximum Salary :" + o1); System.out.println(); //Creating a Query by calling createNativeQuery() method to execute SQL avg() function query = session.createNativeQuery("select avg(salary) from User_Info"); o1 = query.getSingleResult(); System.out.println("Average Salary :" + o1); System.out.println(); //Creating a Query by calling createNativeQuery() method to execute SQL count() function query = session.createNativeQuery("select count(id) from User_Info"); o1 = query.getSingleResult(); System.out.println("Total count of IDs :" + o1); System.out.println(); //Creating a Query by calling createNativeQuery() method to execute SQL sum() function query = session.createNativeQuery("select sum(salary) from User_Info"); o1 = query.getSingleResult(); System.out.println("Total sum of salaries :" + o1); System.out.println(); //Committing the Transaction session.getTransaction().commit(); //closing the session session.close(); > > 

Adding JARs

We are going to add some JARs files to the build path of our Java project. These JARs make the Hibernate work with our database using a specific JDBC Driver for our particular database.

All these JARs are included in the folder named required(within our Hibernate installation folder). So, we need to add all the JARs in the required to our build path of our Java project.

Adding a configuration file

Next, we are going to add a configuration file to our project.

This configuration document ends with an extension .cfg.xml, and it is named as hibernate.cfg.xml.

• A database to which Hibernate connects to perform object-relational mapping and object persistence.
• The JDBC driver class for our specific database, used by the Hibernate to connect to the database.
• The username and password of database, using which the Hibernate connects to the database.
• An SQL Dialect, used by the Hibernate to instruct the database.
• A Entity/POJO class which will be mapped to a database table by Hibernate.
• A command to echo all the SQL commands which are executed by the Hibernate on stdout.
• The mapping entity class, used by Hibernate to correctly map the class(UserInfo) to a database table.

     oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@localhost:1521:XE system promila21 1 org.hibernate.dialect.Oracle10gDialect org.hibernate.cache.internal.NoCacheProvider true create   

Directory Structure of Hibernate Project

The picture above depicts how and where to arrange POJO/Entity(.java) file and the class(that calls the hibernate into action) in a specific directory structure.

Project Folder — HQLExecutingSQLQueries is the name of our Project and it is a top-level directory.

This project folder contains the main package of our project i.e. decodejava, which contains POJO/Entity class file i.e. UserInfo.java and the class that calls the hibernate into action i.e. Hiber.java.

Execution

When you execute the Hiber.java class, you will see the following output —

Hibernate: create sequence hibernate_sequence start with 1 increment by 1 Hibernate: create table USER_INFO (ID number(10,0) not null, First_Name varchar2(255 char), Salary number(10,0), primary key (ID)) Hibernate: select hibernate_sequence.nextval from dual Hibernate: select hibernate_sequence.nextval from dual Hibernate: select hibernate_sequence.nextval from dual Hibernate: insert into USER_INFO (First_Name, Salary, ID) values (?, ?, ?) Hibernate: insert into USER_INFO (First_Name, Salary, ID) values (?, ?, ?) Hibernate: insert into USER_INFO (First_Name, Salary, ID) values (?, ?, ?) Hibernate: select ID,First_Name from User_Info Retrieving values in multiple columns with property 'id' and 'firstName' Id : 1 First Name : First Id : 2 First Name : Second Id : 3 First Name : Third Hibernate: select min(salary) from User_Info Minimum Salary :20000 Hibernate: select max(salary) from User_Info Maximum Salary :40000 Hibernate: select avg(salary) from User_Info Average Salary :31666.6666666666666666666666666666666667 Hibernate: select count(id) from User_Info Total count of IDs :3 Hibernate: select sum(salary) from User_Info Total sum of salaries :95000

This output shows you all the SQL commands executed by the Hibernate within the database to create the tables and save three objects, retrieving multiple columns, executing of SQL queries max(), min(), sum(), count(), avg() functions.

After executing Hiber class, a tables will be created in the database with the name User_Info.

Additionally, let’s query the database and see what are the contents of this table.

This table shows you the objects of class UserInfo class mapped by Hibernate to a table named User_Info in the database.

Источник

Hibernate — Hibernate Query (HQL) примеры: SELECT, INSERT, UPDATE, DELETE

Hibernate может использовать свой мощный язык запросов Hibernate Query Language (HQL), который очень похож на родной SQL. В сравнении с SQL, HQL полностью объектно-ориентирован и использует понятия наследования, полиформизма и связывания.

HQL использует имя класса взамен имени таблицы, а также имя свойства вместо имени колонки!

Используемые технологии:

1. Описание задачи

Изучить примеры основных запросов: SELECT, INSERT, UPDATE, DELETE, которые выполняются с помощью HQL.

2. Структура проекта

Используется база данных и классы из Hibernate – быстрый старт. Пример приложения Hello World. Там же есть исходники. Рекомендую прочитать эту статью, чтобы было понятно что и откуда берется_вставляется.

3. Пример запроса HQL Select

Создаем запрос, который можно прочитать так: из сущности ContactEntity (маппинг на таблицу Contact ) взять строки, в которых колонка firstName = имяПараметра . Дальше параметру присваивается имя и выполняется запрос. Для БД из прошлого примера получим:

Так же можно указать параметр сразу:

Общее замечание

За исключением названий Java классов и свойств, запросы не чувствительны к регистру. Т.е. SeLect будет эквивалентен sELEct , но в то же время org.hibernate.eg.FOO не эквивалентен org.hibernate.eg.Foo , и foo.barSet не равен foo.BARSET .

Кстати многие заметят, что в SQL обычно используются команды в верхнем регистре. В случае работы с Java и Hibernate чаще встречается использование стиля Java (нижний регистр). Ребята из org.jboss акцентируют внимание, что они используют подход Java для работы с Hibernate.

Источник