Search in sources :

Example 1 with DbQuery

use of jodd.db.DbQuery in project jodd by oblac.

the class GenericDao method save.

/**
	 * Simply inserts object into the database.
	 */
public void save(Object entity) {
    DbQuery q = query(insert(entity));
    q.autoClose().executeUpdate();
}
Also used : DbQuery(jodd.db.DbQuery)

Example 2 with DbQuery

use of jodd.db.DbQuery in project jodd by oblac.

the class GenericDao method store.

/**
	 * Saves or updates entity. If ID is not <code>null</code>, entity will be updated.
	 * Otherwise, entity will be inserted into the database.
	 */
public <E> E store(E entity) {
    DbOomManager dboom = DbOomManager.getInstance();
    Class type = entity.getClass();
    DbEntityDescriptor ded = dboom.lookupType(type);
    if (ded == null) {
        throw new DbOomException("Not an entity: " + type);
    }
    if (!isPersistent(ded, entity)) {
        DbQuery q;
        if (keysGeneratedByDatabase) {
            q = query(insert(entity));
            q.setGeneratedKey();
            q.executeUpdate();
            long nextId = q.getGeneratedKey();
            setEntityId(ded, entity, nextId);
        } else {
            long nextId = generateNextId(ded);
            setEntityId(ded, entity, nextId);
            q = query(insert(entity));
            q.executeUpdate();
        }
        q.close();
    } else {
        query(DbEntitySql.updateAll(entity)).autoClose().executeUpdate();
    }
    return entity;
}
Also used : DbOomException(jodd.db.oom.DbOomException) DbQuery(jodd.db.DbQuery) DbOomManager(jodd.db.oom.DbOomManager) DbEntityDescriptor(jodd.db.oom.DbEntityDescriptor)

Example 3 with DbQuery

use of jodd.db.DbQuery in project jodd by oblac.

the class DbOom2Test method testOrm2.

@Test
public void testOrm2() {
    DbOomManager.resetAll();
    DbSession session = new DbThreadSession(cp);
    executeUpdate(session, "drop table BOY if exists");
    executeUpdate(session, "drop table GIRL if exists");
    String sql = "create table GIRL (" + "ID			IDENTITY," + "NAME		varchar(20)	not null," + "SPECIALITY	varchar(20)	null," + "TIME timestamp not null default CURRENT_TIMESTAMP, " + "primary key (ID)" + ')';
    DbQuery query = new DbQuery(sql);
    query.autoClose().executeUpdate();
    assertTrue(query.isClosed());
    executeUpdate(session, "insert into GIRL(ID, NAME, SPECIALITY) values(1, 'Anna', 'swim')");
    executeUpdate(session, "insert into GIRL(ID, NAME, SPECIALITY) values(2, 'Sandra', 'piano')");
    executeUpdate(session, "insert into GIRL(NAME) values('Monica')");
    session.closeSession();
    assertTrue(session.isSessionClosed());
    // prepare
    session = new DbThreadSession(cp);
    // ---------------------------------------------------------------- girls
    DbOomQuery q = new DbOomQuery("select * from GIRL where ID=1");
    Girl girl = q.find(Girl.class);
    checkGirl1(girl);
    assertTrue(q.isActive());
    IdName idName = q.find(IdName.class);
    assertNotNull(idName);
    assertEquals(1, idName.id);
    assertEquals("Anna", idName.name);
    try {
        // this will fail since no entity is registered!
        q.find();
        fail();
    } catch (DbOomException doex) {
    // ignore
    }
    assertEquals(2, DbOomManager.getInstance().getTotalTypes());
    assertEquals(0, DbOomManager.getInstance().getTotalTableNames());
    assertEquals(2, DbOomManager.getInstance().getTotalNames());
    DbOomManager.getInstance().registerEntity(Girl.class, true);
    girl = q.find();
    checkGirl1(girl);
    assertEquals(2, DbOomManager.getInstance().getTotalTypes());
    assertEquals(1, DbOomManager.getInstance().getTotalTableNames());
    assertEquals(2, DbOomManager.getInstance().getTotalNames());
    q.close();
    session.closeSession();
    /**
		 * Test fails on HSQLDB 1.8 since generated columns are not supported.
		 */
    session = new DbThreadSession(cp);
    q = new DbOomQuery("insert into GIRL (NAME) values('Janna')");
    q.setGeneratedColumns();
    q.executeUpdate();
    long key = q.getGeneratedKey();
    assertEquals(4, key);
    q.close();
    q = new DbOomQuery("insert into GIRL (NAME) values('Janna2')");
    q.setGeneratedColumns("ID", "TIME");
    q.executeUpdate();
    Long Key = q.findGeneratedKey(Long.class);
    assertEquals(5, Key.longValue());
    q.close();
    q = new DbOomQuery("insert into GIRL (NAME) values('Sasha')");
    q.setGeneratedColumns("ID, TIME");
    q.executeUpdate();
    ResultSet rs = q.getGeneratedColumns();
    assertEquals(1, q.getOpenResultSetCount());
    try {
        while (rs.next()) {
            int id = rs.getInt(1);
            assertEquals(6, id);
            try {
                rs.getInt(2);
                fail();
            } catch (SQLException sex) {
            // ignore
            }
        }
    } catch (SQLException sex) {
        fail(sex.getMessage());
    }
    q.closeResultSet(rs);
    q.close();
    assertEquals(0, q.getOpenResultSetCount());
    session.closeSession();
    session = new DbThreadSession(cp);
    DbOomManager.getInstance().registerEntity(Girl2.class, true);
    Girl2 g2 = new Girl2("Gwen");
    q = DbEntitySql.insert(g2).query();
    assertEquals("insert into GIRL (NAME) values (:girl2.name)", q.getQueryString());
    q.setGeneratedColumns("ID");
    q.executeUpdate();
    DbOomUtil.populateGeneratedKeys(g2, q);
    //g2.id = Integer.valueOf((int) q.getGeneratedKey());
    q.close();
    assertEquals(7, g2.id.intValue());
    g2 = DbEntitySql.findByColumn(Girl2.class, "name", "Gwen").query().find(Girl2.class);
    assertEquals("Gwen", g2.name);
    assertNull(g2.speciality);
    assertNotNull(g2.time);
    assertEquals(7, g2.id.intValue());
    session.closeSession();
    session = new DbThreadSession(cp);
    q = DbSqlBuilder.sql("select * from $T{Girl g} where $g.name like :what order by $g.id").query();
    q.setString("what", "%anna%");
    List<Girl2> girls = q.list(Girl2.class);
    assertEquals(2, girls.size());
    checkGirl4(girls.get(0));
    checkGirl5(girls.get(1));
    q.close();
    session.closeSession();
}
Also used : DbQuery(jodd.db.DbQuery) SQLException(java.sql.SQLException) Girl2(jodd.db.oom.tst.Girl2) DbSession(jodd.db.DbSession) IdName(jodd.db.oom.tst.IdName) DbThreadSession(jodd.db.DbThreadSession) ResultSet(java.sql.ResultSet) Girl(jodd.db.oom.tst.Girl) Test(org.junit.Test)

Example 4 with DbQuery

use of jodd.db.DbQuery in project jodd by oblac.

the class DbOomTest method testOrm.

@Test
public void testOrm() {
    DbSession session = new DbThreadSession(cp);
    // ---------------------------------------------------------------- insert
    assertEquals(1, DbEntitySql.insert(new Girl(1, "Anna", "swim")).query().autoClose().executeUpdate());
    assertEquals(1, DbEntitySql.insert(new Girl(2, "Sandra", "piano")).query().autoClose().executeUpdate());
    assertEquals(0, session.getTotalQueries());
    DbOomQuery q2 = new DbOomQuery(DbEntitySql.insert(new Girl(3, "Monica", null)));
    q2.setDebugMode();
    assertEquals("insert into GIRL (ID, NAME) values (:girl.id, :girl.name)", q2.getQueryString());
    q2.init();
    assertEquals("insert into GIRL (ID, NAME) values (3, 'Monica')", q2.getQueryString());
    assertEquals(1, q2.autoClose().executeUpdate());
    assertTrue(q2.isClosed());
    assertEquals(1, DbEntitySql.insert(new BadBoy(Integer.valueOf(1), "Johny", Integer.valueOf(3))).query().autoClose().executeUpdate());
    assertEquals(0, session.getTotalQueries());
    DbQuery dq = new DbQuery("select count(*) from GIRL where id>:id");
    dq.setInteger("id", 1);
    assertEquals(2, dq.executeCount());
    //dq.reset();
    dq.setInteger("id", 10);
    assertEquals(1, session.getTotalQueries());
    assertEquals(0, dq.autoClose().executeCount());
    assertEquals(0, session.getTotalQueries());
    // ---------------------------------------------------------------- girl
    DbOomQuery q = new DbOomQuery("select * from GIRL order by ID");
    Girl girl = q.find(Girl.class);
    checkGirl1(girl);
    assertTrue(q.isActive());
    girl = q.find(new QueryMapper<Girl>() {

        public Girl process(ResultSet resultSet) throws SQLException {
            Girl _girl = new Girl();
            _girl.id = resultSet.getInt("ID");
            _girl.name = resultSet.getString("NAME");
            _girl.speciality = resultSet.getString("SPECIALITY");
            return _girl;
        }
    });
    checkGirl1(girl);
    assertTrue(q.isActive());
    IdName idName = q.find(IdName.class);
    assertNotNull(idName);
    assertEquals(1, idName.id);
    assertEquals("Anna", idName.name);
    girl = q.find(Girl.class);
    checkGirl1(girl);
    // list
    List<Girl> listGirl = q.list(Girl.class);
    assertEquals(3, listGirl.size());
    girl = listGirl.get(1);
    checkGirl2(girl);
    listGirl = q.list(new QueryMapper<Girl>() {

        public Girl process(ResultSet resultSet) throws SQLException {
            Girl _girl = new Girl();
            _girl.id = resultSet.getInt("ID");
            _girl.name = resultSet.getString("NAME");
            _girl.speciality = resultSet.getString("SPECIALITY");
            return _girl;
        }
    });
    assertEquals(3, listGirl.size());
    girl = listGirl.get(1);
    checkGirl2(girl);
    listGirl = q.list();
    assertEquals(3, listGirl.size());
    girl = listGirl.get(1);
    checkGirl2(girl);
    List list = q.list(Girl.class);
    assertEquals(3, list.size());
    girl = (Girl) list.get(2);
    checkGirl3(girl);
    checkGirl2((Girl) list.get(1));
    checkGirl1((Girl) list.get(0));
    assertFalse(q.isClosed());
    list = q.list();
    assertEquals(3, list.size());
    girl = (Girl) list.get(2);
    checkGirl3(girl);
    checkGirl2((Girl) list.get(1));
    checkGirl1((Girl) list.get(0));
    assertFalse(q.isClosed());
    // set
    Set<Girl> setGirl = q.listSet(Girl.class);
    assertEquals(3, setGirl.size());
    girl = (setGirl.iterator().next());
    checkGirl1(girl);
    setGirl = q.listSet();
    assertEquals(3, setGirl.size());
    girl = (setGirl.iterator().next());
    checkGirl1(girl);
    Set set = q.listSet(Girl.class);
    assertEquals(3, set.size());
    Iterator iterator = set.iterator();
    assertTrue(iterator.hasNext());
    girl = (Girl) iterator.next();
    checkGirl1(girl);
    assertTrue(iterator.hasNext());
    girl = (Girl) iterator.next();
    checkGirl2(girl);
    assertTrue(iterator.hasNext());
    girl = (Girl) iterator.next();
    checkGirl3(girl);
    assertFalse(iterator.hasNext());
    set = q.listSet();
    assertEquals(3, set.size());
    iterator = set.iterator();
    assertTrue(iterator.hasNext());
    girl = (Girl) iterator.next();
    checkGirl1(girl);
    assertTrue(iterator.hasNext());
    girl = (Girl) iterator.next();
    checkGirl2(girl);
    assertTrue(iterator.hasNext());
    girl = (Girl) iterator.next();
    checkGirl3(girl);
    assertFalse(iterator.hasNext());
    // iterator
    Iterator<Girl> it = q.iterate(Girl.class);
    int count = 0;
    while (it.hasNext()) {
        girl = it.next();
        count++;
        switch(girl.id) {
            case 1:
                checkGirl1(girl);
                break;
            case 2:
                checkGirl2(girl);
                break;
            case 3:
                checkGirl3(girl);
                break;
        }
    }
    assertEquals(3, count);
    it = q.iterate();
    count = 0;
    while (it.hasNext()) {
        girl = it.next();
        count++;
        switch(girl.id) {
            case 1:
                checkGirl1(girl);
                break;
            case 2:
                checkGirl2(girl);
                break;
            case 3:
                checkGirl3(girl);
                break;
        }
    }
    assertEquals(3, count);
    Iterator it2 = q.iterate(Girl.class);
    while (it2.hasNext()) {
        girl = (Girl) it2.next();
    }
    checkGirl3(girl);
    it2 = q.iterate();
    while (it2.hasNext()) {
        girl = (Girl) it2.next();
    }
    checkGirl3(girl);
    q.close();
    // ---------------------------------------------------------------- girl2
    q = new DbOomQuery("select girl.*, girl.* from GIRL girl order by girl.ID");
    list = q.list(Girl.class, Girl.class);
    assertEquals(3, list.size());
    assertEquals(2, ((Object[]) list.get(2)).length);
    girl = (Girl) ((Object[]) list.get(2))[0];
    checkGirl3(girl);
    girl = (Girl) ((Object[]) list.get(2))[1];
    checkGirl3(girl);
    list = q.list();
    assertEquals(3, list.size());
    girl = (Girl) ((Object[]) list.get(2))[0];
    checkGirl3(girl);
    assertEquals(2, ((Object[]) list.get(2)).length);
    girl = (Girl) ((Object[]) list.get(2))[0];
    checkGirl3(girl);
    girl = (Girl) ((Object[]) list.get(2))[1];
    checkGirl3(girl);
    q.close();
    // ---------------------------------------------------------------- boy
    q = new DbOomQuery("select * from BOY order by ID");
    BadBoy badBoy = q.find(BadBoy.class);
    checkBoy(badBoy);
    badBoy = q.find();
    checkBoy(badBoy);
    badBoy = q.find(BadBoy.class);
    checkBoy(badBoy);
    badBoy = q.find();
    checkBoy(badBoy);
    // list
    List<BadBoy> listBadBoyt = q.list(BadBoy.class);
    assertEquals(1, listBadBoyt.size());
    badBoy = listBadBoyt.get(0);
    checkBoy(badBoy);
    list = q.list(BadBoy.class);
    assertEquals(1, list.size());
    badBoy = (BadBoy) list.get(0);
    checkBoy(badBoy);
    // set
    Set<BadBoy> setBadBoy = q.listSet(BadBoy.class);
    assertEquals(1, setBadBoy.size());
    badBoy = (setBadBoy.iterator().next());
    checkBoy(badBoy);
    set = q.listSet(BadBoy.class);
    assertEquals(1, set.size());
    badBoy = (BadBoy) set.iterator().next();
    checkBoy(badBoy);
    // iterator
    Iterator<BadBoy> itBad = q.iterate(BadBoy.class);
    while (itBad.hasNext()) {
        badBoy = itBad.next();
    }
    checkBoy(badBoy);
    Iterator itBad2 = q.iterate(BadBoy.class);
    while (itBad2.hasNext()) {
        badBoy = (BadBoy) itBad2.next();
    }
    checkBoy(badBoy);
    q.close();
    // ---------------------------------------------------------------- join
    q = new DbOomQuery("select * from GIRL join BOY on GIRL.ID=BOY.GIRL_ID");
    girl = q.find(Girl.class);
    checkGirl3(girl);
    Object[] res = q.find(Girl.class, BadBoy.class);
    badBoy = (BadBoy) res[1];
    girl = (Girl) res[0];
    checkGirl3(girl);
    checkBoy(badBoy);
    res = q.find();
    girl = (Girl) res[0];
    badBoy = (BadBoy) res[1];
    checkGirl3(girl);
    checkBoy(badBoy);
    q.close();
    q = new DbOomQuery(DbSqlBuilder.sql("select $C{girl.*}, $C{BadBoy.*} from $T{Girl girl} join $T{BadBoy} on girl.id=$BadBoy.girlId"));
    badBoy = q.withHints("BadBoy.girl, BadBoy").find();
    girl = badBoy.girl;
    checkGirl3(girl);
    checkBoy(badBoy);
    q.close();
    q = new DbOomQuery(DbSqlBuilder.sql("select $C{girl.*}, $C{BadBoy.*} from $T{Girl girl} join $T{BadBoy} on girl.id=$BadBoy.girlId"));
    List<BadBoy> boys1 = q.withHints("BadBoy.girl, BadBoy").list(Girl.class, BadBoy.class);
    assertEquals(1, boys1.size());
    badBoy = boys1.get(0);
    assertNotNull(badBoy);
    girl = badBoy.girl;
    checkGirl3(girl);
    checkBoy(badBoy);
    q.close();
    q = query(sql("select $C{BadBoy.girl.*}, $C{BadBoy.*} from $T{Girl girl} join $T{BadBoy} on girl.id=$BadBoy.girlId"));
    badBoy = q.find();
    girl = badBoy.girl;
    checkGirl3(girl);
    checkBoy(badBoy);
    q.close();
    // ---------------------------------------------------------------- join
    //q = new DbOomQuery("select * from GIRL, BOY where BOY.GIRL_ID=GIRL.ID");
    q = new DbOomQuery("select * from GIRL join BOY on GIRL.ID=BOY.GIRL_ID");
    badBoy = q.find(BadBoy.class);
    // wrong mapping order, girl is first!
    assertNull(badBoy);
    BadGirl badGirl = q.find(BadGirl.class);
    checkBadGirl3(badGirl);
    res = q.find(BadBoy.class, BadGirl.class);
    badBoy = (BadBoy) res[0];
    badGirl = (BadGirl) res[1];
    checkBadGirl3(badGirl);
    // order is invalid
    assertNull(badBoy);
    res = q.find(BadGirl.class, BadBoy.class);
    badBoy = (BadBoy) res[1];
    badGirl = (BadGirl) res[0];
    checkBadGirl3(badGirl);
    checkBoy(badBoy);
    res = q.find(Boy.class, Girl.class);
    Boy boy = (Boy) res[0];
    girl = (Girl) res[1];
    // order is invalid
    assertNull(boy);
    checkGirl3(girl);
    res = q.find(Girl.class, Boy.class);
    boy = (Boy) res[1];
    girl = (Girl) res[0];
    checkBoy(boy);
    checkGirl3(girl);
    // ---------------------------------------------------------------- left join
    q = new DbOomQuery("select * from GIRL left join BOY on GIRL.ID=BOY.GIRL_ID order by GIRL.ID");
    list = q.list(Girl.class, Boy3.class);
    assertEquals(3, list.size());
    checkGirl1((Girl) ((Object[]) list.get(0))[0]);
    assertNull(((Object[]) list.get(0))[1]);
    checkGirl2((Girl) ((Object[]) list.get(1))[0]);
    assertNull(((Object[]) list.get(1))[1]);
    checkGirl3((Girl) ((Object[]) list.get(2))[0]);
    // BOY is set here
    assertNotNull(((Object[]) list.get(2))[1]);
    // 
    list = q.list(Girl.class, Boy.class);
    assertEquals(3, list.size());
    checkGirl1((Girl) ((Object[]) list.get(0))[0]);
    // empty boy is set here, since ID type is primitive int and it can't be null!!!
    assertNull(((Object[]) list.get(0))[1]);
    checkGirl2((Girl) ((Object[]) list.get(1))[0]);
    assertNull(((Object[]) list.get(1))[1]);
    checkGirl3((Girl) ((Object[]) list.get(2))[0]);
    checkBoy((Boy) ((Object[]) list.get(2))[1]);
    // ---------------------------------------------------------------- match
    girl = new Girl();
    girl.id = 1;
    // new values
    girl.speciality = "run";
    Girl girl_condition = new Girl();
    girl_condition.speciality = "swim";
    String tableRef = "ggg";
    DbSqlBuilder dsb = sql().$("select * from ").table(girl, tableRef).$(" where ").match(tableRef, "conditionRef").use("conditionRef", girl_condition);
    q = new DbOomQuery(dsb);
    list = q.list(Girl.class);
    assertEquals(1, list.size());
    // swim
    checkGirl1((Girl) list.get(0));
    dsb = sql().$("update ").table(girl, tableRef).set(tableRef, girl).$(" where ").match(tableRef, "conditionRef").use("conditionRef", girl_condition);
    q = new DbOomQuery(dsb);
    assertEquals(1, q.executeUpdate());
    girl_condition.speciality = "run";
    dsb = sql().$("select * from ").table(girl, tableRef).$(" where ").match(tableRef, "conditionRef").use("conditionRef", girl_condition);
    q = new DbOomQuery(dsb);
    list = q.list(Girl.class);
    assertEquals(1, list.size());
    assertEquals(1, ((Girl) list.get(0)).id);
    // run
    assertEquals("run", ((Girl) list.get(0)).speciality);
    // go back to swim
    girl.speciality = "swim";
    girl_condition.speciality = "run";
    dsb = sql().$("update ").table(girl, tableRef).set(tableRef, girl).$(" where ").match(tableRef, "conditionRef").use("conditionRef", girl_condition);
    q = new DbOomQuery(dsb);
    assertEquals(1, q.executeUpdate());
    // ---------------------------------------------------------------- etc
    badGirl = new BadGirl();
    badBoy = new BadBoy();
    DbSqlBuilder dt = sql("select $C{g.*, b.*} from $T{g}, $T{b} where $M{g=g} and $M{b=b}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    list = q.list(BadBoy.class, BadGirl.class);
    assertEquals(3, list.size());
    dt = sql("select $C{g.*, b.*} from $T{g}, $T{b} where $M{g=g} and $M{b=b}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    list = q.list(BadBoy.class, BadGirl.class);
    assertEquals(3, list.size());
    dt = sql("select g.*, b.* from $T{g g}, $T{b b} where $M{b=b} and $M{g=g}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    list = q.list(BadBoy.class, BadGirl.class);
    assertEquals(3, list.size());
    badGirl.fooname = "Sandra";
    dt = sql("select $C{g.*, b.*} from $T{g}, $T{b} where $M{b=b} and $M{g=g}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    Object[] result = q.find(BadGirl.class, BadBoy.class);
    checkBoy((BadBoy) result[1]);
    checkBadGirl2((BadGirl) result[0]);
    dt = sql("select $C{g.*, b.*} from $T{g}, $T{b} where $M{b=b} and $M{g=g}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    result = q.find(BadGirl.class, BadBoy.class);
    checkBoy((BadBoy) result[1]);
    checkBadGirl2((BadGirl) result[0]);
    dt = sql("select $C{b.*, g.*} from $T{g}, $T{b} where $M{g=g} and $M{b=b}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    result = q.find(BadBoy.class, BadGirl.class);
    checkBoy((BadBoy) result[0]);
    checkBadGirl2((BadGirl) result[1]);
    badBoy.ajdi = Integer.valueOf(1);
    badBoy.nejm = "Johny";
    dt = sql("select b.*, g.* from $T{g g}, $T{b b} where $M{g=g} and $M{b=b}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    result = q.find(BadBoy.class, BadGirl.class);
    checkBoy((BadBoy) result[0]);
    checkBadGirl2((BadGirl) result[1]);
    dt = sql("select $C{b.*, g.*} from $T{g}, $T{b} where $M{g=g} and $M{b=b}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    result = q.find(BadBoy.class, BadGirl.class);
    checkBoy((BadBoy) result[0]);
    checkBadGirl2((BadGirl) result[1]);
    dt = sql("select b.*, g.* from $T{g g}, $T{b b} where $M{b=b} and $M{g=g}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    result = q.find(BadBoy.class, BadGirl.class);
    checkBoy((BadBoy) result[0]);
    checkBadGirl2((BadGirl) result[1]);
    dt = sql("select $C{g.fooid}, $C{b.*} from $T{g}, $T{b} where $M{g=g} and $M{b=b}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    result = q.find(BadGirl.class, BadBoy.class);
    badGirl = (BadGirl) result[0];
    checkBoy((BadBoy) result[1]);
    assertEquals(2, badGirl.fooid.intValue());
    assertNull(badGirl.fooname);
    assertNull(badGirl.foospeciality);
    // ---------------------------------------------------------------- special
    dt = sql("select $g.fooid * 2 as did, $C{g.+} from $T{g} order by $g.fooid").aliasColumnsAs(COLUMN_CODE).use("g", BadGirl.class);
    q = new DbOomQuery(dt);
    // explicitly ignore the first column 'did'
    list = q.list(null, BadGirl.class);
    assertEquals(3, list.size());
    assertEquals(1, ((BadGirl) ((Object[]) list.get(0))[1]).fooid.intValue());
    assertEquals(2, ((BadGirl) ((Object[]) list.get(1))[1]).fooid.intValue());
    assertEquals(3, ((BadGirl) ((Object[]) list.get(2))[1]).fooid.intValue());
    list = q.list(Integer.class, BadGirl.class);
    assertEquals(3, list.size());
    assertEquals(2, ((Integer) ((Object[]) list.get(0))[0]).intValue());
    assertEquals(1, ((BadGirl) ((Object[]) list.get(0))[1]).fooid.intValue());
    assertEquals(4, ((Integer) ((Object[]) list.get(1))[0]).intValue());
    assertEquals(2, ((BadGirl) ((Object[]) list.get(1))[1]).fooid.intValue());
    assertEquals(6, ((Integer) ((Object[]) list.get(2))[0]).intValue());
    assertEquals(3, ((BadGirl) ((Object[]) list.get(2))[1]).fooid.intValue());
    q = new DbOomQuery("select g.ID * 2 as did, g.ID from Girl g order by g.ID");
    list = q.list(Integer.class, BadGirl.class);
    assertEquals(3, list.size());
    assertEquals(2, ((Integer) ((Object[]) list.get(0))[0]).intValue());
    assertEquals(1, ((BadGirl) ((Object[]) list.get(0))[1]).fooid.intValue());
    assertEquals(4, ((Integer) ((Object[]) list.get(1))[0]).intValue());
    assertEquals(2, ((BadGirl) ((Object[]) list.get(1))[1]).fooid.intValue());
    assertEquals(6, ((Integer) ((Object[]) list.get(2))[0]).intValue());
    assertEquals(3, ((BadGirl) ((Object[]) list.get(2))[1]).fooid.intValue());
    q = new DbOomQuery(sql("select $g.id+$b.id as total, $C{g.*}, $g.id*2 as gdub, $C{b.*}, $g.id/3.0, $g.name from $T{g}, $T{b} where $b.girlId=$g.id").aliasColumnsAs(COLUMN_CODE).use("b", Boy.class).use("g", Girl.class));
    list = q.list(Integer.class, Girl.class, Long.class, Boy.class, Float.class, String.class);
    assertEquals(1, list.size());
    result = (Object[]) list.get(0);
    assertEquals(6, result.length);
    assertEquals(4, ((Integer) result[0]).intValue());
    checkGirl3((Girl) result[1]);
    assertEquals(6, ((Long) result[2]).intValue());
    checkBoy((Boy) result[3]);
    assertEquals(1.0f, ((Float) result[4]).floatValue(), 0.05);
    assertEquals("Monica", result[5]);
    q = new DbOomQuery(sql("select $C{g.*}, $C{g.*} from $T{g} where $g.id=3").aliasColumnsAs(COLUMN_CODE).use("g", Girl.class));
    list = q.list(Girl.class, Girl.class);
    assertEquals(1, list.size());
    result = (Object[]) list.get(0);
    checkGirl3((Girl) result[0]);
    checkGirl3((Girl) result[1]);
    q = new DbOomQuery(sql("select $C{g.*}, $g.name from $T{g} where $g.id=3").aliasColumnsAs(COLUMN_CODE).use("g", Girl.class));
    list = q.list(Girl.class, String.class);
    assertEquals(1, list.size());
    result = (Object[]) list.get(0);
    checkGirl3((Girl) result[0]);
    assertEquals("Monica", result[1]);
    q = new DbOomQuery(sql("select $g.name, $C{g.*} from $T{g} where $g.id=3").aliasColumnsAs(COLUMN_CODE).use("g", Girl.class));
    list = q.list(String.class, Girl.class);
    assertEquals(1, list.size());
    result = (Object[]) list.get(0);
    checkGirl3((Girl) result[1]);
    assertEquals("Monica", result[0]);
    //q.reset();
    list = q.list(String.class, Girl.class);
    result = (Object[]) list.get(0);
    assertEquals("Monica", result[0]);
    q.close();
    // ---------------------------------------------------------------- finder
    girl = new Girl();
    girl.id = 1;
    badGirl = new BadGirl();
    badBoy = new BadBoy();
    DbOomQuery f = DbEntitySql.find(girl).aliasColumnsAs(null).query();
    f.setDebugMode();
    assertEquals("select Girl_.ID, Girl_.NAME, Girl_.SPECIALITY from GIRL Girl_ where (Girl_.ID=:girl.id)", f.toString());
    f.init();
    assertEquals("select Girl_.ID, Girl_.NAME, Girl_.SPECIALITY from GIRL Girl_ where (Girl_.ID=1)", f.toString());
    f.close();
    f = DbEntitySql.find(badGirl).aliasColumnsAs(null).query();
    f.setDebugMode();
    assertEquals("select BadGirl_.ID, BadGirl_.NAME, BadGirl_.SPECIALITY from GIRL BadGirl_ where (1=1)", f.toString());
    f.close();
    f = DbEntitySql.find(badBoy).aliasColumnsAs(null).query();
    f.setDebugMode();
    assertEquals("select BadBoy_.ID, BadBoy_.GIRL_ID, BadBoy_.NAME from BOY BadBoy_ where (1=1)", f.toString());
    f.close();
    girl.name = "Monica";
    badGirl.fooname = "Anna";
    badBoy.nejm = "David";
    f = DbEntitySql.find(girl).query();
    f.setDebugMode();
    f.init();
    assertEquals("select Girl_.ID, Girl_.NAME, Girl_.SPECIALITY from GIRL Girl_ where (Girl_.ID=1 and Girl_.NAME='Monica')", f.toString());
    f.close();
    f = DbEntitySql.find(badGirl).query();
    f.setDebugMode();
    f.init();
    assertEquals("select BadGirl_.ID, BadGirl_.NAME, BadGirl_.SPECIALITY from GIRL BadGirl_ where (BadGirl_.NAME='Anna')", f.toString());
    f.close();
    f = DbEntitySql.find(badBoy).query();
    f.setDebugMode();
    f.init();
    assertEquals("select BadBoy_.ID, BadBoy_.GIRL_ID, BadBoy_.NAME from BOY BadBoy_ where (BadBoy_.NAME='David')", f.toString());
    f.close();
    // ---------------------------------------------------------------- whole round
    badGirl = new BadGirl();
    badGirl.fooid = Integer.valueOf(2);
    f = DbEntitySql.findById(badGirl).query();
    list = f.autoClose().list(BadGirl.class);
    assertTrue(f.isClosed());
    assertEquals(1, list.size());
    checkBadGirl2((BadGirl) list.get(0));
    f = DbEntitySql.count(badGirl).query();
    count = (int) f.autoClose().executeCount();
    assertEquals(1, count);
    assertTrue(f.isClosed());
    DbSqlGenerator g = DbEntitySql.deleteById(badGirl);
    f = new DbOomQuery(g).autoClose();
    f.executeUpdate();
    assertTrue(f.isClosed());
    f = DbEntitySql.count(badGirl).query();
    count = (int) f.autoClose().executeCount();
    assertEquals(0, count);
    assertTrue(f.isClosed());
    badGirl.fooid = null;
    f = DbEntitySql.count(badGirl).query().autoClose();
    count = (int) f.executeCount();
    assertEquals(2, count);
    assertTrue(f.isClosed());
    girl = new Girl();
    girl.id = 1;
    girl.name = "A%";
    f = new DbOomQuery("select * from GIRL where id >= :girl.id and name like :girl.name");
    f.setDebugMode();
    f.setBean("girl", girl);
    assertEquals("select * from GIRL where id >= 1 and name like 'A%'", f.toString());
    count = (int) f.executeCount();
    assertEquals(1, count);
    //f.reset();
    girl.id = -2;
    f.setBean("girl", girl);
    assertEquals("select * from GIRL where id >= -2 and name like 'A%'", f.toString());
    count = (int) f.executeCount();
    assertEquals(1, count);
    //f.reset();
    badGirl = new BadGirl();
    badGirl.fooid = Integer.valueOf(3);
    f = DbEntitySql.findByColumn(BadBoy.class, "girlId", badGirl.fooid).query();
    f.setDebugMode();
    f.init();
    assertEquals("select BadBoy_.ID, BadBoy_.GIRL_ID, BadBoy_.NAME from BOY BadBoy_ where BadBoy_.GIRL_ID=3", f.toString());
    f.close();
    f = DbEntitySql.findForeign(BadBoy.class, badGirl).query();
    f.setDebugMode();
    f.init();
    assertEquals("select BadBoy_.ID, BadBoy_.GIRL_ID, BadBoy_.NAME from BOY BadBoy_ where BadBoy_.GIRL_ID=3", f.toString());
    f.close();
    badGirl = new BadGirl();
    badGirl.fooid = Integer.valueOf(3);
    BadGirl bbgg = DbEntitySql.findById(badGirl).query().find(BadGirl.class);
    bbgg.boys = DbEntitySql.findForeign(BadBoy.class, bbgg).query().list(BadBoy.class);
    assertNotNull(bbgg);
    assertEquals(3, bbgg.fooid.intValue());
    assertNotNull(bbgg.boys);
    assertEquals(1, bbgg.boys.size());
    assertEquals(1, bbgg.boys.get(0).ajdi.intValue());
    // ---------------------------------------------------------------- update
    badGirl = new BadGirl();
    badGirl.fooid = Integer.valueOf(1);
    badGirl = DbEntitySql.findById(badGirl).query().find(badGirl.getClass());
    checkBadGirl1(badGirl);
    badGirl.fooname = "Ticky";
    DbEntitySql.update(badGirl).query().executeUpdate();
    badGirl = new BadGirl();
    badGirl.fooid = Integer.valueOf(1);
    badGirl = DbEntitySql.findById(badGirl).query().find(badGirl.getClass());
    checkBadGirl1Alt(badGirl);
    badGirl.foospeciality = null;
    DbEntitySql.updateAll(badGirl).query().executeUpdate();
    badGirl = new BadGirl();
    badGirl.fooid = Integer.valueOf(1);
    badGirl = DbEntitySql.findById(badGirl).query().find(badGirl.getClass());
    checkBadGirl1Alt2(badGirl);
    // ---------------------------------------------------------------- joins
    q.close();
    // ---------------------------------------------------------------- double table names
    q = new DbOomQuery("select g.*, g.* from GIRL g order by g.ID");
    //noinspection unchecked
    List<Object[]> g2 = q.list(Girl.class, Girl.class);
    assertEquals(2, g2.size());
    Object[] g2o = g2.get(0);
    assertEquals(2, g2o.length);
    checkGirl1Alt((Girl) g2o[0]);
    checkGirl1Alt((Girl) g2o[1]);
    q.close();
    q = new DbOomQuery("select g.*, g2.* from GIRL g, GIRL g2 where g.ID=1 and g2.ID=3");
    //noinspection unchecked
    g2 = q.list(Girl.class, Girl.class);
    assertEquals(1, g2.size());
    g2o = g2.get(0);
    assertEquals(2, g2o.length);
    checkGirl1Alt((Girl) g2o[0]);
    checkGirl3Alt((Girl) g2o[1]);
    q.close();
    session.closeSession();
}
Also used : Set(java.util.Set) ResultSet(java.sql.ResultSet) DbQuery(jodd.db.DbQuery) DbSqlBuilder(jodd.db.oom.sqlgen.DbSqlBuilder) ResultSet(java.sql.ResultSet) Iterator(java.util.Iterator) List(java.util.List) DbSession(jodd.db.DbSession) DbThreadSession(jodd.db.DbThreadSession) QueryMapper(jodd.db.QueryMapper) Test(org.junit.Test)

Example 5 with DbQuery

use of jodd.db.DbQuery in project jodd by oblac.

the class CompositeTest method setUp.

@Before
public void setUp() throws Exception {
    super.setUp();
    DbOomManager.resetAll();
    DbOomManager dbOom = DbOomManager.getInstance();
    dbOom.registerEntity(User.class);
    dbOom.registerEntity(Wizard.class);
    session = new DbThreadSession(cp);
    executeUpdate("drop table WIZARD if exists");
    executeUpdate("drop table USER if exists");
    DbQuery query = new DbQuery("create table USER (" + "USER_ID	IDENTITY," + "NAME		varchar(20)	not null" + ')');
    query.executeUpdate();
    query = new DbQuery("insert into USER values(1, 'Gandalf')");
    query.executeUpdate();
    query = new DbQuery("create table WIZARD (" + "WIZARD_ID	IDENTITY," + "LEVEL		INT	not null" + ')');
    query.executeUpdate();
    query = new DbQuery("insert into WIZARD values(1, 7);");
    query.executeUpdate();
}
Also used : DbQuery(jodd.db.DbQuery) DbThreadSession(jodd.db.DbThreadSession) Before(org.junit.Before)

Aggregations

DbQuery (jodd.db.DbQuery)6 DbThreadSession (jodd.db.DbThreadSession)4 DbSession (jodd.db.DbSession)3 Test (org.junit.Test)3 ResultSet (java.sql.ResultSet)2 SQLException (java.sql.SQLException)1 Iterator (java.util.Iterator)1 List (java.util.List)1 Set (java.util.Set)1 QueryMapper (jodd.db.QueryMapper)1 DbEntityDescriptor (jodd.db.oom.DbEntityDescriptor)1 DbOomException (jodd.db.oom.DbOomException)1 DbOomManager (jodd.db.oom.DbOomManager)1 DbSqlBuilder (jodd.db.oom.sqlgen.DbSqlBuilder)1 Enumerator (jodd.db.oom.tst.Enumerator)1 Girl (jodd.db.oom.tst.Girl)1 Girl2 (jodd.db.oom.tst.Girl2)1 IdName (jodd.db.oom.tst.IdName)1 Before (org.junit.Before)1