wlyy2.0/svr/svr-basic/src/main/java/com.yihu.ehr/basic/hibernate/HibenateDemo.java

package com.yihu.ehr.basic.hibernate;

import com.yihu.jw.restmodel.web.Envelop;
import com.yihu.jw.restmodel.web.MixEnvelop;
import com.yihu.jw.rm.hospital.BaseHospitalRequestMapping;
import com.yihu.jw.util.date.DateUtil;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * Created by Trick on 2019/12/10.
 */
@Component
public class HibenateDemo {

    @Autowired
    private HibenateUtils hibenateUtils;

    /**
     * 特别注意，由于oracle 大小写不明感，表别名需要指定大小写（驼峰）的话需要加双引号
     * @param id
     * @param name
     * @param createTime
     * @param page
     * @param size
     * @return
     */
    public Envelop findDoctorInfo(String id, String name, String createTime, Integer page, Integer size){
        String sqlTotal ="SELECT " +
                " COUNT(1) AS \"total\" " +
                " FROM " +
                " base_doctor d " +
                " JOIN base_doctor_hospital h ON d.id = h.doctor_code " +
                " WHERE " +
                " 1 = 1";
        Map<String,Object> params = new HashedMap();

        if(StringUtils.isNotBlank(id)){
            sqlTotal+=" AND d.id =:id";
            params.put("id",id);
        }
        if(StringUtils.isNotBlank(name)){
            sqlTotal+=" AND d.name =:name";
            params.put("name",name);
        }
        if(StringUtils.isNotBlank(createTime)){
            sqlTotal+=" AND d.create_time >:createTime";
            params.put("createTime", DateUtil.stringToDate(createTime,"yyyy-MM-dd"));
        }
        Long count = 0L;
        List<Map<String,Object>> total = hibenateUtils.createSQLQuery(sqlTotal,params);
        if(total!=null){
            //mysql 与 Oracle 聚合函数返回类型不一致，需要判断装换
            count = hibenateUtils.objTransformLong(total.get(0).get("total"));
        }
        String sql ="SELECT " +
                " d.id AS \"id\", " +
                " d.NAME AS \"name\"," +
                " h.dept_name AS \"deptName\"" +
                " FROM " +
                " base_doctor d " +
                " JOIN base_doctor_hospital h ON d.id = h.doctor_code " +
                " WHERE " +
                " 1 = 1 ";
        if(StringUtils.isNotBlank(id)){
            sql+=" AND d.id =:id";
        }
        if(StringUtils.isNotBlank(name)){
            sql+=" AND d.name =:name";
        }
        if(StringUtils.isNotBlank(createTime)){
            sql+=" AND d.create_time >:createTime";
        }
        List<Map<String,Object>> list = hibenateUtils.createSQLQuery(sql,params,page,size);

       return MixEnvelop.getSuccessListWithPage(BaseHospitalRequestMapping.Prescription.api_success, list, page, size, count);
    }
}