BeetlSQL简单使用

戴博

2023-12-01

官网地址：

项目演示使用maven工程，新建一个Maven工程，直接引用如下库

<dependency>
  <groupId>com.ibeetl</groupId>
  <artifactId>beetlsql</artifactId>
  <version>${version}</version>
</dependency>

然后再引入数据库驱动，本章使用sqlserver数据库作为例子，作为快速开始，避免安装数据库服务器，你也可以使用任意其他数据库

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>sqljdbc4</artifactId>
</dependency>

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
</dependency>

<dependency>
   <groupId>org.flywaydb</groupId>
   <artifactId>flyway-core</artifactId>
</dependency>

BeetlSQL访问数据库不依赖数据库连接池，但有数据库连接池是大多数项目的标配。

<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-pool2</artifactId>
</dependency>

最后，需要准备一个sql脚本，以初始化数据库。保存如下sql到resources/db/schema.sql

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

BEGIN;
INSERT INTO `sys_user` VALUES (1, 'lijz', 1, NULL);
INSERT INTO `sys_user` VALUES (2, 'lucy', 1, NULL);
INSERT INTO `sys_user` VALUES (3, 'bear', 2, NULL);
INSERT INTO `sys_user` VALUES (4, 'mike', 1, NULL);
INSERT INTO `sys_user` VALUES (5, 'lisan', 1, NULL);
INSERT INTO `sys_user` VALUES (6, 'xb', 1, NULL);
INSERT INTO `sys_user` VALUES (7, 'duanwu', 2, NULL);
INSERT INTO `sys_user` VALUES (8, 'fenh', 1, NULL);
INSERT INTO `sys_user` VALUES (9, 'lj', 2, NULL);
INSERT INTO `sys_user` VALUES (10, 'gshen', 1, NULL);
INSERT INTO `sys_user` VALUES (11, 'lihui', 1, NULL);
COMMIT;

创建一个Java类com.QuickTest，如下

import com.zaxxer.hikari.HikariDataSource;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.H2Style;
import org.beetl.sql.ext.DBInitHelper;
import org.beetl.sql.ext.DebugInterceptor;

import javax.sql.DataSource;
import java.util.Set;

public class QuickTest {  
	private static   DataSource datasource() {
		HikariDataSource ds = new HikariDataSource();
    //内存数据库
		ds.setJdbcUrl("jdbc:h2:mem:dbtest;DB_CLOSE_ON_EXIT=FALSE");
		ds.setUsername("sa");
		ds.setPassword("");
		ds.setDriverClassName("org.h2.Driver");
		return ds;
	}

	private  static SQLManager getSQLManager(){
    //得到一个数据源
		DataSource dataSource = datasource();
    //得到一个ConnectionSource， 单数据源
		ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource);
    //SQLManagerBuilder 唯一必须的参数就是ConnectionSource
		SQLManagerBuilder builder = new SQLManagerBuilder(source);
    //命名转化，数据库表和列名下划线风格，转化成Java对应的首字母大写，比如create_time 对应ceateTime
		builder.setNc(new UnderlinedNameConversion());
    //拦截器，非必须，这里设置一个debug拦截器，可以详细查看执行后的sql和sql参数
		builder.setInters(new Interceptor[]{new DebugInterceptor()});
    //数据库风格，因为用的是sqlserver,所以使用sqlserver,
		builder.setDbStyle(new SqlServerStyle());
		SQLManager sqlManager = builder.build();
		return sqlManager;
	}
	
    public static void main(String[] args) throws Exception {
        SQLManager sqlManager = getSQLManager();
        //初始化数据脚本，执行后，内存数据库将有一个sys_user表和模拟数据
        DBInitHelper.executeSqlScript(sqlManager,"db/schema.sql");
       // 得到数据库的所有表
        Set<String> all =  sqlManager.getMetaDataManager().allTable();
        System.out.println(all);   
    }

}

新建一个POJO对象 com.UserEntity,与sys_user表对应

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

这个Pojo对象我们注意到使用了@Table注解，表明了关联的表是sys_user. @Table注解是非必须的，如果符合命名转化(NameConversion).也可以不需要

@AutoID 作用于注解上，表示这是一个自增主键, 其他标识主键的注解还有@AssignID，@SeqID

使用beetlSql

按照主键查寻

 UserEntity user  = sqlManager.unique(UserEntity.class,1);

更新

属性全部更新(未赋值的会被赋值空修改掉)

UserEntity user  = sqlManager.unique(UserEntity.class,1);
user.setName("ok123");
sqlManager.updateById(user);

按照模板查询

查询部门为1得到所有用户

UserEntity template = new UserEntity();
template.setDepartmentId(1);
List<UserEntity> list = sqlManager.template(template);

执行SQL

String sql = "select * from sys_user where id=?";
Integer id  = 1;
SQLReady sqlReady = new SQLReady(sql,new Object[]{id});
List<UserEntity> userEntities = sqlManager.execute(sqlReady,UserEntity.class);

String updateSql = "update department set name=? where id =?";
String name="lijz";
SQLReady updateSqlReady = new SQLReady(updateSql,new Object[]{name,id});
sqlManager.executeUpdate(updateSqlReady);

执行模板SQL

像MyBatis那样，BeetlSQL 支持模板SQL。

{
  String sql = "select * from sys_user where department_id=#{id} and name=#{name}";
  UserEntity paras = new UserEntity();
  paras.setDepartmentId(1);
  paras.setName("lijz");
  List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);
}

{
  //或者使用Map作为参数
  String sql = "select * from sys_user where department_id=#{myDeptId} and name=#{myName}";
  Map paras = new HashMap();
  paras.put("myDeptId",1);
  paras.put("myName","lijz");
  List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);
}

模板占位符默认是#{} BeetlSQL会输出"?",如果使用${} 则原样输出内容，这个同MyBatis一致。

模板使用Beetl语法，因此支持复杂的SQL构建

//使用Beetl模板语句
String sql = "select * from sys_user where 1=1 \n" +
  "-- @if(isNotEmpty(myDeptId)){\n" +
  "   and department_id=#{myDeptId}\n" +
  "-- @}\n" +
  "and name=#{myName}";


Map paras = new HashMap();
paras.put("myDeptId",1);
paras.put("myName","lijz");
List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);

模板默认使用-- @ 和 回车作为定界符，因为-- 是sql注释符号，这有助于idea或者其他IDE不会报错

使用Query

通常业务代码需要根据一定逻辑查询数据库，可以使用Query构造较为复杂的单表条件而避免写SQL

Query<UserEntity> query = sqlManager.query(UserEntity.class);
List<UserEntity> entities = query.andEq("department_id",1)
  .andIsNotNull("name").select();

使用LambdaQuery，能很好的支持数据库重构

LambdaQuery<UserEntity> query = sqlManager.lambdaQuery(UserEntity.class);
List<UserEntity> entities = query.andEq(UserEntity::getDepartmentId,1)
  .andIsNotNull(UserEntity::getName).select();

这段查询同上段代码是一样的，不同的是使用lambda表达式，这样如果列名重构，则自动会重构这段代码，不需要作手工修改

使用模板文件

稍微做个复杂项目的人都知道，把复杂SQL放到专门的SQL里维护是个很好的办法。（BeetlSQL3 提供了专门的插件来维护SQL）

默认情况下，sql文件位于classpath的sql目录下，可以在resources目录下新建一个sql目录，并在sql目录下新建一个user.md文件

内容如下

select
===

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

如下代码可以访问并执行这个sql语句

SqlId id = SqlId.of("user","select");
Map map = new HashMap();
map.put("name","n");
List<UserEntity> list = sqlManager.select(id,UserEntity.class,map);

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

一个markdown文件可以包含任意多个sql片段，格式如下

文件一些说明，放在头部可有可无，如果有说明，可以是任意文字
SQL标示
===
以*开头的注释,可选
SQL语句

SQL标示2
===
SQL语句 2

使用SqlId指明sql文件位置不方便，更常见的是在mapper方法里调用

@SqlResource("user") /*sql文件在user.md里*/
public interface UserMapper extends BaseMapper<UserEntity> {
  /**
     * 调用sql文件user.md#select,方法名即markdown片段名字
     * @param name
     * @return
     */
     List<UserEntity> select(String name);
}

这里的select方法没有任何注解，意思是调用sql文件执行，sql文件通过@SqlResource申明，sql片段名字则同方法名，sql中用的参数则同方法参数，调用起来非常方便

user.md:

selectUserByName

select * from sys_user u where 1=1 
-- @ if(isNotEmpty(name)){
and name like #{name}
-- @ }
order by u.id desc
pageQuery
select #{page('*')} from sys_user where 1=1 
-- @if(isNotEmpty(deptId)){
 and department_id=#{deptId}
-- @}
pageQuery2
group 语句翻页需要转成子查询
select 
#{page('*')}
from (
select count(1) total,department_id from sys_user where 1=1 
-- @if(isNotEmpty(deptId)){
   and department_id=#{deptId}
-- @}
group by department_id
) a
pageQuery3
select #{page()} from sys_user where 1=1 
-- @if(isNotEmpty(deptId)){
 and department_id=#{deptId}
-- @}
pageQuery3$count
select count(1) from sys_user /* 使用指定的count语句*/
departmentJsonMapping
映射DepartmentInfo
{
"id":"id",
"name":"name",
"users":
  {
   "id":"u_id",
   "name":"u_name"
   }
}

内置查询API

public T unique(Class clazz,Object pk) 根据主键查询，如果未找到，抛出BeetlSQLException异常.
public T single(Class clazz,Object pk) 根据主键查询，如果未找到，返回null.
public List selectByIds(Class clazz, List<?> pks) 根据一批主键查询
public List all(Class clazz) 查询出所有结果集
public List all(Class clazz, int start, int size) 翻页
public int allCount(Class<?> clazz) 总数
public T lock(Class clazz, Object pk) 同single方法，但会得到一个行级锁，行级锁的失效机制取决于事务失效

template查询

public List template(T t) 根据模板查询，返回所有符合这个模板的数据库
public T templateOne(T t) 根据模板查询，返回一条结果，如果没有找到，返回null，如果找到2条以上，则抛异常
public long templateCount(T t) 获取符合条件的个数

更新操作

public void insert(Object paras) 插入paras到paras这个Entity类关联的表，如果paras对象有自增主键，则自动赋值。如果属性有@InsertIgnore，则不参与插入，其他数据库或者BeetlSQL框架自动赋值的还有@Auto，@Seq, @Version @LogicDelete等注解
public void insertTemplate(Object paras) 插入paras到paras关联的表,忽略属性为空值的属性，如果paras对象有自增主键，则自动赋值。如果属性有@InsertIgnore，则不参与插入，同insert方法
public void insert(Class<?> clazz,Object paras) 插入paras到clazz关联的表。表定义以clazz为准。
public int updateById(Object obj) 根据主键更新，所有值参与更新，除非有@UpdateIgnore 标识的属性
public int updateTemplateById(Object obj) 根据主键更新，属性为空的，@UpdateIgnore 标识的属性不会更新
public int updateBatchTemplateById(Class clazz,List<?> list) 批量根据主键更新,属性为null的不会更新
public int updateTemplateById(Class<?> clazz，Map paras) 根据主键更新，clazz决定了表以及id，paras提供了参数
public int[] updateByIdBatch(List<?> list) 批量更新所有属性，返回对应批量更新成功的更新记录数
public void insertBatch(Class clazz,List<?> list) 批量插入数据，如果数据库自增主键，获取
public void insertBatch(Class clazz,List<?> list,boolean autoAssignKey) 批量插入数据,如果数据库自增主键，获取。
public int upsert(Object obj), 更新或者插入一条。先判断是否主键为空，如果为空，则插入，如果不为空，则从数据库按照此主健取出一条，如果未取到，则插入一条，其他情况按照主键更新。插入后的自增或者序列主健
public int upsertByTemplate(Object obj), 更新或者插入一条。按照模板方式更新或者插入

内置方法

print、println ：输出，同js，如print("table1");
has ：判断是否有此全局变量;
isEmpty ：判断表达式是否为空，不存在，空字符串，空集合都返回true;
debug ：将变量输出到控制台，如 debug(user);
text ：输出变量值本身，但可用于占位符号里
page ：分页函数，用于在PageQuery翻页里，根据上下问决定输出count(1) 或者count(*)，如果有参数，则按照参数输出
join ：将集合或数组内元素用逗号拼接，并输出? 用于占位符，用于in，如