April 27 2015

Hibernate createsqlquery example

Hibernate createsqlquery example

Hibernate allows us to write the queries in native sql. You can execute sql queries from the session with the createSQLQuery() method on the Session interface. The createSQLQuery is used to create a SQL query using the database specific features.

public SQLQuery createSQLQuery(String sqlString) throws HibernateException

Scalar queries

SQL query is to get a list of scalars (values).By default SQL result set rows are returned as Object array.

Example

Table


CREATE TABLE `student` (
 `STUDENT_ID` int(11) NOT NULL,
 `STUDENT_NAME` varchar(45) DEFAULT NULL,
 `FATHER_NAME` varchar(45) DEFAULT NULL,
 `DATE_OF_BIRTH` date DEFAULT NULL,
 `COURSE` varchar(45) DEFAULT NULL,
 `ACTIVE` int(2) DEFAULT NULL,
 PRIMARY KEY (`STUDENT_ID`)
) ;

Test Class : ScalarQueries.java


package com.answersz.dao;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

// hibernate createsqlquery example
public class ScalarQueries {

public static void main(String[] args) {

SessionFactory factory = new AnnotationConfiguration().configure()
 .buildSessionFactory();

Session session = factory.openSession();

System.out.println(" Hibernate createsqlquery example ");

 List<Object[]> results = session.createSQLQuery(
 "select * from student where course='MTECH'").list();

for (Object[] stud : results) {
 System.out.println();
 System.out.println("Student ID : " + stud[0]);
 System.out.println("First Name : " + stud[1]);
 System.out.println("Father Name : " + stud[2]);
 System.out.println("DOB : " + stud[3]);
 }

session.close();
 }
}

Output

Hibernate createsqlquery example
Hibernate: select * from student where course='MTECH'

Student ID : 204
First Name : MARK
Father Name : Anderson
DOB : 1988-04-23

Student ID : 304
First Name : Lewis
Father Name : Paul
DOB : 1986-02-02

Entity queries

Hibernate can also transform SQL results into entity classes using addEntity().If the column names and class properties mappings are same you can write the above query using addEntity.

Example

Entity: Student.java

package com.answersz.dao;

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "student")
public class Student {

@Id
 @Column(name = "STUDENT_ID")
 private Integer studentId;

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

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

@Column(name = "DATE_OF_BIRTH")
 private Date dateofBirth;

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

//getters and setters

}

Test class: EntityQueries.java

package com.answersz.dao;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

// hibernate createsqlquery example
public class EntityQueries {

public static void main(String[] args) {

SessionFactory factory = new AnnotationConfiguration().configure()
 .buildSessionFactory();

Session session = factory.openSession();
 System.out.println(" Hibernate createsqlquery example ");

 List<Student> results = (List<Student>) session
 .createSQLQuery("select * from student where course='MTECH'")
 .addEntity(Student.class).list();

for (Student student : results) {
 System.out.println();
 System.out.println("Student ID : " + student.getStudentId());
 System.out.println("First Name : " + student.getStudentName());
 System.out.println("Father Name : " + student.getFatherName());
 System.out.println("DOB : " + student.getDateofBirth());
 }
 session.close();
 }

}

Output

Hibernate createsqlquery example
Hibernate: select * from student where course='MTECH'
Hibernate: select studentmar0_.STUDENT_ID as STUDENT5_1_, studentmar0_.STUDENT_MARKS_ID as STUDENT1_1_, studentmar0_.STUDENT_MARKS_ID as STUDENT1_1_0_, studentmar0_.COURSE as COURSE1_0_, studentmar0_.OBTAINED_MARKS as OBTAINED3_1_0_, studentmar0_.STUDENT_ID as STUDENT5_1_0_, studentmar0_.TOTAL_MARKS as TOTAL4_1_0_ from student_marks studentmar0_ where studentmar0_.STUDENT_ID=?
Hibernate: select studentmar0_.STUDENT_ID as STUDENT5_1_, studentmar0_.STUDENT_MARKS_ID as STUDENT1_1_, studentmar0_.STUDENT_MARKS_ID as STUDENT1_1_0_, studentmar0_.COURSE as COURSE1_0_, studentmar0_.OBTAINED_MARKS as OBTAINED3_1_0_, studentmar0_.STUDENT_ID as STUDENT5_1_0_, studentmar0_.TOTAL_MARKS as TOTAL4_1_0_ from student_marks studentmar0_ where studentmar0_.STUDENT_ID=?

Student ID : 204
First Name : MARK
Father Name : Anderson
DOB : 1988-04-23 00:00:00.0

Student ID : 304
First Name : Lewis
Father Name : Paul
DOB : 1986-02-02 00:00:00.0


Copyright © 2014. All rights reserved.

Posted April 27, 2015 by answersz in category Hibernate

Leave a Comment

Your email address will not be published. Required fields are marked *

*