hibernate分区mysql查询_hibernate与mysql之间的查询总结

hibernate与mysql之间的查询

//简单的查询;

@Test

public void testHQL_01() {

Session session = sf.openSession();

session.beginTransaction();

Query q = session.createQuery("from Category");

List categories = (List)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 c.name > 'c5'");

List categories = (List)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 c.name desc");

List categories = (List)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 c.name desc");

List categories = (List)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 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.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 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.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 c.name desc");

//每页显示多少行

q.setMaxResults(5);

//每页从第几行开始

q.setFirstResult(4);

List categories = (List)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 c.id, c.name from Category c order by c.name desc");

List categories = (List)q.list();

//q.list()返回的是以List集合的数组形式,所以遍历List集合的数组中，在数组中o[0]的位置是c.id,o[1]的位置是c.name;

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.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, c.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_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(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();

}

//between and的查询

@Test

public void testHQL_14() {

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();

}

//in的查询

@Test

public void testHQL_15() {

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();

}

//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();

}

分享到：

2017-02-01 12:19

浏览 66

评论