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 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

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