hibernate查询
hql查询
//入门
@Test
public void test(){
//1.创建配置对象
Configuration configuration = new Configuration();
configuration.configure(); // 加载hibernat.cfg.xml配置文件
//2. 通过配置对象，创建工厂对象
SessionFactory sessionFactory = configuration.buildSessionFactory();
//3. 通过工厂对象，创建session对象
Session session =sessionFactory.openSession();
//4. 开启事务，并获得事务对象
Transaction beginTransaction = session.beginTransaction();
//5. 实际的业务（模拟注册）
User user =new User();
user.setName("admin");
user.setPassword("123");
session.save(user);
//6. 提交事务
beginTransaction.commit();
//7. 关闭资源
session.close();
sessionFactory.close();
}OID:get()和load()
@Test
//演示持久化对象处于持久态，自动更新数据库
public void test43(){//get方法,查ID
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
User user =session.get(User.class, 5);
session.clear();
User user1 =session.get(User.class, 5);
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}@Test
public void test(){//load方法,延迟加载
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
//2. 实际的业务（模拟注册）
User user =session.load(User.class, 2);
System.out.println("测试");
System.out.println(user.toString());
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}抽取的工具类
原因：SessionFactory对象，创建非常消耗资源，一个项目只需要创建一个
public class HibernateUtils {
static SessionFactory sf; // 静态变量，声明了一个引用。
static{
Configuration configuration = new Configuration();
configuration.configure(); // 加载hibernat.cfg.xml配置文件
sf = configuration.buildSessionFactory();
}
//向外界提供session对象
public static Session openSession(){
return sf.openSession();
}HQL查询
hql全表查询
@Test
// 全表查询
public void test1(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
// select * from user
String hql="from User"; // User:表示持久化类的名称
Query query = session.createQuery(hql);
List<User> list = query.list();
for(User x:list){
System.out.println(x.toString());
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}hql分页查询
@Test
// 分页查询： 3-4
public void test5(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
// select * from user limit 2, 2
String hql="from User";
Query query = session.createQuery(hql);
query.setFirstResult(2); // 从0开始计数，第二条记录后，开始
query.setMaxResults(2); // 结果的结果数量
List<User> list = query.list();
for(User x:list){
System.out.println(x.toString());
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}hql条件查询:id=3
@Test
// 条件查询：id =3
public void test4(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
// select * from user where uid =3
String hql="from User where id =?";
Query query = session.createQuery(hql);
query.setInteger(0,3);
User x = (User) query.uniqueResult();
System.out.println(x.toString());
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}hql条件查询:like
@Test
// 条件查询：，名字包含“刘德华”
public void test3(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
// select * from user where uname like '%刘德华%'
String hql="from User where name like ?";
Query query = session.createQuery(hql);
query.setString(0, "%刘德华%");
List<User> list = query.list();
for(User x:list){
System.out.println(x.toString());
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}hql条件查询:id>3
@Test
// 条件查询：查询id >3
public void test2(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
// select * from user where uid >?
String hql="from User where id >?"; // User:表示持久化类的名称
Query query = session.createQuery(hql);
query.setInteger(0, 3);
List<User> list = query.list();
for(User x:list){
System.out.println(x.toString());
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}QBC查询
QBC全表查询
@Test
// 全表查询
public void test1(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
Criteria criteria = session.createCriteria(User.class);
List<User> list = criteria.list();
for(User x:list){
System.out.println(x.toString());
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}QBC条件查询:id>3
@Test
// 条件查询：查询id >3
public void test2(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
// select * from user where uid >?
// 1. 创建查询对象
Criteria criteria = session.createCriteria(User.class);
// 2. 创建查询条件对象
Criterion criterion = Restrictions.gt("id", 3);
// 3. 查询对象，增加查询条件对象
criteria.add(criterion);
List<User> list = criteria.list();
for(User x:list){
System.out.println(x.toString());
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}QBC条件查询:like
@Test
// 条件查询：，名字包含“刘德华”
public void test3(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
// String hql="from User where name like ?";
// 1. 创建查询对象
Criteria criteria = session.createCriteria(User.class);
// 2. 创建查询条件对象
Criterion criterion = Restrictions.like("name", "%刘德华%");
// 3. 查询对象，增加查询条件对象
criteria.add(criterion);
List<User> list = criteria.list();
for(User x:list){
System.out.println(x.toString());
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}QBC条件查询:id=3
@Test
// 条件查询：id =3
public void test4(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
// String hql="from User where id =?";
// 1. 创建查询对象
Criteria criteria = session.createCriteria(User.class);
// 2. 创建查询条件对象
Criterion criterion = Restrictions.eq("id", 3);
// 3. 查询对象，增加查询条件对象
criteria.add(criterion);
User x = (User) criteria.uniqueResult();
System.out.println(x.toString());
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}QBC分页查询
@Test
// 分页查询： 3-4
public void test5(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
// select * from user limit 2, 2
// 1. 创建查询对象
Criteria criteria = session.createCriteria(User.class);
criteria.setFirstResult(2); // 从0开始计数，第二条记录后，开始
criteria.setMaxResults(2); // 结果的结果数量
List<User> list = criteria.list();
for(User x:list){
System.out.println(x.toString());
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}SQLQuery查询
SQLQuery全表查询1
@Test
// 全表查询
public void test1(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
String sql="select * from user" ;
SQLQuery sqlQuery = session.createSQLQuery(sql);
List<Object[]> list = sqlQuery.list();
for(Object[] x:list){
System.out.println(Arrays.toString(x));
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}SQLQuery全表查询2
@Test
// 全表查询
public void test2(){
Session session =HibernateUtils.openSession();
Transaction beginTransaction = session.beginTransaction();
String sql="select * from user" ;
SQLQuery sqlQuery = session.createSQLQuery(sql);
sqlQuery.addEntity(User.class);
List<User> list = sqlQuery.list();
for(User x:list){
System.out.println(x.toString());
}
//3. 提交事务，关闭资源
beginTransaction.commit();
session.close();
}对象导航
@Test
//对象导航查询
public void test2(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// 参考代码--保存操作：1个部门3个员工
Department department = session.get(Department.class, 1);
System.out.println(department.toString());
for(Employee x:department.getEmployees()){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
} hql增强
条件查询
@Test
// 条件查询
public void test(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// 查询所有性别是“女”的员工
// select * from employee where esex=?
String hql="from Employee where sex=?";
Query createQuery = session.createQuery(hql);
createQuery.setString(0, "女");
List<Employee> list = createQuery.list();
for(Employee x:list){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
}命名查询
@Test
// 命名查询
public void test2(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// 查询所有性别是“女”的员工
// select * from employee where esex=? and id >?
String hql="from Employee where sex= :sex and id>:id";
Query createQuery = session.createQuery(hql);
createQuery.setString("sex", "女");
createQuery.setInteger("id", 5);
List<Employee> list = createQuery.list();
for(Employee x:list){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
}统计查询
@Test
// 统计查询：按照性别分组，统计每个性别的人数
public void test3(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
//sql: select count(eid) from employee group by esex; // esex:表中字段的名字
String hql="select count(id) from Employee group by sex"; //sex: javabean属性的名字
Query createQuery = session.createQuery(hql);
List<Object> list = createQuery.list();
for(Object x:list){
// 小技巧：如何感知对象的实际类型
System.out.println(x.getClass().getName());
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
} 排序查询
@Test
// 排序查询： 根据id，从大到小排序
public void test4(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// select * from employee order by eid desc;
String hql="from Employee order by id desc";
Query createQuery = session.createQuery(hql);
List<Employee> list = createQuery.list();
for(Employee x:list){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
} 投影查询
@Test
// 投影查询：1列， 只查询员工的名字
public void test5(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// select ename from employee;
String hql="select name from Employee";
Query createQuery = session.createQuery(hql);
List<String> list = createQuery.list();
for(String x:list){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
}@Test
// 投影查询：2列， 只查询员工的名字， 性别
public void test6(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// select ename,esex from employee;sql-->hql hql-->sql
// 体现用面向对象的观点，操作关系型的数据库。
String hql="select name, sex from Employee";
Query createQuery = session.createQuery(hql);
List<Object[]> list = createQuery.list();
for(Object[] x:list){
System.out.println(Arrays.toString(x));
}
// 释放资源
transaction.commit();
session.close();
}投影构造查询
@Test
// 投影构造查询
public void test8(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// select ename,esex from employee;
String hql="select new Employee(name, sex) from Employee";
Query createQuery = session.createQuery(hql);
List<Employee> list = createQuery.list();
for(Employee x:list){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
}QBC增强
条件查询
@Test
// 条件查询: 查询id >5 or id <3
// select * from employee where eid >5 or eid <3;
public void test(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
Criteria criteria = session.createCriteria(Employee.class);
// 查询条件对象
Criterion criterion1 = Restrictions.gt("id", 5);
Criterion criterion2 = Restrictions.lt("id", 3);
Criterion criterion3 = Restrictions.or(criterion1, criterion2);
criteria.add(criterion3);
List<Employee> list = criteria.list();
for(Employee x:list){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
}排序查询1
@Test
// 排序查询：先根据性别排序，性别相同，根据id从小到大排序
// select * from employee order by esex, eid ;
public void test3(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
Criteria criteria = session.createCriteria(Employee.class);
// 排序对象
Order order1= Order.asc("sex");
Order order2= Order.asc("id");
criteria.addOrder(order1);
criteria.addOrder(order2);
List<Employee> list = criteria.list();
for(Employee x:list){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
}排序查询2
@Test
// 排序查询：id从大到小
public void test2(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
Criteria criteria = session.createCriteria(Employee.class);
// 排序对象
Order desc = Order.desc("id");
criteria.addOrder(desc);
List<Employee> list = criteria.list();
for(Employee x:list){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
}查询ID最大
@Test
// 查询id最大的员工 selec max(eid) from employee;
public void test5(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
Criteria criteria = session.createCriteria(Employee.class);
// 分组统计对象
Projection projection= Projections.max("id");
criteria.setProjection(projection);
List<Object> list = criteria.list();
for(Object x:list){
System.out.println(x.getClass().getName());
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
}统计分组查询
@Test
// 按照性别分组，统计每个性别员工的人数
// select count(*) from employee group by esex;
public void test6(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
Criteria criteria = session.createCriteria(Employee.class);
// 处理性别分组
Projection projection1=Projections.groupProperty("sex");
// 处理统计每个性别的人数
Projection projection2= Projections.count("id");
// 集合对象
ProjectionList projectionList = Projections.projectionList();
projectionList.add(projection1);
projectionList.add(projection2);
criteria.setProjection(projectionList);
List<Object[]> list = criteria.list();
for(Object[] x:list){
System.out.println(x.getClass().getName());
System.out.println(Arrays.toString(x));
}
// 释放资源
transaction.commit();
session.close();
}离线查询条件对象
@Test
// 查询：id >5
public void test_使用(){
// 1. 离线查询对象的创建
DetachedCriteria dc = DetachedCriteria.forClass(Employee.class);
// 2. 增加查询条件
Criterion criterion = Restrictions.gt("id",5);
// 3. 关联
dc.add(criterion);
// 4. 通过session复活
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
Criteria executableCriteria = dc.getExecutableCriteria(session);
// session.createCriteia(); 真正与数据库的联接在session.
// 5. 进行真正的执行
List<Employee> list = executableCriteria.list();
for(Employee x:list){
System.out.println(x.toString());
}
// 释放资源
transaction.commit();
session.close();
}hql的多表查询
交叉查询
@Test
// 交叉查询
public void test_使用(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// select * from department a, employee b ;
String hql="from Department, Employee";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
System.out.println(list.size());
for(Object[] x:list){
// System.out.println(x.getClass().getName());
System.out.println(Arrays.toString(x));
}
// 释放资源
transaction.commit();
session.close();
}隐式内连接
@Test
// 隐式内联接
public void test2(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// select * from department a, employee b where a.did=b.fk_did;
String hql="from Department a, Employee b where a.id = b.department";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
System.out.println(list.size());
for(Object[] x:list){
// System.out.println(x.getClass().getName());
System.out.println(Arrays.toString(x));
}
// 释放资源
transaction.commit();
session.close();
}显示内连接
@Test
// 显示内联接
public void test4(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// select * from department a inner join employee b on a.did=b.fk_did;
//String hql="from Department a inner join a.employees";
String hql="from Employee e inner join e.department";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
System.out.println(list.size());
for(Object[] x:list){
// System.out.println(x.getClass().getName());
System.out.println(Arrays.toString(x));
}
// 释放资源
transaction.commit();
session.close();
}迫切内连接
@Test
// 迫切内联接--必须要去掉重复的数据： distinct
public void test6(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// 迫切：查询的结果，只封装成一个对象。左边的。员工的数据，封装这个对象的，关联对象。
String hql="select distinct a from Department a inner join fetch a.employees";
Query query = session.createQuery(hql);
List<Department> list = query.list();
for(Department x:list){
// System.out.println(x.getClass().getName());
System.out.println(x.toString());
System.out.println(x.getEmployees().size());
}
// 释放资源
transaction.commit();
session.close();
}级联查询
查询某个部门，及该部门的员工人数
@Test
// 查询某个部门，及该部门的员工人数
public void test_使用(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
// 查询部门
Department department= session.get(Department.class, 1);
System.out.println("测试");
// 查询部门里面所有的员工人数
System.out.println(department.getEmployees().size());
// 释放资源
transaction.commit();
session.close();
}查询所有的部门，以及每个部门的员工人数。
@Test
// fetch=="subselect"，查询多个对象才能体现。
// 查询所有的部门，以及每个部门的员工人数。
public void test_使用2(){
Session session =HibernateUtils.openSession();
Transaction transaction=session.beginTransaction();
Criteria criteria =session.createCriteria(Department.class);
List<Department> list = criteria.list();
for(Department x:list){
System.out.println(x.toString());
System.out.println(x.getEmployees().size());
}
// 释放资源
transaction.commit();
session.close();
}
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
