歡迎您光臨本站 註冊首頁

BeetlSQL 3.0.0-M2 新增效能測試

←手機掃碼閱讀     admin @ 2020-08-17 , reply:0

這次釋出,主要是對效能進行第一輪測試,以驗證從2升級到3後,效能沒有顯著下降。

  • 修復MarkdownClasspathLoader 每次都從sql檔案載入sql片段的bug,導致效能嚴重下降
  • 修復ConfigJoinMapper對映一對多時候不識別大寫列名的bug

效能測試主要測試了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 能最大程度提高開發資料庫訪問的效率和增強相關程式碼維護性

例子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 {
         UserInfo user;
         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);
 }
 

你看到的這些用在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([email protected]
 @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 ]

來源:OsChina
連結:https://www.oschina.net/news/117980/beetl-3-0-0-m2-released
BeetlSQL 3.0.0-M2 新增效能測試已經有27次圍觀

http://coctec.com/news/all/show-post-249423.html