hibernate 查询类小结-优快云博客

本文链接：https://blog.youkuaiyun.com/youyou_yo/article/details/47610267

本文详细介绍了SQL、HQL与QBC技术在数据库操作中的应用，包括限制查询、分页、SQL限定查询、HQL查询、SQL查询等，并通过示例代码进行深入解析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.QBC

Criteria

Criterion

Restrictions

Order

Projections

ProjectionList

DetchedCriteria

方法	说明
Restrictions.eq	＝
Restrictions.allEq	利用 Map 来进行多个等于的限制
Restrictions.gt	＞
Restrictions.ge	＞＝
Restrictions.lt	＜
Restrictions.le	＜＝
Restrictions.between	BETWEEN
Restrictions.like	LIKE
Restrictions.in	in
Restrictions.and	and
Restrictions.or	or
Restrictions.sqlRestriction	用 SQL 限定查询

这种形式比较简单，参考源代码就可以了

public void testQBC() {

   Session session = factory.openSession();
   Criteria criteria = session.createCriteria(User.class);
   Criterion criterion1 = Restrictions.eq("addr", "dalian");
   Criterion criterion2 = Restrictions.eq("name", "tie");
   criteria.add(criterion1);
   criteria.add(criterion2);
   List<User> users = criteria.list();
   for(User user : users){
   System.out.println(user.introduce());
   System.out.println("**********************");
   }

  // Session session = factory.openSession();
  // Criteria criteria = session.createCriteria(User.class);
  // Map para = new HashMap();
  // para.put("addr", "dalian");
  // para.put("name", "tie");
  // Criterion criterion1 = Restrictions.allEq(para);
  // criteria.add(criterion1);
  // List<User> users = criteria.list();
  // for(User user : users){
  // System.out.println(user.introduce());
  // System.out.println("**********************");
  // }

   Session session = factory.openSession();
   Criteria criteria = session.createCriteria(User.class);
   Criterion criterion1 =
   Restrictions.sqlRestriction("{alias}.name='tie' and
   {alias}.addr='dalian'");
  2// Criterion criterion1 = Restrictions.sqlRestriction("{alias}.name=?",有三种重载方式
  // "tie", Hibernate.STRING);
  3// Object[] ob = {"tie","dalian"};
  // Type[] type = {Hibernate.STRING,Hibernate.STRING};
  // Criterion criterion1 = Restrictions.sqlRestriction("{alias}.name=?
  // and {alias}.addr=?", ob, type);
  // criteria.add(criterion1);
  // List<User> users = criteria.list();
  // for(User user : users){
  // System.out.println(user.introduce());
  // System.out.println("**********************");
  // }

  // Session session = factory.openSession();
  // Criteria criteria = session.createCriteria(User.class);
  // criteria.addOrder(Order.desc("age"));
  // List<User> users = criteria.list();
  // for(User user : users){
  // System.out.println(user.introduce());
  // System.out.println("**********************");
  // }

  // Session session = factory.openSession();
  // Criteria criteria = session.createCriteria(User.class);
  // criteria.setProjection(Projections.avg("age"));
  // List<Double> avg_ages = criteria.list();
  // for(Double avg_age : avg_ages){
  // System.out.println("avg_age:"+avg_age);
  // }

  // Session session = factory.openSession();
  // Criteria criteria = session.createCriteria(User.class);
  // criteria.setProjection(Projections.groupProperty("addr"));
  // List<String> addrs = criteria.list();
  // for(String addr : addrs){
  // System.out.println(addr);
  // }

  // Session session = factory.openSession();
  // Criteria criteria = session.createCriteria(User.class);
  // ProjectionList projectionList = Projections.projectionList();
  // projectionList.add(Projections.groupProperty("addr"));
  // projectionList.add(Projections.avg("age"));
  // criteria.setProjection(projectionList);
  // List<Object[]> avgs = criteria.list();
  // for(Object[] avg : avgs){
  // System.out.println(avg[0]+":"+avg[1]);
  // }

  DetachedCriteria detachedCriteria = DetachedCriteria 延迟和session关联
    .forClass(User.class);
  detachedCriteria.add(Restrictions.eq("addr", "dalian"));

Session session = factory.openSession();
Criteria criteria = detachedCriteria.getExecutableCriteria(session);

  List<User> users = criteria.list();
  for (User user : users) {
   System.out.println(user.introduce());
   System.out.println("**********************");
  }

session.close();
}

2.HQL

public void testHQL() {
  // Session session = factory.openSession();
  // String hql = "select avg(age),addr from User group by addr";
  // Query query = session.createQuery(hql);
  // List<float[]> avg_ages = query.list();
  // for(float[] avg_age : avg_ages){
  // System.out.println(avg_age[1]+":"+avg_age[0]);
  // System.out.println("**********************");
  // }

  // Session session = factory.openSession();
  // String hql = "select name,age from User";
  // Query query = session.createQuery(hql);
  // List<Object[]> infos = query.list();
  // for(Object[] info : infos){
  // System.out.println(info[0]+":"+info[1]);
  // System.out.println("**********************");
  // }

  // Session session = factory.openSession();
  // String hql = "update User set age = 18 where name ='tie' and
  // addr='dalian'";
  // Query query = session.createQuery(hql);
  // query.executeUpdate();
  // session.beginTransaction().commit();

  // Session session = factory.openSession();
  // String hql = "delete User where name ='tie' and addr='dalian'";
  // Query query = session.createQuery(hql);
  // query.executeUpdate();
  // session.beginTransaction().commit();

  // Session session = factory.openSession();
  // String hql = "from User where addr=? and name=?";
  // Query query = session.createQuery(hql);
  // query.setString(0, "dalian");
  // query.setString(1, "tie");
  // List<User> users = query.list();
  // for(User user : users){
  // System.out.println(user.introduce());
  // System.out.println("**********************");
  // }

  // Session session = factory.openSession();
  // String hql = "from User u where u.addr=:address and name=:name";
  // Query query = session.createQuery(hql);
  // query.setString("address", "dalian");
  // query.setString("name", "tie");
  // List<User> users = query.list();
  // for(User user : users){
  // System.out.println(user.introduce());
  // System.out.println("**********************");
  // }

把查询语句写到映射文件中

<query name="org.tie.queryUser">
from User u where u.addr=:address and name=:name
</query>

  Session session = factory.openSession();
  Query query = session.getNamedQuery("org.tie.queryUser");
  query.setString("address", "dalian");
  query.setString("name", "tie");
  List<User> users = query.list();
  for (User user : users) {
   System.out.println(user.introduce());
   System.out.println("**********************");
  }

session.close();
}

3.SQL

SQLQuery

<sql-insert>、<sql-update>、<sql-delete>

public void testSQL() {
  // String sql = "select {u.*} from user u";
  // Session session = factory.openSession();
  // SQLQuery sqlQuery = session.createSQLQuery(sql);
  // sqlQuery.addEntity("u",User.class);
  // List<User> users = sqlQuery.list();
  // for(User user : users){
  // System.out.println(user.introduce());
  // System.out.println("**********************");
  // }

<sql-query name="org.tie.sqlQuery">
select {u.*} from user u
<return alias="u" class="org.tie.User"></return>
</sql-query>

  // Session session = factory.openSession();
  // Query query = session.getNamedQuery("org.tie.sqlQuery");
  // List<User> users = query.list();
  // for(User user : users){
  // System.out.println(user.introduce());
  // System.out.println("**********************");
  // }

  <sql-insert>
   insert into user(addr,age,name) values(?,?,?)
  </sql-insert>

Session session = factory.openSession();
  User user = new User();
  user.setName("name");
  user.setAge(3);
  user.setAddr("addr");
  session.save(user);
  session.beginTransaction().commit();

session.close();
}

分页：

public void testPage() {
  // Session session = factory.openSession();
  // String hql = "from User";
  // Query query = session.createQuery(hql);
  // query.setMaxResults(2);
  // query.setFirstResult(0);
  // List<User> users = query.list();
  // for(User user : users){
  // System.out.println(user.introduce());
  // System.out.println("**********************");
  // }

session.close();
}

}