hibernate的hql语句

hibernate的hql语句

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 studentString 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 blocke.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 blocke.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 ");}}*///方式2StringBuilder hql=new StringBuilder("from Student stu ");//判断参数stuif(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:;;merge2java:;;王五java:;;merge3java:;;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;;merge2java;;王五java;;merge3java;;xiugai