一、查询种类
1.oid查询-get
2.对象属性导航查询
3.HQL
4.Criteria
5.原生SQL
二、查询-HQL语法
//学习HQL语法 public class Demo { //基本语法 @Test public void fun1(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql = " from cn.itcast.domain.Customer ";//完整写法 String hql2 = " from Customer "; //简单写法 String hql3 = " from java.lang.Object "; Query query = session.createQuery(hql3); List list = query.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } @Test //排序 public void fun2(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql1 = " from cn.itcast.domain.Customer order by cust_id asc ";//完整写法 String hql2 = " from cn.itcast.domain.Customer order by cust_id desc ";//完整写法 Query query = session.createQuery(hql2); List list = query.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } @Test //条件查询 public void fun3(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql1 = " from cn.itcast.domain.Customer where cust_id =? ";//完整写法 String hql2 = " from cn.itcast.domain.Customer where cust_id = :id ";//完整写法 Query query = session.createQuery(hql2); // query.setParameter(0, 2l); query.setParameter("id", 2l); List list = query.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } @Test //分页查询 public void fun4(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql1 = " from cn.itcast.domain.Customer ";//完整写法 Query query = session.createQuery(hql1); //limit ?,? // (当前页数-1)*每页条数 query.setFirstResult(2); query.setMaxResults(2); List list = query.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } @Test //统计查询 //count 计数 //sum 求和 //avg 平均数 //max //min public void fun5(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql1 = " select count(*) from cn.itcast.domain.Customer ";//完整写法 String hql2 = " select sum(cust_id) from cn.itcast.domain.Customer ";//完整写法 String hql3 = " select avg(cust_id) from cn.itcast.domain.Customer ";//完整写法 String hql4 = " select max(cust_id) from cn.itcast.domain.Customer ";//完整写法 String hql5 = " select min(cust_id) from cn.itcast.domain.Customer ";//完整写法 Query query = session.createQuery(hql5); Number number = (Number) query.uniqueResult(); System.out.println(number); //---------------------------------------------------- tx.commit(); session.close(); } @Test //投影查询 public void fun6(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql1 = " select cust_name from cn.itcast.domain.Customer "; String hql2 = " select cust_name,cust_id from cn.itcast.domain.Customer "; String hql3 = " select new Customer(cust_id,cust_name) from cn.itcast.domain.Customer "; Query query = session.createQuery(hql3); List list = query.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } }
public class Demo2 { //回顾-原生SQL // 交叉连接-笛卡尔积(避免) // select * from A,B // 内连接 // |-隐式内连接 // select * from A,B where b.aid = a.id // |-显式内连接 // select * from A inner join B on b.aid = a.id // 外连接 // |- 左外 // select * from A left [outer] join B on b.aid = a.id // |- 右外 // select * from A right [outer] join B on b.aid = a.id //--------------------------------------------------------------------- //HQL的多表查询 //内连接(迫切) //外连接 // |-左外(迫切) // |-右外(迫切) @Test //HQL 内连接 => 将连接的两端对象分别返回.放到数组中. public void fun1(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql = " from Customer c inner join c.linkMens "; Query query = session.createQuery(hql); List<Object[]> list = query.list(); for(Object[] arr : list){ System.out.println(Arrays.toString(arr)); } //---------------------------------------------------- tx.commit(); session.close(); } @Test //HQL 迫切内连接 => 帮我们进行封装.返回值就是一个对象 public void fun2(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql = " from Customer c inner join fetch c.linkMens "; Query query = session.createQuery(hql); List<Customer> list = query.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } @Test //HQL 左外连接 => 将连接的两端对象分别返回.放到数组中. public void fun3(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql = " from Customer c left join c.linkMens "; Query query = session.createQuery(hql); List<Object[]> list = query.list(); for(Object[] arr : list){ System.out.println(Arrays.toString(arr)); } //---------------------------------------------------- tx.commit(); session.close(); } @Test //HQL 右外连接 => 将连接的两端对象分别返回.放到数组中. public void fun4(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql = " from Customer c right join c.linkMens "; Query query = session.createQuery(hql); List<Object[]> list = query.list(); for(Object[] arr : list){ System.out.println(Arrays.toString(arr)); } //---------------------------------------------------- tx.commit(); session.close(); } }
三、查询-Criteria语法
public class Demo { @Test //基本语法 public void fun1(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Criteria c = session.createCriteria(Customer.class); List<Customer> list = c.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } @Test //条件语法 public void fun2(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Criteria c = session.createCriteria(Customer.class); // c.add(Restrictions.idEq(2l)); c.add(Restrictions.eq("cust_id",2l)); List<Customer> list = c.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } @Test //分页语法 - 与HQL一样 public void fun3(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Criteria c = session.createCriteria(Customer.class); //limit ?,? c.setFirstResult(0); c.setMaxResults(2); List<Customer> list = c.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } @Test //排序语法 public void fun4(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Criteria c = session.createCriteria(Customer.class); c.addOrder(Order.asc("cust_id")); //c.addOrder(Order.desc("cust_id")); List<Customer> list = c.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } @Test //统计语法 public void fun5(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Criteria c = session.createCriteria(Customer.class); //设置查询目标 c.setProjection(Projections.rowCount()); List list = c.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } }
非离线
离线
public class Demo2 { @Test public void fun1(){ //Service/web层 DetachedCriteria dc = DetachedCriteria.forClass(Customer.class); dc.add(Restrictions.idEq(6l));//拼装条件(全部与普通Criteria一致) //---------------------------------------------------- Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Criteria c = dc.getExecutableCriteria(session); List list = c.list(); System.out.println(list); //---------------------------------------------------- tx.commit(); session.close(); } }
四、查询优化
类级别查询
get方法:没有任何策略.调用即立即查询数据库加载数据.
load方法: 应用类级别的加载策略
public class Demo { @Test // get方法 : 立即加载.执行方法时立即发送sql语句查询结果 public void fun1(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Customer c = session.get(Customer.class, 2l); System.out.println(c); //---------------------------------------------------- tx.commit(); session.close(); } @Test // load方法(默认):是在执行时,不发送任何sql语句.返回一个对象.使用该对象时,才执行查询. // 延迟加载: 仅仅获得没有使用.不会查询.在使用时才进行查询. // 是否对类进行延迟加载: 可以通过在class元素上配置lazy属性来控制. //lazy:true 加载时,不查询.使用时才查询b //lazy:false 加载时立即查询. public void fun2(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Customer c = session.load(Customer.class, 2l); //---------------------------------------------------- tx.commit(); session.close(); System.out.println(c); } }
public class Demo { //集合级别的关联 //fetch:select 单表查询 //lazy:true 使用时才加载集合数据. @Test public void fun1(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Customer c = session.get(Customer.class, 2l); Set<LinkMan> linkMens = c.getLinkMens();//关联级别 System.out.println(linkMens); //---------------------------------------------------- tx.commit(); session.close(); } //集合级别的关联 //fetch:select 单表查询 //lazy:false 立即记载集合数据 @Test public void fun2(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Customer c = session.get(Customer.class, 2l); Set<LinkMan> linkMens = c.getLinkMens();//关联级别 System.out.println(linkMens); //---------------------------------------------------- tx.commit(); session.close(); } //集合级别的关联 //fetch:select 单表查询 //lazy:extra 极其懒惰.与懒加载效果基本一致. 如果只获得集合的size.只查询集合的size(count语句) @Test public void fun3(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Customer c = session.get(Customer.class, 2l); Set<LinkMan> linkMens = c.getLinkMens();//关联级别 System.out.println(linkMens.size()); System.out.println(linkMens); //---------------------------------------------------- tx.commit(); session.close(); } //集合级别的关联 //fetch:join 多表查询 //lazy:true|false|extra 失效.立即加载. @Test public void fun4(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- Customer c = session.get(Customer.class, 2l); Set<LinkMan> linkMens = c.getLinkMens();//关联级别 System.out.println(linkMens.size()); System.out.println(linkMens); //---------------------------------------------------- tx.commit(); session.close(); } @Test //fetch: subselect 子查询 //lazy: true 懒加载 public void fun5(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql = "from Customer"; Query query = session.createQuery(hql); List<Customer> list = query.list(); for(Customer c:list){ System.out.println(c); System.out.println(c.getLinkMens().size()); System.out.println(c.getLinkMens()); } //---------------------------------------------------- tx.commit(); session.close(); } @Test //fetch: subselect 子查询 //lazy: false 立即加载 public void fun6(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql = "from Customer"; Query query = session.createQuery(hql); List<Customer> list = query.list(); for(Customer c:list){ System.out.println(c); System.out.println(c.getLinkMens().size()); System.out.println(c.getLinkMens()); } //---------------------------------------------------- tx.commit(); session.close(); } @Test //fetch: subselect 子查询 //lazy: extra 极其懒惰 public void fun7(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql = "from Customer"; Query query = session.createQuery(hql); List<Customer> list = query.list(); for(Customer c:list){ System.out.println(c); System.out.println(c.getLinkMens().size()); System.out.println(c.getLinkMens()); } //---------------------------------------------------- tx.commit(); session.close(); } }
public class Demo2 { @Test //fetch:select 单表查询 //lazy:proxy //customer-true 懒加载 public void fun1(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- LinkMan lm = session.get(LinkMan.class, 3l); Customer customer = lm.getCustomer(); System.out.println(customer); //---------------------------------------------------- tx.commit(); session.close(); } @Test //fetch:join 多表 //lazy: 失效 public void fun3(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- LinkMan lm = session.get(LinkMan.class, 3l); Customer customer = lm.getCustomer(); System.out.println(customer); //---------------------------------------------------- tx.commit(); session.close(); } @Test //fetch:select 单表查询 //lazy:proxy //customer-false 立即加载 public void fun2(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- LinkMan lm = session.get(LinkMan.class, 3l); Customer customer = lm.getCustomer(); System.out.println(customer); //---------------------------------------------------- tx.commit(); session.close(); } }
结论:为了提高效率.fetch的选择上应选择select. lazy的取值应选择 true. 全部使用默认值.
no-session问题解决: 扩大session的作用范围.
public class Demo { @Test public void fun1(){ Session session = HibernateUtils.openSession(); Transaction tx = session.beginTransaction(); //---------------------------------------------------- String hql = "from Customer "; Query query = session.createQuery(hql); List<Customer> list = query.list(); for(Customer c:list){ System.out.println(c.getLinkMens()); } //---------------------------------------------------- tx.commit(); session.close(); } }
五、练习:为客户列表增加查询条件
在上一次笔记的代码基础上，在显示客户列表之前，加入筛选的条件:
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //1获得查询条件 String cust_name = request.getParameter("cust_name"); //2判断查询条件是否不为空 DetachedCriteria dc = DetachedCriteria.forClass(Customer.class); if(cust_name!=null && !"".equals(cust_name)){ dc.add(Restrictions.like("cust_name", "%"+cust_name+"%")); } //不为空=>添加条件 //3 调用Service查询所有客户 List<Customer> list = cs.getAll(dc); //4 将客户列表放入request域 request.setAttribute("list", list); //5 转发到list.jsp显示 request.getRequestDispatcher("/jsp/customer/list.jsp").forward(request, response); }
public List<Customer> getAll(DetachedCriteria dc) { Session session = HibernateUtils.getCurrentSession(); //打开事务 Transaction tx = session.beginTransaction(); List<Customer> list = customerDao.getAll(dc); //关闭事务 tx.commit(); return list; }
public List<Customer> getAll(DetachedCriteria dc) { //1 获得session Session session = HibernateUtils.getCurrentSession(); //2 将离线对象关联到session Criteria c = dc.getExecutableCriteria(session); //3 执行查询并返回 return c.list(); }
76
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
