beetlsql 特点

BeetSql是一个全功能DAO工具， 同时具有Hibernate 优点 & Mybatis优点功能，适用于承认以SQL为中心，同时又需求工具能自动能生成大量常用的SQL的应用。

• 无需注解，自动生成大量内置SQL，轻易完成增删改查功能
• 数据模型支持Pojo，也支持Map/List这种快速模型，也支持混合模型
• SQL 以更简洁的方式，Markdown方式集中管理，同时方便程序开发和数据库SQL调试。
• SQL 模板基于Beetl实现，更容易写和调试，以及扩展
• 简单支持关系映射而不引入复杂的OR Mapping概念和技术。
• 具备Interceptor功能，可以调试，性能诊断SQL，以及扩展其他功能
• 内置支持主从数据库，通过扩展，可以支持更复杂的分库分表逻辑
• 支持跨数据库平台，开发者所需工作减少到最小

5 分钟例子

准备工作

为了快速尝试BeetlSQL，需要准备一个Mysql数据库，然后执行如下sql脚本

1DROP TABLE IF EXISTS `user`;
2CREATE TABLE `user` (
3  `id` int(11) NOT NULL,
4  `name` varchar(64) DEFAULT NULL,
5  `age` int(4) DEFAULT NULL,
6  `userName` varchar(64) DEFAULT NULL,
7  PRIMARY KEY (`id`)
8) ENGINE=InnoDB DEFAULT CHARSET=utf8;

编写一个Pojo类，与数据库表对应

1public class User {
2    Integer id;
3    String name;
4    Integer age;
5    public String getName() {
6        return name;
7    }
8
9    public void setName(String name) {
10        this.name = name;
11    }
12
13    public Integer getId() {
14        return id;
15    }
16
17    public void setId(Integer id) {
18        this.id = id;
19    }
20
21    public Integer getAge() {
22        return age;
23    }
24
25    public void setAge(Integer age) {
26        this.age = age;
27    }
28
29}

代码例子

写一个java的Main方法，内容如下

1// 创建一个简单的ConnectionSource，只有一个master
2ConnectionSource source = ConnectionSourceHelper.simple(driver,url,userName,password);
3// 采用mysql 习俗
4DBStyle mysql = new MysqlStyle();
5// sql语句放在classpagth的/sql 目录下
6SQLLoader loader = new ClasspathLoader("/sql"); 
7// 数据库命名跟java命名采用驼峰转化
8NameConversion nc = new  HumpNameConversion();
9// 最后，创建一个SQLManager
10SqlManager sqlManager = new SqlManager(source,mysql,loader); 
11
12//使用内置的生成的sql 新增用户
13User user = new User();
14user.setAge(19);
15user.setName("xiandafu");
16sqlManager.insert(user);
17//使用内置sql查询用户
18int id = 1;
19user = sqlManager.unque(User.class,id);
20
21//使用user.md 文件里的select语句，参考下一节
22User query = new User();
23query.setName("xiandafu");
24List<User> list = sqlManager.select("user.select",User.class,query)

SQL例子

为了能执行user.select,需要在classpath里建立一个user.md 文件，内容如下

1select
2===
3select * from user where 1=1
4@if(!isEmpty(age)){
5and age = #age#
6@}          
7@if(!isEmpty(name)){
8and name = #name#
9@}

关于如何写sql模板，会稍后章节说明，如下是一些简单说明。

• @ 和回车符号是定界符号，可以在里面写beetl语句。

• "#" 是站位符号，生成sql语句得时候，将输出？，如果你想输出表达式值，需要用text函数，或者任何以db开头的函数，引擎则认为是直接输出文本。

• isEmpty是beetl的一个函数，用来判断变量是否为空或者是否不存在.

sql模板采用beetl原因是因为beetl 语法类似js，且对模板渲染做了特定优化，相比于mybatis，更加容易掌握和功能强大。

BeetlSQL 说明

获得SQLManager

SQLManager 是系统的核心，他提供了所有的dao方法。获得SQLManager，可以直接构造SQLManager.并通过过单例获取如：

1ConnectionSource source = ConnectionSourceHelper.simple(driver,url,userName,password);  
2// 采用mysql 习俗
3DBStyle mysql = new MysqlStyle();
4// sql语句放在classpagth的/sql 目录下
5SQLLoader loader = new ClasspathLoader("/sql"); 
6// 数据库命名跟java命名采用驼峰转化
7NameConversion nc = new  HumpNameConversion();
8// 最后，创建一个SQLManager
9SqlManager sqlManager = new SqlManager(source,mysql,loader);

更常见的是，已经有了DataSource，创建ConnectionSource 可以采用如下代码

ConnectionSource source = ConnectionSourceHelper.single(datasource);

如果是主从Datasource

ConnectionSource source = ConnectionSourceHelper.getMasterSlave(master,slaves)

Spring集成

1<bean id="sqlManager" class="org.beetl.sql.ext.SpringBeetlSql">
2    <property name="cs" >
3        <bean  class="org.beetl.sql.ext.SpringConnectionSource">
4            <property name="master" ref="dataSource"></property>
5        </bean>
6    </property>
7    <property name="dbStyle">
8        <bean class="org.beetl.sql.core.db.MySqlStyle"> </bean>
9    </property>
10    <property name="sqlLoader">
11        <bean class="org.beetl.sql.core.ClasspathLoader"> 
12            <property name="sqlRoot" value="/sql"></property>
13        </bean>
14    </property>
15    <property name="nc">
16        <bean class="org.beetl.sql.core.HumpNameConversion">
17        </bean>
18    </property>
19    <property name="interceptors">
20        <list>
21            <bean class="org.beetl.sql.ext.DebugInterceptor"></bean>
22        </list>
23    </property>
24</bean>

• cs: 指定ConnectionSource，可以用系统提供的DefaultConnectionSource，支持按照CRUD决定主从。例子里只有一个master库

• dbStyle: 数据库类型，目前只支持org.beetl.sql.core.db.MySqlStyle

• sqlLoader: sql语句加载来源

• nc: 命名转化，有驼峰的HumpNameConversion，有数据库下划线的UnderlinedNameConversion

• interceptors:DebugInterceptor 用来打印sql语句，参数和执行时间

注意： 任何使用了Transactional 注解的，将统一使用Master数据源，例外的是@Transactional(readOnly=true),这将让Beetsql选择从数据库。

1public class MyServiceImpl implements MyService {
2
3    @Autowired
4    SpringBeetlSql beetlsql ;
5
6    @Override
7    @Transactional()
8    public int total(User user) {
9
10        SQLManager dao = beetlsql.getSQLMananger();
11        List<User> list = dao.all(User.class);
12        int total = list .size();
13        dao.deleteById(User.class, 3);
14        User u =new User();
15        u.id = 3;
16        u.name="hello";
17        u.age = 12;
18        dao.insert(User.class, u);
19
20        return total;
21
22    }
23
24}

可以参考demo https://git.oschina.net/xiandafu/springbeetlsql

JFinal集成

在configPlugin 里配置BeetlSql

JFinalBeetlSql.init();

默认会采用c3p0 作为数据源，其配置来源于jfinal 配置，如果你自己提供数据源或者主从，可以如下

JFinalBeetlSql.init(master,slaves);

由于使用了Beetlsql，因此你无需再配置 数据库连接池插件，和ActiveRecordPlugin,可以删除相关配置。

在controller里，可以通过JFinalBeetlSql.dao 方法获取到SQLManager

1SQLManager dao = JFinalBeetlSql.dao();
2    BigBlog blog = getModel(BigBlog.class);     
3    dao.insert(BigBlog.class, blog);

如果想控制事物，还需要注册Trans

1public void configInterceptor(Interceptors me) {
2        me.addGlobalActionInterceptor(new Trans());
3    }

然后业务方法使用

1@Before(Trans.class)
2    public void doXXX(){....+

这样，方法执行完毕才会提交事物，任何RuntimeException将回滚，如果想手工控制回滚.也可以通过

1Trans.commit()
2Trans.rollback()

如果习惯了JFinal Record模式，建议用户创建一个BaseBean，封装SQLManager CRUD 方法即可。然后其他模型继承此BaseBean

可以参考demo https://git.oschina.net/xiandafu/jfinal_beet_beetsql_btjson

SQLManager API

查询API

模板类查询（自动生成sql）

• public List template(T t) 根据模板查询，返回所有符合这个模板的数据库
• public List template(T t,RowMapper mapper) 同上，mapper可以提供额外的映射，如处理一对多，一对一
• public List template(T t,int start,int size) 同上，可以翻页
• public List template(T t,RowMapper mapper,int start,int size) 翻页，并增加额外的映射
• public long templateCount(T t) 获取符合条件的个数

通过sqlid查询，sql语句在md文件里

• public List select(String sqlId, Class clazz, Map paras) 根据sqlid来查询，参数是个map

• public List select(String sqlId, Class clazz, Object paras) 根据sqlid来查询，参数是个pojo

• public List select(String sqlId, Class clazz, Map paras, int start, int size)， 增加翻页

• public List select(String sqlId, Class clazz, Object paras, int start, int size) ，增加翻页

• public T selectSingle(String id,Object paras, Class target) 根据sqlid查询，将对应的唯一值映射成指定的taget对象,RowMapper mapper 也随着这些api提供,不在此列出了

• public T selectSingle(String id,Map paras, Class target) 同上，参数是map

• public Integer intValue(String id,Object paras) 查询结果映射成Integer，输入是objct

• public Integer intValue(String id,Map paras) 查询结果映射成Integer，输入是map， 其他还有 longValue，bigDecimalValue

更新API

自动生成sql

• public void insert(Class<?> clazz,Object paras) 插入paras到paras关联的表
• public void insert(Class<?> clazz,Object paras,KeyHolder holder)，插入paras到paras关联的表，如果需要主键，可以通过holder的getKey来获取
• public int updateById(Object obj) 根据主键更新，组件通过annotation表示，如果没有，则认为属性id是主键
• public int[] updateByIdBatch(List<?> list) 批量更新

通过sqlid更新

• public int update(String sqlId, Object obj) 根据sqlid更新
• public int update(String sqlId, Map paras) 根据sqlid更新，输出参数是map
• public int[] updateBatch(String sqlId,List<?> list) 批量更新
• public int[] updateBatch(String sqlId,Map[] maps) 批量更新，参数是个数组，元素类型是map

BeetlSQL Annotation

对于自动生成的sql，默认不需要任何annotaton，类名对应于表名（通过NameConverstion类），getter方法的属性名对应于列明（也是通过NameConverstion类），但有些情况还是需要anntation。

• @<table>(name="xxxx") 告诉beetlsql，此类对应xxxx表。比如数据库有User表，User类对应于User表，也可以创建一个UserQuery对象，也对应于User表

  @<table>(name="user") public class QueryUser ..

• @AutoID,作用于getter方法，告诉beetlsql，这是自增主键

• @AssignID，作用于getter方法，告诉beetlsql，这是主键，且由代码设定主键

• @SeqID(name="xx_seq"，作用于getter方法，告诉beetlsql，这是序列主键。

（注，如果想要获取自增主键或者序列主键，需要在SQLManager.insert中传入一个KeyHolder)

BeetlSQL 模型

BeetlSQL是一个全功能DAO工具，支持的模型也很全面，包括

• Pojo, 也就是面向对象Java Object

• Map/List, 对于一些敏捷开发，可以直接使用Map/List 作为输入输出参数

• 混合模型，推荐使用混合模型。兼具灵活性和更好的维护性。Pojo可以实现QueryResult，或者继承QueryResultBean，这样查询出的ResultSet 除了按照pojo进行映射外，无法映射的值将按照列表/值保存。如下一个混合模型:

  /*混合模型*/ public User extends QueryResultBean{ private int id ; pirvate String name; private int roleId; /*以下是getter和setter 方法*/ }

对于sql语句:

1selectUser
2===
3select u.*,r.name r_name from user u left join role r on u.roleId=r.id .....

执行查询的时候

1List<User> list = sqlManager.select("user.selectUser",User.class,paras);
2for(User user:list){
3    System.out.println(user.getId());
4    System.out.println(user.get("rName"));
5
6}

程序可以通过get方法获取到未被映射到pojo的值，也可以在模板里直接 ${user.rName} 显示（对于大多数模板引擎都支持）

Markdown方式管理

BeetlSQL集中管理SQL语句，SQL 可以按照业务逻辑放到一个文件里，如User对象放到user.md 里，文件可以按照模块逻辑放到一个目录下。文件格式抛弃了XML格式，采用了Markdown，原因是

• XML格式过于复杂，书写不方便
• XML 格式有保留符号，写SQL的时候也不方便，如常用的< 符号 必须转义
• MD 格式本身就是一个文档格式，也容易通过浏览器阅读和维护

目前SQL文件格式非常简单，仅仅是sqlId 和sql语句本身，如下

1文件一些说明，放在头部可有可无，如果有说明，可以是任意文字
2        SQL标示
3        ===
4        SQL语句 
5
6        SQL标示2
7        ===
8        SQL语句 2

所有SQL文件建议放到一个sql目录，sql目录有多个子目录，表示数据库类型，这是公共SQL语句放到sql目录下，特定数据库的sql语句放到各自自目录下 当程序获取SQL语句得时候，先会根据数据库找特定数据库下的sql语句，如果未找到，会寻找sql下的。如下代码

List<User> list = sqlManager.select("user.select",User.class);

SqlManager 会根据当前使用的数据库，先找sql/mysql/user.md 文件，确认是否有select语句，如果没有，则会寻找sql/user.md

(注:默认的ClasspathLoader采用了这种方法，你可以实现SQLLoader来实现自己的格式和sql存储方式，如数据库存储)

SQL 模板基于Beetl实现，更容易写和调试，以及扩展

SQL语句可以动态生成，基于Beetl语言，这是因为

• beetl执行效率高效 ，因此对于基于模板的动态sql语句，采用beetl非常合适

• beetl 语法简单易用，可以通过半猜半式的方式实现，杜绝myBatis这样难懂难记得语法。BeetlSql学习曲线几乎没有

• 利用beetl可以定制定界符号，完全可以将sql模板定界符好定义为数据库sql注释符号，这样容易在数据库中测试，如下也是sql模板（定义定界符为"--" 和 "null",null是回车意思);

  1selectByCond
  2    ===
  3    select * form user where 1=1
  4    --if(age!=null)
  5    age=#age#
  6    --}
  

• beetl 错误提示非常友好，减少写SQL脚本编写维护时间

• beetl 能容易与本地类交互（直接访问Java类），能执行一些具体的业务逻辑 ，也可以直接在sql模板中写入模型常量，即使sql重构，也会提前解析报错

• beetl语句易于扩展，提供各种函数，比如分表逻辑函数，跨数据库的公共函数等

如果不了解beetl，可先自己尝试按照js语法来写sql模板，如果还有疑问，可以查阅官网 http://ibeetl.com

Interceptor功能

BeetlSql可以在执行sql前后执行一系列的Intercetor，从而有机会执行各种扩展和监控，这比已知的通过数据库连接池做Interceptor更加容易。如下Interceptor都是有可能的

• 监控sql执行较长时间语句，打印并收集。TimeStatInterceptor 类完成
• 对每一条sql语句执行后输出其sql和参数，也可以根据条件只输出特定sql集合的sql。便于用户调试。DebugInterceptor完成
• 对sql预计解析，汇总sql执行情况（未完成，需要集成第三方sql分析工具）

你也可以自行扩展Interceptor类，来完成特定需求。 如下，在执行数据库操作前会执行befor，通过ctx可以获取执行的上下文参数，数据库成功执行后，会执行after方法

1public interface Interceptor {
2    public void before(InterceptorContext ctx);
3    public void after(InterceptorContext ctx);
4}

InterceptorContext 如下，包含了sqlId，实际得sql，和实际得参数

1public class InterceptorContext {
2    private String sqlId;
3    private String sql;
4    private  List<Object> paras;
5    private Map<String,Object> env  = null;
6}

内置支持主从数据库

BeetlSql管理数据源，如果只提供一个数据源，则认为读写均操作此数据源，如果提供多个，则默认第一个为写库，其他为读库。用户在开发代码的时候，无需关心操作的是哪个数据库，因为调用sqlScrip 的 select相关api的时候，总是去读取从库，add/update/delete 的时候，总是读取主库。

1sqlManager.insert(User.class,user) // 操作主库，如果只配置了一个数据源，则无所谓主从
2    sqlManager.unique(id,User.class) //读取从库

主从库的逻辑是由ConnectionSource来决定的，如下DefaultConnectionSource 的逻辑

1@Override
2public Connection getConn(String sqlId,boolean isUpdate,String sql,List<?> paras){
3    if(this.slaves==null||this.slaves.length==0) return this.getWriteConn(sqlId,sql,paras);     
4    if(isUpdate) return this.getWriteConn(sqlId,sql,paras);
5    boolean onlyMaster = localMaster.get();
6    if(onlyMaster) return this.getMaster(); 
7    return this.getReadConn(sqlId, sql, paras);
8}

• localMaster 可以强制SQLManager 使用主数据库。参考api SQLManager. useMaster(MasterRunner f)

对于于不同的ConnectionSource 完成逻辑不一样，对于spring，jfinal这样的框架，如果sqlManager在事务环境里，总是操作主数据库，如果是只读事务环境 则操作从数据库。如果没有事务环境，则根据sql是查询还是更新来决定。

如下是SpringConnectionSource 提供的主从逻辑

1@Override
2public Connection getConn(String sqlId,boolean isUpdate,String sql,List paras){
3    //只有一个数据源
4    if(this.slaves==null||this.slaves.length==0) return this.getWriteConn(sqlId,sql,paras);
5    //如果是更新语句，也得走master
6    if(isUpdate) return this.getWriteConn(sqlId,sql,paras);
7    //如果api强制使用master
8    boolean onlyMaster = localMaster.get();
9    if(onlyMaster) return this.getMaster();
10    //在事物里都用master，除了readonly事物
11    boolean inTrans = TransactionSynchronizationManager.isActualTransactionActive();
12    if(inTrans){
13        boolean  isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
14        if(!isReadOnly){
15            return this.getMaster();
16        }
17    }
18
19     return this.getReadConn(sqlId, sql, paras);
20}

可以支持更复杂的分库分表逻辑

开发者也可以通过在Sql 模板里完成分表逻辑而对使用者透明，如下sql语句

1insert into 
2    #text("log_"+ getMonth(date())#
3    values () ...

注：text函数直接输出表达式到sql语句，而不是输出？。

log表示按照一定规则分表，table可以根据输入的时间去确定是哪个表

1select * from 
2    #text("log"+log.date)#
3    where

注：text函数直接输出表达式到sql语句，而不是输出？。

同样，根据输入条件决定去哪个表，或者查询所有表

1@ var tables = getLogTables();
2    @ for(table in tables){
3    select * from #text(table)# 
4    @       if(!tableLP.isLast) print("union");
5    @}      
6    where name = #name#

跨数据库平台

如前所述，BeetlSql 可以通过sql文件的管理和搜索来支持跨数据库开发，如前所述，先搜索特定数据库，然后再查找common。另外BeetlSql也提供了一些夸数据库解决方案

• DbStyle 描述了数据库特性，注入insert语句，翻页语句都通过其子类完成，用户无需操心
• 提供一些默认的函数扩展，代替各个数据库的函数，如时间和时间操作函数date等

添加自定义方法

使用方式同Beetl，可以在btsql-ext.properties里添加自定义的函数. 需要注意的是，beetlsql在**站位符里**总是输出 ?,除非你的函数名是以db开头，如db.ifNull,dbLog等。 或者使用内置的text 函数。对于如下sql语句

select * from ${dbLog()} where id = ${id} and status = "${text(@Constants.RUNNING)}"

会生成如下语句

select * from xxxLog where id = ? and status = "on".

问号对应的的值是变量id