Search in sources :

Example 1 with DbThreadSession

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());
}
Also used : DbThreadSession(jodd.db.DbThreadSession) Boy(jodd.db.oom.tst.Boy) Girl2(jodd.db.oom.tst.Girl2) Before(org.junit.Before)

Example 2 with DbThreadSession

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();
}
Also used : DbSession(jodd.db.DbSession) DbThreadSession(jodd.db.DbThreadSession) JDateTime(jodd.datetime.JDateTime) BigDecimal(java.math.BigDecimal) Test(org.junit.Test)

Example 3 with DbThreadSession

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();
}
Also used : DbSession(jodd.db.DbSession) Girl4(jodd.db.oom.tst.Girl4) Boy4(jodd.db.oom.tst.Boy4) DbThreadSession(jodd.db.DbThreadSession) Room(jodd.db.oom.tst.Room) Test(org.junit.Test)

Example 4 with DbThreadSession

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();
}
Also used : DbSession(jodd.db.DbSession) DbSqlBuilder(jodd.db.oom.sqlgen.DbSqlBuilder) DbThreadSession(jodd.db.DbThreadSession) Girl(jodd.db.oom.tst.Girl) Test(org.junit.Test)

Example 5 with DbThreadSession

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

Aggregations

DbThreadSession (jodd.db.DbThreadSession)9 DbSession (jodd.db.DbSession)7 Test (org.junit.Test)7 DbQuery (jodd.db.DbQuery)4 Girl (jodd.db.oom.tst.Girl)3 ResultSet (java.sql.ResultSet)2 DbSqlBuilder (jodd.db.oom.sqlgen.DbSqlBuilder)2 Girl2 (jodd.db.oom.tst.Girl2)2 Before (org.junit.Before)2 BigDecimal (java.math.BigDecimal)1 SQLException (java.sql.SQLException)1 Iterator (java.util.Iterator)1 List (java.util.List)1 Set (java.util.Set)1 JDateTime (jodd.datetime.JDateTime)1 QueryMapper (jodd.db.QueryMapper)1 Boy (jodd.db.oom.tst.Boy)1 Boy2 (jodd.db.oom.tst.Boy2)1 Boy4 (jodd.db.oom.tst.Boy4)1 Enumerator (jodd.db.oom.tst.Enumerator)1