DbMiscTest.java :  » Development » jodd » jodd » db » Java Open Source

// Copyright (c) 2003-2007, Jodd Team (jodd.sf.net). All Rights Reserved.

package jodd.db;

import jodd.db.connection.ConnectionProvider;
import jodd.db.pool.CoreConnectionPool;
import junit.framework.TestCase;

import java.sql.ResultSet;

public class DbMiscTest extends TestCase {

  ConnectionProvider cp;

  @Override
  protected void setUp() throws Exception {
    super.setUp();
    //noinspection NonConstantStringShouldBeStringBuffer
    String dbFile = System.getProperty("prj.build.dir");
    if (dbFile == null) {
      dbFile = "";
    }
    dbFile += "/db/jodd-db";
    CoreConnectionPool pool = new CoreConnectionPool();
    pool.setDriver("org.hsqldb.jdbcDriver");
    pool.setUrl("jdbc:hsqldb:" + dbFile);
    pool.setUser("sa");
    pool.setPassword("");
    pool.init();
    cp = pool;
  }


  @Override
  protected void tearDown() throws Exception {
    super.tearDown();
    cp.close();
  }

  public void testBig() throws Exception {
    DbSession session = new DbSession(cp);

    // initial data
    String sql = "create table GIRLS (" +
               "ID      integer    not null," +
               "NAME    varchar(20)  not null," +
               "SPECIALITY  varchar(20)  null," +
               "primary key (ID)" +
              ')';

    DbQuery query = new DbQuery(session, sql);
    query.executeUpdate();
    query.close();
    assertTrue(query.isClosed());

    query = new DbQuery(session, "select count(*) from GIRLS");
    assertEquals(0, query.executeCount());
    assertEquals(1, new DbQuery(session, "insert into GIRLS values(1, 'Anna', 'seduction')").executeUpdate());
    assertEquals(1, query.executeCount());
    assertEquals(1, new DbQuery(session, "insert into GIRLS values(2, 'Sandra', 'spying')").executeUpdate());
    assertEquals(2, query.executeCount());
    assertEquals(1, new DbQuery(session, "insert into GIRLS values(3, 'Monica', 'hacking')").executeUpdate());
    assertEquals(3, query.executeCount());
    assertEquals(0, query.getOpenResultSetCount());
    assertEquals(0, DbQuery.totalOpenResultSetCount);
    query.close();


    //  play with the query

    sql = "select * from GIRLS where ID = :id";
    query = new DbProfiledQuery(session, sql, new DbQueryMode().setDebug(true));
    query.setInteger("id", 2);
    ResultSet rs = query.execute();
    assertEquals(1, query.getOpenResultSetCount());
    assertEquals(1, DbQuery.totalOpenResultSetCount);

    assertEquals("select * from GIRLS where ID = 2\nExecution time: ", query.getQueryString().substring(0, 49));
    while (rs.next()) {
      assertEquals(2, rs.getInt(1));
      assertEquals("Sandra", rs.getString(2));
      assertEquals("spying", rs.getString(3));
    }
    assertFalse(query.isClosed());
    session.closeSession();
    assertTrue(query.isClosed());
    assertEquals(0, query.getOpenResultSetCount());
    assertEquals(0, DbQuery.totalOpenResultSetCount);



    // thread dbsession

    DbSession dbts = new DbThreadSession(cp);
    DbQuery q = new DbQuery("select count(*) from GIRLS");
    assertEquals(3, q.executeCount());
    dbts.closeSession();

    assertNull(DbThreadSession.getCurrentSession()); 

    // transaction example

    DbSession session1 = new DbSession(cp);
    DbSession session2 = new DbSession(cp);

    session1.beginTransaction(new DbTransactionMode().setReadOnly(false));
    query = new DbQuery(session1, "insert into GIRLS values(4, 'Jeniffer', 'fighting')");
    assertEquals(1, query.executeUpdate());
    query.close();

    DbQuery query2 = new DbQuery(session2, "select count(*) from GIRLS");
    assertEquals(0, query2.getOpenResultSetCount());
    assertEquals(0, DbQuery.totalOpenResultSetCount);

    rs = query2.execute();
    if (rs.next()) {
      // count before rollback (READ_UNCOMMITTED isolation level)
      assertEquals(4, rs.getInt(1));
    }
    assertEquals(1, query2.getOpenResultSetCount());
    assertEquals(1, DbQuery.totalOpenResultSetCount);

//    // HSQLDB supports transactions at the READ_UNCOMMITTED level, also known
//    // as level 0 transaction isolation.  This means that during the lifetime of
//    // a transaction, other connections to the database can see the changes made
//    // to the data
//
    session1.rollbackTransaction();

    rs = query2.execute();
    assertEquals(2, query2.getOpenResultSetCount());
    assertEquals(2, DbQuery.totalOpenResultSetCount);
    if (rs.next()) {
      assertEquals(3, rs.getInt(1));
    }

    session2.closeSession();
    assertEquals(0, query2.getOpenResultSetCount());
    assertEquals(0, DbQuery.totalOpenResultSetCount);
  
    session1.closeSession();

  }
}