原生sql三步走+细节
来源:互联网 发布:日本服务业知乎 编辑:程序博客网 时间:2024/06/05 11:06
1.获取sql,把条件给他
2.执行语句
3.封装结果
(applicationContext-biz.xml需要加<!-- required --> <import resource="sql/*-sql.xml" />否则获取不到bean)
<bean id="QuerySdjfjl" class="java.lang.String">
<constructor-arg>
<value>
<![CDATA[
SELECT sdfcb.id as id, sdfcb.cbny as cbny, fj.mc as ss, qy.mc as qy, ly.mc as ly, sdfcb.dzongzje as dzje,
sdfcb.lshuizje as lszje,
sdfcb.rszje as rszje,
sdjf.jfje as jfje,sdjf.jfsj as jfsj,
sdfcb.jfzt as jfzt,sdjf.gxsj as gxsj
FROM t_xsgy_sdjfjl sdjf
LEFT JOIN t_xsgy_sdfcbxx sdfcb ON sdfcb.id = sdjf.sdcbxxid
LEFT JOIN t_baseinfo_fj fj ON fj.id = sdfcb.fjid
LEFT JOIN t_baseinfo_ly ly ON ly.id = fj.lyid
LEFT JOIN t_baseinfo_qy qy ON qy.id = ly.qyid
WHERE sdfcb.ssxxid = :ssxx
AND (ly.id = :lyid or :lyid is null)
AND (qy.id = :qyid or :qyid is null)
AND (fj.mc like :fj or :fj is null)
AND (sdfcb.jfzt = :jfzt or :jfzt is null)
AND (sdfcb.cbny = :cbny or :cbny is null) ]]>
</value>
</constructor-arg>
</bean>
public List<SdfcbxxDto> getSdfcbxx(int page, int size, SdfcbxxDto dto, Map<String, String> sortMap) throws Exception {
Query query = getQuerySdfcbxx(dto, sortMap);
query.setFirstResult((page - 1) * size);
query.setMaxResults(size);
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List<Map<String, Object>> list = query.getResultList();
System.out.println("csy-------------------------"+list.size());
List<SdfcbxxDto> sdfcbxxLi = new ArrayList<SdfcbxxDto>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM");
for (Map<String, Object> m : list) {
SdfcbxxDto sdfcbxxDto = new SdfcbxxDto();
System.out.println("csy-------------------------"+m.get("cbny")+m.get("dsy")+m.get("lsby"));
sdfcbxxDto.setId(Long.valueOf(String.valueOf(m.get("id"))));
sdfcbxxDto.setCbny(sdf1.format(sdf1.parse(String.valueOf(m.get("cbny")))));
sdfcbxxDto.setFj(String.valueOf(m.get("ss")));
sdfcbxxDto.setQy(String.valueOf(m.get("qy")));
sdfcbxxDto.setLy(String.valueOf(m.get("ly")));
sdfcbxxDto.setDbengyue(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("dby")))));
sdfcbxxDto.setDshangyue(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("dsy")))));
sdfcbxxDto.setDshiyong(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("dsy1")))));
sdfcbxxDto.setDzongzje(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("dzje")))));
sdfcbxxDto.setLsbenyue(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("lsby")))));
sdfcbxxDto.setLsshangyue(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("lssy")))));
sdfcbxxDto.setLsshiyong(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("lssy1")))));
sdfcbxxDto.setLshuizje(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("lszje")))));
sdfcbxxDto.setRsbenyue(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("rsby")))));
sdfcbxxDto.setRsshangyue(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("rssy")))));
sdfcbxxDto.setRshiyong(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("rssy1")))));
sdfcbxxDto.setRszje(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("rszje")))));
sdfcbxxDto.setYjje(BigDecimal.valueOf(Double.valueOf(String.valueOf(m.get("yjje")))));
sdfcbxxDto.setJfzt(Integer.valueOf(String.valueOf(m.get("jfzt"))));
sdfcbxxDto.setJfsj(sdf.format(sdf.parse(String.valueOf(m.get("jfsj")))));
sdfcbxxLi.add(sdfcbxxDto);
}
return sdfcbxxLi;
}
/**
* 查询语句的拼接
*
* @param shzt
* @param sortMap
* @return
* @throws Exception
*/
private Query getQuerySdfcbxx(SdfcbxxDto dto, Map<String, String> sortMap) {
Long ssxx = getSchoolId();
Query query = null;
String sql = BeanContainer.getBeanContainer().getBean("QuerySdfcbxx").toString();
StringBuffer sb = new StringBuffer();
sb.append(sql);
// if (StringUtils.isNotBlank(dto.getCbny())) {
// sb.append("AND (sdfcb.cbny = :cbny ) ");
//
// }
query = entityManager.createNativeQuery(SqlUtils.getSql(sb.toString(), sortMap));
query.setParameter("ssxx", ssxx);
if(StringUtils.isNotBlank(dto.getCbny())){
query.setParameter("cbny", dto.getCbny());
}else{
query.setParameter("cbny", null);
}
if(StringUtils.isNotBlank(dto.getFj())){
query.setParameter("fj", "%"+dto.getFj()+"%");
}else{
query.setParameter("fj", null);
}
if(StringUtils.isNotBlank(dto.getLy())){
query.setParameter("lyid", dto.getLy());
}else{
query.setParameter("lyid", null);
}
if(StringUtils.isNotBlank(dto.getQy())){
query.setParameter("qyid", dto.getQy());
}else{
query.setParameter("qyid", null);
}
if(dto.getJfzt()!=null){
query.setParameter("jfzt", dto.getJfzt());
}else{
query.setParameter("jfzt", null);
}
return query;
}
// sdfcb.cbny as cbny,
// AND (sdfcb.cbny = :cbny or :cbny is null)
/**
* 查询水电费抄表的总数据
*
* @param dto
* @param wslr
* @param bxztList
* @param sortMap
* @return
*/
public int getContent(SdfcbxxDto dto, Map<String, String> sortMap) {
int content = 0;
try {
content = getQuerySdfcbxx(dto,sortMap).getResultList().size();
} catch (Exception e) {
e.printStackTrace();
}
return content;
}
- 原生sql三步走+细节
- sql细节
- SQL细节
- Windows原生运行Linux的技术细节
- Windows原生运行Linux的技术细节
- play执行原生SQL
- hibernate执行原生sql
- Hibernate 使用原生SQL
- jpa 执行原生sql
- Hibernate 执行原生sql
- Hibernate原生SQL查询
- ThinkPHP 原生SQL查询
- Hibernate 使用原生SQL
- hibernate 原生sql查询
- Ofbiz使用原生Sql
- Hibernate原生SQL查询
- hibernate执行原生SQL
- hibernate 原生sql
- 搭建zoopker+hbase 环境
- ORACLE密码错误验证延迟
- react-native-0.16.1 自定义Android组件部分的源码初探
- Uva 10048 噪音恐惧症——Floyd变形
- tomcat热部署插件jerbel
- 原生sql三步走+细节
- iOS 升级cocoaPods
- Docker原生网络技术简介
- linux服务器安装jdk,tomcat
- tinker安装
- 程序员的4种心态与4种将来
- 并发编程概念理解
- Android 调用 类库 jar 包里面的 窗体 Activity
- 设计模式(十)--外观模式