Examples with FetchCase io.github.ihongs.db.util.FetchCase used on opensource projects

Example 6 with FetchCase

use of io.github.ihongs.db.util.FetchCase in project HongsCORE by ihongs.

the class TestDB method testFetchCase.

@Test
public void testFetchCase() throws HongsException {
    FetchCase caze = new FetchCase(FetchCase.CLEVER);
    FetchCase copy;
    String vali;
    caze.from("table1", "t1").select("f1, `f2` , t1.f3, t1.`f4` , `t1`.f5, `t1`.`f6`").select("CONCAT(f1, `f2`, t1.f3, 12.3, 'ab.c', COUNT(*), COUNT(DISTINCT f1)) AS c1").select("'xyz' AS a1, 45.6 AS !a2, 78.9 AS !`a3`").filter("f1 = ? AND f2 IN (?) AND `f3` LIKE ?", 123, 456, "abc").assort("`f1`, f2 DESC, !a2 ASC, !`a3` DESC").join("table2", "t2").on("`t1_id` = :`id`").select("f1, `f2` , t2.f3, t2.`f4` , `t2`.f5, `t2`.`f6`").select("CONCAT(f1, `f2`, t2.f3, 12.3, 'ab.c', COUNT(*), COUNT(DISTINCT f1)) AS c1").select("'xyz' AS a1, 45.6 AS !a2, 78.9 AS !`a3`").filter("f1 = ? AND f2 IN (?) AND `f3` LIKE ?", 123, 456, "abc").assort("`f1`, f2 DESC, !a2 ASC, !`a3` DESC");
    vali = "SELECT `t1`.f1, `t1`.`f2` , t1.f3, t1.`f4` , `t1`.f5, `t1`.`f6`, CONCAT(`t1`.f1, `t1`.`f2`, t1.f3, 12.3, 'ab.c', COUNT(*), COUNT(DISTINCT `t1`.f1)) AS c1, 'xyz' AS a1, 45.6 AS a2, 78.9 AS `a3` , `t2`.`f1` AS `t2.f1`, `t2`.`f2` AS `t2.f2` , t2.`f3` AS `t2.f3`, t2.`f4` AS `t2.f4` , `t2`.`f5` AS `t2.f5`, `t2`.`f6` AS `t2.f6`, CONCAT(`t2`.f1, `t2`.`f2`, t2.f3, 12.3, 'ab.c', COUNT(*), COUNT(DISTINCT `t2`.f1)) AS `t2.c1`, 'xyz' AS `t2.a1`, 45.6 AS a2, 78.9 AS `a3` FROM `table1` AS `t1` INNER JOIN `table2` AS `t2` ON `t2`.`t1_id` = `t1`.`id` WHERE `t1`.f1 = 123 AND `t1`.f2 IN (456) AND `t1`.`f3` LIKE 'abc' AND `t2`.f1 = 123 AND `t2`.f2 IN (456) AND `t2`.`f3` LIKE 'abc' ORDER BY `t1`.`f1`, `t1`.f2 DESC, a2 ASC, `a3` DESC , `t2`.`f1`, `t2`.f2 DESC, a2 ASC, `a3` DESC";
    if (!vali.equals(caze.toString())) {
        fail("构建查询语句错误\r\n\t目标: " + vali + "\r\n\t实际: " + caze.toString());
    }
    copy = caze.clone();
    copy.gotJoin("t2").in(null);
    vali = "SELECT `t1`.f1, `t1`.`f2` , t1.f3, t1.`f4` , `t1`.f5, `t1`.`f6`, CONCAT(`t1`.f1, `t1`.`f2`, t1.f3, 12.3, 'ab.c', COUNT(*), COUNT(DISTINCT `t1`.f1)) AS c1, 'xyz' AS a1, 45.6 AS a2, 78.9 AS `a3` , `t2`.f1, `t2`.`f2` , t2.f3, t2.`f4` , `t2`.f5, `t2`.`f6`, CONCAT(`t2`.f1, `t2`.`f2`, t2.f3, 12.3, 'ab.c', COUNT(*), COUNT(DISTINCT `t2`.f1)) AS c1, 'xyz' AS a1, 45.6 AS a2, 78.9 AS `a3` FROM `table1` AS `t1` INNER JOIN `table2` AS `t2` ON `t2`.`t1_id` = `t1`.`id` WHERE `t1`.f1 = 123 AND `t1`.f2 IN (456) AND `t1`.`f3` LIKE 'abc' AND `t2`.f1 = 123 AND `t2`.f2 IN (456) AND `t2`.`f3` LIKE 'abc' ORDER BY `t1`.`f1`, `t1`.f2 DESC, a2 ASC, `a3` DESC , `t2`.`f1`, `t2`.f2 DESC, a2 ASC, `a3` DESC";
    if (!vali.equals(copy.toString())) {
        fail("取消查询层名异常\r\n\t目标: " + vali + "\r\n\t实际: " + caze.toString());
    }
    copy = caze.clone();
    copy.gotJoin("t2").by(FetchCase.NONE);
    vali = "SELECT `t1`.f1, `t1`.`f2` , t1.f3, t1.`f4` , `t1`.f5, `t1`.`f6`, CONCAT(`t1`.f1, `t1`.`f2`, t1.f3, 12.3, 'ab.c', COUNT(*), COUNT(DISTINCT `t1`.f1)) AS c1, 'xyz' AS a1, 45.6 AS a2, 78.9 AS `a3` FROM `table1` AS `t1` WHERE `t1`.f1 = 123 AND `t1`.f2 IN (456) AND `t1`.`f3` LIKE 'abc' ORDER BY `t1`.`f1`, `t1`.f2 DESC, a2 ASC, `a3` DESC";
    if (!vali.equals(copy.toString())) {
        fail("移除查询层级异常\r\n\t目标: " + vali + "\r\n\t实际: " + caze.toString());
    }
    copy = caze.gotJoin("t2").clone();
    vali = "SELECT f1, `f2` , t2.f3, t2.`f4` , `t2`.f5, `t2`.`f6`, CONCAT(f1, `f2`, t2.f3, 12.3, 'ab.c', COUNT(*), COUNT(DISTINCT f1)) AS c1, 'xyz' AS a1, 45.6 AS a2, 78.9 AS `a3` FROM `table2` AS `t2` WHERE f1 = 123 AND f2 IN (456) AND `f3` LIKE 'abc' ORDER BY `f1`, f2 DESC, a2 ASC, `a3` DESC";
    if (!vali.equals(copy.toString())) {
        fail("提取下级查询异常\r\n\t目标: " + vali + "\r\n\t实际: " + caze.toString());
    }
    caze = new FetchCase(FetchCase.CLEVER);
    caze.from("xxx", "x").join("yyy", "y", "xxx_id = :id").filter("name = \"Hong's\" AND age = '30'");
    vali = "SELECT `x`.* FROM `xxx` AS `x` INNER JOIN `yyy` AS `y` ON `y`.xxx_id = `x`.id WHERE `x`.name = \"Hong's\" AND `x`.age = '30'";
    if (!vali.equals(caze.toString())) {
        fail("智能模式构建异常\r\n\t目标: " + vali + "\r\n\t实际: " + caze.toString());
    }
    caze = new FetchCase(FetchCase.CLEVER);
    caze.from("a_master_user", "user").filter("state != 1 AND dept_id IN (?)", 1).join("a_master_dept_user", "depts").on("user_id = :user_id").by(FetchCase.LEFT).filter("dept_id IN (?)", 10);
    vali = "SELECT `user`.* FROM `a_master_user` AS `user` LEFT JOIN `a_master_dept_user` AS `depts` ON `depts`.user_id = `user`.user_id WHERE `user`.state != 1 AND `user`.dept_id IN (1) AND `depts`.dept_id IN (10)";
    if (!vali.equals(caze.toString())) {
        fail("实例复合查询异常\r\n\t目标: " + vali + "\r\n\t实际: " + caze.toString());
    }
}

Example 7 with FetchCase

use of io.github.ihongs.db.util.FetchCase in project HongsCORE by ihongs.

the class DBAction method isUnique.

@Action("unique")
public void isUnique(ActionHelper helper) throws HongsException {
    Model ett = getEntity(helper);
    Map req = helper.getRequestData();
    req = getReqMap(helper, ett, "unique", req);
    FetchCase c = new FetchCase();
    c.setOption("INCLUDE_REMOVED", Synt.declare(req.get("include-removed"), false));
    boolean val = ett.unique(req, c);
    helper.reply(null, val ? 1 : 0);
}

Example 8 with FetchCase

use of io.github.ihongs.db.util.FetchCase in project HongsCORE by ihongs.

the class Table method fetchCase.

/**
 * 调用 FetchCase 构建查询
 * 可用 getAll, getOne  得到结果, 以及 delete, update 操作数据
 * 但与 fetchMore,fetchLess 不同, 不会自动关联和排除已删的数据
 * @return 绑定了 db, table 的查询对象
 * @throws io.github.ihongs.HongsException
 */
public FetchCase fetchCase() throws HongsException {
    FetchCase fc = new FetchCase().use(db).from(tableName, name);
    AssocMore.checkCase(fc, getParams());
    return fc;
}

Example 9 with FetchCase

use of io.github.ihongs.db.util.FetchCase in project HongsCORE by ihongs.

the class Model method delete.

/**
 * 删除记录
 *
 * @param rd
 * @param caze
 * @return 删除条数
 * @throws io.github.ihongs.HongsException
 */
public int delete(Map rd, FetchCase caze) throws HongsException {
    Object idz = rd.get(Cnst.ID_KEY);
    if (idz == null) {
        idz = rd.get(table.primaryKey);
    }
    if (idz == null) {
        return this.del(null, null);
    }
    Set<String> ids = new LinkedHashSet();
    if (idz instanceof Collection) {
        ids.addAll((Collection) idz);
    } else {
        ids.add(idz.toString());
    }
    // 检查是否可更新
    FetchCase fc = caze != null ? caze.clone() : fetchCase();
    fc.setOption("MODEL_START", "delete");
    permit(fc, rd, ids);
    for (String id : ids) {
        this.del(id, fc);
    }
    return ids.size();
}

Example 10 with FetchCase

use of io.github.ihongs.db.util.FetchCase in project HongsCORE by ihongs.

the class Model method getInfo.

/**
 * 获取信息
 *
 * @param rd
 * @param caze
 * @return 记录信息
 * @throws io.github.ihongs.HongsException
 */
public Map getInfo(Map rd, FetchCase caze) throws HongsException {
    if (rd == null) {
        rd = new HashMap();
    }
    if (caze == null) {
        caze = fetchCase();
    }
    if (rd.containsKey(Cnst.ID_KEY)) {
        rd.put(table.primaryKey, rd.get(Cnst.ID_KEY));
    }
    caze.setOption("MODEL_START", "getInfo");
    this.filter(caze, rd);
    Map info = table.fetchLess(caze);
    Map data = new HashMap();
    data.put("info", info);
    /**
     * 与 list 保持一致, 用 rn 控制 page
     * rn= 1 正常
     * rn= 0 不给 page
     * rn=-1 返回 page.count=0 缺失 page.count=1 受限
     */
    int rn = Synt.declare(rd.get(Cnst.RN_KEY), 1);
    if (rn == 0) {
        return data;
    }
    Map page = new HashMap();
    data.put("page", page);
    /**
     * 查不到可能是不存在、已删除或受限
     * 需通过 id 再查一遍，区分不同错误
     */
    page.put(Cnst.RN_KEY, rn);
    Object id = rd.get(table.primaryKey);
    if (info != null && !info.isEmpty()) {
        page.put("state", 1);
        page.put("count", 1);
    } else if (rn >= 0 || id == null) {
        page.put("state", 0);
        page.put("count", 0);
    } else {
        FetchCase fc = new FetchCase(FetchCase.STRICT).filter(table.primaryKey, id).select(table.primaryKey);
        Map row = table.fetchLess(fc);
        if (row != null && !row.isEmpty()) {
            page.put("state", 0);
            page.put("count", 1);
        } else {
            page.put("state", 0);
            page.put("count", 0);
        }
    }
    return data;
}