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();
}
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;
}
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();
}
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();
}
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();
}
Aggregations