[大数据](七): 自定义sql方式操作数据库及其多表查询

Bee简单易用：单表操作、多表关联操作，可以不用写sql,极少语句就可以完成SQL操作；10分钟即可学会使用。

Bee功能强大：复杂查询也支持向对象方式，分页查询性能更高，一级缓存即可支持个性化优化。高级需求，还可以方便自定义SQL语句。

自定义sql方式操作数据库及其多表查询

1. 为什么需要自定义SQL

软件开发中，对数据库的访问操作，约80%的工作可以由简单的面向对象操作方式完成，只有约20%的工作才需要复杂的sql语句才能完成。当一个ORM框架为了使这20%的工作量也可以用面向对象的方式实现时，这个ORM框架的复杂度，就会随着完成这20%的比例而急剧上升，但还是避免不了用户用自定义sql语句操作数据库的情况发生。也就是说，即使一个ORM框架可以100%的实现面向对象方式操作数据库，它还是需要提供直接用原生sql语句操作数据库的接口。毕竟，有时用户为了提高性能，需要这样操作。另外，面向切面编程AOP被视为面向对象编程OOP的补充，也说明面向对象不能做完所有的事情。

不应该要求DB与ORM实现100%的对应关系。对于复杂的sql语句，考虑使用自定义的sql语句。也可以用链式编程风格和接近sql语句语法风格的面向对象写法解决。

2. bee.sql.properties

为了统一管理自定义的sql文件，Bee提供了bee.sql.properties用于专门放这些文件。

CustomSql的getCustomSql方法，通过key能获取到该文件中自定义的sql。使用例子如下：

String sql = CustomSql.getCustomSql("osql.example.entity.selectOrders");

如下是一些自定义sql的例子，在第3点的实例中，会用到。

#用户自定义SQL文件(sql为DB能识别的,自定义sql,Bee不转换)
#推荐使用bean的包名或部分包名+有意义的名字作为sql id名称
osql.example.entity.selectOrders=select * from orders where userid=?

osql.example.entity.selectOrdersViaMap=select * from orders where userid=#{userid}

osql.example.entity.selectOrdersLikeNameViaMap=select * from orders where name like #{name%}

osql.example.entity.selectAllOrders=select * from orders

3. 自定义SQL使用实例

可通过PreparedSql接口来使用自定义sql.

PreparedSql接口的部分方法:

自定义sql,有两种占位的方式，userid=?

或者：userid=#{userid}

一种是直接用问号，一直种是#{变量名}.

问号传入的是 Object[] preValues数组，后一种传入的是map.

具体使用例子如下：

  public static void main(String[] args) {
    test();
  }
  public static void test() {

    try {
      PreparedSql preparedSql = BeeFactory.getHoneyFactory().getPreparedSql();

      String sql = CustomSql.getCustomSql("osql.example.entity.selectOrders");
      Logger.info("getCustomSql:  " + sql); //只能在一行的.
      
//      List<String[]> list6 = 
          preparedSql.select(sql,new Object[] { "bee" },1,3);
          preparedSql.select(sql,new Object[] { "bee" },1,3);//test: don't use cache
          preparedSql.select(sql,new Object[] { "bee" },1,3);//test: don't use cache
      
//      osql.example.entity.selectOrders=select * from orders where userid=?
//      List<String[]> list5 = preparedSql.select(sql,new Object[] { "bee"},1,3);

//      List<Orders> list1 = preparedSql.selectSomeField(sql, new Orders(), new Object[] { "bee" });
      List<Orders> list1 = preparedSql.selectSomeField(sql, new Orders(), new Object[] { "bee" },2,3);
      for (int i = 0; i < list1.size(); i++) {
        Logger.info(list1.get(i).toString());
      }
      
      String jsonString1= preparedSql.selectJson(sql, new Object[] { "bee" },1,3);//array  selectJson
      Logger.info(jsonString1);

      String sql2 = CustomSql.getCustomSql("osql.example.entity.selectOrdersViaMap");
      Logger.info("getCustomSql:  " + sql2);

      Map<String, Object> map = new HashMap<>();
      map.put("userid", "bee");
//      List<Orders> list2 = preparedSql.select(sql2, new Orders(), map);//map
//      List<Orders> list2 = preparedSql.select(sql2, new Orders(), map,1,3);//map
      preparedSql.selectSomeField(sql2, new Orders(), map,1,3);//use cache
      List<Orders> list2 = preparedSql.selectSomeField(sql2, new Orders(), map,1,3);//map  selectSomeField
      for (int i = 0; i < list2.size(); i++) {
        Logger.info(list2.get(i).toString());
      }
      
      String jsonString= preparedSql.selectJson(sql2, map,1,3);//map  selectJson
      Logger.info(jsonString);
      
      String sql3 = CustomSql.getCustomSql("osql.example.entity.selectOrdersLikeNameViaMap");
      Logger.info("getCustomSql:  " + sql3);

      Map<String, Object> map2 = new HashMap<>();
      map2.put("name", "Bee");
//      List<Orders> list3 = preparedSql.select(sql3, new Orders(), map2);//map
      List<Orders> list3 = preparedSql.select(sql3, new Orders(), map2,1,3);//map
      for (int i = 0; i < list3.size(); i++) {
        Logger.info(list3.get(i).toString());
      }
      
      String sql4 = CustomSql.getCustomSql("osql.example.entity.selectAllOrders");
      Logger.info("getCustomSql:  " + sql4); //只能在一行的.

//      List<Orders> list4 = preparedSql.select(sql4, new Orders(), new Object[] {});
      List<Orders> list4 = preparedSql.select(sql4, new Orders(), new Object[] {},1,3);
//      List<Orders> list4 = preparedSql.select(sql4, new Orders(), new HashMap());
      for (int i = 0; i < list4.size(); i++) {
        Logger.info(list4.get(i).toString());
      }
      
//      preparedSql.select(sql4, new Orders(), new HashMap()); //test: get from cache
      preparedSql.select(sql4, new Orders(), new Object[] {});
      List<String[]> list7 = preparedSql.select(sql4,new Object[] {},1,3);    
    } catch (BeeException e) {
      e.printStackTrace();
      Logger.error(e.getMessage());
    }
  }

4. 自定义sql的多表查询

在mysql工具navicat中查询多表的数据：

将sql放到：bee.sql.properties

osql.example.userOrders=select orders.userid,user.name,orders.total,orders.createtime from orders,user where orders.userid=user.username and orders.sequence=?
实现代码：

      String sql8 = CustomSql.getCustomSql("osql.example.userOrders");
      List<String[]> list8 = preparedSql.select(sql8,new Object[] {"123456"});
      String str[];
      for (int i = 0; i < list8.size(); i++) {
        str=list8.get(i);
        for (int j = 0; j < str.length; j++) {
          Logger.info(str[j]);
        }
      }

打印的日志：

[Bee] PreparedSqlLib select SQL: 
select orders.userid,user.name,orders.total,orders.createtime from orders,user where orders.userid=user.username and orders.sequence=?   [values]: 123456
bee
Bee
100.00
2020-04-25 17:59:15.0

更多使用例子，请查看bee-exam,地址：

bee-exam: Bee-exam is the test and example of ORM framework Bee.

或：

https://github.com/automvc/bee-exam

关于Bee的链式编程风格使用，请参考：

到这里，自定义SQL操作数据库的用法就介绍完了。要是有什么问题，记得告诉我们哦！