Fetch records which columms as rows using hibernate criteria

I have a table with columns as rows. Now I want to apply restriction on multiple rows with "and" condition.how can we do this using hibernate criteria?

Example :Employee table

Pk  ColumnName.   Value
1.          Empid.               10
1        EmpName.           Sachin
1.        Empsalary.          10,000
2         Empid.               20
2       EmpName.           Dhoni
2.        Empsalary.          8000

So now i want to fetch pk's which have EmpName as sachin and empsal as 10,000

So it should return pk as 1.

Please check image attached

1 answer

  • answered 2018-04-17 09:20 C T Mithun

    You can fetch the primary key list of data from DB using criteria query. Follow the below code snippet:

    EntityManager enm = sessFact.createEntityManager();
        CriteriaBuilder en = sessFact.getCriteriaBuilder();
        CriteriaQuery<Integer> qu = en.createQuery(Integer.class);
        Root<Employee> ro = qu.from(Employee.class);
        qu.select(ro.get("id"));
        qu.where(en.like(ro.get("empName"), "Sachin%"), en.equal(ro.get("empSalary"), 10000 ));
        List<Integer> list= enm.createQuery(qu).getResultList();
        for (Integer name : list) {
           System.out.println("PK: " + name);
        }
    

    in the where clause you can remove the "%" and give the exact search e.g., qu.where(en.like(ro.get("empName"), "Sachin"), en.equal(ro.get("empSalary"), 10000 ));

    The equivalent SQL log generated by hibernate is in below:

    Hibernate: select employee0_.id as col_0_0_ from employee employee0_ where (employee0_.emp_name like ?) and employee0_.emp_salary=10000

    FYI, The referred DB Table structure is as in below:

    CREATE TABLE employee (
    id int(11) NOT NULL AUTO_INCREMENT,
    emp_name varchar(100) DEFAULT NULL,
    emp_address varchar(500) DEFAULT NULL,
    emp_mobile_nos varchar(100) DEFAULT NULL, emp_salary int(12) DEFAULT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    Please find the complete code related to Hibernate in GIT here - https://github.com/ctmithun/HibernateExample/blob/master/Hibernate5Annotation/src/net/roseindia/GetAllData.java