06.原生sql执行

errors
Failed to load YAML frontmatter: (<unknown>): mapping values are not allowed in this context at line 2 column 6

执行SQL

遇到模型无法实现的复杂查询或者基于性能原因，可以通过BaseContextHandler获取上下文Meta,然后通过Meta的getDataAccessor方法获取当前数据库访问信息，允许直接执行SQL。

  public void addManualModels(MetaContainer ctn) {
      BussModelDataAccess bussModelDataAccess = (BussModelDataAccess) ModelDataAccessFactory.getDataAccess(ModelTypeEnum.Buss);
      RelationDBAccessor cr = bussModelDataAccess.getRelationDBAccessor();
      cr.execute("SELECT * FROM meta_model WHERE source=%s", Arrays.asList("manual"));
      for (Map<String, Object> data : cr.fetchMapAll()) {
          ModelMeta modelMeta = new ModelMeta();
          modelMeta = BaseMetaUtil.convertModel(data);
          ctn.addModel(modelMeta);
      }
  }

执行自定义的INSERT\UPDATE\DELETE SQL后，需要调用Cache的invalidate()方法清除缓存以避免模型的数据不一致。

rs.getMeta().getStore().invalidate();

3.5.1.1. 多条件传参

cr.execute("SELECT * FROM meta_model WHERE field1=%s and field2=%s", Arrays.asList("field1Value","field2Value"));

3.5.1.2. in查询传参

cr.execute("SELECT * FROM meta_model WHERE field1 in %s ", Arrays.asList(Arrays.asList("field1Value")));

3.5.1.3. 示例代码

@Model(name = "user", description = "用户")
public class User extends BaseModel {

    @Property(length = 128, displayName = "名称", displayForModel = true)
    private String name;

    @Property(length = 128, displayName = "地址", displayForModel = true)
    private String address;


    /**
     * 直接执行SQL
     */
    @MethodService(name = "fun1")
    public void fun1() {
        //获取数据库连接

        BussModelDataAccess bussModelDataAccess = (BussModelDataAccess) ModelDataAccessFactory.getDataAccess(ModelTypeEnum.Buss);
        RelationDBAccessor dataAccessor = bussModelDataAccess.getRelationDBAccessor();
        //执行sql

        dataAccessor.execute("SELECT * from User ");
        //获取执行结果

        List<Map<String, Object>> appMapList = dataAccessor.fetchMapAll();
        for (Map<String, Object> stringObjectMap : appMapList) {
            System.out.println(stringObjectMap.toString());
        }
    }

    /**
     * 多条件传参
     */
    @MethodService(name = "fun2")
    public void fun2() {
        //获取数据库连接

        BussModelDataAccess bussModelDataAccess = (BussModelDataAccess) ModelDataAccessFactory.getDataAccess(ModelTypeEnum.Buss);
        RelationDBAccessor dataAccessor = bussModelDataAccess.getRelationDBAccessor();
        //执行sql

        dataAccessor.execute("SELECT * from User WHERE name=%s and address=%s", Arrays.asList("zs", "fs"));
        //获取执行结果

        List<Map<String, Object>> appMapList = dataAccessor.fetchMapAll();
        for (Map<String, Object> stringObjectMap : appMapList) {
            System.out.println(stringObjectMap.toString());
        }
    }

    /**
     * in查询传参
     */
    @MethodService(name = "fun3")
    public void fun3() {
        //获取数据库连接

        BussModelDataAccess bussModelDataAccess = (BussModelDataAccess) ModelDataAccessFactory.getDataAccess(ModelTypeEnum.Buss);
        RelationDBAccessor dataAccessor = bussModelDataAccess.getRelationDBAccessor();
        //执行sql

        dataAccessor.execute("SELECT * from User WHERE name=%s and address=%s", Arrays.asList("zs", "fs"));
        dataAccessor.execute("SELECT * from User WHERE name in %s ",
                Arrays.asList(Arrays.asList("zs", "ls")));
        //获取执行结果

        List<Map<String, Object>> appMapList = dataAccessor.fetchMapAll();
        for (Map<String, Object> stringObjectMap : appMapList) {
            System.out.println(stringObjectMap.toString());
        }
    }

}