Examples with FetchCase app.hongs.db.util.FetchCase used on opensource projects

Example 1 with FetchCase

use of app.hongs.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").orderBy("`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").orderBy("`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";
    System.out.println(vali);
    System.out.println(caze.toString());
    assertEquals(vali, 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";
    System.out.println(vali);
    System.out.println(copy.toString());
    assertEquals(vali, copy.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";
    System.out.println(vali);
    System.out.println(copy.toString());
    assertEquals(vali, copy.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";
    System.out.println(vali);
    System.out.println(copy.toString());
    assertEquals(vali, copy.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'";
    System.out.println(vali);
    System.out.println(caze.toString());
    assertEquals(vali, caze.toString());
    caze = new FetchCase(FetchCase.CLEVER);
    caze.from("a_master_user", "user").filter("state != 1 AND dept_id IN (?)", 1).join("a_master_user_dept", "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_user_dept` 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)";
    System.out.println(vali);
    System.out.println(caze.toString());
    assertEquals(vali, caze.toString());
}

Example 2 with FetchCase

use of app.hongs.db.util.FetchCase in project HongsCORE by ihongs.

the class SignAction method signCreate.

/**
 * 登录
 * @param ah
 * @throws HongsException
 */
@Action("create")
@Verify(conf = "master", form = "sign")
public void signCreate(ActionHelper ah) throws HongsException {
    String appid = Synt.declare(ah.getParameter("appid"), "_WEB_");
    String place = Synt.declare(ah.getParameter("place"), "public");
    String username = Synt.declare(ah.getParameter("username"), "");
    String password = Synt.declare(ah.getParameter("password"), "");
    String passcode;
    DB db = DB.getInstance("master");
    Table tb = db.getTable("user");
    FetchCase fc;
    Map ud;
    // 验证密码
    fc = new FetchCase().from(tb.tableName).select("password, passcode, id, name, head, mtime, state").filter("username = ?", username);
    ud = db.fetchLess(fc);
    if (ud.isEmpty()) {
        ah.reply(AuthKit.getWrong("username", "core.username.invalid"));
        return;
    }
    passcode = Synt.declare(ud.get("passcode"), "");
    password = AuthKit.getCrypt(password + passcode);
    if (!password.equals(ud.get("password"))) {
        ah.reply(AuthKit.getWrong("passowrd", "core.password.invalid"));
        return;
    }
    String usrid = (String) ud.get("id");
    String uname = (String) ud.get("name");
    String uhead = (String) ud.get("head");
    int state = Synt.declare(ud.get("state"), 0);
    long utime = Synt.declare(ud.get("mtime"), 0L) * 1000;
    // 验证状态
    if (1 != state) {
        ah.reply(AuthKit.getWrong("state", "core.sign.state.invalid"));
        return;
    }
    // 验证区域
    Set rs = RoleSet.getInstance(usrid);
    if (0 != place.length() && !rs.contains(place)) {
        ah.reply(AuthKit.getWrong("place", "core.sign.place.invalid"));
        return;
    }
    ah.reply(AuthKit.userSign(ah, place, appid, usrid, uname, uhead, utime));
}

Example 3 with FetchCase

use of app.hongs.db.util.FetchCase in project HongsCORE by ihongs.

the class Dept method getRoles.

public Set<String> getRoles(String deptId) throws HongsException {
    if (deptId == null)
        throw new HongsException(0x10000, "Dept Id required!");
    Table asoc;
    FetchCase caze;
    List<Map> rows;
    Set<String> roles = new HashSet();
    asoc = this.db.getTable("a_master_dept_role");
    caze = this.fetchCase();
    caze.select(asoc.name + ".role").filter(asoc.name + ".dept_id = ?", deptId);
    rows = asoc.fetchMore(caze);
    for (Map row : rows) {
        roles.add((String) row.get("role"));
    }
    return roles;
}

Example 4 with FetchCase

use of app.hongs.db.util.FetchCase in project HongsCORE by ihongs.

the class DeptAction method getList.

@Action("list")
public void getList(ActionHelper helper) throws HongsException {
    Map rd = helper.getRequestData();
    FetchCase fc = model.fetchCase();
    fc.setOption("INCLUDE_REMOVED", Synt.declare(rd.get("include-removed"), false));
    fc.setOption("INCLUDE_PARENTS", Synt.declare(rd.get("include-parents"), false));
    rd = model.getList(rd, fc);
    helper.reply(rd);
}

Example 5 with FetchCase

use of app.hongs.db.util.FetchCase in project HongsCORE by ihongs.

the class User method getRoles.

public Set<String> getRoles(String userId) throws HongsException {
    if (userId == null)
        throw new HongsException(0x10000, "User Id required!");
    Table asoc;
    FetchCase caze;
    List<Map> rows;
    Set<String> roles = new HashSet();
    Set<String> depts = new HashSet();
    asoc = this.db.getTable("a_master_user_dept");
    caze = this.fetchCase();
    caze.select(asoc.name + ".dept_id").filter(asoc.name + ".user_id = ?", userId);
    rows = asoc.fetchMore(caze);
    for (Map row : rows) {
        depts.add((String) row.get("dept_id"));
    }
    asoc = this.db.getTable("a_master_dept_role");
    caze = this.fetchCase();
    caze.select(asoc.name + ".role").filter(asoc.name + ".dept_id = ?", depts);
    rows = asoc.fetchMore(caze);
    for (Map row : rows) {
        roles.add((String) row.get("role"));
    }
    asoc = this.db.getTable("a_master_user_role");
    caze = this.fetchCase();
    caze.select(asoc.name + ".role").filter(asoc.name + ".user_id = ?", userId);
    rows = asoc.fetchMore(caze);
    for (Map row : rows) {
        roles.add((String) row.get("role"));
    }
    return roles;
}