EAS BOS：关于数据库sql和oql的使用

最新推荐文章于 2023-10-08 18:17:02 发布

wimpykids

最新推荐文章于 2023-10-08 18:17:02 发布

阅读量1.2k

点赞数

分类专栏： EAS BOS

本文链接：https://blog.csdn.net/wimpykids/article/details/108592519

版权

EAS BOS 专栏收录该内容

78 篇文章 16 订阅

订阅专栏

sql相关：

(1)CompareType说明

   CompareType.EQUALS = ("=");                                       new FilterItemInfo("name","zll") 默认为相等
   CompareType.GREATER = (">");
   CompareType.LESS = ("<");
   CompareType.GREATER_EQUALS = (">=");
   CompareType.LESS_EQUALS = ("<=");
   CompareType.NOTEQUALS = ("<>");
   CompareType.LIKE = ("like");                                   new FilterItemInfo("name","詹%",CompareType.LIKE)
   CompareType.NOTLIKE = ("not like");
   CompareType.INCLUDE = ("in");
   CompareType.NOTINCLUDE = ("not in");
   CompareType.INNER = ("inner");
   CompareType.NOTINNER = ("not inner");
   CompareType.IS = ("is");
   CompareType.ISNOT = ("is not");
   CompareType.EXISTS = ("exists");
   CompareType.NOTEXISTS = ("not exists");
   CompareType.EMPTY = IS;
   CompareType.NOTEMPTY = ISNOT;

   *布尔类型   new FilterItemInfo("isComInspecte", Boolean.FALSE,CompareType.EQUALS)
    * in      String sql="select name from t_studnet where age=18 ";
               new FilterItemInfo("id", sql,CompareType.INCLUDE)

               String sql="'1','2','3'";
               new FilterItemInfo("id", sql,CompareType.INCLUDE)
   * not in 和 EXISTS 和 not exists      和in类似
   * date类型的数据
                  String string = "2018-10-22 ";
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd ");
                Date date = sdf.parse(string);

                  filter.getFilterItems().add(new FilterItemInfo("idzDate",date ,CompareType.EQUALS));//这个字段为真

(2)插入数据
new一个对象，设置对象的值，调用对象的addNew方法即可

   BillStatusLogInfo billLog = new BillStatusLogInfo();//new一个对象
   billLog.setOldStatus(oldStatus);//设置属性
   billLog.setNewStatus(newStatus);
   billLog.setCreateTime(new Timestamp(System.currentTimeMillis()));
   billLog.setCreator(ContextUtil.getCurrentUserInfo(ctx));
   billLog.setBosType(model.getBOSType().toString());

   //调用对象插入数据库
   BillStatusLogFactory.getLocalInstance(ctx).addnew(billLog);//后台调用，在ControllerBean中
   BillStatusLogFactory.getRemoteInstance().addnew(billLog);//前台台调用，在UI中

(3)查询数据
    *通过EntrustChangeFactory得到的Info他们的实体都会对应了一张表各个属性也对应了字段,我们不必了解。
   *FilterInfo（过滤）、Selector（指定属性）以及Sorter（排序）
   *如果不设setMaskString，则默认为and

   方法一：
    EntityViewInfo view = new EntityViewInfo();
   FilterInfo filter = new FilterInfo();
   filter.getFilterItems().add(new FilterItemInfo("isDeclare",CompareType.ISNOT));//条件
    EntrustChangeFactory.getLocalInstance(ctx).getEntrustChangeCollection(view);//后台调用，在ControllerBean中
   EntrustChangeFactory.getRemoteInstance().getEntrustChangeCollection(view);//前台台调用，在UI中

   select *
   from EntrustChange对应的表
    where isDeclare对应的表字段 is not null

   方法二：
   //定义一个selector 查找需要的列
   SelectorItemCollection selector = new SelectorItemCollection();
   selector.add(new SelectorItemInfo("auditor"));//查询属性对应表中莫个字段我们不必了解
   selector.add(new SelectorItemInfo("auditTime"));
   selector.add(new SelectorItemInfo("billStatus"));
   XYBillBaseInfo billBaseInfo = XYBillBaseFactory.getLocalInstance(ctx).getXYBillBaseInfo(pk,selector);//后台调用，在ControllerBean中
   XYBillBaseInfo billBaseInfo = XYBillBaseFactory.getRemoteInstance().getXYBillBaseInfo(pk,selector);//前台台调用，在UI中

   select fauditorid,fauditTime,fbillStatus
   from T_XYBillBase
    where fid = 'id'

   方法三：
   EntityViewInfo evi = new EntityViewInfo();
   SelectorItemCollection selector = evi.getSelector();//直接获取它的
   selector.add("number");
   FilterInfo filter = new FilterInfo();
   filter.getFilterItems().add(new FilterItemInfo("status", "2"));//等价于("status", "2", CompareType.EQUALS)
   filter.getFilterItems().add(new FilterItemInfo("age", "20", CompareType.LESS));
   entityViewInfo.setFilter(filterInfo);
   ProduceDetailFactory.getLocalInstance(ctx).getProduceDetailCollection(evi);   //后端在ControllerBean中调用
   ProduceDetailFactory.getRemoteInstance().getProduceDetailCollection(evi);//前端UI中调用

   EntityViewInfo EntityViewInfo = new EntityViewInfo();// 实体视图信息
    SelectorItemCollection sic = new SelectorItemCollection(); //通过Selector设定属性
    sic.add(new SelectItemInfo("proposer.id"));//表示只获取info上proposer上id属性
    sic.add(new SelectItemInfo("id"));//表示只获取info上id属性
    sic.add(new SelectItemInfo("attendDate"));//表示只获取info上attendDate属性
    sic.add(new SelectItemInfo("dayType"));//表示只获取info上dayType属性
    entityViewInfo.setSelector(sic);//设置属性
    FilterInfo filterInfo = new FilterInfo();//建立过滤条件
    // filterInfo.getFilterItems() 过滤信息组信息
   filterInfo.getFilterItems().add(new FilterItemInfo("proposer.id",personInfo.getId().toString())); //默认为相等
    filterInfo.getFilterItems().add(new FilterItemInfo("attendDate",newDate,CompareType.GREATER_EQUALS));
    entityViewInfo.setFilter(filterInfo);//设置过滤条件



   方法四(不太推荐)：
   String mySql = "select number where status=2 and age<20";
   ProduceDetailFactory.getRemoteInstance().getProduceDetailCollection(mySql);


   select fnumber
    from T_ProduceDetail
    where fstatus = 2
    and fage < 20     //如果20是String，则自动加上 '20'

   方法五：
          注意：
           只支持T-SQL函数和语法
           前端只能写Select语句
           后端CRUD都可以

*查询

String sql = "select now()";
IRowSet rs = com.kingdee.eas.util.app.DbUtil.executeQuery(ctx, sql.toString());

*更新删除添加

String sql = "insert|update|delete";
com.kingdee.eas.util.app.DbUtil.execute(ctx, sql.toString());

*使用占位符

       String sql = "select * from T where fnumber = ?";
       Object[] param = new Object[1];//几个问号就几个参数
       param[0] = "123";
           //建议用集合后转数组
           // List param = new ArrayList();
           // param.add("123");
           // Object[] o = param.toArray();
        IRowSet rs = com.kingdee.eas.util.app.DbUtil.executeQuery(ctx, sql.toString(),param);

    方法六：
         EntityViewInfo view = new EntityViewInfo();
       FilterInfo filter = new FilterInfo();
           filter.getFilterItems().add(new FilterItemInfo("id",getSourceBillId(),CompareType.NOTEQUALS));
           filter.getFilterItems().add(new FilterItemInfo("warehouse.id",getWarehouseSetUpInfo().getId().toString(),CompareType.EQUA
            filter.setMaskString("#0 and #1 and (#2 or #3) and (#4 or #5)");
        view.setFilter(filter );


   对象的属性分为自有属性（一把钥匙）和关联属性（一个圆圈）


   CT_PDA_EntrustChange
   CT_PDA_EntrustChangeEntry