001: // Copyright (c) 2003-2007, Jodd Team (jodd.sf.net). All Rights Reserved.
002:
003: package jodd.db;
004:
005: import jodd.db.connection.ConnectionProvider;
006: import jodd.db.pool.CoreConnectionPool;
007: import junit.framework.TestCase;
008:
009: import java.sql.ResultSet;
010:
011: public class DbMiscTest extends TestCase {
012:
013: ConnectionProvider cp;
014:
015: @Override
016: protected void setUp() throws Exception {
017: super .setUp();
018: //noinspection NonConstantStringShouldBeStringBuffer
019: String dbFile = System.getProperty("prj.build.dir");
020: if (dbFile == null) {
021: dbFile = "";
022: }
023: dbFile += "/db/jodd-db";
024: CoreConnectionPool pool = new CoreConnectionPool();
025: pool.setDriver("org.hsqldb.jdbcDriver");
026: pool.setUrl("jdbc:hsqldb:" + dbFile);
027: pool.setUser("sa");
028: pool.setPassword("");
029: pool.init();
030: cp = pool;
031: }
032:
033: @Override
034: protected void tearDown() throws Exception {
035: super .tearDown();
036: cp.close();
037: }
038:
039: public void testBig() throws Exception {
040: DbSession session = new DbSession(cp);
041:
042: // initial data
043: String sql = "create table GIRLS (" + "ID integer not null,"
044: + "NAME varchar(20) not null,"
045: + "SPECIALITY varchar(20) null," + "primary key (ID)"
046: + ')';
047:
048: DbQuery query = new DbQuery(session, sql);
049: query.executeUpdate();
050: query.close();
051: assertTrue(query.isClosed());
052:
053: query = new DbQuery(session, "select count(*) from GIRLS");
054: assertEquals(0, query.executeCount());
055: assertEquals(1, new DbQuery(session,
056: "insert into GIRLS values(1, 'Anna', 'seduction')")
057: .executeUpdate());
058: assertEquals(1, query.executeCount());
059: assertEquals(1, new DbQuery(session,
060: "insert into GIRLS values(2, 'Sandra', 'spying')")
061: .executeUpdate());
062: assertEquals(2, query.executeCount());
063: assertEquals(1, new DbQuery(session,
064: "insert into GIRLS values(3, 'Monica', 'hacking')")
065: .executeUpdate());
066: assertEquals(3, query.executeCount());
067: assertEquals(0, query.getOpenResultSetCount());
068: assertEquals(0, DbQuery.totalOpenResultSetCount);
069: query.close();
070:
071: // play with the query
072:
073: sql = "select * from GIRLS where ID = :id";
074: query = new DbProfiledQuery(session, sql, new DbQueryMode()
075: .setDebug(true));
076: query.setInteger("id", 2);
077: ResultSet rs = query.execute();
078: assertEquals(1, query.getOpenResultSetCount());
079: assertEquals(1, DbQuery.totalOpenResultSetCount);
080:
081: assertEquals(
082: "select * from GIRLS where ID = 2\nExecution time: ",
083: query.getQueryString().substring(0, 49));
084: while (rs.next()) {
085: assertEquals(2, rs.getInt(1));
086: assertEquals("Sandra", rs.getString(2));
087: assertEquals("spying", rs.getString(3));
088: }
089: assertFalse(query.isClosed());
090: session.closeSession();
091: assertTrue(query.isClosed());
092: assertEquals(0, query.getOpenResultSetCount());
093: assertEquals(0, DbQuery.totalOpenResultSetCount);
094:
095: // thread dbsession
096:
097: DbSession dbts = new DbThreadSession(cp);
098: DbQuery q = new DbQuery("select count(*) from GIRLS");
099: assertEquals(3, q.executeCount());
100: dbts.closeSession();
101:
102: assertNull(DbThreadSession.getCurrentSession());
103:
104: // transaction example
105:
106: DbSession session1 = new DbSession(cp);
107: DbSession session2 = new DbSession(cp);
108:
109: session1.beginTransaction(new DbTransactionMode()
110: .setReadOnly(false));
111: query = new DbQuery(session1,
112: "insert into GIRLS values(4, 'Jeniffer', 'fighting')");
113: assertEquals(1, query.executeUpdate());
114: query.close();
115:
116: DbQuery query2 = new DbQuery(session2,
117: "select count(*) from GIRLS");
118: assertEquals(0, query2.getOpenResultSetCount());
119: assertEquals(0, DbQuery.totalOpenResultSetCount);
120:
121: rs = query2.execute();
122: if (rs.next()) {
123: // count before rollback (READ_UNCOMMITTED isolation level)
124: assertEquals(4, rs.getInt(1));
125: }
126: assertEquals(1, query2.getOpenResultSetCount());
127: assertEquals(1, DbQuery.totalOpenResultSetCount);
128:
129: // // HSQLDB supports transactions at the READ_UNCOMMITTED level, also known
130: // // as level 0 transaction isolation. This means that during the lifetime of
131: // // a transaction, other connections to the database can see the changes made
132: // // to the data
133: //
134: session1.rollbackTransaction();
135:
136: rs = query2.execute();
137: assertEquals(2, query2.getOpenResultSetCount());
138: assertEquals(2, DbQuery.totalOpenResultSetCount);
139: if (rs.next()) {
140: assertEquals(3, rs.getInt(1));
141: }
142:
143: session2.closeSession();
144: assertEquals(0, query2.getOpenResultSetCount());
145: assertEquals(0, DbQuery.totalOpenResultSetCount);
146:
147: session1.closeSession();
148:
149: }
150: }
|