正文

BeetlSQL 3.23.9 发布，SQL防火墙支持

admin
admin V管理员 /0 评论 /168 阅读
0807
此篇文章发布距今已超过460天，您需要注意文章的内容或图片是否可用！

本次调整把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

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

ZhouSa.com-周飒博客