Examples with DbSession jodd.db.DbSession used on opensource projects

Example 1 with DbSession

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

the class DbJtxResourceManager method beginTransaction.

// ---------------------------------------------------------------- resource manager
/**
	 * {@inheritDoc}
	 */
public DbSession beginTransaction(JtxTransactionMode jtxMode, boolean active) {
    DbSession session = new DbSession(connectionProvider);
    if (active) {
        log.debug("begin jtx");
        session.beginTransaction(JtxDbUtil.convertToDbMode(jtxMode));
    }
    return session;
}

Example 2 with DbSession

use of jodd.db.DbSession 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();
}

Example 3 with DbSession

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

the class GenericDaoTest method testAppDao1.

@Test
public void testAppDao1() {
    DbSession session = new DbSession(cp);
    ThreadDbSessionHolder.set(session);
    GenericDao dao = new GenericDao();
    // save
    Girl girl = new Girl();
    girl.setName("Emma");
    girl.setSpeciality("piano");
    girl.setId(Long.valueOf(1));
    dao.save(girl);
    assertEquals(1, girl.getId().longValue());
    Boy boy1 = new Boy();
    boy1.setId(1);
    boy1.setName("Oleg");
    boy1.setGirlId(1);
    Boy boy2 = new Boy();
    boy2.setId(2);
    boy2.setName("Marco");
    boy2.setGirlId(1);
    ArrayList<Boy> boys = new ArrayList<Boy>();
    boys.add(boy1);
    boys.add(boy2);
    dao.saveAll(boys);
    // find
    Boy dbBoy = dao.findById(Boy.class, 1);
    assertEquals(boy1.getId(), dbBoy.getId());
    assertEquals(boy1.getName(), dbBoy.getName());
    assertEquals(boy1.getGirlId(), dbBoy.getGirlId());
    // update
    girl.setSpeciality("Guitar");
    dao.update(girl);
    long count = dao.count(Girl.class);
    assertEquals(1, count);
    Girl dbGirl = dao.findById(Girl.class, 1);
    assertEquals("Guitar", dbGirl.getSpeciality());
    assertEquals("Emma", dbGirl.getName());
    // update property
    dao.updateProperty(girl, "speciality", "math");
    dbGirl = dao.findById(Girl.class, 1);
    assertEquals("math", dbGirl.getSpeciality());
    assertEquals("math", girl.getSpeciality());
    assertEquals("Emma", dbGirl.getName());
    // add one more girl
    Girl girl2 = new Girl();
    girl2.setName("Lina");
    girl2.setSpeciality("crazy");
    girl2.setId(Long.valueOf(2));
    dao.save(girl2);
    count = dao.count(Girl.class);
    assertEquals(2, count);
    Girl emma = dao.findOneByProperty(Girl.class, "name", "Emma");
    assertNotNull(emma);
    assertEquals(1, emma.getId().longValue());
    Girl none = dao.findOneByProperty(Girl.class, "name", "Www");
    assertNull(none);
    // find by matching
    Girl match = new Girl();
    match.setName("Emma");
    dbGirl = dao.findOne(match);
    assertEquals(emma.getId(), dbGirl.getId());
    assertEquals(emma.getName(), dbGirl.getName());
    assertEquals(emma.getSpeciality(), dbGirl.getSpeciality());
    Boy boyMatch = new Boy();
    boyMatch.setGirlId(1);
    List<Boy> dbBoys = dao.find(boyMatch);
    assertEquals(2, dbBoys.size());
    // find by separate matching class
    boyMatch = new Boy();
    boyMatch.setName("Oleg");
    dbBoys = dao.find(Boy.class, boyMatch);
    assertEquals(1, dbBoys.size());
    // correct way
    BoyCriteria boyCriteria = new BoyCriteria();
    boyCriteria.setName("Oleg");
    dbBoys = dao.find(Boy.class, boyCriteria);
    assertEquals(1, dbBoys.size());
    // list all
    dbBoys = dao.listAll(Boy.class);
    assertEquals(2, dbBoys.size());
    // related
    dbBoys = dao.findRelated(Boy.class, emma);
    assertEquals(2, dbBoys.size());
    // delete
    dao.deleteById(Boy.class, 1);
    dao.deleteById(boy2);
    count = dao.count(Boy.class);
    assertEquals(0, count);
    // delete all
    List<Girl> girls = dao.listAll(Girl.class);
    dao.deleteAllById(girls);
    count = dao.count(Girl.class);
    assertEquals(0, count);
    session.closeSession();
    ThreadDbSessionHolder.remove();
}

Example 4 with DbSession

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

the class LiveMapperDbTest method insertEntry.

protected Tester2 insertEntry() {
    DbSession session = new DbSession();
    Tester2 tester2 = new Tester2();
    tester2.id = 1;
    tester2.name = "Hello";
    tester2.value = Integer.valueOf(123);
    tester2.time = new JDateTime(2014, 1, 30, 10, 42, 34, 0).convertToSqlTimestamp();
    tester2.time2 = new JDateTime(2014, 1, 31, 11, 41, 32, 0);
    DbOomQuery dbOomQuery = DbOomQuery.query(session, DbEntitySql.insert(tester2));
    dbOomQuery.setGeneratedKey();
    int result = dbOomQuery.executeUpdate();
    assertEquals(1, result);
    session.closeSession();
    return tester2;
}

Example 5 with DbSession

use of jodd.db.DbSession 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();
}