hibernate的hql语句
Hql 是hibernate query language即hibernate查询语句
SQL结构化查询语句 面向对象
From 对象 where 属性
Hql基本语法:
执行hql语句步骤
实现动态查询
test测试类
运行结果
分页查询
uniqueResult() 获取唯一对象
setFirstResult() 设置从第几条开始
setMaxResult() 设置读取最大记录数
Mysql==>limit oracle===>子查询 rownum sqlservice ====》top rownum
实现代码================================================================
test测试类
Hql 是hibernate query language即hibernate查询语句
SQL结构化查询语句 面向对象
From 对象 where 属性
Hql基本语法:
执行hql语句步骤
1.获取session 2.编写hql语句 3.创建query对象 4.执行查询,得到查询结果
代码演示===================================================
package cn.hibernate.dao;
import java.util.Iterator;
import java.util.List;
import org.hibernate.query.Query;
import cn.hibernate.common.HibernateSessionFactory;
import cn.hibernate.entity.Student;
/**
* 数据访问层
* @author Administrator
*
*/
public class StudentHqlDao {
/**
* 查询语句
* @return
*/
public List<Student> findStu(){
// from Student where id = ?
// from Student = select * from student
String hql = "from Student where id = :id"; //from + 实体类 注意大小写
Query q= HibernateSessionFactory.getSession().createQuery(hql);
// q.setInteger(0, 3); //占位符 ? query 对象相当月preparstream类但是从0开始
// 占位符 ?和 : 不能混用 只能选一种
q.setInteger("id", 3); // 占位符 :
return q.list();
}
/**
* 常用查询语句
* @param stu
* @return
*/
public List<Student> findStu(Student stu){
//常用参数stu对象来设置占位符对象 注意用这种方法 需要占位符名称和对象属性名称一致
String hql = "from Student stu where stu.id = :id and stu.name = :name";
Query q= HibernateSessionFactory.getSession().createQuery(hql);
q.setProperties(stu);
return q.list();
}
/**
* 变异查询语句
* @param params
* @return
*/
public List<Student> findStu(Object[] params){
//这种参数类似preparstream 用数组来循环占位符方式
String hql = "from Student stu where stu.id = :id and stu.name = :name";
Query q= HibernateSessionFactory.getSession().createQuery(hql);
for(int i = 0; i < params.length; i++){
q.setParameter(i, params[i]);
}
return q.list();
}
/**
* iterate查询
* @return
*/
public Iterator<Student> iteratorTest(){
String hql = "from Student";
Query q= HibernateSessionFactory.getSession().createQuery(hql);
return q.iterate();
}
}
package cn.hibernate.biz;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Transaction;
import cn.hibernate.common.HibernateSessionFactory;
import cn.hibernate.dao.StudentHqlDao;
import cn.hibernate.entity.Student;
/**
* 业务处理层
* @author Administrator
*
*/
public class StudentHqlBiz {
StudentHqlDao dao = new StudentHqlDao();
public List<Student> findStus(Student stu){
Transaction tx = null;
List<Student> stus = new ArrayList<Student>();
try {
tx = HibernateSessionFactory.getSession().beginTransaction();
stus = dao.findStu(stu);
tx.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return stus;
}
public Iterator<Student> iterateTest(){
Transaction tx = null;
Iterator<Student> it = null;
try {
tx = HibernateSessionFactory.getSession().beginTransaction();
it = dao.iteratorTest();
while(it.hasNext()){
Student stu = it.next();
System.out.println(stu.getName()+";;;"+stu.getClazz());
}//新增了循环语句因为在测试类报错说是会话关闭
/**
* 和缓存有关 二级缓存,状态是一级缓存 在session中对查询效率意义不大
* 一旦会话session关闭 就没有缓存一级的了
* 二级缓存 是可以共享如果先前有人查询出来 再有人查询可以直接使用的
*/
tx.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return it;
}
}
package cn.hibernate.test;
import java.util.Iterator;
import java.util.List;
import cn.hibernate.biz.StudentBiz;
import cn.hibernate.biz.StudentHqlBiz;
import cn.hibernate.entity.Student;
public class Test {
public static void main(String[] args) {
// Student stu = new Student();
// stu.setId(3);
// stu.setName("xiugai");
StudentHqlBiz biz = new StudentHqlBiz ();
Iterator<Student> it= biz.iterateTest();//iterate()查询只查询id 主键字段
while(it.hasNext()){
Student stu = it.next();
System.out.println(stu.getName());
}// 报错 session已经关闭了
/*List<Student> stus = biz.findStus(stu);
for (Student stu1 : stus) {
System.out.println(stu1.getClazz()+";;"+stu1.getName());
}*/
}
}
实现动态查询
当占位符个数不确定时使用的拼接字符 实现动态查询功能
package cn.hibernate.dao;
import java.util.List;
import java.util.Map;
import cn.hibernate.common.HibernateSessionFactory;
import cn.hibernate.entity.Student;
/**
* 实现动态查询 的数据访问层
* @author Administrator
*
*/
public class StudentDynamicsDao {
public List<Student> findStu(String hql,Student stu){
return HibernateSessionFactory.getSession()
.createQuery(hql)
.setProperties(stu)
.list();
}
/**
* 修改后---连表查询时候 不一定 表和对象属性一致的时候用map参数
* @param hql
* @param stu
* @return
*/
public List<Student> findStu(String hql,Map stu){
return HibernateSessionFactory.getSession()
.createQuery(hql)
.setProperties(stu)
.list();
}
}
package cn.hibernate.biz;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.hibernate.Transaction;
import cn.hibernate.common.HibernateSessionFactory;
import cn.hibernate.dao.StudentDynamicsDao;
import cn.hibernate.entity.Student;
/**
* 动态查询 业务处理层
* @author Administrator
*
*/
public class StudentDynamicsBiz {
StudentDynamicsDao dao = new StudentDynamicsDao();
public List<Student> findStuByConditions(Map stu){
List<Student> stus = new ArrayList<Student>();
// StringBuilder hql=new StringBuilder("from Student stu where 1=1");
// 判断参数stu
// 方式1
/*if (stu != null) {
if(stu.get("name") != null){
hql.append(" and stu.name = :name" );
}
if(stu.get("clazz") != null){
hql.append(" and stu.clazz = :clazz" );
}
if(stu.get("score") != null){
hql.append(" and stu.score = :score ");
}
}*/
// 方式2
StringBuilder hql=new StringBuilder("from Student stu ");
// 判断参数stu
if(stu != null){
boolean isFirst = true;//是否是第一个标志
if(stu.get("clazz") != null){
if(isFirst){
hql.append(" where ");
isFirst = false;
}else {
hql.append(" and ");
}
hql.append(" stu.clazz = :clazz ");
}
if(stu.get("name") != null){
if(isFirst){
hql.append(" where ");
isFirst = false;
}else {
hql.append(" and ");
}
hql.append(" stu.name = :name ");
}
if(stu.get("score") != null){
if(isFirst){
hql.append(" where ");
isFirst = false;
}else {
hql.append(" and ");
}
hql.append(" stu.score = :score ");
}
}
Transaction tx = null;
try {
tx = HibernateSessionFactory.getSession().beginTransaction();
stus = dao.findStu(hql.toString(), stu);
tx.commit();
} catch (Exception e) {
if(tx!=null){
tx.rollback();
}
}
return stus;
}
}
test测试类
package cn.hibernate.test;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import cn.hibernate.biz.StudentBiz;
import cn.hibernate.biz.StudentDynamicsBiz;
import cn.hibernate.biz.StudentHqlBiz;
import cn.hibernate.entity.Student;
public class Test {
public static void main(String[] args) {
// test1();
StudentDynamicsBiz biz = new StudentDynamicsBiz();
Map stu = new HashMap();
stu.put("clazz", "java");
List<Student> stus = biz.findStuByConditions(stu);
for (Student stu1 : stus) {
System.out.println(stu1.getClazz()+":;;"+stu1.getName());
}
}
public static void test1() {
// Student stu = new Student();
// stu.setId(3);
// stu.setName("xiugai");
StudentHqlBiz biz = new StudentHqlBiz();
Iterator<Student> it = biz.iterateTest();// iterate()查询只查询id 主键字段
while (it.hasNext()) {
Student stu = it.next();
System.out.println(stu.getName());
}// 报错 session已经关闭了
/*
* List<Student> stus = biz.findStus(stu); for (Student stu1 : stus) {
* System.out.println(stu1.getClazz()+";;"+stu1.getName()); }
*/
}
}
运行结果
Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.clazz as clazz3_0_, student0_.score as score4_0_ from student student0_ where student0_.clazz=?
java:;;修改姓名
java:;;李四
java:;;merge2
java:;;王五
java:;;merge3
java:;;xiugai
分页查询
uniqueResult() 获取唯一对象
setFirstResult() 设置从第几条开始
setMaxResult() 设置读取最大记录数
Mysql==>limit oracle===>子查询 rownum sqlservice ====》top rownum
实现代码================================================================
package cn.hibernate.dao;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
import org.hibernate.HibernateException;
import cn.hibernate.common.HibernateSessionFactory;
import cn.hibernate.entity.Student;
/**
* 分页查询
* @author Administrator
*
*/
public class StudentPageDao {
/**
*
* @param hql
* @param stu
* @param page 表示第几页
* @param size 每页几条数据 容量
* @return
*/
@SuppressWarnings("unchecked")
public List<Student> findStu(String hql,Map stu, int page,int size){
//假设从page第2页开始读 每页size大小3条数据 那么读取数据从第(2-1)*3=3开始 从0计算的
return HibernateSessionFactory.getSession()
.createQuery("from Student ")
.setProperties(stu)
.setFirstResult((page-1)*size) //从第几条开始
.setMaxResults(size) //读取到的最大容量Size
.list(); //返回第page页数据size条
}
/**
* 返回唯一值用uniqueResult()
* @param hql
* @param stu
* @return
*/
public Integer getCountOfStudent(String hql,Map stu){
//select cout(id) from Student where ….order by …
return (Integer)HibernateSessionFactory.getSession()
.createQuery("select count(name) from Student ")
.setProperties(stu)
.uniqueResult();
//.list().get(0);
}
/**
* 返回唯一值用uniqueResult()
* @param id
* @return
*/
public Student findById(Serializable id){
return (Student)HibernateSessionFactory.getSession()
.createQuery("from Student where id = :id ")
.setParameter("id", id)
.uniqueResult();
}
}
package cn.hibernate.biz;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Transaction;
import cn.hibernate.common.HibernateSessionFactory;
import cn.hibernate.dao.StudentPageDao;
import cn.hibernate.entity.Student;
/**
* 分页查询
* @author Administrator
*
*/
public class StudentPageBiz {
StudentPageDao dao = new StudentPageDao();
public List<Student> findByPage(){
List<Student> list = new ArrayList<Student>();
Transaction tx = null;
try {
tx = HibernateSessionFactory.getSession().beginTransaction();
dao.findStu(null, null, 1, 5);
tx.commit();
} catch (Exception e) {
if(tx!=null){
tx.rollback();
}
}
return list;
}
public Integer getCount(){
Integer count = null;
Transaction tx = null;
try {
tx = HibernateSessionFactory.getSession().beginTransaction();
count = dao.getCountOfStudent(null, null);
tx.commit();
} catch (Exception e) {
if(tx!=null){
tx.rollback();
}
}
return count;
}
public Student getStuById(Serializable id){
Student stu = null;
Transaction tx = null;
try {
tx = HibernateSessionFactory.getSession().beginTransaction();
stu = dao.findById(id);
tx.commit();
} catch (Exception e) {
if(tx!=null){
tx.rollback();
}
}
return stu;
}
}
test测试类
public static void main(String[] args) {
// test1();
// test2();
StudentPageBiz biz = new StudentPageBiz();
List<Student> stus = biz.findByPage();
for (Student stu : stus) {
System.out.println(stu.getClazz()+":;;"+stu.getName());
}
System.out.println(biz.getCount());
Student stu = biz.getStuById(3);
System.out.println(stu.getName());
}
===============================================================
投影查询
Hql投影查询是查询一个持久化类的一个或多个属性值
将每条查询结果封装成object对象
将每条查询结果封装成object数组
将每条查询结果通过构造函数封装成对象
实现代码==========================================================
package cn.hibernate.dao;
import java.util.List;
import java.util.Map;
import cn.hibernate.common.HibernateSessionFactory;
import cn.hibernate.entity.Student;
/**
* 投影查询
* @author Administrator
*
*/
public class StudentProjectDao {
@SuppressWarnings("unchecked")
public List<Object[][]> getStu(String hql,Map stu){
return HibernateSessionFactory.getSession()
.createQuery(hql)
.setProperties(stu)
.list();
}
}
package cn.hibernate.biz;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Transaction;
import cn.hibernate.common.HibernateSessionFactory;
import cn.hibernate.dao.StudentProjectDao;
import cn.hibernate.entity.Student;
public class StudentProjectBiz {
StudentProjectDao dao = new StudentProjectDao();
public List<Object[][]> find(){
List<Object[][]> stu = new ArrayList<Object[][]>();
Transaction tx = null;
String hql = "select stu.clazz,stu.name from Student stu";
try {
tx = HibernateSessionFactory.getSession().beginTransaction();
stu = dao.getStu(hql, null);
tx.commit();
} catch (Exception e) {
if(tx!=null){
tx.rollback();
}
}
return stu;
}
}
test测试类
public static void main(String[] args) {
StudentProjectBiz biz = new StudentProjectBiz();
List<Object[][]> stu = biz.find();
for (Object[] stu1 : stu) {
System.out.println(stu1[0]+";;"+stu1[1]);
}
}
运行结果展示
Hibernate: select student0_.clazz as col_0_0_, student0_.name as col_1_0_ from student student0_
java;;修改姓名
java;;李四
php;;get方法
java;;merge2
java;;王五
java;;merge3
java;;xiugai