3. BeetlSQL 说明

3.1. 获得SQLManager

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

  1. ConnectionSource source = ConnectionSourceHelper.getSimple(driver, url, "", userName, password);
  2. DBStyle mysql = new MySqlStyle();
  3. // sql语句放在classpagth的/sql 目录下
  4. SQLLoader loader = new ClasspathLoader("/sql");
  5. // 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的
  6. UnderlinedNameConversion nc = new  UnderlinedNameConversion();
  7. // 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
  8. SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});

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

  1. ConnectionSource source = ConnectionSourceHelper.getSingle(datasource);

如果是主从Datasource

  1. ConnectionSource source = ConnectionSourceHelper.getMasterSlave(master,slaves)

关于使用Sharding-JDBC实现分库分表，参考主从一章

3.2. 查询API

3.2.1. 简单查询（自动生成sql）
  • public T unique(Class clazz,Object pk) 根据主键查询，如果未找到，抛出异常.

  • public T single(Class clazz,Object pk) 根据主键查询，如果未找到，返回null.

  • public List all(Class clazz) 查询出所有结果集

  • public List all(Class clazz, int start, int size) 翻页

  • public int allCount(Class clazz) 总数
3.2.2 （Query）单表查询

SQLManager提供Query类可以实现单表查询操作

  1. SQLManager sql = ...
  2. List<User> list = sql.query(User.class).andEq("name","hi").orderBy("create_date").select();

sql.query(User.class) 返回了Query类用于单表查询

如果是Java8，则可以使用lambda表示列名

  1. List<User> list1  = sql.lambdaQuery(User.class).andEq(User::getName, "hi").orderBy(User::getCreateDate).select();

lamdba()方法返回了一个LamdbaQuery 类，列名支持采用lambda。

关于Query操作的具体用法，请参考25.1节

Query对象通常适合在业务操作中使用，而不能代替通常的前端界面查询，前端界面查询推荐使用sqlId来查询

3.2.3 template查询
  • public List template(T t) 根据模板查询，返回所有符合这个模板的数据库 同上，mapper可以提供额外的映射，如处理一对多，一对一
  • public T templateOne(T t) 根据模板查询，返回一条结果，如果没有找到，返回null
  • public List template(T t,int start,int size) 同上，可以翻页
  • public long templateCount(T t) 获取符合条件的个数
  • public List template(Class target,Object paras,long start, long size) 模板查询，参数是paras，可以是Map或者普通对象
  • public long templateCount(Class target, Object paras) 获取符合条件个数 翻页的start，系统默认位从1开始，为了兼容各个数据库系统，会自动翻译成数据库习俗，比如start为1，会认为mysql，postgres从0开始（从start－1开始），oralce，sqlserver，db2从1开始（start－0）开始。

然而，如果你只用特定数据库，可以按照特定数据库习俗来，比如，你只用mysql，start为0代表起始纪录，需要配置

  1. OFFSET_START_ZERO = true

这样，翻页参数start传入0即可。

模板查询一般时间较为简单的查询，如用户登录验证

  1. User template = new User();
  2. template.setName(...);
  3. template.setPassword(...);
  4. template.setStatus(1);
  5. User user = sqlManager.templateOne(template);
3.2.4. 通过sqlid查询,sql语句在md文件里

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

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

  • public List select(String sqlId, Class clazz) 根据sqlid来查询，无参数

  • public T selectSingle(String id,Object paras, Class target) 根据sqlid查询，输入是Pojo，将对应的唯一值映射成指定的target对象，如果未找到，则返回空。需要注意的时候，有时候结果集本身是空，这时候建议使用unique

  • public T selectSingle(String id,Map<string, object=””> paras, Class target) 根据sqlid查询，输入是Map，将对应的唯一值映射成指定的target对象，如果未找到，则返回空。需要注意的时候，有时候结果集本身是空，这时候建议使用unique

  • public T selectUnique(String id,Object paras, Class target) 根据sqlid查询，输入是Pojo或者Map，将对应的唯一值映射成指定的target对象,如果未找到，则抛出异常

  • public T selectUnique(String id,Map<string, object=””> paras, Class target) 根据sqlid查询，输入是Pojo或者Map，将对应的唯一值映射成指定的target对象,如果未找到，则抛出异常

  • public Integer intValue(String id,Object paras) 查询结果映射成Integer，如果找不到，返回null，输入是object

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

注意，对于Map参数来说，有一个特殊的key叫着_root,它代表了查询根对象，sql语句中未能找到的变量都会在试图从_root 中查找，关于_root对象，可以参考第8章。 在Map中使用_root, 可以混合为sql提供参数

3.2.5 指定范围查询
  • public List select(String sqlId, Class clazz, Map<string, object=””> paras, int start, int size)， 查询指定范围
  • public List select(String sqlId, Class clazz, Object paras, int start, int size) ，查询指定范围

beetlsql 默认从1 开始，自动翻译为目标数据库的的起始行，如mysql的0，oracle的1

如果你想从0开始，参考11章，配置beetlsql

3.3 翻页查询API

  1. public <T> void pageQuery(String sqlId,Class<T> clazz,PageQuery query)

BeetlSQL 提供一个PageQuery对象,用于web应用的翻页查询,BeetlSql假定有sqlId 和sqlId$count,俩个sqlId,并用这来个来翻页和查询结果总数.如:

  2. queryNewUser
  3. ===
  4. select * from user order by id desc ;
  6. queryNewUser$count
  7. ===
  8. select count(1) from user

对于俩个相似的sql语句,你可以使用use函数,把公共部分提炼出来.

大部分情况下,都不需要2个sql来完成,一个sql也可以,要求使用page函数或者pageTag标签,这样才能同时获得查询结果集总数和当前查询的结果

  2. queryNewUser
  3. ===
  4. select
  5. @pageTag(){
  6. a.*,b.name role_name
  7. @}
  8. from user a left join b ...

如上sql,会在pageQuery查询的时候转为俩条sql语句

  1. select count(1) from user a left join b...
  2. select a.*,b.name role_name  from user a left join b...

如果字段较多,为了输出方便,也可以使用pageTag,字段较少,用page函数也可以. ,具体参考pageTag和page函数说明.翻页代码如下

  1. //从第一页开始查询,无参数
  2. PageQuery query = new PageQuery();
  3. sql.pageQuery("user.queryNewUser", User.class,query);
  4. System.out.println(query.getTotalPage());
  5. System.out.println(query.getTotalRow());
  6. System.out.println(query.getPageNumber());
  7. List<User> list = query.getList();

PageQuery 对象也提供了 orderBy属性，用于数据库排序，如 "id desc"

跨数据库支持

如果你打算使用PageQuery做翻页,且只想提供一个sql语句+page函数,那考虑到跨数据库,应该不要在这个sql语句里包含排序,因为大部分数据库都不支持. page函数生成的查询总数sql语句,因为包含了oder by,在大部分数据库都是会报错的的,比如:select count(1) form user order by name,在sqlserver,mysql,postgresql都会出错,oracle允许这种情况, 因此,如果你要使用一条sql语句+page函数,建议排序用PageQuery对象里有排序属性oderBy,可用于排序,而不是放在sql语句里.

2.8版本以后也提供了标签函数 pageIgnoreTag，可以用在翻页查询里，当查询用作统计总数的时候，会忽略标签体内容，如

  1.   select page("*") from xxx 
  2.   @pageIgnoreTag(){
  3.    order by id 
  4.   @}

如上语句，在求总数的时候，会翻译成 select count(1) from xxx

如果你不打算使用PageQuery+一条sql的方式,而是用两条sql来分别翻页查询和统计总数,那无所谓

或者你直接使用select 带有起始和读取总数的接口,也没有关系,可以在sql语句里包含排序

如果PageQuery对象的totalRow属性大于等于0，则表示已经知道总数，则不会在进行求总数查询

3.4. 更新API

添加，删除和更新均使用下面的API

3.4.1. 自动生成sql
  • public void insert(Object paras) 插入paras到paras关联的表
  • public void insert(Object paras,boolean autoAssignKey) 插入paras到paras对象关联的表,并且指定是否自动将数据库主键赋值到paras里,适用于对于自增或者序列类数据库产生的主健
  • public void insertTemplate(Object paras) 插入paras到paras关联的表,忽略为null值或者为空值的属性
  • public void insertTemplate(Object paras,boolean autoAssignKey) 插入paras到paras对象关联的表,并且指定是否自动将数据库主键赋值到paras里,忽略为null值或者为空值的属性，调用此方法，对应的数据库必须主键自增。
  • public void insert(Class clazz,Object paras) 插入paras到clazz关联的表
  • public void insert(Class clazz,Object paras,KeyHolder holder)，插入paras到clazz关联的表，如果需要主键，可以通过holder的getKey来获取，调用此方法，对应的数据库必须主键自增
  • public int insert(Class clazz,Object paras,boolean autoAssignKey) 插入paras到clazz关联的表，并且指定是否自动将数据库主键赋值到paras里，调用此方法，对应的数据库必须主键自增。
  • public int updateById(Object obj) 根据主键更新，所有值参与更新
  • public int updateTemplateById(Object obj) 根据主键更新，属性为null的不会更新
  • public int updateBatchTemplateById(Class clazz,List list) 批量根据主键更新,属性为null的不会更新
  • public int updateTemplateById(Class clazz，Map paras) 根据主键更新，组件通过clazz的annotation表示，如果没有，则认为属性id是主键,属性为null的不会更新。
  • 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), 更新或者插入一条。先判断是否主键为空，如果为空，则插入，如果不为空，则从数据库 按照此主健取出一条，如果未取到，则插入一条，其他情况按照主键更新。插入后的自增或者序列主健
  • int upsertByTemplate(Object obj) 同上，按照模板插入或者更新。
3.4.2. 通过sqlid更新（删除）
  • public int insert(String sqlId,Object paras,KeyHolder holder) 根据sqlId 插入，并返回主键，主键id由paras对象所指定，调用此方法，对应的数据库表必须主键自增。
  • public int insert(String sqlId,Object paras,KeyHolder holder,String keyName) 同上，主键由keyName指定
  • public int insert(String sqlId,Map paras,KeyHolder holder,String keyName)，同上，参数通过map提供
  • public int update(String sqlId, Object obj) 根据sqlid更新
  • public int update(String sqlId, Map<string, object=””> paras) 根据sqlid更新，输出参数是map
  • public int[] updateBatch(String sqlId,List list) 批量更新
  • public int[] updateBatch(String sqlId,Map<string, object=””>[] maps) 批量更新，参数是个数组，元素类型是map

3.5. 直接执行SQL模板

3.5.1. 直接执行sql模板语句

一下接口sql变量是sql模板

  • public List execute(String sql,Class clazz, Object paras)
  • public List execute(String sql,Class clazz, Map paras)
  • public int executeUpdate(String sql,Object paras) 返回成功执行条数
  • public int executeUpdate(String sql,Map paras) 返回成功执行条数
3.5.2. 直接执行JDBC sql语句
  • 查询 public List execute(SQLReady p,Class clazz) SQLReady包含了需要执行的sql语句和参数，clazz是查询结果，如
  1. List<User> list = sqlManager.execute(new SQLReady("select * from user where name=? and age = ?","xiandafu",18),User.class);)
  • public PageQuery execute(SQLReady p, Class clazz, PageQuery pageQuery)
  1. String  jdbcSql = "  select *from user order by id";
  2. PageQuery query = new PageQuery(1,20);
  3. query = sql.execute(new SQLReady(jdbcSql), User.class, query);

注意:sql参数通过SQLReady 传递，而不是PageQuery。

  • 更新 public int executeUpdate(SQLReady p) SQLReady包含了需要执行的sql语句和参数，返回更新结果
  • public int[] executeBatchUpdate(SQLBatchReady batch) 批量更新（插入）
  • 直接使用Connection public T executeOnConnection(OnConnection call),使用者需要实现onConnection方法的call方法，如调用存储过程
  1. List<User> users = sql.executeOnConnection(new OnConnection<List<User>(){
  2.     @Override
  3.     public List<User> call(Connection conn) throws SQLException {
  4.          CallableStatement cstmt = conn.prepareCall("{ ? = call md5( ? ) }");
  5.          ResultSet rs = callableStatement.executeQuery();
  6.          return this.sqlManagaer.getDefaultBeanProcessors().toBeanList(rs,User.class);
  9.     }
  10. });

3.6. 其他

3.6.1. 强制使用主或者从

如果为SQLManager提供多个数据源，默认第一个为主库，其他为从库，更新语句将使用主库，查询语句使用从库库

可以强制SQLManager 使用主或者从

  • public void useMaster(DBRunner f) DBRunner里的beetlsql调用将使用主数据库库
  • public void useSlave(DBRunner f) DBRunner里的beetlsql调用将使用从数据库库

对于通常事务来说只读事务则从库，写操作事务则总是主库。关于主从支持，参考17章

3.6.2. 生成Pojo代码和SQ片段

用于开发阶段根据表名来生成pojo代码和相应的sql文件

  • genPojoCodeToConsole(String table), 根据表名生成pojo类，输出到控制台.
  • genSQLTemplateToConsole(String table),生成查询，条件，更新sql模板，输出到控制台。
  • genPojoCode(String table,String pkg,String srcPath,GenConfig config) 根据表名，包名，生成路径，还有配置，生成pojo代码
  • genPojoCode(String table,String pkg,GenConfig config) 同上，生成路径自动是项目src路径，或者src/main/java (如果是maven工程)
  • genPojoCode(String table,String pkg),同上，采用默认的生成配置
  • genSQLFile(String table), 同上，但输出到工程，成为一个sql模版,sql模版文件的位置在src目录下，或者src／main／resources（如果是maven）工程.
  • genALL(String pkg,GenConfig config,GenFilter filter) 生成所有的pojo代码和sql模版，
  • genBuiltInSqlToConsole(Class z) 根据类来生成内置的增删改查sql语句，并打印到控制台
  1. sql.genAll("com.test", new GenConfig(), new GenFilter(){
  2.     public boolean accept(String tableName){
  3.         if(tableName.equalsIgnoreCase("user")){
  4.             return true;
  5.         }else{
  6.             return false;
  7.         }
  8.         // return false
  9.     }
  10. });

第一个参数是pojo类包名，GenConfig是生成pojo的配置，GenFilter 是过滤，返回true的才会生成。如果GenFilter为null，则数据库所有表都要生成

警告

必须当心覆盖你掉你原来写好的类和方法，不要轻易使用genAll，如果你用了，最好立刻将其注释掉，或者在genFilter写一些逻辑保证不会生成所有的代码好sql模板文件

3.6.3. 悲观锁 lock

SQLManager 提供如下API实现悲观锁，clazz对应的数据库表，主键为pk的记录实现悲观锁

  1. public <T> T lock(Class<T> clazz, Object pk)

相当于sql语句

  1. select * from xxx where id = ? for update

lock 方法必须用在事务环境里才能生效。事务结束后，自动释放

乐观锁实现请参考@Version 注解