本次调整把BeetlSQL的扩展调整到beetlsql-ext模块下,包含如下子模块
- sql-xml: XML文件支持,允许使用类似MyBatis的XML语法写BeetlSQL的SQL文件
- sql-firewall: SQL防火墙支持,允许对发送到数据库的SQL检查,避免一些常见的SQL错误
- sql-accelerator: 对BeetlSQL进行扩展,提升性能,通过此模块,性能提升30%
如下给高仿myabtis的xml语法,使用了Beetl模板引擎实现
<?xml version="1.0" encoding="UTF-8" ?>
<beetlsql>
<sql id="testAll">
<!-- 测试sql -->
select * from sys_user
<where>
<if test="has(user) and user.name=='a'">
and name='3'
</if>
<bind value="1+99" export="newValue"/>
<isNotEmpty value="'1'">
and name='5'
</isNotEmpty>
and name = #{newValue}
and name in
<foreach items="[1,2,3]" var="id,status" open="(" close=")" separator=",">
#{id+status.index}
</foreach>
<include refid="commonWhere"/>
</where>
</sql>
<sql id="commonWhere">
and name='bdfdsf'
</sql>
<resultMap id="complexListMap">
<result property="id" column="id"/>
<collection property="listInfo" >
<result property="name" column="name"/>
<result property="age" column="age"/>
</collection>
</resultMap>
</beetlsql>
如下是一个sql防火墙使用示例
FireWall fireWall = new FireWall().setDmlCreateEnable(false).setSqlMaxLength(50);
FireWallConfig fireWallConfig = new FireWallConfig(fireWall);
//初始化
fireWallConfig.config(sqlManager);
try{
String sql = "create table u (id int)";
sqlManager.executeUpdate(new SQLReady(sql));
Assert.fail();
}catch (Exception exception){
Assert.assertTrue(exception instanceofBeetlSQLException);
}
如下是使用性能加速模块后的最新性能测试结果,Score越大越好
数据库表增加到20列
Benchmark ModeCntScoreError Units
JMHMain.beetlsqlComplexMapping thrpt3232.763 ±243.693ops/ms
JMHMain.beetlsqlExecuteJdbcthrpt3222.277 ± 68.963ops/ms
JMHMain.beetlsqlExecuteTemplatethrpt3198.478 ± 64.179ops/ms
JMHMain.beetlsqlFile thrpt3191.911 ± 52.679ops/ms
JMHMain.beetlsqlGetAll thrpt35.661 ±2.017ops/ms
JMHMain.beetlsqlInsert thrpt3134.919 ±419.276ops/ms
JMHMain.beetlsqlLambdaQuerythrpt3150.177 ± 39.085ops/ms
JMHMain.beetlsqlOne2Many thrpt3146.740 ± 52.986ops/ms
JMHMain.beetlsqlPageQuerythrpt3128.280 ± 48.814ops/ms
JMHMain.beetlsqlSelectById thrpt3186.317 ± 53.859ops/ms
JMHMain.easyQueryComplexMappingthrpt3 73.130 ± 30.196ops/ms
JMHMain.easyQueryExecuteJdbc thrpt3254.239 ± 33.394ops/ms
JMHMain.easyQueryGetAllthrpt3 15.767 ±4.111ops/ms
JMHMain.easyQueryInsertthrpt3 96.583 ± 54.382ops/ms
JMHMain.easyQueryLambdaQuery thrpt3119.431 ± 30.122ops/ms
JMHMain.easyQueryOne2Manythrpt3 90.403 ± 64.167ops/ms
JMHMain.easyQueryPageQuery thrpt3 79.619 ± 14.064ops/ms
JMHMain.easyQuerySelectByIdthrpt3115.503 ± 25.392ops/ms
JMHMain.flexGetAll thrpt32.554 ±1.472ops/ms
JMHMain.flexInsert thrpt3 74.048 ± 14.794ops/ms
JMHMain.flexPageQuerythrpt3 47.185 ± 23.353ops/ms
JMHMain.flexSelectById thrpt3 69.381 ± 25.800ops/ms
JMHMain.jdbcExecuteJdbcthrpt3631.485 ±291.711ops/ms
JMHMain.jdbcGetAll thrpt3 39.693 ±7.647ops/ms
JMHMain.jdbcInsert thrpt3221.847 ± 1171.190ops/ms
JMHMain.jdbcSelectById thrpt3672.000 ±120.232ops/ms
JMHMain.jpaExecuteJdbc thrpt3 65.684 ± 45.030ops/ms
JMHMain.jpaExecuteTemplate thrpt3 70.961 ± 17.808ops/ms
JMHMain.jpaGetAllthrpt35.189 ±3.821ops/ms
JMHMain.jpaInsertthrpt3 65.872 ± 46.345ops/ms
JMHMain.jpaOne2Manythrpt3105.237 ± 41.245ops/ms
JMHMain.jpaPageQuery thrpt3 63.929 ± 31.189ops/ms
JMHMain.jpaSelectByIdthrpt3346.690 ±147.312ops/ms
JMHMain.mybatisComplexMappingthrpt3111.347 ± 64.790ops/ms
JMHMain.mybatisExecuteTemplate thrpt3 44.240 ± 16.532ops/ms
JMHMain.mybatisFilethrpt3 41.701 ± 10.344ops/ms
JMHMain.mybatisGetAllthrpt34.869 ±1.667ops/ms
JMHMain.mybatisInsertthrpt3 44.899 ± 23.818ops/ms
JMHMain.mybatisLambdaQuery thrpt38.825 ±6.710ops/ms
JMHMain.mybatisPageQuery thrpt3 17.464 ±8.727ops/ms
JMHMain.mybatisSelectByIdthrpt3 44.989 ± 14.594ops/ms
JMHMain.woodExecuteJdbcthrpt3127.590 ± 54.041ops/ms
JMHMain.woodExecuteTemplatethrpt3 89.247 ±715.286ops/ms
JMHMain.woodFile thrpt3124.654 ± 52.517ops/ms
JMHMain.woodGetAll thrpt31.850 ±1.018ops/ms
JMHMain.woodInsert thrpt3 97.668 ± 95.395ops/ms
JMHMain.woodLambdaQuerythrpt3124.571 ± 42.021ops/ms
JMHMain.woodPageQuerythrpt3227.678 ±142.983ops/ms
JMHMain.woodSelectById thrpt3122.248 ± 69.065ops/ms
maven
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetlsql</artifactId>
<version>3.23.9-RELEASE</version>
</dependency>
BeetlSQL 自主研发自 2015 年,目标是提供开发高效,维护高效,运行高效的数据访问框架,它适用范围广,定制性强,写起数据库访问代码特别顺滑,不亚于 MyBatis。你不想写 SQL 也好,或者想更好地写 SQL 也好,BeetlSQL 都能满足这要求,目前支持的数据库如下
- 传统数据库:MySQL (包括支持 MySQL 协议的各种数据库), MariaDB ,Oralce ,Postgres (包括支持 Postgres 协议的各种数据库), DB2 , SQL Server ,H2 , SQLite , Derby ,神通,达梦,华为高斯,人大金仓,PolarDB,GBase8s,GreatSQL 等
- 大数据:HBase,ClickHouse,Cassandar,Hive,GreenPlum
- 物联网时序数据库:Machbase,TD-Engine,IotDB
- SQL 查询引擎:Drill,Presto,Druid
- 内存数据库:ignite,CouchBase
阅读文档 源码和例子 在线体验 多库使用 性能测试