namedJDBC查询

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import com.yundaex.common.dao.impl.BaseDaoImpl;
import com.yundaex.wms.basicdata.constant.RivPrintHistoryLSqlConstant;
import com.yundaex.wms.basicdata.dao.RivPrintHistoryLDao;
import com.yundaex.wms.basicdata.po.RivPrintHistoryL;
import com.yundaex.wms.basicdata.vo.DifferentUserQtyVO;

@Repository("printHistoryLDao")
public class RivPrintHistoryLDaoImpl extends BaseDaoImpl<RivPrintHistoryL> implements RivPrintHistoryLDao {

    @Override
    public List<DifferentUserQtyVO> queryTopQtyUser(String beginDate, String endDate, Integer orgId) {
        MapSqlParameterSource map = new MapSqlParameterSource();
        map.addValue("beginDate", beginDate);
        map.addValue("endDate", endDate);
        map.addValue("orgId", orgId);
        List<DifferentUserQtyVO> results = this.jdbcTemplate.query(RivPrintHistoryLSqlConstant.PRINT_HISTORY_TOP_QTY_USER_SQL,map,
                        new BeanPropertyRowMapper<DifferentUserQtyVO>(DifferentUserQtyVO.class));
        return results;
    }
}

public class RivPrintHistoryLSqlConstant {
    public static String PRINT_HISTORY_TOP_QTY_USER_SQL = 
                      "     SELECT                                                                                    "
                    + "         '打印' AS operateType,                                                                 "
                    + "         tab1.PHL_PRINT_USER_ID AS userId,                                                     "
                    + "         (                                                                                     "
                    + "             SELECT                                                                            "
                    + "                 UP_NAME                                                                       "
                    + "             FROM                                                                              "
                    + "                 tab_user_profile                                                              "
                    + "             WHERE                                                                             "
                    + "                 UP_USER_ID = tab1.PHL_PRINT_USER_ID                                           "
                    + "         ) AS userName,                                                                        "
                    + "         tab1.qty                                                                              "
                    + "     FROM                                                                                      "
                    + "         (                                                                                     "
                    + "             SELECT                                                                            "
                    + "                 PHL_PRINT_USER_ID,                                                            "
                    + "                 count(1) AS qty                                                               "
                    + "             FROM                                                                              "
                    + "                 riv_print_history_l                                                           "
                    + "             WHERE                                                                             "
                    + "                 CREATE_TIME >= :beginDate                                                     "
                    + "             AND CREATE_TIME < :endDate                                                        "
                    + "             GROUP BY                                                                          "
                    + "                 PHL_PRINT_USER_ID                                                             "
                    + "             ORDER BY                                                                          "
                    + "                 qty DESC                                                                      "
                    + "             LIMIT 1                                                                           "
                    + "         ) tab1                                                                                "
                    + "     WHERE                                                                                     "
                    + "         tab1.PHL_PRINT_USER_ID IN (                                                           "
                    + "             SELECT                                                                            "
                    + "                 u.usr_id                                                                      "
                    + "             FROM                                                                              "
                    + "                 tab_user u                                                                    "
                    + "             INNER JOIN tab_user_role ur ON ur.ur_user_id = u.usr_id                           "
                    + "             INNER JOIN tab_role r ON ur.ur_role_id = r.rl_id                                  "
                    + "             INNER JOIN tab_role_data_authority rda ON rda.rda_role_id = r.rl_id               "
                    + "             WHERE                                                                             "
                    + "                 rda.rda_dimension_code = 'organization'                                       "
                    + "             AND rda.rda_entity_id = :orgId                                                    "
                    + "             UNION                                                                             "
                    + "                 SELECT                                                                        "
                    + "                     u.usr_id                                                                  "
                    + "                 FROM                                                                          "
                    + "                     tab_user u                                                                "
                    + "                 INNER JOIN tab_user_profile up ON up.UP_USER_ID = u.usr_id                    "
                    + "                 INNER JOIN tab_domain dmn ON dmn.dmn_id = up.up_domain                        "
                    + "                 INNER JOIN tab_domain_role dr ON dr.dr_domain_id = dmn.dmn_id                 "
                    + "                 INNER JOIN tab_role r ON dr.dr_role_id = r.rl_id                              "
                    + "                 INNER JOIN tab_role_data_authority rda ON rda.rda_role_id = r.rl_id           "
                    + "                 WHERE                                                                         "
                    + "                     rda.rda_dimension_code = 'organization'                                   "
                    + "                 AND rda.rda_entity_id = :orgId                                                "
                    + "                 UNION                                                                         "
                    + "                     SELECT                                                                    "
                    + "                         up.up_id                                                              "
                    + "                     FROM                                                                      "
                    + "                         tab_user_profile up                                                   "
                    + "                     WHERE                                                                     "
                    + "                         up.up_user_prop = 'admin'                                             "
                    + "         )                                                                                     "
            ;
}

 

public List<Map<String, Object>> queryOrgInfo(List<String> orgParam) {
        Map<String, Object> params = new HashMap<String, Object>();
        String sql = "select org.org_id, org.org_code from riv_organization org where org.org_code in (:orgCodes)";
        params.put("orgCodes", orgParam);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, params);
        return list;
    }

 

import java.util.List;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import com.yundaex.common.dao.impl.BaseDaoImpl;
import com.yundaex.wms.outbound.dao.RivMainpickStartPrintTemplateDao;
import com.yundaex.wms.outbound.po.RivMainpickStartPrintTemplate;


@Repository
public class RivMainpickStartPrintTemplateDaoImpl extends BaseDaoImpl<RivMainpickStartPrintTemplate> implements RivMainpickStartPrintTemplateDao {

    @Override
    public List<Integer> queryExistRecordsByOnhIds(List<Integer> onhIds) {
         MapSqlParameterSource map = new MapSqlParameterSource();
            map.addValue("onhIds", onhIds);
            String sql = "select ONH_ID from riv_mainpick_start_print_template where ONH_ID in(:onhIds)";
            List<Integer> results = this.jdbcTemplate.queryForList(sql,map,Integer.class);
            return results;
    }
}