hibernate与mysql之间的查询
//简单的查询;
@Test
public void testHQL_01() {
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();
}
//条件查询;
@Test
public void testHQL_02() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Category c where > 'c5'");
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
session.getTransaction().commit();
session.close();
}
//排序查询;
@Test
public void testHQL_03() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Category c order by desc");
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
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 desc");
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
session.getTransaction().commit();
session.close();
}
//找出位于最小值和最大值之间的查询;
@Test
public void testHQL_05() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Category c where > :min and < :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 > :min and < :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();
}
//占位符的设置
@Test
public void testHQL_06() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Category c where > ? and < ?");
q.setParameter(0, 2)
.setParameter(1, 8);
// q.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();
}
//分页
@Test
public void testHQL_07() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Category c order by desc");
//每页显示多少行
q.setMaxResults(5);
//每页从第几行开始
q.setFirstResult(4);
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getId() + "-" + c.getName());
}
session.getTransaction().commit();
session.close();
}
//查询字段放入集合数组
@Test
public void testHQL_08() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select , from Category c order by desc");
List<Object[]> categories = (List<Object[]>)q.list();
//q.list()返回的是以List集合的数组形式,所以遍历List集合的数组中,在数组中o[0]的位置是,o[1]的位置是;
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 = 1");
List<Topic> topics = (List<Topic>)q.list();
for(Topic t : topics) {
System.out.println(t.getTitle());
//System.out.println(t.getCategory().getName());
}
session.getTransaction().commit();
session.close();
}
//left right join的连接查询
//为什么不能直接写Category名,而必须写t.category
//因为有可能存在多个成员变量(同一个类),需要指明用哪一个成员变量的连接条件来做连接
@Test
public void testHQL_10() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select t.title, 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_11() {
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.getCont());
session.getTransaction().commit();
session.close();
}
//统计查询
@Test
public void testHQL_12() {
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_13() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select max(), min(), avg(), sum() from Msg m");
Object[] o = (Object[])q.uniqueResult();
System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]);
session.getTransaction().commit();
session.close();
}
//between and的查询
@Test
public void testHQL_14() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where 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();
}
//in的查询
@Test
public void testHQL_15() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where 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();
}
//is null 与 is not null
@Test
public void testHQL_16() {
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();
}