# SQLManager
SQLManager是BeetlSQL较为底层的类，用于操作数据库，一个系统允许多个SQLManager，原则上，一个SQLManager对应一个业务库，比如一个SQLManager对应订单库，一个SQLManager对应客户库。 如果是多租户，或者分库分表，应该在一个SQLManger内部实现。可以参《多库使用》
> 对于使用BeetlSQL3，掌握SQLManager即可使用，但更推荐的是使用Mapper接口方式，因为他更易于维护。Mapper底层调用了SQLManager API。
构造SQLManger通常是框架集成已经做好的，也可以通过SQLManagerBuilder实现
```java
ConnectionSource source = ConnectionSourceHelper.getSimple(driver, url, "", userName, password);
//source是唯一必须的参数，其他参数都有默认值
SQLManagerBuilder builder = new SQLManagerBuilder(source);
//设置NameConversion，这里数据库命名采用下划线风格，使用UnderlinedNameConversion
builder.setNc(new UnderlinedNameConversion());
//设置一个拦截器，输出debug日志，包含了sql语句和执行参数，执行时间
builder.setInters(new Interceptor[]{new DebugInterceptor()});
//设置数据库分隔，必须跟数据库一样
builder.setDbStyle(new H2Style());
SQLManager sqlManager = builder.build();
```
更常见的是，已经有了DataSource，创建ConnectionSource 可以采用如下代码
```java
ConnectionSource source = ConnectionSourceHelper.getSingle(datasource);
```
如果是主从DataSource
```java
ConnectionSource source = ConnectionSourceHelper.getMasterSlave(master,slaves)
```
# 内置语句
SQLManager内置了大量的sql语句，程序员无需编写sql就能实现数据库的CRUD。所有可运行的例子都可以在 https://gitee.com/xiandafu/beetlsql/tree/3.0/sql-samples 找到
## 内置查询API
* public <T> T unique(Class<T> clazz,Object pk) 根据主键查询，如果未找到，抛出`BeetlSQLException`异常.
* public <T> T single(Class<T> clazz,Object pk) 根据主键查询，如果未找到，返回null.
* public <T> List<T> selectByIds(Class<T> clazz, List<?> pks) 根据一批主键查询
- public <T> List<T> all(Class<T> clazz) 查询出所有结果集
- public <T> List<T> all(Class<T> clazz, int start, int size) 翻页
- public int allCount(Class<?> clazz) 总数
- public <T> T lock(Class<T> clazz, Object pk) 同single方法，但会得到一个行级锁，
这里的clazz代表了数据库的表，通过NameConversion得出其表名，比如clazz是SysUser，如果NameConversion设置的是UnderlinedNameConversion，那么表名是`sys_user` 。SQLManager会调用NameConversion.getTableName(Class c) 得出表名字
NameConversion实现会考虑类上是否有`@Table` 注解，如果有，则表名以`@Table`注解为准
```java
@Table(name="sys_user")
public class UserEntity{
}
```
> 通过类名以entity结尾，表示这个类跟数据库表定义完全一致。其他诸如vo，dto结尾，则表示是普通POJO，可能是表的部分属性，或者多个表的综合属性
## template查询
- public <T> List<T> template(T t) 根据模板查询，返回所有符合这个模板的数据库
- public <T> T templateOne(T t) 根据模板查询，返回一条结果，如果没有找到，返回null，如果找到2条以上，则抛异常
- public <T> long templateCount(T t) 获取符合条件的个数
> templateOne在BeetlSQL2中是不报错的，如果有多条，只返回第一条，但导致了某银行系统项目的问题，所以决定改掉这个坑
## 更新操作
- public void insert(Object paras) 插入paras到paras这个Entity类关联的表，如果paras对象有自增主键，则自动赋值。如果属性有@InsertIgnore，则不参与插入
- public void insertTemplate(Object paras) 插入paras到paras关联的表,忽略属性为空值的属性，如果paras对象有自增主键，则自动赋值。如果属性有@InsertIgnore，则不参与插入
- public void insert(Class<?> clazz,Object paras) 插入paras到clazz关联的表
- public int updateById(Object obj) 根据主键更新，所有值参与更新，除非有@UpdateIgnore标识的属性
- public int updateTemplateById(Object obj) 根据主键更新，属性为空的的不会更新
- public int updateBatchTemplateById(Class clazz,List<?> list) 批量根据主键更新,属性为null的不会更新
- public int updateTemplateById(Class<?> clazz，Map paras) 根据主键更新，clazz决定了表以及id，paras提供了参数
- public int[] updateByIdBatch(List<?> list) 批量更新所有属性，返回对应批量更新成功的更新记录数
- public void insertBatch(Class clazz,List<?> list) 批量插入数据，如果数据库自增主键，获取
- public void insertBatch(Class clazz,List<?> list,boolean autoAssignKey) 批量插入数据,如果数据库自增主键，获取。
- public int upsert(Object obj), 更新或者插入一条。先判断是否主键为空，如果为空，则插入，如果不为空，则从数据库 按照此主健取出一条，如果未取到，则插入一条，其他情况按照主键更新。插入后的自增或者序列主健
- public int upsertByTemplate(Object obj), 更新或者插入一条。按照模板方式更新或者插入
> 批处理操作目前版本并未考虑到数据库能接收的最大量，因此，还需要程序自己控制每批的数量，设置成每批3000个比较通用
更新和插入针对的对象有全量操作，即对象的每个属性都参与（当然，此属性必须与数据库列有对应，如果没有，BeetlSQL忽略此属性），也有模板操作，即不为空的属性参与操作。模板操作可以保证只操作数据库表部分数据。
# 执行SQL
SQLManager提供了直接执行JDBC SQL或者模板SQL的接口。当然，正如其他DAO工具，更为推荐的是在Mapper里执行，可以参考《Mapper》
- 查询 public <T> List<T> execute(SQLReady p,Class<T> clazz) 。SQLReady包含了需要执行的sql语句和参数，clazz是查询结果，如
```java
List<User> list = sqlManager.execute(new SQLReady("select * from sys_user where name=? and age = ?","xiandafu",18),User.class);)
```
clazz可以是POJO对象，也可以是Map，如果是POJO，则一一映射，如果是Map，则把结果集放到Map里
- public <T> PageResult<T> execute(SQLReady p, Class<T> clazz, PageRequest<T> pageReqeust)
~~~java
String sql = "select * from sys_user where department_id=?";
PageRequest request = DefaultPageRequest.of(1,10);
SQLReady sqlReady = new SQLReady(sql,new Object[]{1});
PageResult pr = sqlManager.execute(sqlReady,UserEntity.class,request);
DefaultPageResult pageResult = (DefaultPageResult)pr;
printPageResult(pageResult);
public void printPageResult(DefaultPageResult pageResult){
System.out.println(pageResult.getPage());
System.out.println(pageResult.getPageSize());
System.out.println(pageResult.getTotalRow());
System.out.println(pageResult.getTotalPage());
System.out.println(pageResult.getList());
}
~~~
PageRequest和PageResult是用于BeetlSQL3里各种翻页的入参和出参，详情可以参考本章的《翻页》
- 更新 public int executeUpdate(SQLReady p) SQLReady包含了需要执行的sql语句和参数，返回更新结果
- public int[] executeBatchUpdate(SQLBatchReady batch) 批量更新（插入）
- 直接使用Connection public <T> T executeOnConnection(OnConnection<T> call),使用者需要实现onConnection方法的call方法，如调用存储过程
```java
List<User> users = sql.executeOnConnection(new OnConnection<List<User>(){
@Override
public List<User> call(Connection conn) throws SQLException {
CallableStatement cstmt = conn.prepareCall("{ ? = call md5( ? ) }");
ResultSet rs = callableStatement.executeQuery();
return this.sqlManagaer.getDefaultBeanProcessors().toBeanList(rs,User.class);
}
});
```
SQLManager到目前为止，还不支持对存储过程的封装调用，只能通过executeOnConnection来实现
# 执行模板SQL
模板SQL通常放到文件里单独管理，这样的好处是易于维护。同时，模板SQL具备很多函数，容易实现复杂的SQL生成。当然模板SQL可以在java里编写和执行
- public <T> List<T> execute(String sqlTemplate,Class<T> clazz, Object paras), 查询，返回查询结果列表
- public <T> List<T> execute(String sqlTemplate,Class<T> clazz, Map paras) 参数是Map
- public int executeUpdate(String sqlTemplate,Object paras) 返回成功执行条数
- public int executeUpdate(String sqlTemplate,Map paras) 返回成功执行条数
```java
String sql = "select * from sys_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);
```
或者
```java
//或者使用Map作为参数
String sql = "select * from sys_user where department_id=#{myDeptId} and name=#{myName}";
Map paras = new HashMap();
paras.put("myDeptId",1);
paras.put("myName","lijz");
List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);
```
模板提供了很多函数和表达式，方便生成复杂的sql
```java
String sql = "select * from sys_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);
```
这里，join函数会输出,并记录各个变量分别是1,2,3,4,5
```
?,?,?,?,?
```
或者一个较为复杂的模板语句(更常见的复杂的sql维护在markdown文件里，后面章节会说明)
```java
//使用Beetl模板语句
String sql = "select * from sys_user where 1=1 \n" +
"-- @if(isNotEmpty(myDeptId)){\n" +
" and department_id=#{myDeptId}\t\n" +
"-- @}\n" +
"and name=#{myName}";
Map paras = new HashMap();
paras.put("myDeptId",1);
paras.put("myName","lijz");
List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);
```
# 把SQL放到文件里
像MyBatis那样，复杂的SQL放到XML文件中维护，好处非常多，这也是为什么复杂的企业系统，互联网电商后台都采用sql文件维护sql的原因。当然，如果你项目足够简单，也可以在java里维护SQL。但BeetlSQL3推荐尽量使用markdown维护SQL
一个Markdown文件包含多个SQL片段，因此可以通过文件名+ sql标识来引用sql片段，比如
```java
SqlId id = SqlId.of("user","select");
```
这里表示一个user.md文件中的select片段
```java
SqlId id = SqlId.of("sys.user","select");
```
这表示sys/user.md中的select片段
文件默认位于classpath的sql目录下，这是在SQLManagerBuilder中定义的，你可以设置不同的目录
```java
SQLManagerBuilder builder = new SQLManagerBuilder(source);
ClasspathLoader sqlLoader = new MarkdownClasspathLoader("sql","utf-8");
builder.setSqlLoader(sqlLoader)；
```
BeetlSQL通过SqlId来锁定sql片段的内容。但也考虑数据库类型，考虑到跨库操作，可能同一个查询在不同数据库下确实是不同的sql，BeetlSQL会进一步查询数据库下有没有特定的sql。比如SQLManager的dbStyle是H2Style，则查询
`sql/user.md` 和 `sql/h2/user.md` ,如果数据库方言下有特定的sql片段，则优先使用
关于如何写sql模板，会稍后章节说明，如下是一些简单说明。
- 采用md格式，===上面是sql语句在本文件里的唯一标示，下面则是sql语句。
- `-- @` 和`回车符号`是定界符号，可以在里面写beetl语句。
- `#{}` 是占位符号，生成sql语句得时候，将输出？，如果你想输出表达式值，需要用text函数，或者任何以db开头的函数，引擎则认为是直接输出文本。
- `${}`直接输出文本内容而不是占位符，需要开发者自行考虑是否sql注入。beetlsql支持定制`${}`语义来自动判断sql注入安全
如下是一个简单的markdwon文件
```markdown
select
===
select * from sys_user where 1=1
@if(!isEmpty(age)){
and age = #{age}
@}
@if(!isEmpty(name)){
and name = {name}
@}
updateById
===
```sql
update sys_user set status=1 where id = #{id}
```
```
BeetlSQL提供如下API调用SQL片段查询数据库
- public <T> List<T> select(SqlId sqlId, Class<T> clazz, Map<String, Object> paras) 根据sqlid来查询，参数是个map
- public <T> List<T> select(SqlId sqlId, Class<T> clazz, Object paras) 根据sqlid来查询，参数是个pojo
- public <T> List<T> select(SqlId sqlId, Class<T> clazz) 根据sqlid来查询，无参数
- public <T> T selectSingle(SqlId id,Object paras, Class<T> target) 根据sqlid查询，输入是Pojo，将对应的唯一值映射成指定的target对象，如果未找到，则返回空。需要注意的时候，有时候结果集本身是空，这时候建议使用unique
- public <T> T selectSingle(SqlId id,Map<String, Object> paras, Class<T> target) 根据sqlid查询，输入是Map，将对应的唯一值映射成指定的target对象，如果未找到，则返回空。需要注意的时候，有时候结果集本身是空，这时候建议使用unique
- public <T> T selectUnique(SqlId id,Object paras, Class<T> target) 根据sqlid查询，输入是Pojo或者Map，将对应的唯一值映射成指定的target对象,如果未找到，则抛出异常
- public <T> T selectUnique(String id,Map<String, Object> paras, Class<T> target) 根据sqlid查询，输入是Pojo或者Map，将对应的唯一值映射成指定的target对象,如果未找到，则抛出异常
- public Integer intValue(SqlId id,Object paras) 查询结果映射成Integer，如果找不到，返回null，输入是object
- public Integer intValue(SqlId id,Map paras) 查询结果映射成Integer，如果找不到，返回null，输入是map，其他还有 longValue，bigDecimalValue
通过sqlid更新（删除）
- public int insert(SqlId sqlId,Object paras,KeyHolder holder) 根据sqlId 插入，并返回主键，主键id由paras对象所指定，调用此方法，对应的数据库表必须主键自增。
- public int insert(SqlId sqlId,Object paras,KeyHolder holder,String keyName) 同上，主键由keyName指定
- public int insert(SqlId sqlId,Map paras,KeyHolder holder,String keyName)，同上，参数通过map提供
- public int update(SqlId sqlId, Object obj) 根据sqlid更新
- public int update(SqlId sqlId, Map<String, Object> paras) 根据sqlid更新，输出参数是map
- public int[] updateBatch(SqlId sqlId,List<?> list) 批量更新
- public int[] updateBatch(SqlId sqlId,Map<String, Object>[] maps) 批量更新，参数是个数组，元素类型是map
```java
SqlId id = SqlId.of("user","select");
Map map = new HashMap();
map.put("name","n");
List<UserEntity> list = sqlManager.select(id,UserEntity.class,map);
```
# 翻页查询
无论是执行JDBC SQL，还是模板SQL，还是SQL文件，SQLManager都提供了翻页操作API，翻页操作需要用到俩个对象
PageRequest，用于翻页请求，PageResult 翻页执行结果
```
PageRequest request = DefaultPageRequest.of(1,10);
```
如上代码访问第一页，期望每页10个结果集
PageResult包含了总的个数，以及当前分页的内容,定义如下
```java
public interface PageResult<T> {
public long getTotalRow();
public List<T> getList();
public long getTotalPage();
}
```
> 用户可以自由实现PageRequest和PageResult，默认是DefaultPageRequest和DefaultPageResult
JDBC SQL翻页查询
```java
String sql = "select * from sys_user where department_id=?";
PageRequest request = DefaultPageRequest.of(1,10);
SQLReady sqlReady = new SQLReady(sql,new Object[]{1});
PageResult pr = sqlManager.execute(sqlReady,UserEntity.class,request);
DefaultPageResult pageResult = (DefaultPageResult)pr;
```
模板SQL 翻页查询
```java
String sql = "select #{page('*')} from sys_user where department_id=#{id}";
PageRequest request = DefaultPageRequest.of(1,10);
Map map = new HashMap<>();
map.put("id",1);
PageResult pr = sqlManager.executePageQuery(sql,UserEntity.class,map,request);
```
使用了函数page(),用于动态生成俩条sql，一条是输出count(1),求总数的，一条是输出page入参的值，用于查询结果
如果sql语句是group by，则还需要封装成子查询
```java
String sql = "select #{page('*')} from (select count(1) total, department_id from sys_user group by department_id ) a";
PageRequest request = DefaultPageRequest.of(1,10);
PageResult<Map> pr = sqlManager.executePageQuery(sql,Map.class,null,request);
```
SQL文件翻页
SQL文件翻页同模板翻页SQL一样，提供一个page()函数，用于方便转化为count语句和查询语句
```sql
select
#{page("*")}
from sys_user a order by id asc
```
或者使用pageTag
```sql
select
-- @pageTag(){
id,name,department_id,create_time
--@ }
from sys_user a order by id asc
```
java代码如下
```JAVA
SqlId selectById = SqlId.of("user","select");
PageRequest pageRequest = DefaultPageRequest.of(1,20);
PageResult pageResult = sqlManager.pageQuery(selectById,UserEntity.class,new HashMap(),pageRequest);
```
为了提高性能,BeetlSQL提供了pageIgnoreTag 标签函数，在求总数的时候忽略order by
```java
select
-- @pageTag(){
id,name,department_id,create_time
--@ }
from sys_user a
-- @pageIgnoreTag(){
order by id asc
-- @}
```
更进一步，可以提供俩个SQL来分别作为求总数以及查询结果集，有时候也能优化查询
```sql
queryNewUser
===
select * from user order by id desc ;
queryNewUser$count
===
select count(1) from user
```
当翻页查询的时候，如果存在$count，则使用此sql片段作为求总数sql
Java代码如下
```JAVA
SqlId selectById = SqlId.of("user","queryNewUser");
PageRequest pageRequest = DefaultPageRequest.of(1,20);
PageResult pageResult = sqlManager.pageQuery(selectById,UserEntity.class,new HashMap(),pageRequest);
```
PageRequest对象有一个方法是isTotalRequired，因此可以避免每次都查询，比如,构造PageRequest传入 false
```java
PageRequest pageRequest = DefaultPageRequest.of(1,20,false);
```
# SQLResult
有时候，也许你只需要SQL及其参数列表，然后传给你自己的dao工具类，这时候你需要SQLResult，它包含了你需要的sql，和sql参数。 SQLManager 有如下方法，你需要传入sqlid，和参数即可
```java
public SQLResult getSQLResult(String sqlId, Map<String, Object> paras)
```
paras 是一个map，如果你只有一个pojo作为参数，你可以使用“_root” 作为key，这样sql模版找不到名称对应的属性值的时候，会寻找_root 对象，如果存在，则取其同名属性。
SQLResult 如下：
```java
public class SQLResult {
public String jdbcSql;
public List<SQLParameter> jdbcPara;
public Object[] toObjectArray(){}
}
```
jdbcSql是渲染过后的sql，jdbcPara 是对应的参数描述，toObjectArray 是sql对应的参数值。
SQLParameter 用来描述参数，主要包含了
* value: 参数值
* expression ，参数对应的表达式,如下sql
~~~sql
select * from user where id = #{id
~~~
则expression 就是字符串id
* type，expression 类型，因为sql里有可能是一个复杂的表达式，因此type有如下值
NAME_GENEARL:简单的表达式，如id
NAME_EXPRESSION：复杂表达式，比如函数调用，逻辑运算表达式
对于开发者来说，只需要关心sql对应的参数值即可，因此可以调用toObjectArray得到。
# NameConversion
SQLManager里最重要的配置之一是NameConversion，即命名转化，以UnderlinedNameConversion为例子
当数据库表名是sys_user,或者SYS_USER, 对应的Java名字是SysUser,即首字母和下划线后的字母大写，其他小写
反之，如果类名是SysUser，则期望的表名是sys_user。对于列和java属性，也适用同样规则
另外一个常用的NameConversion实现是DefaultNameConversion，即表名，列名与Java类名和属性名一致
NameConversion定义如下:
```java
public abstract class NameConversion {
/****
* 根据实体class获取表名
* @param c
* @return
*/
public abstract String getTableName(Class<?> c);
/****
* 根据class和属性名，获取字段名，此字段必须存在表中，否则返回空
* @param c
* @param attrName
* @return
*/
public abstract String getColName(Class<?> c,String attrName);
/****
* 根据class和colName获取属性名
* @param c
* @param colName
* @return
*/
public abstract String getPropertyName(Class<?> c,String colName);
//忽略其他方法
}
```
NameConversion的实现类 还应该考虑POJO上定义的@Table，以及属性（或者getter）方法上定义的@Column，这个与JPA是一样的
```java
@Table(name="sys_user")
public class UserEntity {
@AutoID
private Integer id;
private String name;
@Column("dept_id")
private Integer departmentId;
}
```
# DBStyle
SQLManager另外一个最重要的配置是DBStyle，每个数据库都在BeetlSQL里都有一个DBStyle，即使某些数据库是类似的，比如MySqlStyle和DamengStyle(达梦)，PostgresStyle和OpenGaussStyle（华为高斯)
DBStyle决定了数据库独特的特性，这会在BeetlSQL3扩展里专门讲到。这里列出一些主要的数据库和对应的DbStyle
* MySQL : MySqlStyle
* Oralce: OracleStyle
* Postgres: PostgresStyle
* SQL Server :SqlServerStyle 或者 SqlServer2012Style（适用2012以上）
* H2：H2Style
* DB2，DB2SqlStyle
* Derby:DerbyStyle
* SQLite:SQLiteStyle
* Clickhouse:ClickHouseStyle
* Apache Cassandra:CassandraSqlStyle
* Apache Drill:DrillStyle
* Apache Druid:DruidStyle
* Apache Hbase:HBaseStyle
* Apache Hive:HiveStyle
* Apache Ignite: IgniteStyle
* CouchBase:CouchBaseStyle
* Machbase:MachbaseStyle
* PrestoSQL: PrestoStyle
* 神通数据库：ShenTongSqlStyle
* 阿里云数据库 polar:PolarDBStyle
* TD-Engine :TaosStyle
* 华为高斯:OpenGaussStyle
* 人大金仓：KingbaseStyle
* 达梦:DamengStyle
# Inerceptor
BeetlSql可以在执行sql前后执行一系列的Intercetor，从而有机会执行各种扩展和监控，这比通过过数据库连接池做监控更加容易。如下Interceptor都是有可能的
- 监控sql执行较长时间语句，打印并收集。TimeStatInterceptor 类完成
- 对每一条sql语句执行后输出其sql和参数，也可以根据条件只输出特定sql集合的sql。便于用户调试。DebugInterceptor完成
- 有机会在执行jdbc查询前更改sql，更改参数等，甚至可以设置ThreadLocal参数从而在ConnectionSource子类里更改数据源
- 跟踪执行的sql和响应的参数
- 缓存功能(自带SimpleCacheInterceptor)，可以通过before直接取得缓存结果而不需要查询JDBC，但需要注意的是，在DAO层缓存不如在Service层缓存更加灵活，很多web框架，比如SpringBoot 都支持桌子Service级别缓存
> DebugInterceptor 类是常用类，会将sql和参数打印到控制台，如果你想集合log4j等工具，你需要继承DebugInterceptor，实现println方法，使用你的log框架打印
你也可以自行扩展Interceptor类，来完成特定需求。 如下，在执行数据库操作前会执行before，通过ctx可以获取执行的上下文参数，数据库成功执行后，会执行after方法，如果执行出错，会调用exception
```java
public interface Interceptor {
public void before(InterceptorContext ctx);
public void after(InterceptorContext ctx);
public void exception(InterceptorContext ctx,Exception ex);
}
```
InterceptorContext 如下，executeContext参数，也就包含了sqlId，实际得sql，是查询还是修改，和实际得参数, 也包括执行结果result。对于查询，执行结果是查询返回的结果集条数，对于更新，返回的是成功条数，
```java
private Map<String, Object> env = null;
private ExecuteContext executeContext;
```
env变量可以用于InterceptorContext上下文保存临时变量，比如TimeStatInterceptor，调用before，保存当前时间到env里。调用after，取出保存的时间，得出整个jdbc调用时长
```java
@Override
public void before(InterceptorContext ctx) {
if(!include(ctx.getExecuteContext().sqlId)){
return;
}
ctx.setEnv(new HashMap<>());
ctx.getEnv().put("stat.time", System.currentTimeMillis());
}
@Override
public void after(InterceptorContext ctx) {
if(!include(ctx.getExecuteContext().sqlId)){
return;
}
long end = System.currentTimeMillis();
long start = (Long) ctx.get("stat.time");
if ((end - start) > max) {
ExecuteContext executeContext = ctx.getExecuteContext();
print(executeContext.sqlId.toString(), executeContext.sqlResult.jdbcSql, executeContext.sqlResult.jdbcPara, (end - start));
}
}
```