1、HQL查询-hibernate Query Language(多表查询,但不复杂时使用)
Hibernate独家查询语言,属于面向对象的查询语言
- 基本查询
//-------------------------------------------
//1> 书写HQL语句
//String hql = " from Customer where cust_id = 1 ";
String hql = " from Customer "; // 查询所有Customer对象
//2> 根据HQL语句创建查询对象
Query query = session.createQuery(hql);
//3> 根据查询对象获得查询结果集
List<Customer> list = query.list(); // 返回list结果
//query.uniqueResult();//接收唯一的查询结果
System.out.println(list);
//-------------------------------------------
条件查询
- 1、’ ? ’ 号占位符
//------------------------------------------- //1> 书写HQL语句 String hql = " from Customer where cust_id = ? "; // 查询所有Customer对象 //2> 根据HQL语句创建查询对象 Query query = session.createQuery(hql); //设置参数 //query.setLong(0, 1l); query.setParameter(0, 1l); //3> 根据查询对象获得查询结果 Customer c = (Customer) query.uniqueResult(); System.out.println(c); //-------------------------------------------
- 2、命名占位符
//------------------------------------------- //1> 书写HQL语句 String hql = " from Customer where cust_id = :cust_id "; // 查询所有Customer对象 //2> 根据HQL语句创建查询对象 Query query = session.createQuery(hql); //设置参数 query.setParameter("cust_id", 1l); //3> 根据查询对象获得查询结果 Customer c = (Customer) query.uniqueResult(); System.out.println(c); //-------------------------------------------
分页查询
//-------------------------------------------
//1> 书写HQL语句
String hql = " from Customer "; // 查询所有Customer对象
//2> 根据HQL语句创建查询对象
Query query = session.createQuery(hql);
//设置分页信息 limit ?,?
query.setFirstResult(1);
query.setMaxResults(1);
//3> 根据查询对象获得查询结果
List<Customer> list = query.list();
System.out.println(list);
//-------------------------------------------
2、Criteria查询(单表条件查询)
Hibernate自创的无语句面向对象查询
- 基本查询
//-------------------------------------------
//查询所有的Customer对象
Criteria criteria = session.createCriteria(Customer.class);
List<Customer> list = criteria.list();
System.out.println(list);
// Customer c = (Customer) criteria.uniqueResult();
//-------------------------------------------
条件查询
//条件查询 //HQL语句中,不可能出现任何数据库相关的信息的 // > gt // >= ge // < lt // <= le // == eq // != ne // in in // between and between // like like // is not null isNotNull // is null isNull // or or // and and
//-------------------------------------------
//创建criteria查询对象
Criteria criteria = session.createCriteria(Customer.class);
//添加查询参数 => 查询cust_id为1的Customer对象
criteria.add(Restrictions.eq("cust_id", 1l));
//执行查询获得结果
Customer c = (Customer) criteria.uniqueResult();
System.out.println(c);
//-------------------------------------------
- 分页 查询
//-------------------------------------------
//创建criteria查询对象
Criteria criteria = session.createCriteria(Customer.class);
//设置分页信息 limit ?,?
criteria.setFirstResult(1);
criteria.setMaxResults(2);
//执行查询
List<Customer> list = criteria.list();
System.out.println(list);
//-------------------------------------------
- 设置查询总记录数
//-------------------------------------------
//创建criteria查询对象
Criteria criteria = session.createCriteria(Customer.class);
//设置查询的聚合函数 => 总行数
criteria.setProjection(Projections.rowCount());
//执行查询
Long count = (Long) criteria.uniqueResult();
System.out.println(count);
//-------------------------------------------
3、原生SQL查询(复杂的业务查询)
基本查询
1、返回数组List
//------------------------------------------- //1 书写sql语句 String sql = "select * from cst_customer"; //2 创建sql查询对象 SQLQuery query = session.createSQLQuery(sql); //3 调用方法查询结果 List<Object[]> list = query.list(); //query.uniqueResult(); for(Object[] objs : list){ System.out.println(Arrays.toString(objs)); } //-------------------------------------------
2、返回对象List
//-------------------------------------------
//1 书写sql语句
String sql = "select * from cst_customer";
//2 创建sql查询对象
SQLQuery query = session.createSQLQuery(sql);
//指定将结果集封装到哪个对象中
query.addEntity(Customer.class);
//3 调用方法查询结果
List<Customer> list = query.list();
System.out.println(list);
//-------------------------------------------
- 条件查询
//-------------------------------------------
//1 书写sql语句
String sql = "select * from cst_customer where cust_id = ? ";
//2 创建sql查询对象
SQLQuery query = session.createSQLQuery(sql);
query.setParameter(0, 1l);
//指定将结果集封装到哪个对象中
query.addEntity(Customer.class);
//3 调用方法查询结果
List<Customer> list = query.list();
System.out.println(list);
//-------------------------------------------
- 分页查询
//-------------------------------------------
//1 书写sql语句
String sql = "select * from cst_customer limit ?,? ";
//2 创建sql查询对象
SQLQuery query = session.createSQLQuery(sql);
query.setParameter(0, 0);
query.setParameter(1, 1);
//指定将结果集封装到哪个对象中
query.addEntity(Customer.class);
//3 调用方法查询结果
List<Customer> list = query.list();
System.out.println(list);
//-------------------------------------------