# dynamic-sql

**Repository Path**: giteeyyq/dynamic-sql

## Basic Information

- **Project Name**: dynamic-sql
- **Description**: dynamic sql parse by mybatis
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2020-04-16
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# dynamic-sql

PS：不仅仅只用来写SQL，完全可以用其模版写其他的东西，比如邮件、短信等等


## update feature:

#### 2020-09-28：（version：0.0.5）
> 小改动

#### 2020-09-24：（version：0.0.4）
> 重新打包，更改util包名字为sqlutils（避免和smc本地包冲突）


#### 2020-09-18：（version：0.0.3）
> 1. 移除lombok、guava、apache相关jar包，只依赖mybatis
> 2. 支持声明式接口（跟mybatis一样，只要接口即可）  
> 3. 添加Spring支持，支持在BeanFactory初始化前注入容器

改动用法：参考Demo即可




---

### 1.Description
解决使用SQL语句来调用自研搜索引擎的问题：  
1. 动态SQL生成（直接使用mybatis解析器）
2. 出参解析（反射动态解析）

### 2.Installation
依赖包有：mybatis
```xml
<dependency>
    <groupId>com.yt.marketing</groupId>
    <artifactId>common-dynamic-sql</artifactId>
    <version>0.0.5</version>
    <!--按需排除依赖-->
    <exclusions>
        <exclusition>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
        </exclusition>
    </exclusions>
    <!-- 如果配合Spring的话需要自己引入 -->
</dependency>

<plugin>
    <artifactId>maven-compiler-plugin</artifactId>
    <version>3.8.0</version>
    <configuration>
        <source>1.8</source>
        <target>1.8</target>
        <compilerArgs>
            <!--要加上这个才能通过反射解析参数名称-->
            <arg>-parameters</arg>
        </compilerArgs>
    </configuration>
</plugin>
```

### 3.Instructions
1、准备mapper.xml文件（跟mybatis一样即可）

2、准备Dao（调用搜索引擎的查询类，名字自己定义）

3、配置（配置DynamicSqlRegistry实例并将xml和Dao进行绑定）

4、编辑自己的查询方法

### 4.Demo
xml文件
```xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="DemoSqlGenerator">

    <sql id="Base_Column_List">
        id, act_id, template_id, share_num, scan_num, used_num, join_num, award_num, vote_num, terminate_num, terminate_vote_num, creator, create_time, editor, edit_time, is_deleted
    </sql>

    <update id="test0">
        update t_cmc_act_count
        set
            used_num = used_num + 1
        where
              is_deleted = 0
    </update>

    <update id="test">
        update t_cmc_act_count
        set
            used_num = used_num + #{usedNum,jdbcType=INTEGER},
            editor = #{editor,jdbcType=VARCHAR}
        where
              is_deleted = 0
          and id = #{id,jdbcType=BIGINT}
    </update>

    <select id="test2">
        SELECT
            IFNULL(SUM(join_num), 0) joinNum
        FROM
            t_cmc_act_count
        WHERE
            is_deleted = 0
            <if test="templateId != null and templateId > 1">
                AND template_id = #{templateId}
            </if>
    </select>


    <select id="test3">
        SELECT
            template_id templateId,
            IFNULL( SUM( join_num ), 0 ) joinTotalNum,
            IFNULL( SUM( scan_num ), 0 ) scanTotalNum
        FROM
            t_cmc_act_count
        WHERE
            is_deleted = 0
            and template_id in
            <foreach collection="templateIdList" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        GROUP BY
            template_id
    </select>

    <select id="test4">
        SELECT
            template_id templateId,
            IFNULL( SUM( join_num ), 0 ) joinTotalNum,
            IFNULL( SUM( scan_num ), 0 ) scanTotalNum
        FROM
            t_cmc_act_count
        WHERE
            is_deleted = 0
            and template_id in
            <foreach collection="templateIdList" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="actId != null and actId > 0">
                AND act_id = #{actId}
            </if>
        GROUP BY
            template_id
    </select>


    <select id="test5">
        SELECT
            template_id templateId,
            IFNULL( SUM( scan_num ), 0 ) scanTotalNum
        FROM
            t_cmc_act_count
        WHERE
            is_deleted = 0
            and template_id in
            <foreach collection="templateIdList" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="actId != null and actId > 0">
                AND act_id = #{actId}
            </if>
    </select>

    <insert id="test6">
        insert into
            t_cmc_act_count
        values
            <foreach collection="shopList" item="item" index="index" separator=",">
                (
                    #{item.shopId},
                    #{item.shopName},
                    #{item.createTime}
                )
            </foreach>
    </insert>

</mapper>
```
测试类
```java
/**
 * @author beats
 */
public interface DemoSqlGenerator {

    String test0();

    String test(Map<String, Object> param);

//    String test(Long id, Integer usedNum, String editor);

    /*
     * 动态SQL解析，if
     */
    String test2(Long templateId);

    /*
     * 动态SQL解析，for
     * list传入，
     * 每个item都是 __frch_item_i  其中 i = 0, 1, 2, 3 ...
     *  if (boundSql.hasAdditionalParameter(propertyName)) {
     *      value = boundSql.getAdditionalParameter(propertyName);
     *  }
     */
    String test3(List<Long> templateIdList);

    /*
     * 动态SQL解析，for + if
     * list传入，
     * 每个item都是 __frch_item_i  其中 i = 0, 1, 2, 3 ...
     *  if (boundSql.hasAdditionalParameter(propertyName)) {
     *      value = boundSql.getAdditionalParameter(propertyName);
     *  }
     */
    String test4(List<Long> templateIdList, Long actId);

    /*
     * 动态SQL解析，for + if
     * 对象传入，
     * 每个item都是 __frch_item_i  其中 i = 0, 1, 2, 3 ...
     *  if (boundSql.hasAdditionalParameter(propertyName)) {
     *      value = boundSql.getAdditionalParameter(propertyName);
     *  }
     */
    String test5(ParamTest paramTest);

    String test6(List<ShopQuery> shopList);

    String batchQueryActivityStockByParam(List<ActivityStockQuery> list);

}
```
绑定关系
```java
/**
 * @author beats
 */
public class Demo {

    public static void main(String[] args) throws IOException {
        DynamicSqlDaoRegistry registry = new DynamicSqlDaoRegistry();
        DynamicSqlFactory factory = new DynamicSqlFactoryBuilder().dynamicSqlDaoRegistry(registry).build();
        try {
            //                new ClassPathResource("mapper/DemoDao.xml").getInputStream(),
            //                new FileInputStream(new ClassPathResource("mapper/DemoDao.xml").getFile()),
            //                new FileInputStream(ResourceUtils.getFile("classpath:mapper/DemoDao.xml")),
            registry.addConfig(DemoDao.class.getClassLoader().getResourceAsStream("testmapper/DemoDao.xml"), DemoDao.class);
            //            instance.setCompactSql(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
        DemoSqlGenerator demoSqlGenerator = factory.getDaoInstance(DemoSqlGenerator.class);

        // 测试无参数
        System.out.println("====================");
        System.out.println("0、测试无参数");
        String test0 = demoSqlGenerator.test0();
        System.out.println(test0);

        // 测试map
        Map<String, Object> param = new HashMap<>();
        param.put("id", 10023L);
        param.put("usedNum", 199);
        param.put("editor", "张三");
        String test = demoSqlGenerator.test(param);
        System.out.println(test);

        //        // 测试多参数
//        System.out.println("====================");
//        System.out.println("1、测试多参数");
//        demoSqlGenerator.test(10023L, 199, "sys");

        //         测试if不包含
        System.out.println("====================");
        System.out.println("2、测试if不包含templateId");
        String test2 = demoSqlGenerator.test2(100101L);
        System.out.println(test2);

        // 测试for
        System.out.println("====================");
        System.out.println("3、测试for");
        String test3 = demoSqlGenerator.test3(Lists.newArrayList(100101L, 100102L));
        System.out.println(test3);

        // 测试for+if
        System.out.println("====================");
        System.out.println("4、测试for + if，多参数传入");
        String test4 = demoSqlGenerator.test4(Lists.newArrayList(100101L, 100102L), 1001L);
        System.out.println(test4);

        System.out.println("====================");
        System.out.println("5、测试for + if，对象传入");

        // 如果是对象，需要继承SqlSearchBaseDO
//        ParamTest param = new ParamTest();
//        List<Long> templateIdList = new ArrayList<>();
//        templateIdList.add(1001L);
//        templateIdList.add(1002L);
//        param.setTemplateIdList(templateIdList);
//        param.setActId(100101L);
//        demoSqlGenerator.test5(param);

        // 测试对象list
        System.out.println("====================");
        System.out.println("6、测试for，list对象传入");
        List<ShopQuery> shopList = new ArrayList<>();
        Date now = new Date();
        shopList.add(new ShopQuery(100101L, "shop100101", now));
        shopList.add(new ShopQuery(100102L, "shop100102", now));
        String test6 = demoSqlGenerator.test6(shopList);
        System.out.println(test6);


        //        ActivityStockQuery query = new ActivityStockQuery();
        //        query.setParentId(1001L);
        //        query.setActivityId(1001L);
        //        query.setStatusList(Lists.newArrayList(1));
        //        ActivityStockQuery query2 = new ActivityStockQuery();
        //        query2.setParentId(1002L);
        //        query2.setActivityId(1002L);
        //        query2.setStatusList(Lists.newArrayList(1, 2));
        //        StockBizInfo stockBizInfo = new StockBizInfo(2, 1002L);
        //        StockBizInfo stockBizInfo2 = new StockBizInfo(3, 10022L);
        //        query2.setStockBizInfoList(Lists.newArrayList(stockBizInfo, stockBizInfo2));
        //        demoSqlGenerator.batchQueryActivityStockByParam(Lists.newArrayList(query, query2));
    }
}

```

Spring中引入：
```java
@Slf4j
@Configuration
public class AdapterConfiguration {

    @Bean
    public DynamicSqlFactory dynamicSqlHelper() {
        DynamicSqlMapperRegistry registry = new DynamicSqlMapperRegistry();
        DynamicSqlFactory factory = new DynamicSqlFactoryBuilder().dynamicSqlMapperRegistry(registry).build();
        try {
            // 有几个加载几个
            registry.addConfig(new ClassPathResource("search/AuthAuditSearchSql.xml").getInputStream(), AuthAuditSearchSqlGenerator.class);
            // registry.addConfig.....
        } catch (IOException e) {
            log.error("加载搜索配置文件失败...", e);
            throw new RuntimeException("加载搜索配置文件失败...", e);
        }
        return factory;
    }

    @Bean
    public DynamicSqlSpringProcessor dynamicSqlSpringProcessor(DynamicSqlFactory factory) {
        return new DynamicSqlSpringProcessor(factory);
    }

}

配置扫包即可
```

解析出参
```java
YtSqlQueryResult queryResult = sqlSearchService.query(sql);
// 解析为count数量
YtSearchUtil.getCount(queryResult.getData().getLines());

// 解析为普通对象
YtSearchUtil.getCount(queryResult.getData().getLines());

// 解析为自定义Object list
YtSearchUtil.parseArray(data.getHeaders(), data.getLines(), XXXDTO.class);
```