HQL简单用法
来源：互联网 发布：潍坊发电机组网络销售 编辑：程序博客网 时间：2024/05/22 18:55
总共涉及到四个类：
(1)Category.java
package edu.smc.hibernate;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;@Entitypublic class Category {private int id;private String name;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}}
(2)Topic.java
package edu.smc.hibernate;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;import javax.persistence.ManyToOne;@Entitypublic class Topic {private int id;private String title;private Category category;@Id@GeneratedValuepublic int getId() {return id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public Category getCategory() {return category;}public void setCategory(Category category) {this.category = category;}public void setId(int id) {this.id = id;} @ManyToOnepublic Category getCatetory() {return category;}public void setCatetory(Category catetory) {this.category = category;}}
(3)Msg.java
package edu.smc.hibernate;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;import javax.persistence.ManyToOne;@Entitypublic class Msg {private int id;private String cont;public String getCont() {return cont;}public void setCont(String cont) {this.cont = cont;}private Topic topic;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}@ManyToOnepublic Topic getTopic() {return topic;}public void setTopic(Topic topic) {this.topic = topic;}}
(4)MsgInfo.java
package edu.smc.hibernate;public class MsgInfo {private int id;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getCont() {return cont;}public void setCont(String cont) {this.cont = cont;}public String getTopicName() {return topicName;}public void setTopicName(String topicName) {this.topicName = topicName;}public String getCategoryName() {return categoryName;}public void setCategoryName(String categoryName) {this.categoryName = categoryName;}private String cont;private String topicName;private String categoryName;public MsgInfo(int id,String cont,String topicName,String categoryName){this.id=id;this.cont=cont;this.topicName=topicName;this.categoryName=categoryName;}}1.取出所有的Category对象 .
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category");List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getName());}session.getTransaction().commit();session.close();2.取出Category.name>c5 的对象
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c where c.name > 'c5'");List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getName());}session.getTransaction().commit();session.close();
3.取出所有Category对象，并用Category.name进行倒序排序
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c order by c.name desc");List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getName());}session.getTransaction().commit();session.close();
4.取出id大约参数min并且小于参数max的Category对象
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c where c.id > :min and c.id < :max").setInteger("min", 2).setInteger("max", 8);List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getId() + "-" + c.getName());}session.getTransaction().commit();session.close();
5.取出id在某两个数之间的Category对象（预处理方式）
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c where c.id > ? and c.id < ?");q.setParameter(0, 2).setParameter(1, 8);List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getId() + "-" + c.getName());}session.getTransaction().commit();session.close();
6.分页
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c order by c.name desc");q.setMaxResults(4); /*设置每页最多的记录数*/q.setFirstResult(2);/*设置从那一条记录开始取*/List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getId() + "-" + c.getName());}session.getTransaction().commit();session.close();7.值取出 Category对象的 name,id 并且按照name进行降序排序
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select c.id, c.name from Category c order by c.name desc");List<Object[]> categories = (List<Object[]>)q.list();for(Object[] o : categories) {System.out.println(o[0] + "-" + o[1]);}session.getTransaction().commit();session.close();8.取出Catetory id为1的Topic对象
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Topic t where t.category.id = 1");List<Topic> topics = (List<Topic>)q.list();for(Topic t : topics) {System.out.println(t.getTitle());}session.getTransaction().commit();session.close();
9.取出Category id为1的 Topic下的Msg对象
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Msg m where m.topic.category.id = 1");for(Object o : q.list()) {Msg m = (Msg)o;System.out.println(m.getCont());}session.getTransaction().commit();session.close();
10.取出一些数据组成一个新的对象（VO,DTO）
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select new edu.smc.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg");for(Object o : q.list()) {MsgInfo m = (MsgInfo)o;System.out.println(m.getCont());}session.getTransaction().commit();session.close();
11.Join的写法
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select t.title, c.name from Topic t join t.category c "); /** 不能直接写Category名，而必须写t.category,因为有可能存在多个成员变量（属于一个类），需要指明用哪一个成员变量的连接条件来做连接*/for(Object o : q.list()) {Object[] m = (Object[])o;System.out.println(m[0] + "-" + m[1]);}session.getTransaction().commit();session.close();
12.使用uniqueResult方法（当确定返回的实例只有一个或者null时 用uniqueResult()方法）
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Msg m where m = :MsgToSearch "); /*这里是对象相等*/Msg m = new Msg();m.setId(1);q.setParameter("MsgToSearch", m); /*设置参数为 Msg对象*/Msg mResult = (Msg)q.uniqueResult(); /*使用 Query 的 uniqueResult()方法*/System.out.println(mResult.getCont());session.getTransaction().commit();session.close();
13 .获取总个数
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select count(*) from Msg m");long count = (Long)q.uniqueResult();System.out.println(count);session.getTransaction().commit();session.close();
14.max , min ,avg, sum的用法
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select max(m.id), min(m.id), avg(m.id), sum(m.id) from Msg m");Object[] o = (Object[])q.uniqueResult();System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]);session.getTransaction().commit();session.close();
15. between....and
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Msg m where m.id between 3 and 5");for(Object o : q.list()) {Msg m = (Msg)o;System.out.println(m.getId() + "-" + m.getCont());}session.getTransaction().commit();session.close();16.集合的用法
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Msg m where m.id in (3,4, 5)");for(Object o : q.list()) {Msg m = (Msg)o;System.out.println(m.getId() + "-" + m.getCont());}session.getTransaction().commit();session.close();17. is not null
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Msg m where m.cont is not null");for(Object o : q.list()) {Msg m = (Msg)o;System.out.println(m.getId() + "-" + m.getCont());}session.getTransaction().commit();session.close();18.empty
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Topic t where t.msgs is empty");for(Object o : q.list()) {Topic t = (Topic)o;System.out.println(t.getId() + "-" + t.getTitle());}session.getTransaction().commit();session.close();
19.like( % 代表一个或多个，_ 代表一个)
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Topic t where t.title like '%5'");for(Object o : q.list()) {Topic t = (Topic)o;System.out.println(t.getId() + "-" + t.getTitle());}session.getTransaction().commit();session.close(); Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Topic t where t.title like '_5'"); for(Object o : q.list()) { Topic t = (Topic)o; System.out.println(t.getId() + "-" + t.getTitle()); } session.getTransaction().commit(); session.close();20.内置函数（不常用）
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select lower(t.title)," + "upper(t.title)," + "trim(t.title)," + "concat(t.title, '***')," + "length(t.title)" +" from Topic t ");for(Object o : q.list()) {Object[] arr = (Object[])o;System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] + "-" + arr[3] + "-" + arr[4] + "-");}session.getTransaction().commit();session.close(); Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("select abs(t.id)," + "sqrt(t.id)," + "mod(t.id, 2)" + " from Topic t "); for(Object o : q.list()) { Object[] arr = (Object[])o; System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] ); } session.getTransaction().commit(); session.close(); Session session = sf.openSession(); /*日期，时间，时间戳*/ session.beginTransaction(); Query q = session.createQuery("select current_date, current_time, current_timestamp, t.id from Topic t"); for(Object o : q.list()) { Object[] arr = (Object[])o; System.out.println(arr[0] + " | " + arr[1] + " | " + arr[2] + " | " + arr[3]); } session.getTransaction().commit(); session.close();21.日期比较
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Topic t where t.createDate < :date");q.setParameter("date", new Date());for(Object o : q.list()) {Topic t = (Topic)o;System.out.println(t.getTitle());}session.getTransaction().commit();session.close();22.分组 Having
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title having count(*) >= 1") ;for(Object o : q.list()) {Object[] arr = (Object[])o;System.out.println(arr[0] + "|" + arr[1]);}session.getTransaction().commit();session.close();23.子查询
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)") ;for(Object o : q.list()) {Topic t = (Topic)o;System.out.println(t.getTitle());}session.getTransaction().commit();session.close();24.关键字ALL的使用
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id, 2)= 0) ") ;for(Object o : q.list()) {Topic t = (Topic)o;System.out.println(t.getTitle());}session.getTransaction().commit();session.close();25.Exist(可用in实现exist的功能，exist的效率更高，所以一般选用exist)
Session session = sf.openSession();session.beginTransaction();// t.id not in (1)Query q = session.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id=t.id)") ;//Query q = session.createQuery("from Topic t where exists (select m.id from Msg m where m.topic.id=t.id)") ;for(Object o : q.list()) {Topic t = (Topic)o;System.out.println(t.getTitle());}session.getTransaction().commit();session.close();26.更新
Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("update Topic t set t.title = upper(t.title)") ;q.executeUpdate();q = session.createQuery("from Topic");for(Object o : q.list()) {Topic t = (Topic)o;System.out.println(t.getTitle());}session.createQuery("update Topic t set t.title = lower(t.title)").executeUpdate();session.getTransaction().commit();session.close();27.命名查询（便于集中管理）
在类中写
@NamedQueries(
{
@NamedQuery(name="topic.selectCertainTopic", query="from Topic t where t.id = :id")
}
)
Session session = sf.openSession();session.beginTransaction();Query q = session.getNamedQuery("topic.selectCertainTopic");q.setParameter("id", 5);Topic t = (Topic)q.uniqueResult();System.out.println(t.getTitle());session.getTransaction().commit();session.close();28.Native SQL
Session session = sf.openSession();session.beginTransaction();SQLQuery q = session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getName());}session.getTransaction().commit();session.close();
注：HQL 应该和导航相结合，共同为查询提供服务。
- HQL简单用法
- HQL查简单询语句用法Query
- hql用法
- Hql用法
- Hql用法
- Hql用法
- HQL 语言基本用法
- HQL 语言基本用法
- HQL 语言基本用法
- HQL 语言基本用法
- HQL 语言基本用法
- HQL语句的用法
- HQL具体用法
- HQL语言 like 用法
- HQL 语言基本用法
- HQL具体用法
- Hql的用法
- hql的基本用法
- 关于很多概念
- java例程练习（枚举Enum类）
- asp.net 把word转化成二进制文件存储到数据库
- Zune自动同步音乐;视频教程
- ASP.NET 中实现 读取数据库二进制word并下载
- HQL简单用法
- NDC2012：玩网游是为了满足心理需求
- OpenNI的安装与开发环境配置
- WP7手机Zune官方升级教程
- Desire
- Eclipse 工程 ubuntu 乱码
- 海量存储系列之一
- 弹性系数法 预测
- XAP部署错误代码大全
