牺牲了一年工作外业余时间的的构思和开发，BeetlSQL终于迎来了新的开始，BeetlSQL3正式发布。BeetlSQL3不仅仅类似MyBatis和JPA的综合，更大的目标是作为数据系统访问框架而非工具。对标Spring Data。
1<dependency> 2 <groupId>com.ibeetl</groupId> 3 <artifactId>beetlsql</artifactId> 4 <version>3.0.0-RELEASE</version> 5</dependency>
BeetlSQL的目标是提供开发高效，维护高效，运行高效的数据库访问框架，在一个系统多个库的情况下，提供一致的编写代码方式。支持如下数据平台
- 传统数据库：MySQL,MariaDB,Oralce,Postgres,DB2,SQL Server，H2,SQLite,Derby，神通，达梦，华为高斯，人大金仓，PolarDB等
- 大数据：HBase，ClickHouse，Cassandar，Hive
- 物联网时序数据库：Machbase，TD-Engine，IotDB
- SQL查询引擎:Drill,Presto，Druid
- 内存数据库:ignite，CouchBase
阅读文档 源码和例子 QQ群:219324263,636321496
BeetlSQL3 性能测试
测试维度是ops/ms，每毫秒的调用次数
1Benchmark Mode Cnt Score Error Units 2JMHMain.beetlsqlComplexMapping thrpt 5 212.378 ± 26.222 ops/ms 3JMHMain.beetlsqlExecuteJdbc thrpt 5 428.713 ± 66.192 ops/ms 4JMHMain.beetlsqlExecuteTemplate thrpt 5 374.943 ± 20.214 ops/ms 5JMHMain.beetlsqlFile thrpt 5 433.001 ± 65.448 ops/ms 6JMHMain.beetlsqlInsert thrpt 5 236.244 ± 112.102 ops/ms 7JMHMain.beetlsqlLambdaQuery thrpt 5 247.289 ± 19.310 ops/ms 8JMHMain.beetlsqlOne2Many thrpt 5 108.132 ± 10.934 ops/ms 9JMHMain.beetlsqlPageQuery thrpt 5 203.751 ± 9.395 ops/ms 10JMHMain.beetlsqlSelectById thrpt 5 393.437 ± 15.685 ops/ms 11JMHMain.jdbcExecuteJdbc thrpt 5 1083.310 ± 80.947 ops/ms 12JMHMain.jdbcInsert thrpt 5 308.341 ± 231.163 ops/ms 13JMHMain.jdbcSelectById thrpt 5 1019.370 ± 92.946 ops/ms 14JMHMain.jpaExecuteJdbc thrpt 5 94.600 ± 15.624 ops/ms 15JMHMain.jpaExecuteTemplate thrpt 5 133.017 ± 12.954 ops/ms 16JMHMain.jpaInsert thrpt 5 81.232 ± 26.971 ops/ms 17JMHMain.jpaOne2Many thrpt 5 101.506 ± 11.301 ops/ms 18JMHMain.jpaPageQuery thrpt 5 117.748 ± 4.512 ops/ms 19JMHMain.jpaSelectById thrpt 5 335.945 ± 27.186 ops/ms 20JMHMain.mybatisComplexMapping thrpt 5 102.402 ± 11.129 ops/ms 21JMHMain.mybatisExecuteTemplate thrpt 5 202.619 ± 16.978 ops/ms 22JMHMain.mybatisFile thrpt 5 151.151 ± 4.251 ops/ms 23JMHMain.mybatisInsert thrpt 5 141.469 ± 43.092 ops/ms 24JMHMain.mybatisLambdaQuery thrpt 5 15.558 ± 1.481 ops/ms 25JMHMain.mybatisPageQuery thrpt 5 63.705 ± 7.592 ops/ms 26JMHMain.mybatisSelectById thrpt 5 197.130 ± 19.461 ops/ms 27JMHMain.weedExecuteJdbc thrpt 5 416.941 ± 22.256 ops/ms 28JMHMain.weedExecuteTemplate thrpt 5 439.266 ± 57.130 ops/ms 29JMHMain.weedFile thrpt 5 477.561 ± 37.926 ops/ms 30JMHMain.weedInsert thrpt 5 231.444 ± 92.598 ops/ms 31JMHMain.weedLambdaQuery thrpt 5 422.707 ± 64.716 ops/ms 32JMHMain.weedPageQuery thrpt 5 246.018 ± 18.724 ops/ms 33JMHMain.weedSelectById thrpt 5 380.348 ± 20.968 ops/ms
代码示例
例子1,内置方法，无需写SQL完成常用操作
1UserEntity user = sqlManager.unique(UserEntity.class,1); 2 3user.setName("ok123"); 4sqlManager.updateById(user); 5 6UserEntity newUser = new UserEntity(); 7newUser.setName("newUser"); 8newUser.setDepartmentId(1); 9sqlManager.insert(newUser); 10
输出日志友好,可反向定位到调用的代码
1┏━━━━━ Debug [user.selectUserAndDepartment] ━━━ 2┣ SQL： select * from user where 1 = 1 and id=? 3┣ 参数： [1] 4┣ 位置： org.beetl.sql.test.QuickTest.main(QuickTest.java:47) 5┣ 时间： 23ms 6┣ 结果： [1] 7┗━━━━━ Debug [user.selectUserAndDepartment] ━━━
例子2 使用SQL
1String sql = "select * from user where id=?"; 2Integer id = 1; 3SQLReady sqlReady = new SQLReady(sql,new Object[id]); 4List<UserEntity> userEntities = sqlManager.execute(sqlReady,UserEntity.class); 5//Map 也可以作为输入输出参数 6List<Map> listMap = sqlManager.execute(sqlReady,Map.class);
例子3 使用模板SQL
1String sql = "select * from user where department_id=#{id} and name=#{name}"; 2UserEntity paras = new UserEntity(); 3paras.setDepartmentId(1); 4paras.setName("lijz"); 5List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras); 6 7String sql = "select * from user where id in ( #{join(ids)} )"; 8List list = Arrays.asList(1,2,3,4,5); Map paras = new HashMap(); 9paras.put("ids", list); 10List<UserEntity> users = sqlManager.execute(sql, UserEntity.class, paras);
例子4 使用Query类
支持重构
1LambdaQuery<UserEntity> query = sqlManager.lambdaQuery(UserEntity.class); 2List<UserEntity> entities = query.andEq(UserEntity::getDepartmentId,1) 3 .andIsNotNull(UserEntity::getName).select(); 4
例子5 把数十行SQL放到sql文件里维护
1//访问user.md#select 2SqlId id = SqlId.of("user","select"); 3Map map = new HashMap(); 4map.put("name","n"); 5List<UserEntity> list = sqlManager.select(id,UserEntity.class,map);
例子6 复杂映射支持
支持像mybatis那样复杂的映射
-
自动映射
@Data @ResultProvider(AutoJsonMapper.class) public static class MyUserView { Integer id; String name; DepartmentEntity dept; }
-
配置映射，比MyBatis更容易理解，报错信息更详细
{ "id": "id", "name": "name", "dept": { "id": "dept_id", "name": "dept_name" }, "roles": { "id": "r_id", "name": "r_name" } }
例子7 最好使用mapper来作为数据库访问类
1@SqlResource("user") /*sql文件在user.md里*/ 2public interface UserMapper extends BaseMapper<UserEntity> { 3 4 @Sql("select * from user where id = ?") 5 UserEntity queryUserById(Integer id); 6 7 @Sql("update user set name=? where id = ?") 8 @Update 9 int updateName(String name,Integer id); 10 11 @Template("select * from user where id = #{id}") 12 UserEntity getUserById(Integer id); 13 14 @SpringData/*Spring Data风格*/ 15 List<UserEntity> queryByNameOrderById(String name); 16 17 /** 18 * 可以定义一个default接口 19 * @return 20 */ 21 default List<DepartmentEntity> findAllDepartment(){ 22 Map paras = new HashMap(); 23 paras.put("exlcudeId",1); 24 List<DepartmentEntity> list = getSQLManager().execute("select * from department where id != #{exlcudeId}",DepartmentEntity.class,paras); 25 return list; 26 } 27 28 29 /** 30 * 调用sql文件user.md#select,方法名即markdown片段名字 31 * @param name 32 * @return 33 */ 34 List<UserEntity> select(String name); 35 36 37 /** 38 * 翻页查询,调用user.md#pageQuery 39 * @param deptId 40 * @param pageRequest 41 * @return 42 */ 43 PageResult<UserEntity> pageQuery(Integer deptId, PageRequest pageRequest); 44 45 @SqlProvider(provider= S01MapperSelectSample.SelectUserProvider.class) 46 List<UserEntity> queryUserByCondition(String name); 47 48 @SqlTemplateProvider(provider= S01MapperSelectSample.SelectUs 49 List<UserEntity> queryUserByTemplateCondition(String name); 50 51 @Matcher /*自己定义个Matcher注解也很容易*/ 52 List<UserEntity> query(Condition condition,String name); 53}
你看到的这些用在Mapper上注解都是可以自定义，自己扩展的
例子8 使用Fetch 注解
可以在查询后根据Fetch注解再次获取相关对象，实际上@FetchOne和 @FetchMany是自定义的，用户可自行扩展
1 @Data 2 @Table(name="user") 3 @Fetch 4 public static class UserData { 5 @Auto 6 private Integer id; 7 private String name; 8 private Integer departmentId; 9 @FetchOne("departmentId") 10 private DepartmentData dept; 11 } 12 13 /** 14 * 部门数据使用"b" sqlmanager 15 */ 16 @Data 17 @Table(name="department") 18 @Fetch 19 public static class DepartmentData { 20 @Auto 21 private Integer id; 22 private String name; 23 @FetchMany("departmentId") 24 private List<UserData> users; 25 } 26
例子9 不同数据库切换
可以自行扩展ConditionalSQLManager的decide方法，来决定使用哪个SQLManager
1 SQLManager a = SampleHelper.init(); 2 SQLManager b = SampleHelper.init(); 3 Map<String, SQLManager> map = new HashMap<>(); 4 map.put("a", a); 5 map.put("b", b); 6 SQLManager sqlManager = new ConditionalSQLManager(a, map); 7 8 //不同对象，用不同sqlManager操作，存入不同的数据库 9 UserData user = new UserData(); 10 user.setName("hello"); 11 user.setDepartmentId(2); 12 sqlManager.insert(user); 13 14 DepartmentData dept = new DepartmentData(); 15 dept.setName("dept"); 16 sqlManager.insert(dept); 17
使用注解 @TargetSQLManager来决定使用哪个SQLManger
1 @Data 2 @Table(name = "department") 3 @TargetSQLManager("b") 4 public static class DepartmentData { 5 @Auto 6 private Integer id; 7 private String name; 8 }
例子10 如果想给每个sql语句增加一个sqlId标识
这样好处是方便数据库DBA与程序员沟通
1 public static class SqlIdAppendInterceptor implements Interceptor{ 2 @Override 3 public void before(InterceptorContext ctx) { 4 ExecuteContext context = ctx.getExecuteContext(); 5 String jdbcSql = context.sqlResult.jdbcSql; 6 String info = context.sqlId.toString(); 7 //为发送到数据库的sql增加一个注释说明，方便数据库dba能与开发人员沟通 8 jdbcSql = "/*"+info+"*/\n"+jdbcSql; 9 context.sqlResult.jdbcSql = jdbcSql; 10 } 11 }
例子11 代码生成框架
可以使用内置的代码生成框架生成代码何文档，也可以自定义的，用户可自行扩展SourceBuilder类
1 List<SourceBuilder> sourceBuilder = new ArrayList<>(); 2 SourceBuilder entityBuilder = new EntitySourceBuilder(); 3 SourceBuilder mapperBuilder = new MapperSourceBuilder(); 4 SourceBuilder mdBuilder = new MDSourceBuilder(); 5 //数据库markdown文档 6 SourceBuilder docBuilder = new MDDocBuilder(); 7 8 sourceBuilder.add(entityBuilder); 9 sourceBuilder.add(mapperBuilder); 10 sourceBuilder.add(mdBuilder); 11 sourceBuilder.add(docBuilder); 12 SourceConfig config = new SourceConfig(sqlManager,sourceBuilder); 13 //只输出到控制台 14 ConsoleOnlyProject project = new ConsoleOnlyProject(); 15 String tableName = "USER"; 16 config.gen(tableName,project);
例子13 定义一个Beetl函数
1 GroupTemplate groupTemplate = groupTemplate(); 2 groupTemplate.registerFunction("nextDay",new NextDayFunction()); 3 4 Map map = new HashMap(); 5 map.put("date",new Date()); 6 String sql = "select * from user where create_time is not null and create_time<#{nextDay(date)}"; 7 List<UserEntity> count = sqlManager.execute(sql,UserEntity.class,map);
nextDay函数是一个Beetl函数，非常容易定义,非常容易在sql模板语句里使用
1 public static class NextDayFunction implements Function { 2 3 @Override 4 public Object call(Object[] paras, Context ctx) { 5 Date date = (Date) paras[0]; 6 Calendar c = Calendar.getInstance(); 7 c.setTime(date); 8 c.add(Calendar.DAY_OF_YEAR, 1); // 今天+1天 9 return c.getTime(); 10 } 11 }
例子14 更多可扩展的例子
根据ID或者上下文自动分表，toTable是定义的一个Beetl函数，
1 static final String USER_TABLE="${toTable('user',id)}"; 2 @Data 3 @Table(name = USER_TABLE) 4 public static class MyUser { 5 @AssignID 6 private Integer id; 7 private String name; 8 }
定义一个Jackson注解，@Builder是注解的注解，表示用Builder指示的类来解释执行，可以看到BeetlSQL的注解可扩展性就是来源于@Build注解
1@Retention(RetentionPolicy.RUNTIME) 2@Target(value = {ElementType.METHOD, ElementType.FIELD}) 3@Builder(JacksonConvert.class) 4public @interface Jackson { 5 6}
定义一个@Tenant 放在POJO上，BeetlSQL执行时候会给SQL添加额外参数，这里同样使用了@Build注解
1/** 2 * 组合注解，给相关操作添加额外的租户信息，从而实现根据租户分表或者分库 3 */ 4@Retention(RetentionPolicy.RUNTIM@ 5@Target(value = {ElementType.TYPE}) 6@Builder(TenantContext.class) 7public @interface Tenant { 8 9}
使用XML而不是JSON作为映射
1@Retention(RetentionPolicy.RUNTIME) 2@Target(value = {ElementType.TYPE}) 3@Builder(ProviderConfig.class) 4public @interface XmlMapping { 5 String path() default ""; 6} 7
参考源码例子 PluginAnnotationSample了解如何定义自定的注解，实际上BeetlSQL有一半的注解都是通过核心注解扩展出来的
BeetlSQL的架构
除了SQLManager和ClassAnnoations，任何一部分都可以扩展