Examples with Sql org.nutz.dao.sql.Sql used on opensource projects

Example 21 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class DaoUpTest method test_sql.

/**
     * 自定义SQL, 解决绝大部分Dao接口无法满足你的sql调用
     */
@Test
public void test_sql() {
    // 不再啰嗦, 不然你也烦了..
    Dao dao = DaoUp.me().dao();
    // 首先,我们用前一个test的pojo建表
    dao.create(SystemUser.class, true);
    dao.create(SystemTeam.class, true);
    // 建1个用户,一个Team
    SystemUser user = new SystemUser();
    user.setName("wendal");
    SystemTeam team = new SystemTeam();
    team.setName("root");
    user.setTeam(team);
    dao.insertWith(user, "team");
    // Dao接口大部分操作都生成单表SQL, 除了@ManyMany.
    // 所以很多人觉得@One也查2次很浪费啊, 那这里写一条
    String str = "select u.*,t.* from $user_table u INNER JOIN $team_table t ON u.t_id = t.id where u.nm=@name";
    // 创建一个自定义Sql对象, 还有各种形式哦
    Sql sql = Sqls.create(str);
    // $开头的是变量,会直接拼入sql哦
    sql.vars().set("user_table", dao.getEntity(SystemUser.class).getTableName());
    // 同上
    sql.vars().set("team_table", dao.getEntity(SystemTeam.class).getTableName());
    // @开头的是参数, 会变成问号哦
    sql.params().set("name", "wendal");
    // Sqls.callback提供N种常用的回调,你需要的一般都在里面了
    sql.setCallback(Sqls.callback.record());
    // 注意, execute是没有返回值的, 所需要的值都通过callback来获取
    dao.execute(sql);
    // 不抛出异常就是执行成功
    //Sqls.callback.record()的返回值就是Record
    Record record = sql.getObject(Record.class);
    // 从Record还原出Pojo来
    SystemUser u = record.toEntity(dao.getEntity(SystemUser.class), "u.");
    assertNotNull(u);
    SystemTeam t = record.toEntity(dao.getEntity(SystemTeam.class), "t.");
    assertNotNull(t);
    u.setTeam(t);
}

Example 22 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class QueryTest method test_record_to_entity.

@Test
public void test_record_to_entity() {
    if (dao.meta().isH2())
        // h2死活过不去啊
        return;
    dao.each(Pet.class, null, new Each<Pet>() {

        public void invoke(int index, Pet pet, int length) {
            pet.setNickName("AA_" + pet.getName().toUpperCase());
            // 不知道为啥h2数据库会抛出表不存在的异常
            dao.update(pet);
        }
    });
    Entity<Pet> en = dao.getEntity(Pet.class);
    Sql sql = Sqls.queryRecord("SELECT * FROM t_pet");
    dao.execute(sql);
    List<Record> recs = sql.getList(Record.class);
    Pet[] pets = new Pet[recs.size()];
    int i = 0;
    for (Record rec : recs) pets[i++] = rec.toEntity(en);
    for (Pet pet : pets) assertEquals("AA_" + pet.getName().toUpperCase(), pet.getNickName());
}

Example 23 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class BoneCP_Test method clear_links.

@Test
public void clear_links() {
    dao.create(Pet.class, true);
    Sql sql1 = Sqls.create("INSERT INTO t_pet (name) VALUES ('A')");
    Sql sql2 = Sqls.create("INSERT INTO t_pet (nocol) VALUES ('B')");
    try {
        dao.execute(sql1, sql2);
        fail();
    } catch (DaoException e) {
    }
    assertEquals(0, dao.count(Pet.class));
}

Example 24 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class CustomizedSqlsTest method test_dynamic_insert.

@Test
public void test_dynamic_insert() {
    pojos.init();
    ((NutDao) dao).setSqlManager(new FileSqlManager("org/nutz/dao/test/sqls/exec.sqls"));
    int platoonId = 23;
    try {
        pojos.initPlatoon(platoonId);
        Sql sql = dao.sqls().create("tank.insert");
        sql.vars().set("id", platoonId);
        sql.params().set("code", "T1").set("weight", 12);
        dao.execute(sql);
        sql = dao.sqls().create("tank.insert");
        sql.vars().set("id", platoonId);
        sql.params().set("code", "T2").set("weight", 13);
        dao.execute(sql);
        sql = dao.sqls().create("tank.insert");
        sql.vars().set("id", platoonId);
        sql.params().set("code", "T3").set("weight", 14);
        dao.execute(sql);
        sql = dao.sqls().create("tank.insert");
        sql.vars().set("id", platoonId);
        sql.params().set("code", "T4").set("weight", 15);
        dao.execute(sql);
        TableName.run(platoonId, new Atom() {

            public void run() {
                assertEquals(4, dao.count(Tank.class));
            }
        });
    } catch (SqlNotFoundException e) {
    } finally {
        pojos.dropPlatoon(platoonId);
    }
}

Example 25 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class CustomizedSqlsTest method test_cnd_pager.

@Test
public void test_cnd_pager() {
    pojos.init();
    Sql sql = Sqls.create("select * from t_pet $condition");
    sql.setCondition(Cnd.where("name", "=", "wendal"));
    Pager pager = dao.createPager(1, 20);
    sql.setPager(pager);
    dao.execute(sql);
}