6Hibernate进阶----青软S2SH(笔记)

 

 

 

关于关联关系的配置,用注解配置如下(这里引用的jar包是javax.persistence)

//    @ManyToOne(fetch=FetchType.LAZY)
    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="supplier_id",nullable=true)
    private Supplier supplier;

用xml文件配置如下,这个跟注解配置有些不同,就不详解了,反正我也不会用xml配置

 

 

例子

/* 使用HQL对查询结果按照Customer的userName进行降序排序 */
    public static void orderByUserNameByDesc_HQL() {
        // 1.获取session对象
        Session session = HibernateUtils.getSession();
        // 2.编写hql语句
        String hql = "from Customer c order by c.userName desc";
        // 3.以HQL作为参数，调用session的createQuery()方法创建Query对象
        Query query = session.createQuery(hql);
        // 4.调用query对象的list()等方法遍历结果
        List<Customer> list = query.list();
        // 打印结果
        for (Customer c : list) {
            System.out.println(c.getId() + "\t" + c.getUserName());
        }
    }

 

一个使用hql来进行动态查询的使用方法(用map存储参数)

@SuppressWarnings("unchecked")public long getXXXCount(Map<String,String> conditions) throws Exception {
        String hql = "select count(u.id)";
        Map<String,Object> valMap = createPartHql(hql,conditions);
        hql = (String)valMap.get("hql");
        valMap.remove("hql");
        
        Query query = getQueryFromMapAndHql(valMap,hql);
        return (long)query.list().get(0);
    }
private static Map<String,Object> createPartHql(String hql,Map<String,String> conditions){
        Map<String,Object> valMap = new HashMap<>(); 
        boolean conditionsIsNull = true;
        for(Map.Entry<String, String> entry:conditions.entrySet()){  
            if(StringUtils.isNotEmpty(entry.getValue()))
                conditionsIsNull = false;
        } 
　　　　　　hql = hql+" from xxxx u where u.deleteFlg = 0";

            if(StringUtils.isNotEmpty(conditions.get("roleType"))){
                hql = hql+" and u.roleType = :roleType";
                valMap.put("roleType", Integer.parseInt(conditions.get("roleType")));
            }
            if(StringUtils.isNotEmpty(conditions.get("loginId"))){
                hql = hql+" and u.loginId like :loginId";
                valMap.put("loginId", "%"+conditions.get("loginId")+"%");
            }

        valMap.put("hql", hql);
        return valMap;
    }


---------------------------
public Query getQueryFromMapAndHql(Map<String,Object> FieldsMap,String hql){
        Query query = this.getSessionFactory().getCurrentSession().createQuery(hql);
        for(Map.Entry<String, Object> entry : FieldsMap.entrySet()){
            query.setParameter(entry.getKey(), entry.getValue());
        }
        return query;
    }
    public Query getQueryFromMapAndHql(Map<String,String> FieldsMap,Query query){
        for(Map.Entry<String, String> entry : FieldsMap.entrySet()){
            query.setParameter(entry.getKey(), entry.getValue()+"");
        }
        return query;
    }

其他示例:

package com.qst.chapter06.demo;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashSet;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;

import com.qst.chapter06.pojos.Customer;
import com.qst.chapter06.pojos.Order;
import com.qst.chapter06.pojos.Product;
import com.qst.chapter06.util.HibernateUtils;

public class HqlCriteriaBusinessDemo {
    public static void main(String[] args) {
        // findCustomersByAddress_HQL("青岛市");
        // findCustomersByAddress_QBC("青岛市");
        // orderByUserNameByDesc_HQL();
        // orderByUserNameByDesc_QBC();
        // 第2页，每页3条记录
        // List<Customer> list=listPageCustomer_HQL(2,3);
        // List<Customer> list = listPageCustomer_QBC(2, 3);
        // 打印结果
        // for (Customer c : list) {
        // System.out.println(c.getId() + "\t" + c.getUserName());
        // }
        // Customer c = findOneCustomer_HQL();
        // Customer c = findOneCustomer_QBC();
        // System.out.println(c.getId() + "\t" + c.getUserName());
        // List<Customer> list=findCustomersByName1("zhangsan1");
        // List<Customer> list=findCustomersByName2("zhangsan1");
        // //打印结果
        // for (Customer c : list) {
        // System.out.println(c.getId() + "\t" + c.getUserName());
        // }
        // findCustomerByJoin();
        // findCustomerByFetchJoin();
        // findCustomerByLeftJoin();
        // findCustomerByLeftFetch();
        // groupByCustomer();
        // printOrders_HQL();
        // printOrders_QBC();
        // //////////////////////////////////////////////
        // List<Product> listProduct = findProductsByHQL("打印机", 560.0);
        // List<Product> listProduct = findProductsByCriteria("打印机", 560.0);
        // List<Product> listProduct= findProductsByQBE(product);
        // Product product=new Product();
        // product.setName("打印机");
        // product.setPrice(560.0);
        // //////////////////////////////////////////////
//        DetachedCriteria cri = DetachedCriteria.forClass(Product.class);
//        // 根据用户的动态查询条件，创建DetachedCriteria对象
//        cri.add(Restrictions.ilike("name", "打印机", MatchMode.ANYWHERE));
//        cri.add(Restrictions.eq("price", 560.0));
//        List<Product> listProduct = findProducts(cri);
//        for (Product p : listProduct) {
//            System.out.println(p.getId() + "\t" + p.getName() + "\t"
//                    + p.getPrice() + "\t" + p.getDescription());
//        }
        /////////////////////////////////////////////////////////////
        findProductsBySubQuerys();

    }

    /* 使用HQL检索根据地址查询Customer */
    public static void findCustomersByAddress_HQL(String address) {
        // 1.获取session对象
        Session session = HibernateUtils.getSession();
        // 2.编写hql语句
        String hql = "from Customer c where c.address = :address";
        // // 3.以HQL作为参数，调用session的createQuery()方法创建Query对象
        // Query query = session.createQuery(hql);
        // // 4.调用Query对象的setXXX()方法为参数赋值
        // query.setString("address", address);
        // // 5.调用Query对象的list()等方法得到查询结果
        // List<Customer> list = query.list();
        // Query对象匿名方式
        List<Customer> list = session.createQuery(hql)
                .setString("address", address).list();
        // 遍历输出结果
        for (Customer c : list) {
            System.out.println(c.getId() + "\t" + c.getUserName());
        }
    }

    /* 使用QBC检索根据地址查询Customer */
    public static void findCustomersByAddress_QBC(String address) {
        // 1.获取session对象
        Session session = HibernateUtils.getSession();
        // 2.以Customer的Class对象作为参数，创建Criteria对象
        Criteria critera = session.createCriteria(Customer.class);
        // // 3.调用Criteria对象的add()方法，增加Criterion查询条件
        // critera.add(Restrictions.eq("address", address));
        // // 4.执行Criteria的list()方法返回查询结果
        // List<Customer> list = critera.list();
        // Criteria对象匿名方式
        List<Customer> list = session.createCriteria(Customer.class)
                .add(Restrictions.eq("address", address)).list();
        // 遍历输出结果
        for (Customer c : list) {
            System.out.println(c.getId() + "\t" + c.getUserName());
        }
    }

    /* 使用HQL对查询结果按照Customer的userName进行降序排序 */
    public static void orderByUserNameByDesc_HQL() {
        // 1.获取session对象
        Session session = HibernateUtils.getSession();
        // 2.编写hql语句
        String hql = "from Customer c order by c.userName desc";
        // 3.以HQL作为参数，调用session的createQuery()方法创建Query对象
        Query query = session.createQuery(hql);
        // 4.调用query对象的list()等方法遍历结果
        List<Customer> list = query.list();
        // 打印结果
        for (Customer c : list) {
            System.out.println(c.getId() + "\t" + c.getUserName());
        }
    }

    /* 使用QBC检索中的Order类对查询结果按照Customer的userName进行降序排序 */
    public static void orderByUserNameByDesc_QBC() {
        // 1.获取session对象
        Session session = HibernateUtils.getSession();
        // 2.以Customer的Class对象作为参数，创建Criteria对象
        Criteria critera = session.createCriteria(Customer.class);
        // 3.调用criteria对象的addOrder()方法条件排序规则
        critera.addOrder(org.hibernate.criterion.Order.desc("userName"));
        List<Customer> list = critera.list();
        // 打印结果
        for (Customer c : list) {
            System.out.println(c.getId() + "\t" + c.getUserName());
        }
    }

    /* 使用HQL分页查询Customer信息 */
    public static List<Customer> listPageCustomer_HQL(int pageNo, int perPageNum) {
        Session session = HibernateUtils.getSession();
        String hql = "from Customer c order by c.userName desc";
        Query query = session.createQuery(hql);
        query.setFirstResult((pageNo - 1) * perPageNum);
        query.setMaxResults(perPageNum);
        List<Customer> list = query.list();
        return list;
    }

    /* 使用QBC分页查询Customer信息 */
    public static List<Customer> listPageCustomer_QBC(int pageNo, int perPageNum) {
        Session session = HibernateUtils.getSession();
        Criteria criteria = session.createCriteria(Customer.class);
        criteria.setFirstResult((pageNo - 1) * perPageNum);
        criteria.setMaxResults(perPageNum);
        List<Customer> list = criteria.list();
        return list;
    }

    /* 利用HQL检索单个Customer对象 */
    public static Customer findOneCustomer_HQL() {
        Session session = HibernateUtils.getSession();
        String hql = "from Customer c order by c.userName desc";
        Customer customer = (Customer) session.createQuery(hql)
                .setMaxResults(1).uniqueResult();
        return customer;
    }

    /* 利用QBC检索单个Customer对象 */
    public static Customer findOneCustomer_QBC() {
        Session session = HibernateUtils.getSession();
        Customer customer = (Customer) session.createCriteria(Customer.class)
                .addOrder(org.hibernate.criterion.Order.desc("userName"))
                .setMaxResults(1).uniqueResult();
        return customer;
    }

    /* HQL检索3月份的订单对象 */
    public static void printOrders_HQL() {
        Session session = HibernateUtils.getSession();
        // HQL检索日期在指定范围之内
        String hql = "from Order o where o.date between ? and ?";
        // 创建一个日期格式类，用于格式化日期
        SimpleDateFormat dateFormat = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss");
        try {

            List<Order> list = session.createQuery(hql)
                    .setParameter(0, dateFormat.parse("2015-03-01 00:00:00"))
                    .setParameter(1, dateFormat.parse("2015-03-31 23:59:59"))
                    .list();

            // 打印结果
            for (Order o : list) {
                System.out.println(o.getId() + "\t" + o.getDate());
            }
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /* QBC检索3月份的订单对象 */
    public static void printOrders_QBC() {
        Session session = HibernateUtils.getSession();
        // 创建一个日期格式类，用于格式化日期
        SimpleDateFormat dateFormat = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss");
        try {
            // QBC检索
            List<Order> list = session
                    .createCriteria(Order.class)
                    .add(Restrictions.between("date",
                            dateFormat.parse("2015-03-01 00:00:00"),
                            dateFormat.parse("2015-03-31 23:59:59"))).list();
            // 打印结果
            for (Order o : list) {
                System.out.println(o.getId() + "\t" + o.getDate());
            }
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static List<Customer> findCustomersByName1(String name) {
        // 获取Session对象
        Session session = HibernateUtils.getSession();
        // 创建HQL
        String hql = "from Customer as c where c.userName = :name";
        Query query = session.createQuery(hql);
        // 按照参数名字进行绑定
        query.setString("name", name);
        return query.list();
    }

    public static List<Customer> findCustomersByName2(String name) {
        Session session = HibernateUtils.getSession();
        String hql = "from Customer as c where c.userName = ?";
        Query query = session.createQuery(hql);
        // 按照参数位置进行绑定
        query.setString(0, name);
        return query.list();
    }

    /* HQL内连接 */
    public static void findCustomerByJoin() {
        Session session = HibernateUtils.getSession();
        // 使用HQL进行内连接
        String hql = "from Customer c inner join c.orders o where c.userName like :name";
        Query query = session.createQuery(hql);
        query.setString("name", "z%");
        List<Object[]> list = query.list();
        for (Object[] objs : list) {
            Customer customer = (Customer) objs[0];
            System.out.print(customer.getId() + " * " + customer.getUserName()
                    + " * ");
            Order order = (Order) objs[1];
            System.out.print(order.getOrderNo() + " * ");
            System.out.print(order.getDate());
            System.out.println();
        }

    }

    /* HQL预先抓取内连接 */
    public static void findCustomerByFetchJoin() {
        Session session = HibernateUtils.getSession();
        String hql = "from Customer c inner join fetch c.orders o where c.userName like :name";
        Query query = session.createQuery(hql);
        query.setString("name", "z%");
        List<Customer> list = query.list();
        // 使用HashSet过滤重复元素
        HashSet<Customer> set = new HashSet<Customer>(list);
        for (Customer customer : set) {
            System.out.print(customer.getId() + " " + customer.getUserName()
                    + " ");
            for (Order order : customer.getOrders()) {
                System.out.print(order.getOrderNo() + " ");
            }
            System.out.println();
        }
    }

    /* HQL左外连接 */
    public static void findCustomerByLeftJoin() {
        Session session = HibernateUtils.getSession();
        String hql = "from Customer c left outer join  c.orders o where c.address = ?";
        Query query = session.createQuery(hql);
        query.setString(0, "青岛市");
        List<Object[]> list = query.list();
        for (Object[] objs : list) {
            Customer customer = (Customer) objs[0];
            System.out.print(customer.getId() + " " + customer.getUserName()
                    + " ");
            Order order = (Order) objs[1];
            if (objs[1] != null)
                System.out.print(order.getOrderNo());
            System.out.println();
        }

    }

    /* 预先抓取左外连接 */
    public static void findCustomerByLeftFetch() {
        Session session = HibernateUtils.getSession();
        String hql = "from Customer c left join fetch c.orders  where c.address = ?";
        Query query = session.createQuery(hql);
        query.setString(0, "青岛市");
        List<Customer> list = query.list();
        HashSet<Customer> set = new HashSet<Customer>(list);
        for (Customer customer : set) {
            System.out.print(customer.getId() + " " + customer.getUserName()
                    + " ");
            for (Order order : customer.getOrders()) {
                System.out.print(order.getOrderNo() + " ");
            }
            System.out.println();
        }

    }

    public static void groupByCustomer() {
        Session session = HibernateUtils.getSession();
        String hql = "select c.userName,count(o) from Customer c left join c.orders o group by c.id";
        Query query = session.createQuery(hql);
        List<Object[]> list = query.list();
        for (Object[] objs : list) {
            String username = (String) objs[0];
            Long count = (Long) objs[1];
            System.out.println("用户名: " + username + "  订单数：" + count);
        }

    }

    public static List<Product> findProductsByHQL(String name, Double price) {

        Session session = HibernateUtils.getSession();
        StringBuffer buffer = new StringBuffer();
        // 生成基础SQL
        buffer.append("from Product p where 1=1");
        // 如果name满足条件，则加入语句中
        if (name != null) {
            buffer.append(" and lower(p.name) like :name");
        }
        // 如果age满足条件，则加入语句中
        if (price != null && price != 0) {
            buffer.append(" and p.price = :price");
        }
        Query query = session.createQuery(buffer.toString());
        if (name != null) {
            query.setString("name", "%" + name.toLowerCase() + "%");
        }
        if (price != null && price != 0) {
            query.setDouble("price", price);
        }
        return query.list();

    }

    public static List<Product> findProductsByCriteria(String name, Double price) {

        Session session = HibernateUtils.getSession();
        Criteria criteria = session.createCriteria(Product.class);
        if (name != null) {
            criteria.add(Restrictions.ilike("name", name, MatchMode.ANYWHERE));
        }
        if (price != null && price != 0) {
            criteria.add(Restrictions.eq("price", price));
        }
        return criteria.list();
    }

    public static List<Product> findProductsByQBE(Product product) {

        Session session = HibernateUtils.getSession();
        /* customer为样本对象，根据查询条件创建的对象 */
        Example example = Example.create(product)// 根据样本对象创建Example对象
                .enableLike(MatchMode.ANYWHERE)// 对所有String类型的字段进行模糊匹配
                .excludeNone()// 不把为空的字段加入where子句中
                .excludeZeroes()// 不把值为0的字段加入where子句中
                .ignoreCase();// 忽略所有String类型字段的大小写
        Criteria criteria = session.createCriteria(Product.class);
        criteria.add(example);
        return criteria.list();
    }

    // 在业务逻辑层 把DetachedCriteria对象与Session对象绑定，并返回查询结果
    public static List<Product> findProducts(DetachedCriteria detachedCriteria) {
        Session session = HibernateUtils.getSession();
        Criteria criteria = detachedCriteria.getExecutableCriteria(session);
        return criteria.list();
    }

    public static void findProductsBySubQuerys() {

        Session session = HibernateUtils.getSession();
        String hql = "from Product p where p.price=(select p1.price from Product p1 where p1.name=:name) and p.name!=:name";
        Query query = session.createQuery(hql);
        query.setString("name", "打印机");
        List<Product> list = query.list();
        for (Product p : list) {
            System.out.println(p.getId() + "\t" + p.getName() + "\t"
                    + p.getPrice() + "\t" + p.getDescription());
        }
    }
}

 

增加查询条件的方法如下

.add(Restrictions.eq("address", address))

/* 使用QBC检索中的Order类对查询结果按照Customer的userName进行降序排序 */
    public static void orderByUserNameByDesc_QBC() {
        // 1.获取session对象
        Session session = HibernateUtils.getSession();
        // 2.以Customer的Class对象作为参数，创建Criteria对象
        Criteria critera = session.createCriteria(Customer.class);
        // 3.调用criteria对象的addOrder()方法条件排序规则
        critera.addOrder(org.hibernate.criterion.Order.desc("userName"));
        List<Customer> list = critera.list();
        // 打印结果
        for (Customer c : list) {
            System.out.println(c.getId() + "\t" + c.getUserName());
        }
    }

/* 使用QBC检索根据地址查询Customer */
    public static void findCustomersByAddress_QBC(String address) {
        // 1.获取session对象
        Session session = HibernateUtils.getSession();
        // 2.以Customer的Class对象作为参数，创建Criteria对象
        Criteria critera = session.createCriteria(Customer.class);
        // // 3.调用Criteria对象的add()方法，增加Criterion查询条件
        // critera.add(Restrictions.eq("address", address));
        // // 4.执行Criteria的list()方法返回查询结果
        // List<Customer> list = critera.list();
        // Criteria对象匿名方式
        List<Customer> list = session.createCriteria(Customer.class)
                .add(Restrictions.eq("address", address)).list();
        // 遍历输出结果
        for (Customer c : list) {
            System.out.println(c.getId() + "\t" + c.getUserName());
        }
    }