package Usertest;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.query.Query;
import org.junit.Test;
import text.User;
public class Hql {
@Test
public void Query1(){//搜索User中的所有属性输出姓名
// TODO Auto-generated method stub
SessionFactory sf = null;
Session session = null;
Transaction ts = null;
try {
sf = HibernateUtil.getSessionFactory();
session = sf.getCurrentSession();
ts = session.beginTransaction();
User user=new User();
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(i+"--"+user.getUsername());
}
ts.commit();
} catch (HibernateException e) {
// TODO Auto-generated catch block
if(ts != null)
{
ts.rollback();
}
e.printStackTrace();
}
}
@Test
//搜索多个属性返回数组
public void Queryattributes(){
// TODO Auto-generated method stub
SessionFactory sf = null;
Session session = null;
Transaction ts = null;
try {
sf = HibernateUtil.getSessionFactory();
session = sf.getCurrentSession();
ts = session.beginTransaction();
User user=new User();
Query query = session.createQuery("select u.username, u.password from User as u");
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]);
}
ts.commit();
} catch (HibernateException e) {
// TODO Auto-generated catch block
if(ts != null)
{
ts.rollback();
}
e.printStackTrace();
}
}
@Test
//指定新建一个对象传回,注意,此处需要在实体类中创建含参构造方法和无参构造方法,无参构造方法是为了其他语句使用,因为系统默认是调用无参的构造方法
public void Querynewattributes(){
// TODO Auto-generated method stub
SessionFactory sf = null;
Session session = null;
Transaction ts = null;
try {
sf = HibernateUtil.getSessionFactory();
session = sf.getCurrentSession();
ts = session.beginTransaction();
Query query = session.createQuery("select new User(u.username,u.password) from User as u");
List users = query.list();
for(int i=0;i<users.size();i++)
{
User user =(User)users.get(i);
System.out.println(user.getUsername()+"的密码为:"+user.getPassword());
}
ts.commit();
} catch (HibernateException e) {
// TODO Auto-generated catch block
if(ts != null)
{
ts.rollback();
}
e.printStackTrace();
}
}
@Test
public void countattributes(){//返回共有多少条记录,平均年龄以及最大年龄
// TODO Auto-generated method stub
SessionFactory sf = null;
Session session = null;
Transaction ts = null;
try {
sf = HibernateUtil.getSessionFactory();
session = sf.getCurrentSession();
ts = session.beginTransaction();
Query query = session.createQuery("select count(*) from User");
Object count = (Object)query.uniqueResult();
System.out.println("共有"+count+"条记录");
query = session.createQuery("select avg(u.age) from User u");
Number average =(Number)query.uniqueResult();
System.out.println("平均年龄为:"+average);
query = session.createQuery("select max(u.age) from User u");
Number max =(Number)query.uniqueResult();
System.out.println("最大年龄为:"+max);
ts.commit();
} catch (HibernateException e) {
// TODO Auto-generated catch block
if(ts != null)
{
ts.rollback();
}
e.printStackTrace();
}
}
@Test
//提取第5-9个对象
public void query(){
// TODO Auto-generated method stub
SessionFactory sf = null;
Session session = null;
Transaction ts = null;
try {
sf = HibernateUtil.getSessionFactory();
session = sf.getCurrentSession();
ts = session.beginTransaction();
Query query=session.createQuery("from User");
query.setFirstResult(4);
query.setMaxResults(5);
List result=query.list();
for(int i=0;i<result.size();i++)
{
User user =(User)result.get(i);
System.out.println( user.getId()+" "+user.getUsername()+"--"+user.getPassword());
}
ts.commit();
} catch (HibernateException e) {
// TODO Auto-generated catch block
if(ts != null)
{
ts.rollback();
}
e.printStackTrace();
}
}
@Test
//嵌套子查询
public void doublequery(){
// TODO Auto-generated method stub
SessionFactory sf = null;
Session session = null;
Transaction ts = null;
try {
sf = HibernateUtil.getSessionFactory();
session = sf.getCurrentSession();
ts = session.beginTransaction();
Query query = session.createQuery("from User u where u.age>(select avg(age) from User)");
List users = query.list();
for(int i=0;i<users.size();i++)
{
User user =(User)users.get(i);
System.out.println(user.getId()+"--姓名:"+user.getUsername()+"--年龄"+user.getAge());
}
ts.commit();
} catch (HibernateException e) {
// TODO Auto-generated catch block
if(ts != null)
{
ts.rollback();
}
e.printStackTrace();
}
}
}
注意,创建新对象需要在实体类中创建含参构造方法和无参构造方法,无参构造方法是为了其他语句使用,因为系统默认是调用无参的构造方法
public User(){}
public User(int id, String username, String password, String gender, int age) {
this.id = id;
this.username = username;
this.password = password;
this.gender = gender;
this.age = age;
}
搜索user中的所有属性输出姓名
搜索多个属性返回数组
统计总的记录数以及年龄的最大值和平均值输出结果
嵌套子查询
提取第5-9个对象结果
就简单运行这几个啦~