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

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