hibernate中hql查询，这里是例子程序，比较完整，忘记时可以对比复习(三)
() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Category c where c.category_name > 'c5'");
List categories = (List)q.list();
for(Category c : categories) {
System.out.println(c.getCategory_name());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_03() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Category c where c.category_name > 'c5' order by c.category_name desc");
List categories = (List)q.list();
for(Category c : categories) {
System.out.println(c.getCategory_name());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_04() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select distinct c from Category c order by c.category_name desc");
List categories = (List)q.list();
for(Category c : categories) {
System.out.println(c.getCategory_name());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_05() {
Session session = sf.openSession();
session.beginTransaction();
/*Query q = session.createQuery("from Category c where c.id > :min and c.id < :max");
//q.setParameter("min", 2);
//q.setParameter("max", 8);
q.setInteger("min", 2);
q.setInteger("max", 8);*/
Query q = session.createQuery("from Category c where c.id > :min and c.id < :max")
.setInteger("min", 2)
.setInteger("max", 8);
List categories = (List)q.list();
for(Category c : categories) {
System.out.println(c.getId() + "-" + c.getCategory_name());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_06() {
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);
q.setParameter(1, 8);
List categories = (List)q.list();
for(Category c : categories) {
System.out.println(c.getId() + "-" + c.getCategory_name());
}
session.getTransaction().commit();
session.close();
}
//分页
@Test
public void testHQL_07() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Category c order by c.category_name asc");
q.setMaxResults(4);
q.setFirstResult(1);
List categories = (List)q.list();
for(Category c : categories) {
System.out.println(c.getId() + "-" + c.getCategory_name());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_08() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select c.id, c.category_name from Category c order by c.category_name desc");
List categories = (List)q.list();
for(Object[] o : categories) {
System.out.println(o[0] + "-" + o[1]);
}
session.getTransaction().commit();
session.close();
}
//设定fetch type 为lazy后将不会有第二条sql语句
@Test
public void testHQL_09() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.category.id = 1");
List topics = (List)q.list();
for(Topic t : topics) {
System.out.println(t.getTopic_title());
//System.out.println(t.getCategory().getName());
}
session.getTransaction().commit();
session.close();
}
//设定fetch type 为lazy后将不会有第二条sql语句
@Test
public void testHQL_10() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.category.id = 1");
List topics = (List)q.list();
for(Topic t : topics) {
System.out.println(t.getTopic_title());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_11() {
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.getMsg_cont());
}
session.getTransaction().commit();
session.close();
}
//了解即可
//VO Value Object
//DTO data transfer object
@Test
public void testHQL_12() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select new com.bjsxt.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();
}
//动手测试left right join
//为什么不能直接写Category名，而必须写t.category
//因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量的连接条件来做连接
@Test
public void testHQL_13() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select t.topic_title, c.category_name from Topic t join t.category c "); //join Category c
for(Object o : q.list()) {
Object[] m = (Object[])o;
System.out.println(m[0] + "-" + m[1]);
}
session.getTransaction().commit();
session.close();
}
//学习使用uniqueResult
@Test
public void testHQL_14() {
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 mResult = (Msg)q.uniqueResult();
System.out.println(mResult.getMsg_cont());
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_15() {
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();
}
@Test
public void testHQL_16() {
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();
}
@Test
public void testHQL_17() {
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.getMsg_cont());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_18() {
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.getMsg_cont());
}
session.getTransaction().commit();
session.close();
}
//is null 与 is not null
@Test
public void testHQL_19() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m.msg_cont is not null");
for(Object o : q.list()) {
Msg m = (Msg)o;
System.out.println(m.getId() + "-" + m.getMsg_cont());
}
session.getTransaction().commit();
session.close();
}
}