检索语句的使用

**

检索语句的使用

**
1.简单查询
@Test
public void testQuery(){
System.out.println(“test--------”);
User user = null;
SessionFactory sessionFactory = SessionFactoryUtil.getSessionFactory();
Session session = null;
session = sessionFactory.openSession();
org.hibernate.Query query =session.createQuery(“from User”);
List users = query.list();
for (int i=0;i<users.size();i++){
user = (User)users.get(i);
System.out.println(user.getUid()+" “+user.getUsername()+” “+user.getPassword()+” “+user.getAge()+” "+user.getGender());

}

}
Sql语句：

Hibernate:
select
tuser0_.uid as uid0_,
tuser0_.username as username0_,
tuser0_.password as password0_,
tuser0_.age as age0_,
tuser0_.gender as gender0_,
from
hibernate.t_user tuser0_
结果：

2.属性查询
查询所有人的姓名：
@Test
public void testQueryUserName(){
System.out.println(“test-------”);
String username = null;
SessionFactory sessionFactory = SessionFactoryUtil.getSessionFactory();
Session session = null;
session = sessionFactory.openSession();
org.hibernate.Query query =session.createQuery(“select user.username from User user “);
List usernames = query.list();
for (int i=0;i<usernames.size();i++){
username = (String)usernames.get(i);
System.out.println(1+i+”–”+username);
}
}

Sql语句：

Hibernate:
select
tuser0_.username as col_0_0_,
from
hibernate.t_user tuser0_
结果：

查询id和姓名：
@Test
public void testQueryIDName(){
System.out.println(“test-------”);
SessionFactory sessionFactory = SessionFactoryUtil.getSessionFactory();
Session session = null;
session = sessionFactory.openSession();
org.hibernate.Query query =session.createQuery("select user.uid,user.username from User user “);
List usernames = query.list();
for (int i=0;i<usernames.size();i++){
Object[] obj = (Object[]) usernames.get(i);
System.out.println(obj[0]+” "+obj[1]);
}
}

Sql语句：

Hibernate:
select
tuser0_.userid as col_0_0_,
tuser0_.username as col_1_0_,
from
hibernate.t_user tuser0_

结果：

3.实体化查询
@Test
public void testQueryNew(){
System.out.println(“test-------”);
TUserEntity user = null;
SessionFactory sessionFactory = SessionFactoryUtil.getSessionFactory();
Session session = null;
session = sessionFactory.openSession();
org.hibernate.Query query =session.createQuery(“select new User(user.uid,user.username,user.password,user.age,user.gender) from User as user “);
List users = query.list();
for (int i=0;i<users.size();i++){
user = (User) users.get(i);
System.out.println(user.getUid()+” “+user.getUsername()+” " +user.getPassword()+” “+user.getAge()+” "+user.getGender());
}
}

Sql语句：

Hibernate:
select
tuser0_.userid as col_0_0_,
tuser0_.username as col_1_0_,
tuser0_.password as col_2_0_,
tuser0_.age as col_3_0_,
tuser0_.gender as col_4_0_,
from
hibernate.t_user tuser0_

结果：

4.统计查询
不重复记录数统计：
@Test
public void testCount(){
System.out.println(“test------”);
SessionFactory sessionFactory = SessionFactoryUtil.getSessionFactory();
Session session = null;
session = sessionFactory.openSession();
org.hibernate.Query query =session.createQuery("select distinct count(*) from User ");
Object count = (Object)query.uniqueResult();
System.out.println(“共有”+count+“条记录”);
}

Sql语句：

Hibernate:
select
distinct count(*) as col_0_0_,
from
hibernate.t_user tuser0_
共有5条记录

结果：

统计平均数和最大值：
@Test
public void testAvg(){
System.out.println(“test-------”);
TUserEntity user = null;
SessionFactory sessionFactory = SessionFactoryUtil.getSessionFactory();
Session session = null;
session = sessionFactory.openSession();
org.hibernate.Query query =session.createQuery(“select avg(user.age),max(user.age)from User as user “);
// String[] number = new String[6];
List users = query.list();
for (int i=0;i<users.size();i++){
Object obj[] = (Object[]) users.get(i);
System.out.println(obj[0]+”–”+obj[1]);
}
}

Sql语句：

Hibernate:
select
avg(tuser0_.age) as col_0_0_,
max(tuser0_.age) as col_1_0_,
from
hibernate.t_user tuser0_

@Test
public void testGroupQuery(){
System.out.println(“test--------”);
TUserEntity user = null;
SessionFactory sessionFactory = SessionFactoryUtil.getSessionFactory();
Session session = null;
session = sessionFactory.openSession();
org.hibernate.Query query =session.createQuery(“select user.gender,avg(user.age) from User as user group by user.gender having avg(user.age) > 17”);

List users = query.list();
for (int i=0;i<users.size();i++){
    Object[] obj = (Object[]) users.get(i);
    DecimalFormat decimalFormat = new DecimalFormat("#.00");
    System.out.println(obj[0]+" "+decimalFormat.format(obj[1]));

}

}

Sql语句：

Hibernate:
select
tuser0_.gender as col_0_0_,
avg(tuser0_.age) as col_1_0_,
from
hibernate.t_user tuser0_
group by
tuser0_.gender
having
avg(tuser0_.age)>17

结果：

5.子查询
Where子查询：
@Test
public void testById(){
System.out.println(“test------”);
User user = null;
SessionFactory sessionFactory = SessionFactoryUtil.getSessionFactory();
Session session = null;
session = sessionFactory.openSession();
org.hibernate.Query query =session.createQuery(“from User as user where user.uid=2”);
List users = query.list();
for (int i=0;i<users.size();i++){
user = (User)users.get(i);
System.out.println(user.getUid()+" “+user.getUsername()+” “+user.getPassword()+” "+user.getGender());

}

}

SQL语句：

Hibernate:
select
tuser0_.uid as uid0_,
tuser0_.username as username0_,
tuser0_.password as password0_,
tuser0_.age as age0_,
tuser0_.gender as gender0_,
from
hibernate.t_user tuser0_
where
tuser0_.uid=2

结果：