1、第一种,用?占位符,如:
//登录(用?占位符)
public List LoginUser(UserPO up)throws Exception{
Session session = HibernateSessionFactory.getSession();
String hql = "from UserPO where name = ? and pwd= ?";
Query query = session.createQuery(hql);
query.setString(0, up.getName());
query.setString(1, up.getPwd());
List list = query.list();
session.close();
return list;
}
2、用“:+命名”占位符,如:
//登录(用":命名"占位符)
public List LoginUser2(UserPO up)throws Exception{
Session session = HibernateSessionFactory.getSession();
String hql = "from UserPO where name = :n and pwd= :p";
Query query = session.createQuery(hql);
query.setString("n", up.getName());
query.setString("p", up.getPwd());
List list = query.list();
session.close();
return list;
}2.1、使用这种占位符还可以这样设值,如://登录(用":命名"占位符,用setParameter设值)
public List LoginUser3(UserPO up)throws Exception{
Session session = HibernateSessionFactory.getSession();
String hql = "from UserPO where name = :n and pwd= :p";
Query query = session.createQuery(hql);
query.setParameter("n", up.getName());
query.setParameter("p",up.getPwd());
List list = query.list();
session.close();
return list;
}
使用这种方式不需要写明映射的类型,Hibernate会通过配置自动给我们转,但是由于Hibernate有两种日期格式:Date和TIMESTAMP,所以对于日期类型必须写明映射的类型。写法: 3、按照对象进行参数绑定,如: //登录(用":命名"占位符,用setProperties设值,命名参数必须要与被绑定的属性名相同)
public List LoginUser4(UserPO up)throws Exception{
Session session = HibernateSessionFactory.getSession();
String hql = "from UserPO where name = :name and pwd= :pwd";
Query query = session.createQuery(hql);
query.setProperties(up);
List list = query.list();
session.close();
return list;
}
4、使用条件查询(Criteria),如: //登录(用条件查询 Criteria)完全脱离sql语句和hql语句
public List LoginUser5(UserPO up)throws Exception{
Session session = HibernateSessionFactory.getSession();
Criteria cri = session.createCriteria(UserPO.class);
cri.add(Restrictions.eq("name", up.getName()));
cri.add(Restrictions.eq("pwd", up.getPwd()));
List list = cri.list();
session.close();
return list;
}
5、离线条件查询,如:
//登录(用离线条件查询 DetachedCriteria)
public List LoginUser6(UserPO up)throws Exception{
Session session = HibernateSessionFactory.getSession();
DetachedCriteria dc = DetachedCriteria.forClass(UserPO.class);
dc.add(Restrictions.eq("name", up.getName()));
dc.add(Restrictions.eq("pwd", up.getPwd()));
Criteria cri = dc.getExecutableCriteria(session);
List list = cri.list();
session.close();
return list;
}
使用离线可以将其写在业务层,以参数的形式传入,以减少DAO的代码。
6、分页查询:分页查询是数据库应用中的处理方式,query和criteria接口都提供了用于分页查询的方法:
1)setFirstResult(int):指定从哪一个对象开始查询,参数是索引位置,从0开始。
2)setMaxResult(int):指定一次最多查询的对象数量。
// 首页上最新商品的查询
public List<Product> findNew() {
// 使用离线条件查询:
DetachedCriteria criteria = DetachedCriteria.forClass(Product.class);
// 按日期进行倒序排序:
criteria.addOrder(Order.desc("pdate"));
// 执行查询:
List<Product> list = this.getHibernateTemplate().findByCriteria(criteria, 0, 10);
return list;
}
// 根据商品ID查询商品
public Product findByPid(Integer pid) {
return this.getHibernateTemplate().get(Product.class, pid);
}
// 根据分类id查询商品的个数
public int findCountCid(Integer cid) {
String hql = "select count(*) from Product p where p.categorySecond.category.cid = ?";
List<Long> list = this.getHibernateTemplate().find(hql,cid);
if(list != null && list.size() > 0){
return list.get(0).intValue();
}
return 0;
}
// 根据分类id查询商品的集合
public List<Product> findByPageCid(Integer cid, int begin, int limit) {
// select p.* from category c,categorysecond cs,product p where c.cid = cs.cid and cs.csid = p.csid and c.cid = 2
// select p from Category c,CategorySecond cs,Product p where c.cid = cs.category.cid and cs.csid = p.categorySecond.csid and c.cid = ?
String hql = "select p from Product p join p.categorySecond cs join cs.category c where c.cid = ?";
// 分页另一种写法:
List<Product> list = this.getHibernateTemplate().execute(new PageHibernateCallback<Product>(hql, new Object[]{cid}, begin, limit));
if(list != null && list.size() > 0){
return list;
}
return null;
}
封装的公共类
package cn.itcast.shop.utils;
import java.sql.SQLException;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
public class PageHibernateCallback<T> implements HibernateCallback<List<T>>{
private String hql;
private Object[] params;
private int startIndex;
private int pageSize;
public PageHibernateCallback(String hql, Object[] params,
int startIndex, int pageSize) {
super();
this.hql = hql;
this.params = params;
this.startIndex = startIndex;
this.pageSize = pageSize;
}
public List<T> doInHibernate(Session session) throws HibernateException,
SQLException {
//1 执行hql语句
Query query = session.createQuery(hql);
//2 实际参数
if(params != null){
for(int i = 0 ; i < params.length ; i ++){
query.setParameter(i, params[i]);
}
}
//3 分页
query.setFirstResult(startIndex);
query.setMaxResults(pageSize);
return query.list();
}
}
public class PageBean<T> {
private int page; // 当前页数
private int totalCount; // 总记录数
private int totalPage; // 总页数
private int limit; // 每页显示的记录数
private List<T> list; // 每页显示数据的集合.
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getLimit() {
return limit;
}
public void setLimit(int limit) {
this.limit = limit;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
}