這次釋出,主要是對效能進行第一輪測試,以驗證從2升級到3後,效能沒有顯著下降。
效能測試主要測試了BeetlSQL,MyBatis(plus),JPA(Hibernate),JDBC和 Weed3, BeetlSQL和Weed3都是國產DAO工具
Benchmark Mode Cnt Score Error Units
JMHMain.beetlsqlComplexMapping thrpt 5 195.364 ± 77.645 ops/ms
JMHMain.beetlsqlExecuteJdbc thrpt 5 394.119 ± 194.906 ops/ms
JMHMain.beetlsqlExecuteTemplate thrpt 5 381.499 ± 26.086 ops/ms
JMHMain.beetlsqlFile thrpt 5 447.060 ± 11.511 ops/ms
JMHMain.beetlsqlInsert thrpt 5 251.468 ± 130.649 ops/ms
JMHMain.beetlsqlLambdaQuery thrpt 5 264.216 ± 15.167 ops/ms
JMHMain.beetlsqlOne2Many thrpt 5 109.499 ± 14.781 ops/ms
JMHMain.beetlsqlPageQuery thrpt 5 209.418 ± 10.847 ops/ms
JMHMain.beetlsqlSelectById thrpt 5 382.884 ± 22.160 ops/ms
JMHMain.jdbcExecuteJdbc thrpt 5 1096.030 ± 37.110 ops/ms
JMHMain.jdbcInsert thrpt 5 331.819 ± 228.323 ops/ms
JMHMain.jdbcSelectById thrpt 5 1069.215 ± 81.210 ops/ms
JMHMain.jpaExecuteJdbc thrpt 5 109.956 ± 13.743 ops/ms
JMHMain.jpaExecuteTemplate thrpt 5 134.473 ± 11.563 ops/ms
JMHMain.jpaInsert thrpt 5 81.052 ± 14.006 ops/ms
JMHMain.jpaOne2Many thrpt 5 101.677 ± 13.461 ops/ms
JMHMain.jpaPageQuery thrpt 5 119.050 ± 7.998 ops/ms
JMHMain.jpaSelectById thrpt 5 324.978 ± 14.455 ops/ms
JMHMain.mybatisComplexMapping thrpt 5 96.171 ± 13.213 ops/ms
JMHMain.mybatisExecuteTemplate thrpt 5 192.065 ± 17.957 ops/ms
JMHMain.mybatisFile thrpt 5 136.911 ± 12.952 ops/ms
JMHMain.mybatisInsert thrpt 5 142.749 ± 34.862 ops/ms
JMHMain.mybatisLambdaQuery thrpt 5 14.581 ± 1.696 ops/ms
JMHMain.mybatisPageQuery thrpt 5 62.365 ± 9.497 ops/ms
JMHMain.mybatisSelectById thrpt 5 194.090 ± 46.959 ops/ms
JMHMain.weedExecuteJdbc thrpt 5 454.037 ± 37.330 ops/ms
JMHMain.weedExecuteTemplate thrpt 5 253.859 ± 21.164 ops/ms
JMHMain.weedFile thrpt 5 534.792 ± 48.711 ops/ms
JMHMain.weedInsert thrpt 5 233.368 ± 141.993 ops/ms
JMHMain.weedLambdaQuery thrpt 5 454.978 ± 46.672 ops/ms
JMHMain.weedPageQuery thrpt 5 237.196 ± 33.993 ops/ms
JMHMain.weedSelectById thrpt 5 420.247 ± 28.087 ops/ms
從測試結果看,BeetlSQL效能非常好,至少是JPA和Mybatis的2倍以上,且滿足所有的9種測試場景
maven
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetlsql-all</artifactId>
<version>3.0.0-M2</version>
</dependency>
BeetlSQL是一款資料庫訪問工具庫,廣泛應用到企業應用,網際網路專案。相比於BeetlSQL2,新版支援更多基於JDBC的資料來源,包括支傳統資料庫,大資料NOSQL,以及大資料SQL查詢引擎,在易用性何擴充套件性也做了大幅度修改。BeetlSQL3 能最大程度提高開發資料庫訪問的效率和增強相關程式碼維護性
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] ━━━
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);
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);
支援重構
LambdaQuery<UserEntity> query = sqlManager.lambdaQuery(UserEntity.class);
List<UserEntity> entities = query.andEq(UserEntity::getDepartmentId,1)
.andIsNotNull(UserEntity::getName).select();
//訪問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);
支援像mybatis那樣複雜的對映
@Data
@ResultProvider(AutoJsonMapper.class)
public static class MyUserView {
UserInfo user;
DepartmentEntity dept;
}
{
"id": "id",
"name": "name",
"dept": {
"id": "dept_id",
"name": "dept_name"
},
"roles": {
"id": "r_id",
"name": "r_name"
}
}
@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);
}
你看到的這些用在Mapper上註解都是可以自定義,自己擴充套件的
可以在查詢後根據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;
}
可以自行擴充套件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;
}
這樣好處是方便資料庫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;
}
}
可以使用內建的程式碼生成框架生成程式碼何文件,也可以自定義的,使用者可自行擴充套件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);
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();
}
}
根據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的架構如下,歡迎參與到BeetlSQL3的生態開發
[admin
]