The goal of the BeetlSQL featured in this issue is to provide a database access framework that is efficient to develop, maintain, and run, providing a consistent way of writing code in the case of multiple libraries in a system.

Project Description

• Traditional database: MySQL (compatible with MySQL agreement all kinds of big domestic database), MariaDB, Oralce, Postgres, DB2, SQL Server, H2, SQLite, Derby, avatar, dream, gauss, huawei Jin Cang of National People’s Congress, PolarDB < / li >
• Big data: HBase, ClickHouse, Cassandar, Hive
• iot timing database: Machbase, TD-Engine, IotDB
• SQL query engine :Drill,Presto, Druid
• Memory database :ignite, CouchBase

BeetlSQL is not simply similar to MyBatis or Hibernate, or a combination of both. BeetlSQL’s ambition is to benchmark and even surpass Spring Data as a unified framework for Data access, regardless of traditional databases. Or big data, or query engines or timing libraries, in-memory databases.

BeetlSQL structure

BeetlSQL dependence

< dependency> 
    <groupId>com.ibeetl</groupId>
    <artifactId>beetlsql</artifactId>
    <version>3.${version}</version>
</dependency>

Old version dependent

<dependency>
    <groupId>com.ibeetl</groupId>
    <artifactId>beetlsql</artifactId>
    <version>2.13.3.RELEASE</version>
</dependency>

Code example

< Example 1, built-in method, no need to write SQL to complete common operations

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);

< Example 2 Using 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 can also be used as an input/output parameter 
List< Map>  listMap =  sqlManager.execute(sqlReady,Map.class); 

< Example 3 Using template 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);

Example 4 Using the Query class

LambdaQuery< UserEntity>  query = sqlManager.lambdaQuery(UserEntity.class);
List< UserEntity>  entities = query.andEq(UserEntity::getDepartmentId,1)
                    .andIsNotNull(UserEntity::getName).select();

< Example 6 Complex mapping support

• Automatic mapping

@Data
@ResultProvider(AutoJsonMapper.class)
 public static class MyUserView {
        Integer id;
        String name;
        DepartmentEntity dept;
 }

• Configure mappings, easier to understand than MyBatis, more detailed error messages

{
	"id": "id",
	"name": "name",
	"dept": {
		"id": "dept_id",
		"name": "dept_name"
	},
	"roles": {
		"id": "r_id",
		"name": "r_name"
	}
}

Example 7 It is best to use mapper as the database access class

@SqlResource("user") /*sql file in 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 DataStyle */
    List<UserEntity> queryByNameOrderById(String name);

    /**
     * You can define a default interface
     * @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;
    }


    /**
     *The sql file user.m #select is called, and the method name is the markdown fragment name
     * @param name
     * @return
     */
     List<UserEntity> select(String name);


    /**
     *To turn the page, call user.m #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 /* It's easy to define your own Matcher annotation */
    List<UserEntity> query(Condition condition,String name);
}

Example 11 Code generation framework

Code documentation can be generated using the built-in code generation framework, or custom, users can extend the SourceBuilder class

	List<SourceBuilder> sourceBuilder = new ArrayList<>();
	SourceBuilder entityBuilder = new EntitySourceBuilder();
	SourceBuilder mapperBuilder = new MapperSourceBuilder();
	SourceBuilder mdBuilder = new MDSourceBuilder();
	// Database markdown document 
	SourceBuilder docBuilder = new MDDocBuilder();

	sourceBuilder.add(entityBuilder);
	sourceBuilder.add(mapperBuilder);
	sourceBuilder.add(mdBuilder);
	sourceBuilder.add(docBuilder);
    SourceConfig config = new SourceConfig(sqlManager,sourceBuilder);
   //Output to console only 
	ConsoleOnlyProject project = new ConsoleOnlyProject();
	String tableName = "USER";
	config.gen(tableName,project);