关于拼接SQL语句sqlMap的使用方法

来源：互联网发布：现在开淘宝卖什么好卖编辑：程序博客网时间：2024/05/17 21:06

1.为什么使用？

主要还是为了代码中获取到值，然后带入SQL语句中拼接查询

2.怎么使用？

1）bean继承了BaseEntity类，该类中有

    /**     * 自定义SQL（SQL标识，SQL内容）     */    protected Map<String, String> sqlMap;    @JsonIgnore    @XmlTransient    public Map<String, String> getSqlMap() {        if (sqlMap == null){            sqlMap = Maps.newHashMap();        }        return sqlMap;    }     public void setSqlMap(Map<String, String> sqlMap) {        this.sqlMap = sqlMap;    }

2）XML中如何写？

<select id="findList" resultType="ZlfbBean">        SELECT * FROM (            SELECT    <include refid="ZlfbBeanColumns"/>            FROM ZL_HCZZ i <include refid="ZlfbBeanJoins"/>            <where>            AND i.del_flag = #{DEL_FLAG_NORMAL}             ${sqlMap.dsf} //此处加入service中的限制条件            <if test="zlbh != null and zlbh != ''">                AND i.ZLBH = #{zlbh}            </if>            <if test="createBy != null and createBy.id != null and createBy.id != ''">                AND i.CREATE_BY = #{createBy.id}            </if>            <if test="office != null and office.id != null and office.id != ''">                AND i.OFFICE_ID = #{office.id}            </if>            </where>            ORDER BY i.CREATE_DATE DESC,i.UPDATE_DATE DESC        ) t        <where>            AND t.del_flag = #{DEL_FLAG_NORMAL}             <if test="beginInDate != null and beginInDate != ''">                AND t.CREATE_DATE <![CDATA[ >= #{beginInDate} ]]>            </if>            <if test="endInDate != null and endInDate != ''">                AND t.CREATE_DATE <![CDATA[ <= #{endInDate} ]]>            </if>        </where>    </select>

3）service中：

public Page<ZlfbBean> findzlfb(Page<ZlfbBean> page,ZlfbBean bean){        // 生成数据权限过滤条件（dsf为dataScopeFilter的简写，在xml中使用 ${sqlMap.dsf}调用权限SQL）        User user = UserUtils.getUser();        String dsf = dataScopeFilter( user, "l", "k");//返回的是如AND (k.id='user.getId()')        if((bean.getCreateBy()==null || bean.getCreateBy().getId().equals("")) && (bean.getOffice()==null || bean.getOffice().getId().equals(""))){            dsf = dsf+" OR i.zlbh IN( "                +" SELECT  DISTINCT t1.zlbh FROM ZL_HCZZ t1 join ZL_HCZZSP t2 on t1.zlbh=t2.zlbh where t2.CREATE_BY = "+user.getId()                +" ) "                +" OR i.zlbh IN( "                 +" SELECT  DISTINCT t1.zlbh FROM ZL_HCZZ t1 join ZL_HCZZQS t2 on t1.zlbh=t2.zlbh where t2.CREATE_BY = "+user.getId()+" OR ((SELECT COUNT(1) FROM SYS_USER WHERE id="+user.getId()+" and qsqx='01' and office_id='"+user.getOffice().getId()+"') > 0 ) AND t2.QS_DW = '"+user.getOffice().getId()+"' "                +" ) "                +" OR i.zlbh IN( "                +" SELECT  DISTINCT t1.zlbh FROM ZL_HCZZ t1 join ZL_HCZZFK t2 on t1.zlbh=t2.zlbh where t2.CREATE_BY = "+user.getId()                +" ) ";        }        bean.getSqlMap().put("dsf", dsf);给sqlmap集合中添加值，即添加一个dsf字符串的值为dsf        // 设置分页参数        bean.setPage(page);        // 执行分页查询        page.setList(zlfbDao.findList(bean));        return page;    }

阅读全文

1 0