beetlsql简单应用

1.引入依赖

<dependency>
    <groupId>com.ibeetl</groupId>
    <artifactId>beetlsql</artifactId>
    <version>2.13.8.RELEASE</version>
</dependency>

2.配置文件

//mysql
datasource.type=mysql
beetl.db.style=org.beetl.sql.core.db.MySqlStyle
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/kettle-master?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
jdbc.username=xxx
jdbc.password=xxx
//DM
datasource.type=dm
jdbc.url=jdbc:dm://ip:port?compatibleMode=oracle&localTimezone=480
jdbc.username=xxx
jdbc.password=xxx
jdbc.driver=dm.jdbc.driver.DmDriver
beetl.db.style=org.beetl.sql.core.db.DamengStyle

3.使用

1.自定义sql

public interface KJobRecordDao extends BaseMapper<KJobRecord> {
    @Sql("SELECT DATE_FORMAT( start_time, '%Y-%m-%d' ) as dayTime,count(*) as dayCount FROM k_job_record WHERE record_status = ? AND start_time > ? AND start_time < ? GROUP BY DATE_FORMAT( start_time, '%Y-%m-%d' ) ORDER BY DATE_FORMAT( start_time, '%Y-%m-%d' )")
    public List<DayCountDto> getJobRunResultGroupByDay(Integer recordStatus, String startDate, String endDate);

}

2.通过md文件定义sql

public interface KJobDao extends BaseMapper<KJob> {
    @SqlStatement(params = "kJob,start,size")
    List<KJob> pageQuery(KJob kJob, Integer start, Integer size);
}

在sql文件中新增sql文件夹,并新增对应语法目录,beetlsql会自动在对应的目录下查找文件

sql/dameng/kjob.md

pageQuery
===
    *数据库表分页查询
    SELECT #use("cols")# from (
    SELECT ROWNUM rn , t.* from (
    SELECT #use("cols")# from 
    k_job 
    #use("condition")#
    #use("sort")# )t
    @if(!isEmpty(start)&&!isEmpty(size)){
        where ROWNUM <= #start#+#size#
    @}
    ) tt
    @if(!isEmpty(start)&&!isEmpty(size)){
        where rn > #start#
    @}

官方文档:快速开始 - Document (ibeetl.com)