/*
* 功能:获取没有回复的帖子
* is empty and is not empty 判断一个集合是否为空
select
topic0_.id as id2_, topic0_.category_id as category3_2_, topic0_.name as name2_
from Topic topic0_
where
not (exists (select msgs1_.id from Msg msgs1_ where topic0_.id=msgs1_.topic_id))
将topic中的每条记录和msg进行比较(通过id比较),若存在,则说明有回帖,不选该帖子。当不存在时,才输出
*/
@Test
public void HQ_1() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("from Topic t where t.msgs is empty");
List<Topic> topcis=(List<Topic>)q.list();
for(Topic t:topcis){
System.out.println(t.getName());
}
session.getTransaction().commit();
}
/*
* 模糊查询:
* %表示任意0个或者多个字符。可匹配任意类型和长度的字符
*/
@Test
public void HQ_2() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("from Topic t where t.name like '%5'");
List<Topic> topcis=(List<Topic>)q.list();
for(Topic t:topcis){
System.out.println(t.getName());
}
session.getTransaction().commit();
}
/*
* 模糊查询
* 表示任意单个字符。匹配任意单个字符。
*
*/
@Test
public void HQ_3() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("from Topic t where t.name like '_5'");
List<Topic> topcis=(List<Topic>)q.list();
for(Topic t:topcis){
System.out.println(t.getName());
}
session.getTransaction().commit();
}
/*
* 对一些字段使用函数
*
*/
@Test
public void HQ_4() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("select lower(t.name),upper(t.name),trim(t.name),concat(t.name,'***'),length(t.name) from Topic t");
List<Object[]> objs=(List<Object[]>)q.list();
for(Object[] obj:objs){
System.out.println(obj[0]+"--"+obj[1]+"--"+obj[2]+"--"+obj[3]+"--"+obj[4]);
}
session.getTransaction().commit();
}
/*
* abs:求绝对值
*sqrt:开平方根
*/
@Test
public void HQ_5() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("select abs(t.id),sqrt(t.id),mod(t.id,2) from Topic t");
List<Object[]> topics=(List<Object[]>)q.list();
for(Object[] obj:topics){
System.out.println(obj[0]+"--"+obj[1]+"--"+obj[2]);
}
session.getTransaction().commit();
}
/*
*
* 经常用到
*/
@Test
public void HQ_6() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("select current_date,current_time,current_timestamp,t.id from Topic t");
List<Object[]> ts=(List<Object[]>)q.list();
for(Object[] obj:ts){
System.out.println(obj[0]+"--"+obj[1]+"--"+obj[2]);//2017-11-04--16:51:25--2017-11-04 16:51:25.0
}
session.getTransaction().commit();
}
/*
* group by:字段必须是select中的字段。
*/
@Test
public void HQ_7() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("select t.name,count(*) from Topic t group by t.name");//count(*)是一组中有几个主题
List<Object[]> ts=(List<Object[]>)q.list();
for(Object[] obj:ts){
System.out.println(obj[0]+"--"+obj[1]);
}
session.getTransaction().commit();
}
/*
*having是对分组后结果进行筛选,having中的字段也必须是select中的字段
*/
@Test
public void HQ_8() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("select t.name,count(*) from Topic t group by t.name having count(*)>=2");
List<Object[]> categories=(List<Object[]>)q.list();
for(Object[] o:categories){
System.out.println(o[0]+"---"+o[1]);
}
session.getTransaction().commit();
}
/*
* 子查询
*/
@Test
public void HQ_9() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("from Topic t where t.id <(select avg(t.id) from Topic t)");
List<Topic> topics=(List<Topic>)q.list();
for(Topic c:topics){
System.out.println(c.getName());
}
session.getTransaction().commit();
}
/*
*all:所有
*/
@Test
public void HQ_10() {
Session session=sf.getCurrentSession();
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)");
List<Topic> topics=(List<Topic>)q.list();
for(Topic m:topics){
System.out.println(m.getName());
}
session.getTransaction().commit();
}
@Test
public void HQ_11() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("from Topic t where not exists(select m.id from Msg m where m.topic.id=t.id)");
List<Topic> topics=(List<Topic>)q.list();
for(Topic t:topics){
System.out.println(t.getName());
}
session.getTransaction().commit();
}
/*
*update and delete
*/
@Test
public void HQ_12() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createQuery("update Topic t set t.name=upper(t.name)");
q.executeUpdate();
q=session.createQuery("from Topic");
List<Topic> topics=(List<Topic>)q.list();
for(Topic t:topics){
System.out.println(t.getName());
}
session.getTransaction().commit();
}
/*
* Native:因为在hibernate.cfg.xml中设置的方言是 <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
* 所以可以用本地语言。
* limit 2,4 从2开始,偏移4个
*/
@Test
public void HQ_13() {
Session session=sf.getCurrentSession();
session.beginTransaction();
Query q=session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);//注意此时select中是实体名,并不是类名
List<Category> cas=q.list();
for(Category c:cas){
System.out.println(c.getName());
}
session.getTransaction().commit();
}
(16)QL2:模糊查询(%,_)、常用的函数、group by、having、子查询、update 、delete、native
最新推荐文章于 2024-01-26 14:54:18 发布