Query object using Criteria Hibernate

Question

I am trying out a sample in Hibernate using Criteria. Below are my tables :

CREATE TABLE  test.college (
  collegeId int(11) NOT NULL AUTO_INCREMENT,
  collegeName varchar(255) DEFAULT NULL,
  PRIMARY KEY (collegeId)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

and

CREATE TABLE  test.student (
  studentId int(11) NOT NULL AUTO_INCREMENT,
  studentName varchar(255) DEFAULT NULL,
  college_id int(11) DEFAULT NULL,
  PRIMARY KEY (studentId),
  KEY FKF3371A1B11FE0A03 (college_id),
  CONSTRAINT FKF3371A1B11FE0A03 FOREIGN KEY (college_id) REFERENCES college (collegeId)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

So my entity classes are:

College.Java

package com.hibernate.onetomany;

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class College {

    private int collegeId;
    private String collegeName;
    private List<Student> students;

    @Id
    @GeneratedValue
    public int getCollegeId() {
        return collegeId;
    }
    public void setCollegeId(int collegeId) {
        this.collegeId = collegeId;
    }
    public String getCollegeName() {
        return collegeName;
    }
    public void setCollegeName(String collegeName) {
        this.collegeName = collegeName;
    }
    @OneToMany(targetEntity=Student.class,mappedBy="college",cascade=CascadeType.ALL,fetch=FetchType.LAZY)
    public List<Student> getStudents() {
        return students;
    }
    public void setStudents(List<Student> students) {
        this.students = students;
    }
}

And my Student.java

package com.hibernate.onetomany;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;


@Entity
public class Student {

    private int studentId;
    private String studentName;
    private College college;

    @Id
    @GeneratedValue
    public int getStudentId() {
        return studentId;
    }
    public void setStudentId(int studentId) {
        this.studentId = studentId;
    }
    public String getStudentName() {
        return studentName;
    }
    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }
    @ManyToOne
    @JoinColumn(name="college_id")
    public College getCollege() {
        return college;
    }
    public void setCollege(College college) {
        this.college = college;
    }

}

In the above example i have used one to many association.

Below is my main method:

TestStudent.java

package com.hibernate.onetomany;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Restrictions;

public class TestStudent {

    public static void main(String[] args){
            readRecords();
    }

    private static void readRecords() {
        SessionFactory factory = new Configuration().configure().buildSessionFactory(); 
        Session session = factory.openSession();
        session.beginTransaction();

        //Criteria cr = session.createCriteria(College.class,"college").createAlias("college.collegeId", "abc", JoinType.FULL_JOIN);
        Criteria cr = session.createCriteria(College.class).add(Restrictions.eq("collegeId", 2));


        List<College> collegeList = cr.list();

        for(College college : collegeList){
            System.out.println("CollegeID : " + college.getCollegeId());
            System.out.println("CollegeName : " + college.getCollegeName());
            List<Student> studentList = college.getStudents();
            for(Student student : studentList){
                System.out.println("StudentID : " + student.getStudentId());
                System.out.println("StudentName : " + student.getStudentName());
            }
        }
    }
}

And my hibernate.cfg.xml

<?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>
        <!-- Database connection settings -->
        <property name="connection.driver_class">
            com.mysql.jdbc.Driver</property>
        <property name="connection.url">
            jdbc:mysql://localhost:3306/world</property>
        <property name="connection.username">user1</property>
        <property name="connection.password">password</property>
        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>
        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
        <mapping class="com.hibernate.onetomany.College" /> 
        <mapping class="com.hibernate.onetomany.Student" />
    </session-factory>
</hibernate-configuration>

Now the above example runs smooth and perfect. But i have a small requirement. I have to pass a filter condition that is: Get the result set where studentName is : ABC So i want to filter the resultset using name of student.

In short i want to use the below piece of code to get the result: Criteria cr = session.createCriteria(College.class).add(Restrictions.eq("studentName", "ABC"));

How can i achieve the above requirement using the same OneToMany approach?

Looking forward to your solutions. Thanks in advance.

Answer 1

You can use @NamedQuery or @NamedNativeQuery :

@Entity
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "college.findByStudentName",
        query = "SELECT * from test.college WHERE collegeId IN (SELECT college_id from test.student WHERE studentName = (:name))",
        resultClass = College.class
    )
)}
public class College {

    ...

}

EDIT :

here's how to use named queries :

List colleges = session.getNamedQuery("college.findByStudentName")
        .setString("name", "Linda Berry")
        .list();