内容简介:牺牲了一年工作外业余时间的的构思和开发,BeetlSQL终于迎来了新的开始,BeetlSQL3正式发布。BeetlSQL3不仅仅类似MyBatis和JPA的综合,更大的目标是作为数据系统访问框架而非工具。对标Spring Data。 <depende...
牺牲了一年工作外业余时间的的构思和开发,BeetlSQL终于迎来了新的开始,BeetlSQL3正式发布。BeetlSQL3不仅仅类似MyBatis和JPA的综合,更大的目标是作为数据系统访问框架而非工具。对标Spring Data。
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetlsql</artifactId>
<version>3.0.0-RELEASE</version>
</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
BeetlSQL3 性能测试
测试维度是ops/ms,每毫秒的调用次数
Benchmark Mode Cnt Score Error Units
JMHMain.beetlsqlComplexMapping thrpt 5 212.378 ± 26.222 ops/ms
JMHMain.beetlsqlExecuteJdbc thrpt 5 428.713 ± 66.192 ops/ms
JMHMain.beetlsqlExecuteTemplate thrpt 5 374.943 ± 20.214 ops/ms
JMHMain.beetlsqlFile thrpt 5 433.001 ± 65.448 ops/ms
JMHMain.beetlsqlInsert thrpt 5 236.244 ± 112.102 ops/ms
JMHMain.beetlsqlLambdaQuery thrpt 5 247.289 ± 19.310 ops/ms
JMHMain.beetlsqlOne2Many thrpt 5 108.132 ± 10.934 ops/ms
JMHMain.beetlsqlPageQuery thrpt 5 203.751 ± 9.395 ops/ms
JMHMain.beetlsqlSelectById thrpt 5 393.437 ± 15.685 ops/ms
JMHMain.jdbcExecuteJdbc thrpt 5 1083.310 ± 80.947 ops/ms
JMHMain.jdbcInsert thrpt 5 308.341 ± 231.163 ops/ms
JMHMain.jdbcSelectById thrpt 5 1019.370 ± 92.946 ops/ms
JMHMain.jpaExecuteJdbc thrpt 5 94.600 ± 15.624 ops/ms
JMHMain.jpaExecuteTemplate thrpt 5 133.017 ± 12.954 ops/ms
JMHMain.jpaInsert thrpt 5 81.232 ± 26.971 ops/ms
JMHMain.jpaOne2Many thrpt 5 101.506 ± 11.301 ops/ms
JMHMain.jpaPageQuery thrpt 5 117.748 ± 4.512 ops/ms
JMHMain.jpaSelectById thrpt 5 335.945 ± 27.186 ops/ms
JMHMain.mybatisComplexMapping thrpt 5 102.402 ± 11.129 ops/ms
JMHMain.mybatisExecuteTemplate thrpt 5 202.619 ± 16.978 ops/ms
JMHMain.mybatisFile thrpt 5 151.151 ± 4.251 ops/ms
JMHMain.mybatisInsert thrpt 5 141.469 ± 43.092 ops/ms
JMHMain.mybatisLambdaQuery thrpt 5 15.558 ± 1.481 ops/ms
JMHMain.mybatisPageQuery thrpt 5 63.705 ± 7.592 ops/ms
JMHMain.mybatisSelectById thrpt 5 197.130 ± 19.461 ops/ms
JMHMain.weedExecuteJdbc thrpt 5 416.941 ± 22.256 ops/ms
JMHMain.weedExecuteTemplate thrpt 5 439.266 ± 57.130 ops/ms
JMHMain.weedFile thrpt 5 477.561 ± 37.926 ops/ms
JMHMain.weedInsert thrpt 5 231.444 ± 92.598 ops/ms
JMHMain.weedLambdaQuery thrpt 5 422.707 ± 64.716 ops/ms
JMHMain.weedPageQuery thrpt 5 246.018 ± 18.724 ops/ms
JMHMain.weedSelectById thrpt 5 380.348 ± 20.968 ops/ms
代码示例
例子1,内置方法,无需写 SQL 完成常用操作
UserEntity user = sqlManager.unique(UserEntity.class,1);
user.setName("ok123");
sqlManager.updateById(user);
UserEntity newUser = new UserEntity();
newUser.setName("newUser");
newUser.setDepartmentId(1);
sqlManager.insert(newUser);
输出日志友好,可反向定位到调用的代码
┏━━━━━ Debug [user.selectUserAndDepartment] ━━━
┣ SQL: select * from user where 1 = 1 and id=?
┣ 参数: [1]
┣ 位置: org.beetl.sql.test.QuickTest.main(QuickTest.java:47)
┣ 时间: 23ms
┣ 结果: [1]
┗━━━━━ Debug [user.selectUserAndDepartment] ━━━
例子2 使用SQL
String sql = "select * from user where id=?";
Integer id = 1;
SQLReady sqlReady = new SQLReady(sql,new Object[id]);
List<UserEntity> userEntities = sqlManager.execute(sqlReady,UserEntity.class);
//Map 也可以作为输入输出参数
List<Map> listMap = sqlManager.execute(sqlReady,Map.class);
例子3 使用模板SQL
String sql = "select * from user where department_id=#{id} and name=#{name}";
UserEntity paras = new UserEntity();
paras.setDepartmentId(1);
paras.setName("lijz");
List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);
String sql = "select * from user where id in ( #{join(ids)} )";
List list = Arrays.asList(1,2,3,4,5); Map paras = new HashMap();
paras.put("ids", list);
List<UserEntity> users = sqlManager.execute(sql, UserEntity.class, paras);
例子4 使用Query类
支持重构
LambdaQuery<UserEntity> query = sqlManager.lambdaQuery(UserEntity.class);
List<UserEntity> entities = query.andEq(UserEntity::getDepartmentId,1)
.andIsNotNull(UserEntity::getName).select();
例子5 把数十行SQL放到sql文件里维护
//访问user.md#select
SqlId id = SqlId.of("user","select");
Map map = new HashMap();
map.put("name","n");
List<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来作为数据库访问类
@SqlResource("user") /*sql文件在user.md里*/
public interface UserMapper extends BaseMapper<UserEntity> {
@Sql("select * from user where id = ?")
UserEntity queryUserById(Integer id);
@Sql("update user set name=? where id = ?")
@Update
int updateName(String name,Integer id);
@Template("select * from user where id = #{id}")
UserEntity getUserById(Integer id);
@SpringData/*Spring Data风格*/
List<UserEntity> queryByNameOrderById(String name);
/**
* 可以定义一个default接口
* @return
*/
default List<DepartmentEntity> findAllDepartment(){
Map paras = new HashMap();
paras.put("exlcudeId",1);
List<DepartmentEntity> list = getSQLManager().execute("select * from department where id != #{exlcudeId}",DepartmentEntity.class,paras);
return list;
}
/**
* 调用sql文件user.md#select,方法名即markdown片段名字
* @param name
* @return
*/
List<UserEntity> select(String name);
/**
* 翻页查询,调用user.md#pageQuery
* @param deptId
* @param pageRequest
* @return
*/
PageResult<UserEntity> pageQuery(Integer deptId, PageRequest pageRequest);
@SqlProvider(provider= S01MapperSelectSample.SelectUserProvider.class)
List<UserEntity> queryUserByCondition(String name);
@SqlTemplateProvider(provider= S01MapperSelectSample.SelectUs
List<UserEntity> queryUserByTemplateCondition(String name);
@Matcher /*自己定义个Matcher注解也很容易*/
List<UserEntity> query(Condition condition,String name);
}
你看到的这些用在Mapper上注解都是可以自定义,自己扩展的
例子8 使用Fetch 注解
可以在查询后根据Fetch注解再次获取相关对象,实际上@FetchOne和 @FetchMany是自定义的,用户可自行扩展
@Data
@Table(name="user")
@Fetch
public static class UserData {
@Auto
private Integer id;
private String name;
private Integer departmentId;
@FetchOne("departmentId")
private DepartmentData dept;
}
/**
* 部门数据使用"b" sqlmanager
*/
@Data
@Table(name="department")
@Fetch
public static class DepartmentData {
@Auto
private Integer id;
private String name;
@FetchMany("departmentId")
private List<UserData> users;
}
例子9 不同数据库切换
可以自行扩展ConditionalSQLManager的decide方法,来决定使用哪个SQLManager
SQLManager a = SampleHelper.init();
SQLManager b = SampleHelper.init();
Map<String, SQLManager> map = new HashMap<>();
map.put("a", a);
map.put("b", b);
SQLManager sqlManager = new ConditionalSQLManager(a, map);
//不同对象,用不同sqlManager操作,存入不同的数据库
UserData user = new UserData();
user.setName("hello");
user.setDepartmentId(2);
sqlManager.insert(user);
DepartmentData dept = new DepartmentData();
dept.setName("dept");
sqlManager.insert(dept);
使用注解 @TargetSQLManager来决定使用哪个SQLManger
@Data
@Table(name = "department")
@TargetSQLManager("b")
public static class DepartmentData {
@Auto
private Integer id;
private String name;
}
例子10 如果想给每个sql语句增加一个sqlId标识
这样好处是方便数据库DBA与 程序员 沟通
public static class SqlIdAppendInterceptor implements Interceptor{
@Override
public void before(InterceptorContext ctx) {
ExecuteContext context = ctx.getExecuteContext();
String jdbcSql = context.sqlResult.jdbcSql;
String info = context.sqlId.toString();
//为发送到数据库的sql增加一个注释说明,方便数据库dba能与开发人员沟通
jdbcSql = "/*"+info+"*/\n"+jdbcSql;
context.sqlResult.jdbcSql = jdbcSql;
}
}
例子11 代码生成框架
可以使用内置的代码生成框架生成代码何文档,也可以自定义的,用户可自行扩展SourceBuilder类
List<SourceBuilder> sourceBuilder = new ArrayList<>();
SourceBuilder entityBuilder = new EntitySourceBuilder();
SourceBuilder mapperBuilder = new MapperSourceBuilder();
SourceBuilder mdBuilder = new MDSourceBuilder();
//数据库markdown文档
SourceBuilder docBuilder = new MDDocBuilder();
sourceBuilder.add(entityBuilder);
sourceBuilder.add(mapperBuilder);
sourceBuilder.add(mdBuilder);
sourceBuilder.add(docBuilder);
SourceConfig config = new SourceConfig(sqlManager,sourceBuilder);
//只输出到控制台
ConsoleOnlyProject project = new ConsoleOnlyProject();
String tableName = "USER";
config.gen(tableName,project);
例子13 定义一个Beetl函数
GroupTemplate groupTemplate = groupTemplate();
groupTemplate.registerFunction("nextDay",new NextDayFunction());
Map map = new HashMap();
map.put("date",new Date());
String sql = "select * from user where create_time is not null and create_time<#{nextDay(date)}";
List<UserEntity> count = sqlManager.execute(sql,UserEntity.class,map);
nextDay函数是一个Beetl函数,非常容易定义,非常容易在sql模板语句里使用
public static class NextDayFunction implements Function {
@Override
public Object call(Object[] paras, Context ctx) {
Date date = (Date) paras[0];
Calendar c = Calendar.getInstance();
c.setTime(date);
c.add(Calendar.DAY_OF_YEAR, 1); // 今天+1天
return c.getTime();
}
}
例子14 更多可扩展的例子
根据ID或者上下文自动分表,toTable是定义的一个Beetl函数,
static final String USER_TABLE="${toTable('user',id)}";
@Data
@Table(name = USER_TABLE)
public static class MyUser {
@AssignID
private Integer id;
private String name;
}
定义一个Jackson注解,@Builder是注解的注解,表示用Builder指示的类来解释执行,可以看到BeetlSQL的注解可扩展性就是来源于@Build注解
@Retention(RetentionPolicy.RUNTIME)
@Target(value = {ElementType.METHOD, ElementType.FIELD})
@Builder(JacksonConvert.class)
public @interface Jackson {
}
定义一个@Tenant 放在POJO上,BeetlSQL执行时候会给SQL添加额外参数,这里同样使用了@Build注解
/**
* 组合注解,给相关操作添加额外的租户信息,从而实现根据租户分表或者分库
*/
@Retention(RetentionPolicy.RUNTIM@
@Target(value = {ElementType.TYPE})
@Builder(TenantContext.class)
public @interface Tenant {
}
使用XML而不是JSON作为映射
@Retention(RetentionPolicy.RUNTIME)
@Target(value = {ElementType.TYPE})
@Builder(ProviderConfig.class)
public @interface XmlMapping {
String path() default "";
}
参考源码例子 PluginAnnotationSample了解如何定义自定的注解,实际上BeetlSQL有一半的注解都是通过核心注解扩展出来的
BeetlSQL的架构
除了SQLManager和ClassAnnoations,任何一部分都可以扩展
以上所述就是小编给大家介绍的《BeetlSQL 3.0.0 正式发布,历经一年开发重构》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:- 历经数月投诉后,Firefox 移除了带误导性的按钮
- 历经 35 个版本迭代,NG-ZORRO 1.0.0 发布
- 历经11个测试版,苹果 macOS Mojave 正式版发布
- IMI v0.0.8 历经两周实战,做了 30 多项改进
- 历经27年,人脸识别技术如何发展至识别精度达99.5%以上
- 历经15个小时 终于评出这8本最受欢迎的SQL书籍
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Programming Python
Mark Lutz / O'Reilly Media / 2006-8-30 / USD 59.99
Already the industry standard for Python users, "Programming Python" from O'Reilly just got even better. This third edition has been updated to reflect current best practices and the abundance of chan......一起来看看 《Programming Python》 这本书的介绍吧!