How to use conditional sql query in hibernate

Question

Suppose I have UserDetails table with feilds:

UserId, RoleId, UserName, Password, Name, Mobile

and UserRole whit:

RoleId, RoleName, UserPage

I want to get certain information by joining two(or multiple tables) tables. I have query as:

SELECT ud.UserId,
    ud.RoleId,
    ud.UserName,
    ud.Password,
    ud.Name,
    ud.Mobile,
    ud.DateOfBirth,
    ur.RoleName,
    ur.UserPage    
FROM UserRole ur join UserDetails ud on ur.RoleId =ud.RoleId and UserName='user1';

With JdbcTemplate in spring I am able to get but With hibernate how to get returned values?

subro · Accepted Answer · 2016-08-26 09:56:38Z

1

I think your problem can be solved by using criteria in hibernate or use native SQL query in hibernate to fetch all the data from database.

answered Aug 26, 2016 at 9:56

subro

1142 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joe Over a year ago

can u give an example?

subro Over a year ago

String sql = "SELECT * FROM TESTEMPLOYEE"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(TestEmployee.class); List results = query.list(); But its is not recommended to use native sql in hibernate. instead you can use HQL . and Hibernate is ORM tool. its help in mapping java object to database and viceversa.. so you need to create pojo and map them properly

bhanu avinash · Accepted Answer · 2016-08-26 10:38:22Z

1

Both the ways you can use.

This is sql query:

String userName = 'user1'

createSQLQuery("SELECT ud.UserId,
    ud.RoleId,
    ud.UserName,
    ud.Password,
    ud.Name,
    ud.Mobile,
    ud.DateOfBirth,
    ur.RoleName,
    ur.UserPage    
FROM UserRole ur join UserDetails ud on ur.RoleId =ud.RoleId and UserName="+userName)

This is hql query:

FROM UserRole ur join fetch UserDetails ud  where ud.UserName='user1';

use pojo class names as table names and variables in pojo classes as column names

edited Aug 26, 2016 at 10:38

answered Aug 26, 2016 at 10:11

bhanu avinash

4822 silver badges16 bronze badges

3 Comments

Joe Over a year ago

that means i need to create pojo classes for all tables(which i think resource consuming). I can create pojo of returning value pojo but creating all is not good. Any other solution?

Manchikanti Aishwarya Over a year ago

Without creating pojo classes,how hibernate will works?

Joe Over a year ago

Actually I thought like jdbctemplate we can marshal it, so no need of pojo!

Collectives™ on Stack Overflow

How to use conditional sql query in hibernate

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related