work_27_一次springBoot+orcal+Mabits PageHele的使用

1.springBoot+Orcal的使用

需要的特殊pom

<!-- orcal -->
<dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
</dependency>

 <!-- pagehelper :分页插件 -->
<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
</dependency>

 application.properties

### datasource
spring.datasource.url=jdbc:oracle:thin:@IP:1521/数据库
spring.datasource.username=*****
spring.datasource.password=*****
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

spring.datasource.type=org.apache.tomcat.jdbc.pool.DataSource
spring.datasource.tomcat.max-wait=10000
spring.datasource.tomcat.max-active=30
spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.validation-query=select 1 from dual
spring.datasource.tomcat.validation-interval=30000

 

2.一次简单的分页查询

controller中在数据返回页面之前需要使用PageInfo包装

  PageInfo<Settlement> settlementPageInfo = new PageInfo<>(settlementList);

执行sql之前开启分页

  PageHelper.startPage(page, size);

ORCAL语句（注意特殊字符的转义<![CDATA[ >= ]]>）

　　(1)&　　　&
　　(2)<　　　<
　　(3)>　　　>
　　(4)＂　　　"
　　(5)＇　　　'

或者<![CDATA[ >= ]]>

<select id="settlementSelect" resultType="com.xxl.job.admin.settlementselect.Settlement">
    SELECT
        T .term_id termId,
        t2.shop_name shopName,
        SUM (T.trans_at/100) transAt
        FROM
        ISMNGDB.tbl_trans_log_his T,
        ISMNGDB.tb_shop t2,
        ISMNGDB.tb_shop_pos t1
        WHERE
        T .term_id = t1.pos_barcode
        AND t2.shop_id =t1.shop_id
        AND T.resp_cd1 ='00'
        AND T.trans_st ='1'
        AND T.mchnt_cd ='801320120010467'
        AND T.trans_id IN ('S22', 'G22', 'S32', 'G32')
        AND T.settle_dt <![CDATA[ >= ]]> #{strat}
        AND T.settle_dt <![CDATA[ <= ]]> #{end}
        GROUP BY T.term_id,t2.shop_name
</select>