use of jodd.db.DbThreadSession in project jodd by oblac.
the class EntityCacheTest method setUp.
@Override
@Before
public void setUp() throws Exception {
super.setUp();
DbOomManager.resetAll();
DbOomManager dbOom = DbOomManager.getInstance();
dbOom.registerEntity(Girl2.class);
dbOom.registerEntity(Boy.class);
dbSession = new DbThreadSession(cp);
assertEquals(1, DbEntitySql.insert(new Girl2(1, "Anna", "swim")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Girl2(2, "Sandra", "piano")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Girl2(3, "Emma", "nothing")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Boy(1, "Johny", 2)).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Boy(2, "Marco", 2)).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Boy(3, "Hugo", 1)).query().executeUpdate());
}
use of jodd.db.DbThreadSession in project jodd by oblac.
the class MappingTest method testMapping.
@Test
public void testMapping() throws SQLException {
DbSession session = new DbThreadSession(cp);
executeUpdate(session, "drop table FOO if exists");
String sql = "create table FOO (" + "ID integer not null," + "NUMBER integer not null," + "STRING integer not null," + "STRING2 integer not null," + "BOO integer not null," + "COLOR varchar(50) not null," + "WEIGHT integer not null," + "TIMESTAMP timestamp not null," + "TIMESTAMP2 timestamp not null," + "CLOB longvarchar not null," + "BLOB longvarbinary not null," + "DECIMAL real not null," + "DECIMAL2 varchar(50) not null," + "JDT1 bigint not null," + "JDT2 varchar(50) not null," + "primary key (ID)" + ')';
executeUpdate(session, sql);
sql = "insert into FOO values (1, 555, 173, 7, 999, 'red', 1, '2009-08-07 06:05:04.3333', '2010-01-20 01:02:03.4444', 'W173', 'ABCDEF', 1.01, '-7.17', 0, '0')";
executeUpdate(session, sql);
DbOomManager dbOom = DbOomManager.getInstance();
dbOom.registerEntity(Foo.class);
SqlTypeManager.register(Boo.class, BooSqlType.class);
SqlTypeManager.register(FooWeight.class, FooWeigthSqlType.class);
List<Foo> foos = new DbOomQuery("select * from FOO").list(Foo.class);
assertEquals(1, foos.size());
Foo foo = foos.get(0);
assertEquals(1, foo.id);
assertEquals(555, foo.number.value);
assertEquals("173", foo.string);
assertEquals("7", foo.string2);
assertEquals(999, foo.boo.value);
assertEquals(FooColor.red, foo.color);
assertEquals(FooWeight.light, foo.weight);
assertNotNull(foo.timestamp);
assertEquals(109, foo.timestamp.getYear());
assertEquals(6, foo.timestamp.getHours());
assertEquals(5, foo.timestamp.getMinutes());
assertNotNull(foo.timestamp2);
assertEquals(2010, foo.timestamp2.getYear());
assertEquals(1, foo.timestamp2.getHour());
assertEquals(2, foo.timestamp2.getMinute());
assertNotNull(foo.clob);
assertEquals(4, foo.clob.length());
assertEquals("W173", foo.clob.getSubString(1, 4));
assertEquals(3, foo.blob.length());
assertEquals((byte) 0xAB, foo.blob.getBytes(1, 3)[0]);
assertEquals((byte) 0xCD, foo.blob.getBytes(1, 3)[1]);
assertEquals((byte) 0xEF, foo.blob.getBytes(1, 3)[2]);
assertEquals("1.01", foo.decimal.toString().substring(0, 4));
assertEquals("-7.17", foo.decimal2.toString().substring(0, 5));
assertEquals("1970-01-01", foo.jdt1.toString("YYYY-MM-DD"));
assertEquals("1970-01-01", foo.jdt2.toString("YYYY-MM-DD"));
foo.string = "371";
foo.string2 = "007";
foo.boo.value = 213;
foo.color = FooColor.yellow;
foo.weight = FooWeight.heavy;
foo.number.value = 222;
foo.timestamp.setYear(108);
foo.decimal = new Double("34.12");
foo.decimal2 = new BigDecimal("1.099");
DbOomQuery doq = new DbOomQuery(DbEntitySql.update(foo));
foo.jdt1.setDay(2);
foo.jdt1.setYear(3000);
foo.jdt2.setDay(3);
foo.jdt2.setYear(2900);
doq.executeUpdate();
doq = new DbOomQuery(DbEntitySql.updateColumn(foo, "timestamp2", new JDateTime("2010-02-02 20:20:20.222")));
doq.executeUpdate();
foos = new DbOomQuery("select * from FOO").list(Foo.class);
assertEquals(1, foos.size());
foo = foos.get(0);
assertEquals(1, foo.id);
assertEquals("371", foo.string);
assertEquals("7", foo.string2);
assertEquals(213, foo.boo.value);
assertEquals(222, foo.number.value);
assertEquals(FooColor.yellow, foo.color);
assertEquals(FooWeight.heavy, foo.weight);
assertEquals(108, foo.timestamp.getYear());
assertEquals(2010, foo.timestamp2.getYear());
assertEquals(20, foo.timestamp2.getHour());
assertEquals(20, foo.timestamp2.getMinute());
assertEquals(4, foo.clob.length());
assertEquals("W173", foo.clob.getSubString(1, 4));
assertEquals(3, foo.blob.length());
assertEquals("34.12", foo.decimal.toString());
assertEquals("1.099", foo.decimal2.toString().substring(0, 5));
assertEquals("3000-01-02", foo.jdt1.toString("YYYY-MM-DD"));
assertEquals("2900-01-03", foo.jdt2.toString("YYYY-MM-DD"));
executeUpdate(session, "drop table FOO if exists");
session.closeSession();
}
use of jodd.db.DbThreadSession in project jodd by oblac.
the class DbHint2Test method testHint.
@Test
public void testHint() {
DbSession dbSession = new DbThreadSession(cp);
// prepare data
assertEquals(1, DbEntitySql.insert(new Room(1, "Room1")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Room(2, "Room2")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Boy4(1, 1, "Oleg")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Boy4(2, 2, "Stephene")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Boy4(3, 2, "Joe")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Girl4(1, 1, "Anna")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Girl4(2, 2, "Sandra")).query().executeUpdate());
assertEquals(1, DbEntitySql.insert(new Girl4(3, 3, "Jossy")).query().executeUpdate());
// select
DbOomQuery dbOomQuery = new DbOomQuery(sql("select $C{room.*}, $C{boy.*}, $C{girl.*} " + "from $T{Room room} join $T{Boy4 boy} on $room.id=$boy.roomId " + "join $T{Girl4 girl} on $boy.id=$girl.boyId " + "order by $room.id, $boy.id"));
List<Object[]> results = dbOomQuery.autoClose().list(Room.class, Boy4.class, Girl4.class);
assertEquals(3, results.size());
Object[] row = results.get(0);
assertEquals(1, ((Room) row[0]).getId().longValue());
assertEquals(1, ((Boy4) row[1]).id.longValue());
assertEquals(1, ((Girl4) row[2]).getId().longValue());
// ---------------------------------------------------------------- hints
dbOomQuery = new DbOomQuery(sql("select $C{room.*}, $C{room.boys:boy.*}, $C{room.boys.girl:girl.*} " + "from $T{Room room} join $T{Boy4 boy} on $room.id=$boy.roomId " + "join $T{Girl4 girl} on $boy.id=$girl.boyId " + "order by $room.id, $boy.id"));
dbOomQuery.entityAwareMode(true).autoClose();
List<Room> rooms = dbOomQuery.list(Room.class, Boy4.class, Girl4.class);
assertEquals(2, rooms.size());
// room #1
Room room1 = rooms.get(0);
assertEquals(1, room1.getId().longValue());
assertEquals(1, room1.getBoys().size());
Boy4 boy1 = room1.getBoys().get(0);
assertEquals(1, boy1.id.intValue());
Girl4 girl1 = boy1.girl;
assertEquals(1, girl1.getId().longValue());
// room #2
Room room2 = rooms.get(1);
assertEquals(2, room2.getId().longValue());
assertEquals(2, room2.getBoys().size());
Boy4 boy2 = room2.getBoys().get(0);
Boy4 boy3 = room2.getBoys().get(1);
assertEquals(2, boy2.id.longValue());
assertEquals(3, boy3.id.longValue());
Girl4 girl2 = boy2.girl;
assertEquals(2, girl2.getId().longValue());
Girl4 girl3 = boy3.girl;
assertEquals(3, girl3.getId().longValue());
dbSession.closeSession();
}
use of jodd.db.DbThreadSession in project jodd by oblac.
the class DbNoTableTest method testMappingNoTable.
@Test
public void testMappingNoTable() {
DbSession session = new DbThreadSession(cp);
assertEquals(1, DbEntitySql.insert(new Girl(1, "Anna", "swim")).query().autoClose().executeUpdate());
assertEquals(0, session.getTotalQueries());
// one
DbOomQuery q = new DbOomQuery(DbSqlBuilder.sql("select $C{g.id} + 10, UCASE($C{g.name}) from $T{Girl g}"));
Object[] row = q.find(Integer.class, String.class);
assertEquals(Integer.valueOf(11), row[0]);
assertEquals("ANNA", row[1]);
// two
DbSqlBuilder dbSqlBuilder = DbSqlBuilder.sql("select $g.id + 10 as $C{Bean1.sum}, UCASE($g.name) as $C{Bean1.bigName}, $C{g.*} from $T{Girl g} where $g.id=1").aliasColumnsAs(ColumnAliasType.COLUMN_CODE);
assertEquals("select g.ID + 10 as col_0_, UCASE(g.NAME) as col_1_, g.ID as col_2_, g.NAME as col_3_, g.SPECIALITY as col_4_ from GIRL g where g.ID=1", dbSqlBuilder.generateQuery());
dbSqlBuilder.resetAll();
assertEquals("select g.ID + 10 as Bean1$SUM, UCASE(g.NAME) as Bean1$BIG_NAME, g.ID, g.NAME, g.SPECIALITY from GIRL g where g.ID=1", dbSqlBuilder.generateQuery());
dbSqlBuilder.reset();
q = new DbOomQuery(dbSqlBuilder);
row = q.find(Bean1.class, Girl.class);
Bean1 bean1 = (Bean1) row[0];
Girl girl = (Girl) row[1];
assertNotNull(bean1);
assertEquals(11, bean1.getSum().intValue());
assertEquals("ANNA", bean1.getBigName());
assertNotNull(girl);
assertEquals("Anna", girl.name);
assertEquals("swim", girl.speciality);
assertEquals(1, girl.id);
// three
dbSqlBuilder = DbSqlBuilder.sql("select $g.id + 10 as $C{Bean1.sum}, UCASE($g.name) as $C{Bean1.bigName}, $C{g.*} from $T{Girl g} where $g.id=1");
assertEquals("select g.ID + 10 as Bean1$SUM, UCASE(g.NAME) as Bean1$BIG_NAME, g.ID, g.NAME, g.SPECIALITY from GIRL g where g.ID=1", dbSqlBuilder.generateQuery());
dbSqlBuilder.reset();
q = new DbOomQuery(dbSqlBuilder);
row = q.find(Bean1.class, Girl.class);
bean1 = (Bean1) row[0];
girl = (Girl) row[1];
assertNotNull(bean1);
assertEquals(11, bean1.getSum().intValue());
assertEquals("ANNA", bean1.getBigName());
assertNotNull(girl);
assertEquals("Anna", girl.name);
assertEquals("swim", girl.speciality);
assertEquals(1, girl.id);
session.closeSession();
}
use of jodd.db.DbThreadSession 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();
}
Aggregations