一、hql

1. 什么是hql
HQL是Hibernate Query Language的缩写

查全部

2. hql和sql区别/异同
HQL SQL
类名/属性 表名/列名
区分大小写,关键字不区分大小写 不区分大小写
别名 别名
?，从下标0开始计算位置(hibernate5之后不支持) ?，从顺序1开始计算位置
:命名参数 不支持:命名参数
面向对象的查询语言 面向结构查询语言

hqlTest

public class HqlTest {

Session session=null;  
Transaction trans=null;  
BookDao bookDao=new BookDao();

// @Before
// public void setUp() {
// session=SessionFactoryUtils.openSession();
// trans=session.beginTransaction();
// }
//
// @After
// public void tearDown() {
// trans.commit();
// SessionFactoryUtils.closeSession();
// }

@SuppressWarnings("deprecation")

@Test  
public  void test() {  
    String hql="";  
    //一.HQL  
    //1.HQL之查询返回List<T>  

// hql="from Book";
// Query createQuery = session.createQuery(hql, Book.class);
// List list = createQuery.list();
// for (Book book : list) {
// System.out.println(book);
// }

    //2.HQL之查询返回单个属性  

// hql="select b.bookName from Book b";
// Query query = session.createQuery(hql);
// List list = query.list();
// for (Object object : list) {
// System.out.println(object);
// }
//3.HQL之查询返回多个属性
// hql="select bookId,bookName from Book";
// Query createQuery = session.createQuery(hql);
// List list = createQuery.list();
// for (Object object : list) {
// //Object==object[]
// System.out.println(Arrays.toString((Object[])object));
// }
//4.HQL之new Map
// hql="select new Map(b.bookId as bid, b.bookName as bname) from Book b";
// Query query = session.createQuery(hql);
// List> list = query.list();
// for (Map map : list) {
// System.out.println(map);
// }

// 5.HQL之new 构造方法
// hql="select new Book(b.bookId,b.bookName,b.price) from Book b ";
// Query createQuery = session.createQuery(hql);
// List list = createQuery.list();
// for (Object object : list) {
// System.out.println(object);
// }
// 6.HQL之命名参数
//hql="from Book where price>:min and price<:max"; // hql="from Book where bookId in (:ids)"; // Query query = session.createQuery(hql, Book.class);
// query.setParameter("min", 40f);
// query.setParameter("max", 1000f);
// query.setParameterList("ids",new Integer[] {1,2,3});
// List list = query.list();
// for (Book book : list) {
// System.out.println(book);
// }

    //7.HQL之聚合函数(count/sum/avg/min/max)  

// hql="select sum(b.price) as total from Book b";
// Query createQuery = session.createQuery(hql);
// List list = createQuery.list();
// for (Object object : list) {
// System.out.println(object);
// }
// 8.HQL之连接查询
// hql="select b.bookId,b.bookName,b.price"
// + ",c.categoryId,c.categoryName from Book b inner join "
// + "b.categroies c";
// Query query = session.createQuery(hql);
// List list = query.list();
// for (Object object : list) {
// System.out.println(Arrays.toString((Object[])object));
// }
// 9.HQL之分页查询
// int page=1;
// int rows=2;
//
// hql="from Book";
// Query query = session.createQuery(hql, Book.class);
// query.setFirstResult((page-1)* rows);
// query.setMaxResults(rows);
// List list = query.list();
// for (Book book : list) {
// System.out.println(book);
// }

    //二.原生sql  
    //1.原生sql查询返回Object\[\]  
    String sql="";  

// sql="select book_id,book_name,price from t_book_hb";
// NativeQuery query = session.createNativeQuery(sql);
// List list = query.list();
// for (Object object : list) {
// System.out.println(Arrays.toString((Object[])object));
// }

    //2.原生sql返回list<T>  

// sql="select book_id,book_name,price from t_book_hb";
// NativeQuery query = session.createNativeQuery(sql, Book.class);
// List list = query.list();
// for (Book book : list) {
// System.out.println(book);
// }

    //3.原生sql查询返回单个属性  

// sql="select book_name from t_book_hb";
// NativeQuery query = session.createNativeQuery(sql);
// List list = query.list();
// for (Object object : list) {
// System.out.println(object);
// }

    //4.原生sql查询返回List<Map>,适用于多表联查返回结果  
    sql="SELECT \*  FROM t\_book\_hb b,t\_book\_category\_hb bc,t\_category\_hb c\\r\\n" +  
            "WHERE b.\`book\_id\`=bc.\`bid\` AND bc.\`cid\`=c.\`category\_id\`";  
    NativeQuery query = session.createNativeQuery(sql);

    //将结果集转换为Map集合  
    query.setResultTransformer(CriteriaSpecification.ALIAS\_TO\_ENTITY\_MAP);  
    List<Map<String,Object>> list= query.list();  
    for (Map<String,Object> map : list) {  
    System.out.println(map);  
}

}

BaseDao

public Session getSession() {  
    return SessionFactoryUtils.openSession();  
}

/\*\*  
 \* 设置Query的查询参数  
 \* @param params  
 \* @param query  
 \* @return  
 \*/  
public void setParamters(Map<String,Object> params,Query query) {  
    if(null==params||0==params.size())  
        return;

    Set<Entry<String, Object>> set = params.entrySet();  
    String name=null;  
    Object value=null;  
    for (Entry<String, Object> entry : set) {  
        name=entry.getKey();  
        value=entry.getValue();  
        //判断参数是否是Collection，一般用于List/Set集合参数时使用  
        if(value instanceof Collection)  
            query.setParameterList(name, (Collection)value);  
        //判断参数是否是Object\[\]  
        else if(value instanceof Object\[\])  
            query.setParameterList(name, (Object\[\])value);  
        else  
            query.setParameter(name, value);  
    }  
}

/\*\*  
 \* 将普通hql语句转换成查询总记录数的hql语句  
 \* @param hql  
 \* @return  
 \*/  
public String countSql(String hql) {  
    //from Book  
    //select \* from Book  
    int start=hql.toUpperCase().indexOf("FROM");  
    return "select count(1) "+hql.substring(start);  
}

/\*\*  
 \* 查询(支持分页)  
 \* @param hql       普通hql语句  
 \* @param params    请求参数  
 \* @param pageBean  分页对象  
 \* @return  
 \*/  
@SuppressWarnings("rawtypes")  
public List executeQuery(String hql,Map<String,Object> params,PageBean pageBean) {  
    Session session=getSession();  
    Query query=null;  
    //1.根据满足条件查询总记录数  
    if(null!=pageBean&&pageBean.isPagination()) {  
        //select count(1) from Book where  
        String countHql=this.countSql(hql);  
        query = session.createQuery(countHql);  
        this.setParamters(params, query);  
    }  
    query=session.createQuery(hql);  
    //2.根据满足条件查询分页记录  
    if(null!=pageBean&&pageBean.isPagination()) {  
        query.setFirstResult(pageBean.getStartIndex());  
        query.setMaxResults(pageBean.getRows());  
    }  
    this.setParamters(params, query);  
    return query.list();  
}

BookDao

public class BookDao extends BaseDao{

public void addBook(Book book) {  
    Session session = SessionFactoryUtils.openSession();  
    Transaction transaction = session.beginTransaction();  
    //保存  
    session.save(book);  
    transaction.commit();  
    SessionFactoryUtils.closeSession();  
}

public Book get(Book book) {  
    Session session = SessionFactoryUtils.openSession();  
    Transaction transaction = session.beginTransaction();  
    //保存  
    Book b = session.get(Book.class, book.getBookId());  
    if(b!=null) {  
        Hibernate.initialize(b.getCategroies());  
    }  
    transaction.commit();  
    SessionFactoryUtils.closeSession();  
    return b;  
}

//被控方  
public void delBook(Book book) {  
    Session session = SessionFactoryUtils.openSession();  
    Transaction transaction = session.beginTransaction();  
    Book b = session.get(Book.class, book.getBookId());  
    if(null!=b) {  
        //解除关联关系  
        Set<Category> categroies = b.getCategroies();  
        for (Category category : categroies) {  
            b.getCategroies().remove(category);  
        }  
        session.delete(b);  
    }

    transaction.commit();  
    SessionFactoryUtils.closeSession();

}

/***************************通用查询***************************/

public List<Book> list1(Book book,PageBean pageBean) {  
    Session session = SessionFactoryUtils.openSession();  
    Transaction transaction = session.beginTransaction();

    //下面代码处理的是book实体类的条件查询  
    String bookName= book.getBookName();

    //sql语句where后面可以直接写true，而hql不能出现true  
    String hql= "from Book where 1=1";  
    if(StringUtils.isNotBlank(bookName)) {  
        hql +=" and bookName like :bookName";  
    }  
    Query query=session.createQuery(hql);  
    if(StringUtils.isNotBlank(bookName)) {  
        query.setParameter("bookName",bookName);  
    }

    //处理的是分页  
    if(pageBean !=null && pageBean.isPagination()) {  
         query.setFirstResult(pageBean.getStartIndex());  
         query.setMaxResults(pageBean.getRows());  
    }  
     List list = query.list();

    transaction.commit();  
    session.close();  
    return list;

}

public List<Book> list2(Book book, PageBean pageBean) {  
    Session session = SessionFactoryUtils.openSession();  
    Transaction transaction = session.beginTransaction();

// 下面的代码处理的是book实体类的条件查询
String bookName = book.getBookName();
Map map = new HashMap();
// sql语句where后面可以直接写true，而hql语句不能出现true
String hql = "from Book where 1=1";
if(StringUtils.isNotBlank(bookName)) {
hql += " and bookName like :bookName";
map.put("bookName", bookName);
}
List list = super.executeQuery(hql, map, pageBean);

    transaction.commit();  
    session.close();  
    return list;  
}  

}

测试

@Test
public void testList1() {
Book book = new Book();
// book.setBookName("T226");
PageBean pageBean = new PageBean();
pageBean.setPage(2);
List list1 = this.bookDao.list1(book, pageBean);
for (Book b : list1) {
System.out.println(b);
}
}

@Test
public void testList2() {
Book book = new Book();
book.setBookName("%西游记%");
PageBean pageBean = new PageBean();
// pageBean.setPage(2);
List list1 = bookDao.list2(book, pageBean);
for (Book b : list1) {
System.out.println(b);
}
}