SQLQuery 实现别名映射

最新推荐文章于 2023-06-04 14:57:31 发布

转载最新推荐文章于 2023-06-04 14:57:31 发布 · 308 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/rigid/p/3824469.html

文章标签：

#数据库

本文介绍了一个用于查询工作订单列表的方法，该方法通过接收参数映射并根据这些参数查询数据库来获取工作订单信息。具体参数包括工单编号、工单主题、创建日期范围等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public List getQueryList(Map paramMap, int start, int maxResults) throws DataAccessException, HibernateException,
            InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
        String woNo = "";
        String woSubject = "";
        Date creationDateFrom = null;
        Date creationDateEnd = null;

        if (paramMap.get("woNo") != null) {
            woNo = (String) paramMap.get("woNo");
        }
        ;
        if (paramMap.get("woSubject") != null) {
            woSubject = (String) paramMap.get("woSubject");
        }
        if (paramMap.get("creationDateFrom") != null) {
            creationDateFrom = (Date) paramMap.get("creationDateFrom");
        }
        if (paramMap.get("creationDateEnd") != null) {
            creationDateEnd = (Date) paramMap.get("creationDateEnd");
        }

        StringBuffer buf = new StringBuffer();
        buf.append("select abh.*,");
        buf.append("fw.wo_no bill_code,");
        buf.append("fw.wo_type bill_type,");
        buf.append("fw.wo_subject bill_title,");
        buf.append("fw.created_date creation_date,");
        buf.append("fw.process_instance_id process_instance_id,");
        buf.append("'transated' task_type,");
        buf.append("(select au.user_name from arch_user au where au.user_id=abh.created_by) created_name,");
        buf.append("(select ao.org_name from arch_org ao where ao.org_id=abh.arch_org_company_id) region_company_segment,");
        buf.append("(select ao.org_name from arch_org ao where ao.org_id=abh.arch_org_dept_id) region_dept_segment");
        buf.append(" from FAS_WORKORDER fw, ACCOUNT_BILL_HEAD abh where 1 = 1 ");
        buf.append(" and fw.businessid = abh.account_bill_head_id");
        buf.append(" and fw.wo_no like :woNo");
        buf.append(" and fw.wo_subject like :woSubject");
        buf.append(" and fw.created_date between nvl(:creationDateFrom,fw.created_date) and nvl(:creationDateEnd,sysdate)");
        buf.append(" order by fw.created_date desc");

        List beanList = null;
        Session session = null;
        try {
            session = accountBillHeadDAO.getHibernateTemplate().getSessionFactory().openSession();
            //获得本地sql查询对象
            SQLQuery query = session.createSQLQuery(buf.toString());
            //设置字符串参数
            query.setString("woNo", "%" + woNo + "%");
            query.setString("woSubject", "%" + woSubject + "%");
            //设置日期参数
            query.setDate("creationDateFrom", creationDateFrom);
            query.setDate("creationDateEnd", creationDateEnd);
            //设置分页
            query.setFirstResult(start);
            query.setMaxResults(maxResults);
            query.setResultTransformer(new ExtColumnToBean(AccountBillHeadVOImpl.class));
            
            beanList = query.list();
        } catch (RuntimeException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            session.flush();
            session.close();
        }
        return beanList;
    }