BeetlSQL 3.23.11 发布，SQL 查询性能提高 40%~300%

• 性能优化:SQL查询后结果映射到Java Entity，性能至少提供40%，有些场景提高300%
• 更好的支持动态表，向操作普通POJO一样操作动态表,比使用Map更方便
• 代码生成支持指定父类
• SQ防火墙的性能部分优化

性能优化结果:

查询	优化前（ops/ms)	优化后(ops/ms)	mybatis
beetlsqlExecuteJdbc	222	318	/
beetlsqlExecuteTemplate	198	268	44
beetlsqlFile	191	266	41
beetlsqlGetAll	5	13	4
beetlsqlLambdaQuery	150	196	9
beetlsqlPageQuery	128	159	17
beetlsqlSelectById	186	259	43

性能优化办法:

优化了Java反射调用，使用生成字节码直接调用，参考BeanASMPerfTest.java, myasm 性能非常接近手写的代码（直接调用）,远远高于反射调用，也高于使用其他字节码增强工具reflectAsm

  * BeanASMPerfTest.direct(直接调用) thrpt 3 3597282.120 ± 2031070.301 ops/ms * BeanASMPerfTest.myasm (字节码调用) thrpt 3 1889599.007 ± 1013919.850 ops/ms * BeanASMPerfTest.propertySet(反射调用) thrpt 3 3462.095 ± 2748.584 ops/ms * BeanASMPerfTest.reflectAsm(字节码工具) thrpt 3 18580.151 ± 13286.590 ops/ms

动态表支持：方便的通过BeetlSQL访问动态表

  SQLManager sqlManager = getSQLManager(); int max = 5; //创建5个表 for(int i=0;i<max;i++){ String dml="create table my_table"+i+"(id int NOT NULL" + ",name varchar(20)" + ",PRIMARY KEY (`id`)" + ") "; sqlManager.executeUpdate(new SQLReady(dml)); } // 配置加载类，使用默认的BassEntity，也可以使用任何类作为父类,比如MyOfficeEntity DynamicEntityLoader<BaseEntity> dynamicEntityLoader = new DynamicEntityLoader(sqlManager); for(int i=0;i<max;i++){ //Class<? extends MyOfficeEntity> c = dynamicEntityLoader.getDynamicEntity("my_table"+i,MyOfficeEntity.class); Class<? extends BaseEntity> c = dynamicEntityLoader.getDynamicEntity("my_table"+i); long count = sqlManager.allCount(c); System.out.println(count); } for(int i=0;i<max;i++){ Class<? extends BaseEntity> c = dynamicEntityLoader.getDynamicEntity("my_table"+i); BaseEntity obj = c.newInstance(); obj.setValue("id",1); obj.setValue("name","hello"); sqlManager.insert(obj); } 

 

性能增速扩展包

 <dependency> <groupId>com.ibeetl</groupId> <artifactId>sql-accelerator</artifactId> <version>3.23.11-RELEASE</version> </dependency>

动态表支持扩展包

 <dependency> <groupId>com.ibeetl</groupId> <artifactId>sql-dynamic-table</artifactId> <version>3.23.11-RELEASE</version> </dependency>

SQL防火墙扩展包

 <dependency> <groupId>com.ibeetl</groupId> <artifactId>sql-firewall</artifactId> <version>3.23.11-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

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

 Benchmark Mode Cnt Score Error Units JMHMain.beetlsqlComplexMapping thrpt 5 240.875 ± 26.462 ops/ms JMHMain.beetlsqlExecuteJdbc thrpt 5 318.344 ± 15.416 ops/ms JMHMain.beetlsqlExecuteTemplate thrpt 5 268.337 ± 16.458 ops/ms JMHMain.beetlsqlFile thrpt 5 266.299 ± 12.910 ops/ms JMHMain.beetlsqlGetAll thrpt 5 13.444 ± 0.110 ops/ms JMHMain.beetlsqlInsert thrpt 5 139.848 ± 67.006 ops/ms JMHMain.beetlsqlLambdaQuery thrpt 5 196.197 ± 10.572 ops/ms JMHMain.beetlsqlOne2Many thrpt 5 162.912 ± 7.643 ops/ms JMHMain.beetlsqlPageQuery thrpt 5 159.421 ± 9.474 ops/ms JMHMain.beetlsqlSelectById thrpt 5 259.260 ± 12.983 ops/ms JMHMain.easyQueryComplexMapping thrpt 5 70.631 ± 9.350 ops/ms JMHMain.easyQueryExecuteJdbc thrpt 5 251.553 ± 13.124 ops/ms JMHMain.easyQueryGetAll thrpt 5 15.556 ± 0.919 ops/ms JMHMain.easyQueryInsert thrpt 5 94.289 ± 46.075 ops/ms JMHMain.easyQueryLambdaQuery thrpt 5 116.482 ± 6.220 ops/ms JMHMain.easyQueryOne2Many thrpt 5 93.552 ± 7.485 ops/ms JMHMain.easyQueryPageQuery thrpt 5 79.880 ± 4.101 ops/ms JMHMain.easyQuerySelectById thrpt 5 118.695 ± 5.622 ops/ms JMHMain.flexGetAll thrpt 5 2.629 ± 0.175 ops/ms JMHMain.flexInsert thrpt 5 69.733 ± 9.791 ops/ms JMHMain.flexPageQuery thrpt 5 44.935 ± 13.077 ops/ms JMHMain.flexSelectById thrpt 5 69.332 ± 4.738 ops/ms JMHMain.jdbcExecuteJdbc thrpt 5 692.908 ± 29.405 ops/ms JMHMain.jdbcGetAll thrpt 5 41.017 ± 0.999 ops/ms JMHMain.jdbcInsert thrpt 5 235.220 ± 170.271 ops/ms JMHMain.jdbcSelectById thrpt 5 685.740 ± 21.861 ops/ms JMHMain.jpaExecuteJdbc thrpt 5 67.159 ± 6.612 ops/ms JMHMain.jpaExecuteTemplate thrpt 5 75.465 ± 4.267 ops/ms JMHMain.jpaGetAll thrpt 5 5.279 ± 0.542 ops/ms JMHMain.jpaInsert thrpt 5 63.982 ± 10.347 ops/ms JMHMain.jpaOne2Many thrpt 5 105.669 ± 6.529 ops/ms JMHMain.jpaPageQuery thrpt 5 66.304 ± 4.215 ops/ms JMHMain.jpaSelectById thrpt 5 344.930 ± 15.419 ops/ms JMHMain.mybatisComplexMapping thrpt 5 114.651 ± 7.607 ops/ms JMHMain.mybatisExecuteTemplate thrpt 5 44.020 ± 2.327 ops/ms JMHMain.mybatisFile thrpt 5 41.459 ± 1.880 ops/ms JMHMain.mybatisGetAll thrpt 5 4.961 ± 0.197 ops/ms JMHMain.mybatisInsert thrpt 5 43.624 ± 5.580 ops/ms JMHMain.mybatisLambdaQuery thrpt 5 9.141 ± 1.295 ops/ms JMHMain.mybatisPageQuery thrpt 5 17.451 ± 1.409 ops/ms JMHMain.mybatisSelectById thrpt 5 43.716 ± 4.768 ops/ms JMHMain.woodExecuteJdbc thrpt 5 106.223 ± 88.136 ops/ms JMHMain.woodExecuteTemplate thrpt 5 120.959 ± 5.220 ops/ms JMHMain.woodFile thrpt 5 123.587 ± 9.268 ops/ms JMHMain.woodGetAll thrpt 5 1.961 ± 0.158 ops/ms JMHMain.woodInsert thrpt 5 101.279 ± 50.068 ops/ms JMHMain.woodLambdaQuery thrpt 5 128.158 ± 7.174 ops/ms JMHMain.woodPageQuery thrpt 5 240.657 ± 13.274 ops/ms JMHMain.woodSelectById thrpt 5 125.943 ± 5.696 ops/ms