# 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)); } } ```