BeetlSQL 3.23.9 发布，SQL防火墙支持
本次调整把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 instanceof BeetlSQLException);
}
如下是使用性能加速模块后的最新性能测试结果，Score越大越好
数据库表增加到20列
Benchmark Mode Cnt Score Error Units
JMHMain.beetlsqlComplexMapping thrpt 3 232.763 ± 243.693 ops/ms
JMHMain.beetlsqlExecuteJdbc thrpt 3 222.277 ± 68.963 ops/ms
JMHMain.beetlsqlExecuteTemplate thrpt 3 198.478 ± 64.179 ops/ms
JMHMain.beetlsqlFile thrpt 3 191.911 ± 52.679 ops/ms
JMHMain.beetlsqlGetAll thrpt 3 5.661 ± 2.017 ops/ms
JMHMain.beetlsqlInsert thrpt 3 134.919 ± 419.276 ops/ms
JMHMain.beetlsqlLambdaQuery thrpt 3 150.177 ± 39.085 ops/ms
JMHMain.beetlsqlOne2Many thrpt 3 146.740 ± 52.986 ops/ms
JMHMain.beetlsqlPageQuery thrpt 3 128.280 ± 48.814 ops/ms
JMHMain.beetlsqlSelectById thrpt 3 186.317 ± 53.859 ops/ms
JMHMain.easyQueryComplexMapping thrpt 3 73.130 ± 30.196 ops/ms
JMHMain.easyQueryExecuteJdbc thrpt 3 254.239 ± 33.394 ops/ms
JMHMain.easyQueryGetAll thrpt 3 15.767 ± 4.111 ops/ms
JMHMain.easyQueryInsert thrpt 3 96.583 ± 54.382 ops/ms
JMHMain.easyQueryLambdaQuery thrpt 3 119.431 ± 30.122 ops/ms
JMHMain.easyQueryOne2Many thrpt 3 90.403 ± 64.167 ops/ms
JMHMain.easyQueryPageQuery thrpt 3 79.619 ± 14.064 ops/ms
JMHMain.easyQuerySelectById thrpt 3 115.503 ± 25.392 ops/ms
JMHMain.flexGetAll thrpt 3 2.554 ± 1.472 ops/ms
JMHMain.flexInsert thrpt 3 74.048 ± 14.794 ops/ms
JMHMain.flexPageQuery thrpt 3 47.185 ± 23.353 ops/ms
JMHMain.flexSelectById thrpt 3 69.381 ± 25.800 ops/ms
JMHMain.jdbcExecuteJdbc thrpt 3 631.485 ± 291.711 ops/ms
JMHMain.jdbcGetAll thrpt 3 39.693 ± 7.647 ops/ms
JMHMain.jdbcInsert thrpt 3 221.847 ± 1171.190 ops/ms
JMHMain.jdbcSelectById thrpt 3 672.000 ± 120.232 ops/ms
JMHMain.jpaExecuteJdbc thrpt 3 65.684 ± 45.030 ops/ms
JMHMain.jpaExecuteTemplate thrpt 3 70.961 ± 17.808 ops/ms
JMHMain.jpaGetAll thrpt 3 5.189 ± 3.821 ops/ms
JMHMain.jpaInsert thrpt 3 65.872 ± 46.345 ops/ms
JMHMain.jpaOne2Many thrpt 3 105.237 ± 41.245 ops/ms
JMHMain.jpaPageQuery thrpt 3 63.929 ± 31.189 ops/ms
JMHMain.jpaSelectById thrpt 3 346.690 ± 147.312 ops/ms
JMHMain.mybatisComplexMapping thrpt 3 111.347 ± 64.790 ops/ms
JMHMain.mybatisExecuteTemplate thrpt 3 44.240 ± 16.532 ops/ms
JMHMain.mybatisFile thrpt 3 41.701 ± 10.344 ops/ms
JMHMain.mybatisGetAll thrpt 3 4.869 ± 1.667 ops/ms
JMHMain.mybatisInsert thrpt 3 44.899 ± 23.818 ops/ms
JMHMain.mybatisLambdaQuery thrpt 3 8.825 ± 6.710 ops/ms
JMHMain.mybatisPageQuery thrpt 3 17.464 ± 8.727 ops/ms
JMHMain.mybatisSelectById thrpt 3 44.989 ± 14.594 ops/ms
JMHMain.woodExecuteJdbc thrpt 3 127.590 ± 54.041 ops/ms
JMHMain.woodExecuteTemplate thrpt 3 89.247 ± 715.286 ops/ms
JMHMain.woodFile thrpt 3 124.654 ± 52.517 ops/ms
JMHMain.woodGetAll thrpt 3 1.850 ± 1.018 ops/ms
JMHMain.woodInsert thrpt 3 97.668 ± 95.395 ops/ms
JMHMain.woodLambdaQuery thrpt 3 124.571 ± 42.021 ops/ms
JMHMain.woodPageQuery thrpt 3 227.678 ± 142.983 ops/ms
JMHMain.woodSelectById thrpt 3 122.248 ± 69.065 ops/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
