目录
使用环境:多表查询,条件不复杂时使用
查询所有对象
@Test
public void test4(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~~~~
//1.书写Hql 可以书写完整的类名,如果类名在项目中唯一也可以简写 如果是查询对象所有信息,即select * 则可以省略
//string hql = "from com.ycsj.domain.Customer"; //书写完整类型 省略select *
String hql = "from Customer"; //项目中唯一类名
//2.根据hql创建查询对象
Query query = session.createQuery(hql);
//3.查询
//返回list结果
List<Customer> list = query.list();
//返回单个对象
//query.uniqueResult();
System.out.println(list);
//~~~~~~~~~~~~~~
tx.commit();
}
条件查询
/**
* 条件查询
* 在hql语句中不会出现数据库相关的信息 条件中where子句查询的是实体类中的字段
*/
@Test
public void test5(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~~~~
//1.书写Hql
//string hql = "from com.ycsj.domain.Customer";
String hql = "from Customer where cust_id=1";
//2.根据hql创建查询对象
Query query = session.createQuery(hql);
//3.查询
//返回list结果
// List<Customer> list = query.list();
//返回单个对象
Customer custormer = (Customer) query.uniqueResult();
System.out.println(custormer);
//~~~~~~~~~~~~~~
tx.commit();
}
使用带问号站位符的条件查询
/**
* 条件查询
* 问号占位符 在新版本的hibernate框架中使用?+数字 来替代,原来的问号
*/
@Test
public void test6(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~~~~
//1.书写Hql
//string hql = "from com.ycsj.domain.Customer";
String hql = "from Customer where cust_id=?0";
//2.根据hql创建查询对象
Query query = session.createQuery(hql);
//设置参数
query.setParameter(0,1);
//3.查询
//返回list结果
// List<Customer> list = query.list();
//返回单个对象
Customer custormer = (Customer) query.uniqueResult();
System.out.println(custormer);
//~~~~~~~~~~~~~~
tx.commit();
}
使用命名占位符的条件查询
/**
* 条件查询
* 命名占位符:+命名
*/
@Test
public void test7(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~~~~
//1.书写Hql
//string hql = "from com.ycsj.domain.Customer";
String hql = "from Customer where cust_id=:cust_id";
//2.根据hql创建查询对象
Query query = session.createQuery(hql);
//设置参数
query.setParameter("cust_id",1); //与上面的命名相对应
//3.查询
//返回list结果
// List<Customer> list = query.list();
//返回单个对象
Customer custormer = (Customer) query.uniqueResult();
System.out.println(custormer);
//~~~~~~~~~~~~~~
tx.commit();
}
分页查询
/**
* 分页查询
*/
@Test
public void test8(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~~~~
//1.书写Hql
//string hql = "from com.ycsj.domain.Customer";
String hql = "from Customer";
//2.根据hql创建查询对象
Query query = session.createQuery(hql);
//设置分页信息
query.setFirstResult(0);
query.setMaxResults(1);
//3.查询
//返回list结果
List<Customer> list = query.list();
//返回单个对象
//Customer custormer = (Customer) query.uniqueResult();
System.out.println(list);
//~~~~~~~~~~~~~~
tx.commit();
}
排序
@Test
public void test1(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~
String hql = " from LinkMan order by lkm_id asc";
String hql2 = " from LinkMan order by lkm_id desc";
Query query = session.createQuery(hql2);
List<LinkMan> list = query.list();
System.out.println(list);
//~~~~~~~~~~~~~~~~~
tx.commit();
session.close();
}
聚合函数
//count
//max
//min
//avg
@Test
public void test2(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~
String hql = " select count(*) from LinkMan order";
String hql2 = " select avg(lkm_id) from LinkMan order";
String hql3 = " select max(lkm_id) from LinkMan order";
String hql4 = " select min(lkm_id) from LinkMan order";
Query query = session.createQuery(hql3);
Number number = (Number) query.uniqueResult();
System.out.println(number);
//~~~~~~~~~~~~~~~~~
tx.commit();
session.close();
}
投影检索
就是不查询所有 只查询部分字段
@Test
public void test3(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~
String hql1 = " select lkm_name from LinkMan";
String hql2 = " select lkm_id,lkm_name from LinkMan"; //返回数组
String hql3 = " select new LinkMan(lkm_id,lkm_name) from LinkMan"; //需要在实体类中创建响应的构造函数 自动封装到对象中
Query query = session.createQuery(hql3);
//查询某一列,将查询出来的结果封装到一个数组中
List list = query.list();
System.out.println(list);
//~~~~~~~~~~~~~~~~~
tx.commit();
session.close();
}
多表查询
/*回顾原生sql * 交叉连接 ---笛卡尔积 *select * from A,B A中有m条记录 B中有n条记录 查询结果有m*n条记录 这种情况的应用场景几乎没有 *内连接 * |----隐式内连接 * select * from A,B where A.xxx=B.yyy; * |----显示内连接 * 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支持破切查询 fetch
/**
* hql多表查询
* 内连接 将数据封装到数组中
*
* [Ljava.lang.Object;@5432c277
* [Ljava.lang.Object;@15e0fe05
* [Ljava.lang.Object;@1128620c
* [Ljava.lang.Object;@6bf13698
*/
@Test
public void test(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~~~~~~~~~~~
String hql = " from Customer c inner join c.linkMans";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] arr:list){
System.out.println(arr);
}
//~~~~~~~~~~~~~~~~~~~~~~~~
tx.commit();
session.close();
}
/**
* 迫切内连接 帮助把数据封装到对象中
*[Customer{cust_id=1, cust_name='zhiduowa', linkMans=[LinkMan{lkm_id=1, lkm_name='董事长'}, LinkMan{lkm_id=2, lkm_name='总经理'}]}
*/
@Test
public void test1(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~~~~~~~~~~~
String hql = " from Customer c inner join fetch c.linkMans";
Query query = session.createQuery(hql);
List<Customer> list = query.list();
System.out.println(list);
//~~~~~~~~~~~~~~~~~~~~~~~~
tx.commit();
session.close();
}
/**
* 左外连接 左边的数据都会显示
*/
@Test
public void test3(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~~~~~~~~~~~
String hql = " from Customer c left join c.linkMans";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] arr:list){
System.out.println(arr);
}
//~~~~~~~~~~~~~~~~~~~~~~~~
tx.commit();
session.close();
}
/**
* 右外连接 右边的数据都会显示 被连接的数据全部显示 不管有没有对应
*/
@Test
public void test4(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//~~~~~~~~~~~~~~~~~~~~~~~~
String hql = " from Customer c right join c.linkMans";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] arr:list){
System.out.println(arr);
}
//~~~~~~~~~~~~~~~~~~~~~~~~
tx.commit();
session.close();
}
}