狂刃贺岁新版专题

BeetlSQL3 特点

2025-12-31 01:34:10 • 刺客觉醒

派别:以SQL为中心的ORM工具

内置常见增删改查功能，简单系统100%不用写sql，使用内置SQL,或者Query API实现。复杂项目也能节省50%工作量强化SQL管理，可以通过md文件管理sql，使用Beetl模板编写复杂sql，提供idea插件编写md文件全面支持跨数据库平台，支持30+数据库，且提供扩展支持功能

支持NOSQL，如ClickhHouse，Elastic，Hive等

支持SQL查询引擎，如Apache Drill，Presto等

支持一对一，一对多等常见的映射。

支持多库，主从库的扩展，支持各种多租户设计，如单表多租户，多表多租户，多库多租户等

可以使用约定习俗映射，复杂查询结果支持通过json配置映射到POJO

提供idea插件

提供丰富的扩展功能，80%的功能都可以自行扩展，打造自己个性化的数据库发访问框架，扩展适应新的数据库&NOSQL&查询引擎，可以扩展定制成自己数据库访问框架

代码生成功能，根据表结构生成实体，DAO，数据库文档等功能

代码设计简单，可以很快掌握BeetlSQL源码。

数据库访问工具的痛点数据库访问工具各有优缺点，如下是一下·常用缺点

开发效率低，如mybatis，还需要搭配plus工具才能提高开发效率，而JOOQ这样的又不适合用在复杂系统中

错误提示不准确，如mybatis这样核心使用OGNL和xml解析器，导致错误提示不准确。英文提示对国人不友好。

JOOQ，Hibeernate等无SQL管理，遇到复杂的sql特别难维护，比如在Java里拼写sql，遇到调整就麻烦，对DBA不友好

跨数据库平台，即使Hibernate，也完全做不到跨数据库，如何处理同一个查询需求，不同数据库使用不同方式实现

Hibernate这样定义的HQL 比SQL难维护，经常出现知道SQL怎么写，但不知道HQL怎么写的窘境。

缺少数据库和NOSQL无缝切换很难，比如一部分业务要无缝切换到NOSQL上

数据库重构对代码影响非常大，比如数据库列修改，单库变成多库，系统调整较大

BeetlSQL3 例子所有例子都可以从https://gitee.com/xiandafu/beetlsql/tree/master/sql-samples 看到和运行

如果项目编译通过，那可以直接运行单元测试或者samples模块的实例代码

sql-test包含了单元测试，有60+单元测试方法，采用了H2数据库，数据脚本是db-init.sql因此可以直接运行任何一个单元测试方法.

sql-samples 又包含了三个模块大约100个例子，所有例子也采用了H2数据库，可以直接使用。

quickstart: BeetlSQL基础使用例子，可以快速了解BeetlSQL3

usage: BeetlSQL所有API和功能

plugin:BeetlSQL高级扩展实例

以usage模块为例子，包含如下代码

S01MapperSelectSample 15个例子， mapper中的查询演示S02MapperUpdateSample 11个例子， mapper中更新操作S03MapperPageSample 3个例子，mapper中的翻页查询S04QuerySample 9个例子，Query查询S05QueryUpdateSample 3个例子，Query完成update操作S06SelectSample 14个例子，SQLManager 查询APIS07InsertSample 8个例子，SQLManager 插入新数据API,主键生成S08UpdateSample 6个例子,更新数据S09JsonMappingSample 5个例子， json配置映射S10FetchSample 2个例子，关系映射S11BeetlFunctionSample 2个例子，自定义sql脚本的方法sql-integration包含了spring等框架的单元测试例子

sql-spring 包含spring单库，多库单元测试sql-springboot 包含了springboot 单库，多库单元测试sql-jfinal 包含了jfinal单元测试sql-solon，包含了solon单元测试sql-act 包含了act框架单元测试通过这些例子你可以建立第一步对BeetlSQL的印象，也可以在BeetlSQL 在线体验 http://121.42.237.11:8080/beetlsql_online/ 中直接运行简单例子

BeetlSQL的Spring Boot多库多租户例子 https://gitee.com/xiandafu/springboot3-beetl-beetlsql-example，包含了10种多库多租户实现方案

基础例子

/**

* 入门演示内置SQLManager用法和BaseMapper用法，项目中更推荐使用BaseMapper，而不是较为底层的SQLManager

* @author xiandafu

public class S1QuickStart {

SQLManager sqlManager;

UserMapper mapper = null;

public S1QuickStart(SQLManager sqlManager) {

this.sqlManager = sqlManager;

mapper = sqlManager.getMapper(UserMapper.class);

}

public static void main(String[] args) throws Exception {

SQLManager sqlManager = SampleHelper.getSqlManager();

S1QuickStart quickStart = new S1QuickStart(sqlManager);

quickStart.baseSqlManager();

quickStart.executeSql();

quickStart.executeTemplate();

quickStart.query();

quickStart.mapper();

quickStart.sqlResource();

}

/**

* 使用内置sqlManager方法

public void baseSqlManager(){

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

user.setName("ok123");

sqlManager.updateById(user);

UserEntity newUser = new UserEntity();

newUser.setName("newUser");

newUser.setDepartmentId(1);

sqlManager.insert(newUser);

UserEntity template = new UserEntity();

template.setDepartmentId(1);

List list = sqlManager.template(template);

}

//执行sql语句方法

public void executeSql(){

String sql = "select * from user where id=?";

Integer id = 1;

SQLReady sqlReady = new SQLReady(sql,new Object[id]);

List 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模板语句

public void executeTemplate(){

{

String sql = "select * from user where department_id=#{id} and name=#{name}";

UserEntity paras = new UserEntity();

paras.setDepartmentId(1);

paras.setName("lijz");

List list = sqlManager.execute(sql,UserEntity.class,paras);

}

{

//或者使用Map作为参数

String sql = "select * from user where department_id=#{myDeptId} and name=#{myName}";

Map paras = new HashMap();

paras.put("myDeptId",1);

paras.put("myName","lijz");

List list = sqlManager.execute(sql,UserEntity.class,paras);

}

{

//使用Beetl模板语句

String sql = "select * from user where 1=1 \n" +

"-- @if(isNotEmpty(myDeptId)){\n" +

" and department_id=#{myDeptId}\t\n" +

"-- @}\n" +

"and name=#{myName}";

Map paras = new HashMap();

paras.put("myDeptId",1);

paras.put("myName","lijz");

List list = sqlManager.execute(sql,UserEntity.class,paras);

}

public void query(){

{

Query query = sqlManager.query(UserEntity.class);

List entities = query.andEq("department_id",1)

.andIsNotNull("name").select();

}

{

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

LambdaQuery query = sqlManager.lambdaQuery(UserEntity.class);

List entities = query.andEq(UserEntity::getDepartmentId,1)

.andIsNotNull(UserEntity::getName).select();

}

/**

* 最常用的方式，编写一个Mapper类，mapper方法提供数据库访问接口，beetlsql提供丰富的beetlsql实现

public void mapper(){

// 内置BaseMapper方法调用

List list = mapper.all();

boolean isExist = mapper.exist(2);

UserEntity me = mapper.unique(1);

me.setName("newName");

mapper.updateById(me);

//调用其他方法

UserEntity user = mapper.getUserById(1);

UserEntity user2 = mapper.queryUserById(2);

mapper.updateName("newName2",2);

List users = mapper.queryByNameOrderById("newName2");

List depts = mapper.findAllDepartment();

}

/**

* 对于复杂sql语句，比如几十行，甚至几百行的sql模板语句，放到markdown文件里是个不错的想法

* 参考sql/user.md#select

public void sqlResource(){

SqlId id = SqlId.of("user","select");

//or SqlId id = SqlId.of("user.select");

Map map = new HashMap();

map.put("name","n");

List list = sqlManager.select(id,UserEntity.class,map);

UserMapper mapper = sqlManager.getMapper(UserMapper.class);

mapper.select("n");

}

}结果集映射/**

* 演示如何将数据库查询结果映射到java对象上

* @author xiandafu

public class S2MappingSample {

SQLManager sqlManager;

UserMapper mapper =null;

public S2MappingSample(SQLManager sqlManager) {

this.sqlManager = sqlManager;

mapper = sqlManager.getMapper(UserMapper.class);

}

public static void main(String[] args) throws Exception {

SQLManager sqlManager = SampleHelper.getSqlManager();

S2MappingSample mappingSample = new S2MappingSample(sqlManager);

mappingSample.column();

mappingSample.toMap();

mappingSample.view();

mappingSample.mappingProvider();

mappingSample.jsonConfig();

mappingSample.autoMapping();

mappingSample.myAttributeAnnotation();

}

/**

* 使用@Column注解，或者按照NameConversion来自动映射

public void column() {

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

}

/**

* 可以把查询结果转化成Map,在java中，注意，滥用map作为也业务对象是非常糟糕设计

public void toMap() {

SQLReady sqlReady = new SQLReady("select id,name from user");

List list = sqlManager.execute(sqlReady, Map.class);

}

/**

public void view() {

//映射所有列

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

//映射只有一个KeyInfo标注的属性，本例子中department属性不在查询结果范围里

TestUser keyInfo = sqlManager.viewType(TestUser.KeyInfo.class).unique(TestUser.class, 1);

}

/**

* 使用额外的映射类来映射

public void mappingProvider() {

//运行时刻指定一个映射类

TestUser testUser = sqlManager.rowMapper(MyRowMapper.class).unique(TestUser2.class, 1);

//使用@RowProvider注解为类指定一个Mapper，这个更常用

TestUser2 testUser2 = sqlManager.unique(TestUser2.class, 1);

}

/**

* 使用json 配置来映射，类似mybatis的xml配置

public void jsonConfig() {

String sql = "select d.id id,d.name name ,u.id u_id,u.name u_name " +

" from department d join user u on d.id=u.department_id where d.id=?";

Integer deptId = 1;

SQLReady ready = new SQLReady(sql,new Object[]{deptId});

List list = sqlManager.execute(ready,DepartmentInfo.class);

System.out.println(list.toString());

}

/**

* 使用json 配置来映射，类似mybatis的xml配置

public void autoMapping() {

List list = mapper.allUserView();

System.out.println(list);

}

/**

* 自定义一个属性注解Base64,用于编码和解码属性字段

public void myAttributeAnnotation(){

UserData userData = new UserData();

userData.setName("123456");

sqlManager.insert(userData);

UserData data = sqlManager.unique(UserData.class,userData.getId());

System.out.println("user name "+data.getName());

UserEntity entity = sqlManager.unique(UserEntity.class,userData.getId());

System.out.println("db value "+entity.getName());

}

/**

* 演示使用Column 注解映射java属性与表列名，

@Data

@Table(name="user")

public static class MyUser {

@Column("id")

@AutoID

Integer myId;

@Column("name")

String myName;

}

@Data

@Table(name="user")

public static class TestUser {

public static interface KeyInfo {

}

@Column("id")

@AutoID

@View(KeyInfo.class)

Integer myId;

@Column("name")

@View(KeyInfo.class)

String myName;

Integer departmentId;

}

@RowProvider(MyRowMapper.class)

public static class TestUser2 extends TestUser {

}

/**

* 使用json配置来映射,如果映射配置过长，建议放到文件中，使用resource说明配置路径

@Data

@ResultProvider(JsonConfigMapper.class)

// @JsonMapper(

// "{'id':'id','name':'name','users':{'id':'u_id','name':'u_name'}}")

@org.beetl.sql.annotation.entity.JsonMapper(resource ="user.departmentJsonMapping")

public static class DepartmentInfo {

Integer id;

String name;

List users;

}

@Data

public static class UserInfo {

Integer id;

String name;

}

/**

* 如果数据库查询的结果与类定义一致，也可以使用AutoJsonMapper

@Data

@ResultProvider(AutoJsonMapper.class)

public static class MyUserView {

Integer id;

String name;

DepartmentEntity dept;

}

public static class MyRowMapper implements RowMapper {

@Override

public TestUser mapRow(ExecuteContext ctx, Object obj, ResultSet rs, int rowNum, Annotation config) throws SQLException {

TestUser testUser = (TestUser) obj;

testUser.setMyName(testUser.getMyName() + "-" + System.currentTimeMillis());

return testUser;

}

@Table(name="user")

@Data

public static class UserData{

@AutoID

Integer id;

@Base64

String name;

}

@Retention(RetentionPolicy.RUNTIME)

@Target(value = {ElementType.METHOD, ElementType.FIELD})

@Builder(Base64Convert.class)

public static @interface Base64 {

}

/**

* 自定义一个注解，实现把属性字段加密存入数据库，取出的时候解密

public static class Base64Convert implements AttributeConvert {

Charset utf8 = Charset.forName("UTF-8");

public Object toDb(ExecuteContext ctx, Class cls, String name, Object dbValue) {

String value= (String) BeanKit.getBeanProperty(dbValue,name);

byte[] bs = java.util.Base64.getEncoder().encode(value.getBytes(utf8));

return new String(bs,utf8);

}

public Object toAttr(ExecuteContext ctx, Class cls, String name, ResultSet rs, int index) throws SQLException {

String value = rs.getString(index);

return new String(java.util.Base64.getDecoder().decode(value),utf8);

}

}翻页查询public class S3PageSample {

SQLManager sqlManager;

UserMapper mapper =null;

public S3PageSample(SQLManager sqlManager) {

this.sqlManager = sqlManager;

mapper = sqlManager.getMapper(UserMapper.class);

}

public static void main(String[] args) throws Exception {

SQLManager sqlManager = SampleHelper.getSqlManager();

S3PageSample page = new S3PageSample(sqlManager);

page.baseRange();

page.page();

page.jdbcPage();

page.resourceSqlPage();

page.resourceGroupSqlPage();

}

/**

* 范围查询

public void baseRange(){

List all = mapper.all();

long count = mapper.allCount();

UserEntity template = new UserEntity();

template.setDepartmentId(1);

UserEntity user1 = mapper.templateOne(template);

}

/**

* 翻页查询，使用模板sql

public void page(){

/**

* sql模板语句的page函数能自动把sql模板语句转为为求总数语句

String sql = "select #{page('*')} from user where department_id=#{id}";

PageRequest request = DefaultPageRequest.of(1,10);

Map map = new HashMap<>();

map.put("id",1);

PageResult pr = sqlManager.executePageQuery(sql,UserEntity.class,map,request);

//强制转化为DefaultPageResult,

DefaultPageResult pageResult = (DefaultPageResult)pr;

printPageResult(pageResult);

}

/**

* 直接使用jdbc sql

public void jdbcPage(){

/**

* 解析jdbc sql语句，生成求总数语句

String sql = "select * from user where department_id=?";

PageRequest request = DefaultPageRequest.of(1,10);

SQLReady sqlReady = new SQLReady(sql,new Object[]{1});

PageResult pr = sqlManager.execute(sqlReady,UserEntity.class,request);

DefaultPageResult pageResult = (DefaultPageResult)pr;

printPageResult(pageResult);

}

/**

* 翻页查询通常很复杂，SQL很长，把sql语句放到sql文件里是个好办法，也是最常用的办法

public void resourceSqlPage(){

PageRequest request = DefaultPageRequest.of(1,10);

PageResult pr = mapper.pageQuery(1,request);

DefaultPageResult pageResult = (DefaultPageResult)pr;

printPageResult(pageResult);

}

/**

* 对分组语句进行翻页查询，需要嵌套在子查询里，比如


* select count(1),name from user group by name
*

* 如上分组提供给beetlsql的时候，应该编写成


* select #{page()} from ( select count(1),name from user group by name ) a
*

public void resourceGroupSqlPage(){

PageRequest request = DefaultPageRequest.of(1,10);

PageResult pr = mapper.pageQuery2(1,request);

DefaultPageResult pageResult = (DefaultPageResult)pr;

printPageResult(pageResult);

}

public void printPageResult(DefaultPageResult pageResult){

System.out.println(pageResult.getPage());

System.out.println(pageResult.getPageSize());

System.out.println(pageResult.getTotal());

System.out.println(pageResult.getTotalPage());

System.out.println(pageResult.getResult());

}

}演示like，batchUpdate,in 操作public class S4Other {

SQLManager sqlManager;

UserMapper mapper = null;

public S4Other(SQLManager sqlManager) {

this.sqlManager = sqlManager;

mapper = sqlManager.getMapper(UserMapper.class);

}

public static void main(String[] args) throws Exception {

SQLManager sqlManager = SampleHelper.getSqlManager();

S4Other others = new S4Other(sqlManager);

others.like();

others.in();

others.batch();

others.sqlResult();

}

/**

* like

public void like() {

String sql = "select * from user where name like #{name}";

Map paras = new HashMap();

String name = "%li%";

paras.put("name", name);

List users = sqlManager.execute(sql, UserEntity.class, paras);

//同样效果

sql = "select * from user where name like #{'%'+name+'%'}";

paras = new HashMap();

name = "li";

paras.put("name", name);

users = sqlManager.execute(sql, UserEntity.class, paras);

//同样效果

SQLReady sqlReady = new SQLReady("select * from user where name like ?"

,new Object[]{"%"+name+"%"});

users = sqlManager.execute(sqlReady,UserEntity.class);

}

/**

* in

public void in() {

//使用beetlsql提供的join函数,接受一个list变量

String sql = "select * from user where id in ( #{join(ids)} )";

List list = Arrays.asList(1,2,3,4,5);

Map paras = new HashMap();

paras.put("ids", list);

List users = sqlManager.execute(sql, UserEntity.class, paras);

}

/**

* batch

public void batch() {

//批量插入

UserEntity user1 = new UserEntity();

user1.setName("b1");

user1.setDepartmentId(1);

UserEntity user2 = new UserEntity();

user2.setName("b2");

user2.setDepartmentId(1);

//根据组件批量更新

List data = Arrays.asList(user1,user2);

sqlManager.insertBatch(UserEntity.class,data);

data.get(1).setName("bb11");

sqlManager.updateByIdBatch(data);

//循环删除，执行多次

data.stream().forEach(userEntity -> mapper.deleteById(userEntity.getId()));

}

/**

* 不执行，只得到sql语句和参数

public void sqlResult(){

Map map = new HashMap();

map.put("name","li");

SQLResult sqlResult = sqlManager.getSQLResult(SqlId.of("user","select"),map);

String targetJdbc = sqlResult.jdbcSql;

Object[] paras = sqlResult.toObjectArray();

System.out.println(targetJdbc);

System.out.println(Arrays.asList(paras));

}

}自动fetch

/**

* 演示自动fetch,类似orm，但不同于orm，CRUD在ORM概念下过于复杂，

* BeetlSQL的fetch没有那么多复杂概念，仅仅是加载对象后看看还有没有需要再加载的对象

* @author xiandafu

public class S5Fetch {

SQLManager sqlManager;

public S5Fetch(SQLManager sqlManager) {

this.sqlManager = sqlManager;

}

public static void main(String[] args) throws Exception {

//为了简单起见，俩个sqlManager都来自同一个数据源，实际是不同数据库，甚至是NOSQL

SQLManager sqlManager = SampleHelper.init();

S5Fetch fetch = new S5Fetch(sqlManager);

fetch.fetchOne();

fetch.fetchMany();

}

/**

public void fetchOne(){

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

System.out.println(user.getDept());

//fetchOne 会合并查询提高性能

List users = sqlManager.all(UserData.class);

System.out.println(users.get(0).getDept());

}

public void fetchMany(){

DepartmentData dept = sqlManager.unique(DepartmentData.class,1);

System.out.println(dept.getUsers());

}

/**

* 用户数据使用"a" sqlmanager

@Data

@Table(name="user")

@Fetch

public static class UserData {

@Auto

private Integer id;

private String name;

private Integer departmentId;

@FetchOne("departmentId")

private DepartmentData dept;

}

/**

* 部门数据使用"b" sqlmanager

@Data

@Table(name="department")

@Fetch

public static class DepartmentData {

@Auto

private Integer id;

private String name;

@FetchMany("departmentId")

private List users;

}

}多数据库参考 https://gitee.com/xiandafu/springboot3-beetl-beetlsql-example，或者源码S6MoreDatabase中提供的简单例子

master-slave-database 主从库例子mutiple-database 多库（业务库）例子tenant 多租户例子，包含了单库单表多租户，单库多表租户，以及多库多租户例子代码生成框架演示代码生成框架，以及生成代码和数据库文档

/**

* 演示beetlsql 代码生成框架

* @author xiandafu

public class S7CodeGen {

SQLManager sqlManager;

public S7CodeGen(SQLManager sqlManager) {

this.sqlManager = sqlManager;

initGroupTemplate();

}

protected void initGroupTemplate(){

//指定模板文件路径，正常情况下，不需要要指定，默认在classpath:templates，但idea的环境读取不到

GroupTemplate groupTemplate = BaseTemplateSourceBuilder.getGroupTemplate();

String root = System.getProperty("user.dir");

//代码模板在sql-gen，你可以指定自己的模板路径

String templatePath = root+"/sql-gen/src/main/resources/templates/";

FileResourceLoader resourceLoader = new FileResourceLoader(templatePath);

groupTemplate.setResourceLoader(resourceLoader);

}

public static void main(String[] args) throws Exception {

//为了简单起见，俩个sqlManager都来自同一个数据源，实际是不同数据库，甚至是NOSQL

SQLManager sqlManager = SampleHelper.init();

S7CodeGen gen = new S7CodeGen(sqlManager);

gen.genCode();

gen.genDoc();

gen.genAllDoc();

}

/**

* 代码生成，生成实体，mapper代码

public void genCode(){

List sourceBuilder = new ArrayList<>();

SourceBuilder entityBuilder = new EntitySourceBuilder();

SourceBuilder mapperBuilder = new MapperSourceBuilder();

SourceBuilder mdBuilder = new MDSourceBuilder();

sourceBuilder.add(entityBuilder);

sourceBuilder.add(mapperBuilder);

sourceBuilder.add(mdBuilder);

SourceConfig config = new SourceConfig(sqlManager,sourceBuilder);

//如果有错误，抛出异常而不是继续运行1

EntitySourceBuilder.getGroupTemplate().setErrorHandler(new ReThrowConsoleErrorHandler() );

ConsoleOnlyProject project = new ConsoleOnlyProject();

String tableName = "USER";

config.gen(tableName,project);

}

/**

* 生成数据库文档

public void genDoc(){

List sourceBuilder = new ArrayList<>();

SourceBuilder docBuilder = new MDDocBuilder();

sourceBuilder.add(docBuilder);

SourceConfig config = new SourceConfig(sqlManager,sourceBuilder);

//如果有错误，抛出异常而不是继续运行1

EntitySourceBuilder.getGroupTemplate().setErrorHandler(new ReThrowConsoleErrorHandler() );

ConsoleOnlyProject project = new ConsoleOnlyProject();

String tableName = "USER";

config.gen(tableName,project);

}

/**

* 生成数据库文档

public void genAllDoc(){

List sourceBuilder = new ArrayList<>();

SourceBuilder docBuilder = new MDDocBuilder();

sourceBuilder.add(docBuilder);

SourceConfig config = new SourceConfig(sqlManager,sourceBuilder);

//如果有错误，抛出异常而不是继续运行1

EntitySourceBuilder.getGroupTemplate().setErrorHandler(new ReThrowConsoleErrorHandler() );

StringOnlyProject project = new StringOnlyProject();

config.genAll(project);

String output = project.getContent();

System.out.println(output);

}