hibernate 手写sql语句代码片记录

最新推荐文章于 2022-06-17 10:38:35 发布

咖啡猫没有梦

最新推荐文章于 2022-06-17 10:38:35 发布

阅读量2k

点赞数

分类专栏： java 文章标签： hibernate sql

本文链接：https://blog.csdn.net/oftoo/article/details/54947886

版权

java 专栏收录该内容

14 篇文章

订阅专栏

代码片

package com.zzy.cowhide.service;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.hibernate.Page;
import org.springside.modules.orm.hibernate.SimpleHibernateTemplate;

import com.zzy.cowhide.bean.OrderReportBean;
import com.zzy.cowhide.entity.OrderOd;
import com.zzy.cowhide.utils.StringUtilsEx;

@Service
@Transactional
public class OrderReportService {

    private SimpleHibernateTemplate<OrderOd, Integer> orderDao;


    @Autowired
    public void setSessionFactory(SessionFactory sessionFactory) {
        orderDao = new SimpleHibernateTemplate<OrderOd, Integer>(sessionFactory, OrderOd.class);
    }


    /**
     * 
     * @Title: find 
     * @Description: TODO(每日交易额统计    type == 0) 
     * @param page
     * @return
     * @throws ParseException    设定文件 
     * List<OrderOdBean>    返回类型 
     * @throws 
     * @author oftoo
     */
    @SuppressWarnings("unchecked")
    public List<OrderReportBean> findByDay(Page<OrderOd> page) throws ParseException {
        Map<String, String> parameters = page.getQueryMap();
        String startDate = null, endDate =null, shopName = null;
        if (parameters.containsKey("startDate") && !StringUtilsEx.isNullOrEmpty(parameters.get("startDate"))) {
            startDate = parameters.get("startDate") + " 00:00:00";
        }
        if (parameters.containsKey("endDate") && !StringUtilsEx.isNullOrEmpty(parameters.get("endDate"))) {
            endDate = parameters.get("endDate") + " 23:59:59";
        }
        if (parameters.containsKey("shopName") && !StringUtilsEx.isNullOrEmpty(parameters.get("shopName"))) {
            shopName = parameters.get("shopName");
        }

        StringBuilder sb = new StringBuilder();
        List<OrderReportBean> listBean = new ArrayList<OrderReportBean>();
        sb.append(" SELECT shop.company_name AS name , SUM( order_od.total) AS total, count(order_od.id) AS ods, 0 AS TYPE, DATE_FORMAT(order_od.create_time,'%Y-%m-%d') AS time");
        sb.append(" FROM order_od ");
        sb.append(" LEFT JOIN shop on order_od.saler_member_id = shop.id ");
        sb.append(" LEFT JOIN member on shop.member_id = member.id ");
        sb.append(" WHERE ");
        sb.append("  1 = 1");
        sb.append(" AND order_od.status IN (3,4) ");//订单状态表示完成的
        if (shopName != null && shopName.equals("")) {
            sb.append(" AND shop.shop_name LIKE    =  '%+" + shopName + "%'");
        }
        if (startDate !=null && endDate != null) {
            sb.append(" AND order_od.create_time >= " + "'" + startDate + "'");//开始时间
            sb.append(" AND order_od.create_time < " + "'" + endDate + "'");//结束时间
        }

        sb.append(" GROUP BY saler_member_id,DATE_FORMAT(order_od.create_time,'%Y-%m-%d') ");
        List<Object[]> list = orderDao.getSession().createSQLQuery(sb.toString()).list();
        for (Object[] objects : list) {
            OrderReportBean bean = new OrderReportBean();
            bean.setName(objects[0]);
            bean.setTotal(objects[1]);
            bean.setOds(objects[2]);
            bean.setType(objects[3]);
            bean.setTime(objects[4]);
            listBean.add(bean);
        }
        return listBean;
    }


    /**
     * 
     * @Title: findSum 
     * @Description: TODO(历史交易额统计  type == 1) 
     * @param page
     * @return
     * @throws ParseException    设定文件 
     * List<OrderOdBean>    返回类型 
     * @throws 
     * @author oftoo
     */
    @SuppressWarnings("unchecked")
    public List<OrderReportBean> findSum(Page<OrderOd> page) throws ParseException {
        Map<String, String> parameters = page.getQueryMap();
        String startDate = null, endDate =null, shopName = null;
        if (parameters.containsKey("startDate") && !StringUtilsEx.isNullOrEmpty(parameters.get("startDate"))) {
            startDate = parameters.get("startDate") + " 00:00:00";
        }
        if (parameters.containsKey("endDate") && !StringUtilsEx.isNullOrEmpty(parameters.get("endDate"))) {
            endDate = parameters.get("endDate") + " 23:59:59";
        }
        if (parameters.containsKey("shopName") && !StringUtilsEx.isNullOrEmpty(parameters.get("shopName"))) {
            shopName = parameters.get("shopName");
        }
        List<OrderReportBean> listBean = new ArrayList<OrderReportBean>();

        StringBuilder sp = new StringBuilder();
        sp.append(" SELECT shop.company_name AS NAME ,SUM( order_od.total) AS total, COUNT(order_od.id) AS ods, 1 AS TYPE, DATE_FORMAT(order_od.create_time,'%Y-%m-%d') AS TIME");
        sp.append(" FROM order_od ");
        sp.append(" LEFT JOIN shop on order_od.saler_member_id = shop.id ");
        sp.append(" LEFT JOIN member on shop.member_id = member.id ");
        sp.append(" WHERE ");
        sp.append("  1 = 1");
        sp.append(" AND order_od.status IN (3,4) ");//订单状态表示完成的
        if (shopName != null && shopName.equals("")) {
            sp.append(" AND shop.shop_name LIKE    =  '%+" + shopName + "%'");
        }
        if (startDate !=null && endDate != null) {
            sp.append(" AND order_od.create_time >= " + "'" + startDate + "'");//开始时间
            sp.append(" AND order_od.create_time < " + "'" + endDate + "'");//结束时间
        }
        sp.append(" GROUP BY saler_member_id");
        List<Object[]> countList = orderDao.getSession().createSQLQuery(sp.toString()).list();
        for (Object[] objects : countList) {
            OrderReportBean bean = new OrderReportBean();
            bean.setName(objects[0]);
            bean.setTotal(objects[1]);
            bean.setOds(objects[2]);
            bean.setType(objects[3]);
            bean.setTime("截止  " + objects[4]);
            listBean.add(bean);
        }

        return listBean;
    }


    /**
     * 
     * @Title: find 
     * @Description: TODO(查询全部) 
     * @param page
     * @return
     * @throws ParseException    设定文件 
     * List<OrderOdBean>    返回类型 
     * @throws 
     * @author oftoo
     */
    public List<OrderReportBean> find(Page<OrderOd> page) throws ParseException {
        Map<String, String> parameters = page.getQueryMap();
        String type = null;
        List<OrderReportBean> listBean = new ArrayList<OrderReportBean>();
        if (parameters.containsKey("type") && !StringUtilsEx.isNullOrEmpty(parameters.get("type"))) {
            type = parameters.get("type");
        }
        if (type == null || Integer.valueOf(type) == 2) {//查询全部
            listBean = this.findByDay(page);
            listBean.addAll(this.findSum(page));
        } else if (Integer.valueOf(type) == 0) {//查询 每日交易记录
            listBean = this.findByDay(page);
        } else if (Integer.valueOf(type) == 1) {//查询历史交易记录
            listBean = this.findSum(page);
        }
        return listBean;
    }



}

代码片二：

    /**
     * 分页查询
     */
    public List<ChemicalCategory> findPageNo(int pageNo) {
        Criteria criteria = chemicalCategoryDao.createCriteria();
        criteria.addOrder(Order.asc("level"));
        criteria.add(Restrictions.eq("status", 1));
        criteria.addOrder(Order.asc("sort"));
        if (pageNo==1) {
            criteria.setFirstResult(0);
        } else {
            criteria.setFirstResult(5*(pageNo-1));
        }
        criteria.setMaxResults(15);
        return chemicalCategoryDao.findByCriteria(criteria);
    }