Hibernate Native SQL

June 2, 2017

12:56 pm

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package corehibernatedemo;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import model.Person;
import model.Student;
import model.Users;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;

/**
 *
 * @author Shailesh Sonare
 */
public class CoreHibernateDemo {
    
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws ParseException {
        // TODO code application logic here
        /*
        Configuration cfg = new Configuration();
        cfg.configure("hibernate.cfg.xml");
        
        StandardServiceRegistryBuilder ssrb = new StandardServiceRegistryBuilder().applySettings(cfg.getProperties());
        
        SessionFactory factory = cfg.buildSessionFactory(ssrb.build());
        
        Session session = factory.openSession();
        
        Transaction t = session.beginTransaction();
        
        List users = session.createQuery("from Users").list();
        System.out.println("done...");
        
        for (Iterator iterator = users.iterator(); iterator.hasNext();) {
            Users e = (Users) iterator.next();
            System.out.println(e.getFirstName());
        }
        
        
        
        Date bdate = new SimpleDateFormat("yyyy-MM-dd").parse("1992-11-15");
        
        Users  usr = new Users("Sunita", "Sonare", bdate);
        
        String hql = "UPDATE Users SET dob = :dob WHERE id = :id";
        
        Query query = session.createQuery(hql);
        query.setParameter("dob", bdate);
        query.setParameter("id", 4);
        query.executeUpdate();
        
        //session.save(usr);
        t.commit();
        session.close();
        System.out.println("DOne....");
        */
        
        //Student s = new Student();
        
        Configuration cfg = new Configuration().configure("hibernate.cfg.xml");
        
        StandardServiceRegistryBuilder ssrb = new StandardServiceRegistryBuilder().applySettings(cfg.getProperties());
        
        SessionFactory factory = cfg.buildSessionFactory(ssrb.build());
        
        Session session = factory.openSession();
        
//        String hql = "FROM Users";
//        List<Users> list = session.createQuery(hql).list();

        String sql = "SELECT first_name, last_name, dob FROM users";
        
        SQLQuery query = session.createSQLQuery(sql);
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); // important when you are using alias <key><value> pair
//        List<Object[]> rows = query.list();
//        
//        for(Object[] row : rows){
//            
//            Users u = new Users(row[0].toString(), row[1].toString(), new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(row[2].toString()));
//            System.out.println(u);
//            System.out.println("--------------");
//        }

        List list = query.list();
        
        for(Object obj : list){
            Map map = (Map)obj;
            System.out.println("" + map.get("first_name"));
            System.out.println("" + map.get("last_name"));
            System.out.println("" + map.get("dob"));
            System.out.println("-------------------------");
        }
    }
    
}

Users.java

package model;
// Generated 30 May, 2017 5:20:27 PM by Hibernate Tools 4.3.1


import java.util.Date;

/**
 * Users generated by hbm2java
 */
public class Users  implements java.io.Serializable {


     private Integer id;
     private String firstName;
     private String lastName;
     private Date dob;

    public Users() {
    }

    public Users(String firstName, String lastName, Date dob) {
       this.firstName = firstName;
       this.lastName = lastName;
       this.dob = dob;
    }
   
    public Integer getId() {
        return this.id;
    }
    
    public void setId(Integer id) {
        this.id = id;
    }
    public String getFirstName() {
        return this.firstName;
    }
    
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return this.lastName;
    }
    
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public Date getDob() {
        return this.dob;
    }
    
    public void setDob(Date dob) {
        this.dob = dob;
    }

    @Override
    public String toString() {
        return "Users{" + "id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", dob=" + dob + '}';
    }

}