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

阅读文档 源码和例子 在线体验 多库使用 性能测试