Tuesday, May 6, 2014

Hibernate HQL CRUD Example

HQLTest.java


package com.pkm.commands;

import com.pkm.domains.User;
import com.pkm.utils.HibernateUtil;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class HQLTest {
    public static void main(String[] args) {
        Session session = null;
     Transaction transaction = null;
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            transaction = session.beginTransaction();
            
            /* INSERT QUERY */
            SQLQuery sQLQuery = session.createSQLQuery("INSERT INTO "
                    + "User(email, first_name, last_name, phone_number, area_code) "
                    + "Values(:email, :first_name, :last_name, :phone_number, :area_code)");
            sQLQuery.setParameter("email", "pritomkucse@gmail.com");
            sQLQuery.setParameter("first_name", "Pritom");
            sQLQuery.setParameter("last_name", "Kumar");
            sQLQuery.setParameter("phone_number", "01727499452");
            sQLQuery.setInteger("area_code", 88);
            int insertResult = sQLQuery.executeUpdate();
            System.out.println("Insert Result: " + insertResult);
            
            /* SELECT QUERY*/
            Query query = session.createQuery("SELECT MIN(id) FROM User");
            query.setMaxResults(1);
            List queryList = query.list();
            System.out.println(queryList);
            
            query = session.createQuery("FROM User");
            query.setMaxResults(5);
            query.setReadOnly(true);
            queryList = query.list();
            for(Object object : queryList) {
                User user = (User) object;
                System.out.println("Email: " + user.getEmail() + ", First Name: " 
                        + user.getFirstName() + ", Last Name: " + user.getLastName() 
                        + ", Phone Area: " + user.getAreaCode() 
                        + ", Phone Number: " + user.getPhoneNumber());
            }
            
            /* UPDATE QUERY */
            User user = (User) queryList.get(0);
            query = session.createQuery("UPDATE User SET email=:email WHERE id = :id");
            query.setParameter("id", user.getId());
            query.setParameter("email", "sdfsdfsdf@gmail.com");
            int updateResult = query.executeUpdate();
            System.out.println("Update Result: " + updateResult);
            
            /* DELETE QUERY */
            user = (User) queryList.get(1);
            query = session.createQuery("DELETE FROM User WHERE id = :id");
            query.setParameter("id", user.getId());
            updateResult = query.executeUpdate();
            System.out.println("Delete Result: " + updateResult);
            
            transaction.commit();
        } catch (Exception ex) {
            if(transaction != null) {
                transaction.rollback();
            }
            System.out.println("Error: " + ex.getMessage());
        } finally {
            if(session != null) {
                session.close();
            }
        }
    }
}


You have to include antlr.jar to use HQL in your hibernate program.

Data saved in database as follows:


The java entity class used for this example:


package com.pkm.domains;

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
public class User implements java.io.Serializable {   
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Long id;
    public void setId(Long id) {
        this.id = id;
    }
    public Long getId() {
        return this.id;
    }
    
    @Column(name = "email", unique = true, nullable = false, length = 100)
    private String email;
    public void setEmail(String email) {
        this.email = email;
    }
    public String getEmail() {
        return this.email;
    }
     
    @Column(name = "first_name", nullable = false)
    private String firstName;
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getFirstName() {
        return this.firstName;
    }
     
    @Column(name = "last_name", nullable = true)
    private String lastName;
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public String getLastName() {
        return this.lastName;
    }
     
    @Column(name = "phone_number", nullable = false)
    private String phoneNumber;
    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }
    public String getPhoneNumber() {
        return this.phoneNumber;
    }
     
    @Column(name = "area_code", nullable = true)
    private int areaCode;
    public void setAreaCode(int areaCode) {
        this.areaCode = areaCode;
    }
    public int getAreaCode() {
        return this.areaCode;
    }
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "date_updated", unique = false, nullable = true, length = 100)
    private Date dateUpdated;
    public void setDateUpdated(Date dateUpdated) {
        this.dateUpdated = dateUpdated;
    }
    public Date getDateUpdated() {
        return this.dateUpdated;
    }
    
    public User() {
        this.dateUpdated = new Date();        
    }
}

Output as following


Hibernate: INSERT INTO User(email, first_name, last_name, phone_number, area_code) Values(?, ?, ?, ?, ?)
Insert Result: 1
Hibernate: select min(user0_.id) as col_0_0_ from User user0_ limit ?
[14]
Hibernate: select user0_.id as id0_, user0_.date_updated as date2_0_, user0_.email as email0_, user0_.first_name as first4_0_, user0_.last_name as last5_0_, user0_.phone_number as phone6_0_, user0_.area_code as area7_0_ from User user0_ limit ?
Email: sdfsdfsdf@gmail.com, First Name: Pritom, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Email: 1399089957760@gmail.com, First Name: First name changed, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Email: 1399089987865@gmail.com, First Name: Pritom, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Email: 1399089987945@gmail.com, First Name: First name changed, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Email: 1399090021059@gmail.com, First Name: Pritom, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Hibernate: update User set email=? where id=?
Update Result: 1
Hibernate: delete from User where id=?
Delete Result: 1

No comments:

Post a Comment