1.HQL(Hibernate Query Language)
HQL别名查询
HQL查询返回结果方法:
list():返回List类型的查询结果,返回所有满足条件的对象。
uniqueResult():返回单个对象。 默认返回Object
强转:User user = (User)query.uniqueResult();
query.setFirstResult(0);//起始行数据,从0开始
query.setMaxResults(3);//每页显示几条数据
2.QBC(Query By Criteria)
HQL
public class Testhql {
/**
* 12.17 测试类--->使用HQL查询
*/
public static void main(String[] args) {
// save();
// delete();
findQm();
// findBm();
// findTj();
// findJm();
// findFy();
// findPx();
// findJh();
// findTy();
// findNlj();
// findZlj();
// findYlj();
}
/*
*
* 全名查询
*/
public static void findQm() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
Query query= session.createQuery("from Department");
List<Department> dlist=query.list();
for (Department department : dlist) {
System.out.println(department);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 别名查询
*/
public static void findBm() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
Query query= session.createQuery("from Department as d");
List<Department> dlist=query.list();
for (Department department : dlist) {
System.out.println(department);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 条件查询
*/
public static void findTj() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
Query query= session.createQuery("from Department where name=?");
query.setParameter(0, "后勤部");
// List<Department> dlist=query.list();
// for (Department department : dlist) {
// System.out.println(department);
// }
Department d=(Department) query.uniqueResult();
System.out.println("部门信息是"+d);
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 具名查询--->使用参数替代下标,预编译
*/
public static void findJm() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
Query query= session.createQuery("from Department where name=:name");
query.setParameter("name", "后勤部");
List<Department> dlist=query.list();
for (Department department : dlist) {
System.out.println(department);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 分页查询
*/
public static void findFy() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
Query query= session.createQuery("from Department");
query.setFirstResult(0);//起始行数据,从0开始
query.setMaxResults(3);//每页显示几条数据
List<Department> dlist=query.list();
for (Department department : dlist) {
System.out.println(department);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
* 排序查询
*/
public static void findPx() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
//按照id降序
//先按照name升序,后按照id降序-->from Department order by name,id desc;
Query query= session.createQuery("from Department order by id desc");
List<Department> dlist=query.list();
for (Department department : dlist) {
System.out.println(department);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 聚合查询--->查询数据库表中数据总数、返回单个对象
*/
public static void findJh() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查询数据库表中的总数据数,返回的是一个Long对象
//query.uniqueResult()
Query query= session.createQuery("select count(id) from Department");
Long count=(Long)query.uniqueResult();
System.out.println(count);
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 投影查询--->查询局部字段
*/
public static void findTy() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象--->注意实体类中带参数的构造方法
//第一种方法--->返回的是Object数组
// Query query= session.createQuery("select name,telephone from Employee");
// List<Object[]> list=query.list();
// for (Object[] objects : list) {
// for (Object object : objects) {
// System.out.print(object+"\t");
// }
// System.out.println();
// }
//第二种方法--->返回的是Department对象,利用HQL提供的动态构造实例的功能对这些平面数据进行封装;
Query query= session.createQuery("select new Employee(name,telephone) from Employee");
List<Employee> dlist=query.list();
for (Employee e : dlist) {
System.out.println(e);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
* HQL多表查询之内连接
* 需求:显示部门名称及雇员姓名
* 效果:只显示满足条件的数据
*/
public static void findNlj() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查询部门名称及雇员姓名
Query query= session.createQuery("select d.name,e.name from Department d inner join d.employees e");
List<Object[]> list=query.list();
for (Object[] objects : list) {
for (Object object : objects) {
System.out.print(object+"\t");
}
System.out.println();
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
* HQL多表查询之左连接
* 效果:左边表的数据会全部显示<left join>
* 注意:它与内连接的区别
*/
public static void findZlj() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查询部门名称及雇员姓名
Query query= session.createQuery("select d.name,e.name from Department d left join d.employees e");
List<Object[]> list=query.list();
for (Object[] objects : list) {
for (Object object : objects) {
System.out.print(object+"\t");
}
System.out.println();
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
* HQL多表查询之右连接
* 需求:显示部门名称及雇员姓名
* 效果:右边表的数据会全部显示<right join>
*/
public static void findYlj() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查询部门名称及雇员姓名
Query query= session.createQuery("select d.name,e.name from Employee e right join e.department d");
List<Object[]> list=query.list();
for (Object[] objects : list) {
for (Object object : objects) {
System.out.print(object+"\t");
}
System.out.println();
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 实现添加部门功能
*/
public static void save() {
Configuration cfg=new Configuration().configure();
SessionFactory factory=cfg.buildSessionFactory();
Session session=factory.openSession();
Transaction tran=session.beginTransaction();
Department department=new Department();
department.setName("后勤部");
Employee employee1=new Employee();
employee1.setName("小俊");
employee1.setTelephone("555");
employee1.setDepartment(department);
Employee employee2=new Employee();
employee2.setName("小小");
employee2.setTelephone("666");
employee2.setDepartment(department);
department.getEmployees().add(employee1);
department.getEmployees().add(employee2);
session.save(employee1);
session.save(employee2);
session.save(department);
tran.commit();
session.close();
factory.close();
}
/*
*
* 实现部门删除功能
*/
public static void delete() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
Department department = session.load(Department.class, 7);
System.out.println(department.getName());
session.delete(department);
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
}
QBC
public class Testqbc {
/**
* 12.19 测试类--->使用QBC查询
*/
public static void main(String[] args) {
// save();
// delete();
// findQm();
// findTj();
// findFy();
// findPx();
// findJh();
findTy();
}
/*
*
* 全名查询
*/
public static void findQm() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
Criteria ce=session.createCriteria(Department.class);
List<Department> dlist=ce.list();
for (Department department : dlist) {
System.out.println(department);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 条件查询
*/
public static void findTj() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
Criteria ce=session.createCriteria(Employee.class);
//添加查询条件
//ce.add(Restrictions.eq("name", "小小"));
//多个条件
ce.add(Restrictions.and(Restrictions.like("name", "%小%"),Restrictions.like("telephone", "%6%")));
List<Employee> elist=ce.list();
for (Employee employee : elist) {
System.out.println(employee);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 分页查询
*/
public static void findFy() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Employee对象
Criteria ce=session.createCriteria(Employee.class);
ce.setFirstResult(0);//起始行数据,从0开始
ce.setMaxResults(5);//每页显示几条数据
List<Employee> list=ce.list();
for (Employee e : list) {
System.out.println(e);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
* 排序查询
*/
public static void findPx() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
//按照id降序
//先按照name升序,后按照id降序-->from Department order by name,id desc;
Criteria ce=session.createCriteria(Department.class);
ce.addOrder(Order.desc("id"));
ce.addOrder(Order.asc("name"));
List<Department> dlist=ce.list();
for (Department department : dlist) {
System.out.println(department);
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 聚合查询--->查询数据库表中数据总数、返回单个对象
*/
public static void findJh() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查询数据库表中的总数据数,返回的是一个Long对象
//ce.uniqueResult()、Projections常用方法
Criteria ce=session.createCriteria(Department.class);
//ce.setProjection(Projections.count("id"));
ce.setProjection(Projections.max("id"));
Integer count = (Integer)ce.uniqueResult();
System.out.println(count);
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 投影查询--->查询局部字段
*/
public static void findTy() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
//返回的是Employee对象
Criteria ce=session.createCriteria(Employee.class);
ProjectionList plist=Projections.projectionList();
plist.add(Property.forName("name"));
plist.add(Property.forName("telephone"));
ce.setProjection(plist);
List<Object[]> list=ce.list();
for (Object[] objects : list) {
for (Object object : objects) {
System.out.print(object+"\t");
}
System.out.println();
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
* HQL多表查询之内连接
* 需求:显示部门名称及雇员姓名
* 效果:只显示满足条件的数据
*/
public static void findNlj() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查询部门名称及雇员姓名
Query query= session.createQuery("select d.name,e.name from Department d inner join d.employees e");
List<Object[]> list=query.list();
for (Object[] objects : list) {
for (Object object : objects) {
System.out.print(object+"\t");
}
System.out.println();
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
* HQL多表查询之左连接
* 效果:左边表的数据会全部显示<left join>
* 注意:它与内连接的区别
*/
public static void findZlj() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查询部门名称及雇员姓名
Query query= session.createQuery("select d.name,e.name from Department d left join d.employees e");
List<Object[]> list=query.list();
for (Object[] objects : list) {
for (Object object : objects) {
System.out.print(object+"\t");
}
System.out.println();
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
* HQL多表查询之右连接
* 需求:显示部门名称及雇员姓名
* 效果:右边表的数据会全部显示<right join>
*/
public static void findYlj() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查询部门名称及雇员姓名
Query query= session.createQuery("select d.name,e.name from Employee e right join e.department d");
List<Object[]> list=query.list();
for (Object[] objects : list) {
for (Object object : objects) {
System.out.print(object+"\t");
}
System.out.println();
}
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
/*
*
* 实现添加部门功能
*/
public static void save() {
Configuration cfg=new Configuration().configure();
SessionFactory factory=cfg.buildSessionFactory();
Session session=factory.openSession();
Transaction tran=session.beginTransaction();
Department department=new Department();
department.setName("后勤部");
Employee employee1=new Employee();
employee1.setName("小俊");
employee1.setTelephone("555");
employee1.setDepartment(department);
Employee employee2=new Employee();
employee2.setName("小小");
employee2.setTelephone("666");
employee2.setDepartment(department);
department.getEmployees().add(employee1);
department.getEmployees().add(employee2);
session.save(employee1);
session.save(employee2);
session.save(department);
tran.commit();
session.close();
factory.close();
}
/*
*
* 实现部门删除功能
*/
public static void delete() {
Session session = null;
Transaction tran = null;
try {
session = HibernateUtil.openSession();
tran = session.beginTransaction();
// ---------------------------------
// 查找Department对象
Department department = session.load(Department.class, 7);
System.out.println(department.getName());
session.delete(department);
// ---------------------------------
tran.commit();
} catch (Exception e) {
e.printStackTrace();
tran.rollback();
} finally {
if (null != session) {
session.close();
}
}
}
}