Spring Boot 集成 BeetlSQL

一、Spring Boot 集成 BeetlSQL

配置文件pom.xml:

        <!-- BeetlSQL-->
        <dependency>
            <groupId>com.ibeetl</groupId>
            <artifactId>beetl-framework-starter</artifactId>
            <version>1.1.23.RELEASE</version>
        </dependency>

配置文件application.properties：

##BeetlSQL
##默认com,此选项配置beetlsql.daoSuffix来自动扫描com包及其子包下的所有以Dao结尾的Mapper类。
beetlsql.basePackage = com.example.shopgoods.controller.beetlSQL.dao
##将sql打印到控制台
beetl-beetlsql.dev = true
##存放resources/sql文件目录
beetlsql.sqlPath = /sql
##将数据库里的含有下划线字段转化为java的驼峰命名风格
beetlsql.nameConversion = org.beetl.sql.core.UnderlinedNameConversion

二、BeetlSQL在Spring Boot中的测试代码

BeetlSQLController文件代码如下：

package com.example.shopgoods.controller.beetlSQL;

import com.example.shopgoods.controller.beetlSQL.dao.GoodsDao;
import com.example.shopgoods.controller.beetlSQL.dao.Goods;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.engine.PageQuery;
import org.beetl.sql.ext.gen.GenConfig;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @Author: zp
 * @Date: 2019/6/4 15:22
 * @Description:
 */
@RestController
@RequestMapping("/beetlSQL")
public class BeetlSQLController {

    @Autowired
    private SQLManager sqlManager;

    @Autowired
    private GoodsDao goodsDao;

    @PostMapping("/test")
    public String beetlTest() {
        Goods goods = sqlManager.unique(Goods.class, 1);
        return goods.toString();
    }

    @PostMapping("/test1")
    public String beetlTest_1(@RequestBody Goods goods) {

        List<Goods> goodsList = sqlManager.select("goods_temp.selectSample", Goods.class, goods);
        return goodsList.toString();
    }

    @PostMapping("/test2")
    public String beetlTest_2(@RequestParam("pageNumber") int pageNumber, @RequestParam("pageSize") int pageSize) {

        //分页查询
        PageQuery pageQuery = new PageQuery();
        pageQuery.setOrderBy("id desc");
        pageQuery.setPageNumber(pageNumber);
        pageQuery.setPageSize(pageSize);
        sqlManager.pageQuery("goods_temp.selectOfPage", Goods.class, pageQuery);
        System.out.println(pageQuery.getTotalPage());
        System.out.println(pageQuery.getTotalRow());
        System.out.println(pageQuery.getPageNumber());
        System.out.println(pageQuery.getList());

        return pageQuery.getList().toString();
    }

    @PostMapping("/test2_1")
    public String beetlTest_2_1(@RequestParam("pageNumber") int pageNumber, @RequestParam("pageSize") int pageSize) {

        PageQuery pageQuery = new PageQuery();
        pageQuery.setOrderBy("id desc");
        pageQuery.setPageNumber(pageNumber);
        pageQuery.setPageSize(pageSize);
        //分页查询sql
        sqlManager.pageQuery("goods_temp.queryNewUser", Goods.class, pageQuery);
        //分页查询sql
        sqlManager.pageQuery("goods_temp.queryNewUser1", Goods.class, pageQuery);
        System.out.println(pageQuery.getTotalPage());
        System.out.println(pageQuery.getTotalRow());
        System.out.println(pageQuery.getPageNumber());
        System.out.println(pageQuery.getList());

        return pageQuery.getList().toString();
    }


    /**
     * 通过beetlSQL生成数据库表对应的实体类
     * @param tabelName
     * @return
     */
    @PostMapping("/test3/{tabelName}")
    public String beetlTest_3(@PathVariable String tabelName) {
        try {
            //根据表名生成POJO类，输出到控制台
            sqlManager.genPojoCodeToConsole(tabelName);
            System.out.println("分割线==================================================================================================");
            //根据表名生成查询、更新等sql语句，输出到控制台
            sqlManager.genSQLTemplateToConsole(tabelName);
            System.out.println("分割线==================================================================================================");
            //根据表名生成POJO类到项目工程中
            sqlManager.genPojoCode(tabelName, "com.example.shopgoods.controller.beetlSQL.daotest", new GenConfig());
            System.out.println("分割线==================================================================================================");
            //根据表名生成指定表对应的md文件
            sqlManager.genSQLFile(tabelName);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tabelName + "ok";
    }



    @PostMapping("/test4")
    public String beetlTest4(@RequestParam("id") int id) {
        Goods goods = sqlManager.unique(Goods.class, id);
        /**
         * TainBean
         * Goods实体类里没有字段goodsSn，数据库里是goods_sn，该字段会保存到TainBean里
         */

        return goods.get("goodsSn").toString();
    }


    @PostMapping("/test_5")
    public String beetlTest_5(@RequestBody Goods goods) {

        List<Goods> goodsList = goodsDao.selectSample(goods.getGoodsName());

        return goodsList.toString();
    }


    @PostMapping("/test_6")
    public String beetlTest_6(@RequestParam("id") int id) {
        /**
         * 悲观锁
         */
        Goods goods = sqlManager.lock(Goods.class, id);

        return goods.toString();
    }
}

Goods文件代码如下：

package com.example.shopgoods.controller.beetlSQL.dao;

import lombok.Data;
import org.beetl.sql.core.TailBean;
import org.beetl.sql.core.annotatoin.*;

import java.util.Date;


/**
 * @Author: zp
 * @Date: 2019/6/4 15:27
 * @Description:
 */
@Data
@Table(name = "goods")
public class Goods extends TailBean {

    //表示对应数据库的自增主键
    @AutoID
    private int id;
    private String goodsName;

    /**
     * BeetlSQL在实体类属性上使用@Version提供乐观锁功能，
     * 当调用内置更新方法的时候，被@Version注解的字段将会作为where条件的一部分
     * 例如：update user set name = ?, version = version + 1 where id = ? and version = ?
     */

    @Version
    private Integer version;

    //插入时，忽略此属性
    @InsertIgnore
    //BeetlSQL好像不支持LocalDateTime
    private Date createTime ;
    //更新时，忽略此属性
    @UpdateIgnore
    private Date updateTime ;
}

GoodsDao文件代码如下：

package com.example.shopgoods.controller.beetlSQL.dao;

import org.beetl.sql.core.annotatoin.SqlResource;
import org.beetl.sql.core.mapper.BaseMapper;

import java.util.List;

/**
 * @Author: zp
 * @Date: 2019/6/4 16:48
 * @Description:
 */
@SqlResource("goods_temp")
public interface GoodsDao extends BaseMapper<Goods> {

    public List<Goods> selectSample(String goodsName);

}

goods_temp.md文件代码如下：（该文件放在resources/sql目录下）

selectSample
===
* 简单sql查询
    select * from goods g where 1 = 1 
    @if(!isEmpty(goodsName)){
    and g.goods_name = #goodsName#
    @}
    


selectOfPage
===
* 分页查询
select * from goods g
 
selectOfPage$count
===
* 查询数量
select count(1) from goods g


queryNewUser
===
 * 分页查询
 select 
 @pageTag() {
    id,goods_name
 @}
 from goods
 
 
queryNewUser1
===
 * 分页查询
  select #page("*")# from goods
 

参考文献：

SpingBoot与BeetlSQL结合