SpringBoot整合BeetlSql3.0及BeetlSql3.0快速入门说明

一 SpringBoot整合BeetlSql3.0

1.1 加入依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>


        <!--beetlSQL 3.x版本-->
        <!-- https://mvnrepository.com/artifact/com.ibeetl/sql-springboot-starter -->
        <dependency>
            <groupId>com.ibeetl</groupId>
            <artifactId>sql-springboot-starter</artifactId>
            <version>3.10.1-RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP 数据源配置类,数据库连接池-->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>4.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

    </dependencies>

1.2 配置文件

spring.datasource.url=jdbc:mysql://localhost:3306/study
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver


#beetlsql = sqlManager1 #旧版本
#新版本，配置一个sqlManager，#给sqlManager配置一个数据源
beetlsql.sqlManagers=sqlManager1
beetlsql.sqlManager1.ds=datasource
beetlsql.sqlManager1.basePackage=org.beetl.sql.springboot.simple
beetlsql.sqlManager1.dbStyle=org.beetl.sql.core.db.MySqlStyle

1.3 配置数据源

注：beetlsql.sqlManager1.ds=datasource里的datasource是你配置的bean名，bean名最好不要改，配置文件会出现找不到数据源的情况。

@Configuration
public class SimpleDataSourceConfig {

    @Bean(name = "datasource")
    public DataSource datasource(Environment env) {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl(env.getProperty("spring.datasource.url"));
        ds.setUsername(env.getProperty("spring.datasource.username"));
        ds.setPassword(env.getProperty("spring.datasource.password"));
        ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        return ds;
    }

}

1.4 测试

1.4.1 准备

数据库中新建一张user表，并在项目中创建对应的实体类

@Data
@Table(name = "user")
public class UserEntity {
    private Integer id;
    private Integer age;
    private String name;
    private Integer departmentId;
}

1.4.2 测试

创建测试类：

@SpringBootTest
public class SQLManagerTest {

    @Resource
    SQLManager sqlManager;

    @Test
    public void test01(){
        LambdaQuery<UserEntity> query = sqlManager.lambdaQuery(UserEntity.class);
        List<UserEntity> entities = query.andEq(UserEntity::getDepartmentId,1)
                .andIsNotNull(UserEntity::getName).select();
        System.out.println(entities.toString());
    }

}

运行成功截图：

二 BeetlSql3.0快速入门

2.1 BeetlSql3.0官方文档

https://www.kancloud.cn/xiandafu/beetlsql3_guide/3033075

2.2 三种使用方法

2.2.1 直接注入SQLManager

@Resource
SQLManager sqlManager;

2.2.2 Mapper继承BaseMapper<UserEntity>，然后利用SQLManager生成mapper接口的代理对象

dao层：

public interface UserMapper extends BaseMapper<UserEntity> {}

使用：

    @Test
    public void test01(){
        //得到一个UserMapper接口的代理
        UserMapper mapper = sqlManager.getMapper(UserMapper.class);//代替了dao层的注入？
        UserEntity me = mapper.unique(1);//同SQLManager.unique(UserEntity.class,1)
        me.setName("mapper测试");
        mapper.updateById(me);//同同SQLManager.updateById(me);
    }

2.2.3 使用使用模板文件

2.2.3.1 在resources目录下新建一个sql目录，并在sql目录下新建一个user.md文件（相当于mybatis的xml文件，通过SqlId找到对应的SQL）

[//]: # (片段名/SqlId)
select
===

```sql
select * from user u where 1=1
-- @ if(isNotEmpty(name)){
and name like #{'%'+name+'%'}
-- @ }
order by u.id desc
```

2.2.3.2 

sqlManager.select将会查询user.md文件下的select片段，并执行，执行结果映射成UserEntity对象。

    @Resource
    SQLManager sqlManager;

    @Test
    public void test02(){
        //user为模板文件名，select为模板文件中片段名/SqlId
        SqlId id = SqlId.of("user","select");
        Map map = new HashMap();
        map.put("name","n");
        List<UserEntity> list = sqlManager.select(id,UserEntity.class,map);
        System.out.println(list.toString());
    }

截图：