KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > jodd > db > DbMiscTest


1 // Copyright (c) 2003-2007, Jodd Team (jodd.sf.net). All Rights Reserved.
2
3 package jodd.db;
4
5 import jodd.db.connection.ConnectionProvider;
6 import jodd.db.pool.CoreConnectionPool;
7 import junit.framework.TestCase;
8
9 import java.sql.ResultSet JavaDoc;
10
11 public class DbMiscTest extends TestCase {
12
13     ConnectionProvider cp;
14
15     protected void setUp() throws Exception JavaDoc {
16         super.setUp();
17         //noinspection NonConstantStringShouldBeStringBuffer
18 String JavaDoc dbFile = System.getProperty("prj.build.dir");
19         if (dbFile == null) {
20             dbFile = "";
21         }
22         dbFile += "/db/jodd-db";
23         CoreConnectionPool pool = new CoreConnectionPool();
24         pool.setDriver("org.hsqldb.jdbcDriver");
25         pool.setUrl("jdbc:hsqldb:" + dbFile);
26         pool.setUser("sa");
27         pool.setPassword("");
28         pool.init();
29         cp = pool;
30     }
31
32
33     protected void tearDown() throws Exception JavaDoc {
34         super.tearDown();
35         cp.close();
36     }
37
38     public void testBig() throws Exception JavaDoc {
39         DbSession session = new DbSession(cp);
40
41         // initial data
42 String JavaDoc sql = "create table GIRLS (" +
43                            "ID integer not null," +
44                            "NAME varchar(20) not null," +
45                            "SPECIALITY varchar(20) null," +
46                            "primary key (ID)" +
47                             ')';
48
49         DbQuery query = new DbQuery(session, sql);
50         query.executeUpdate();
51         assertTrue(query.isClosed());
52
53         query = new DbQuery(session, "select count(*) from GIRLS");
54         assertEquals(0, query.executeCount());
55         assertEquals(1, new DbQuery(session, "insert into GIRLS values(1, 'Anna', 'seduction')").executeUpdate());
56         assertEquals(1, query.executeCount());
57         assertEquals(1, new DbQuery(session, "insert into GIRLS values(2, 'Sandra', 'spying')").executeUpdate());
58         assertEquals(2, query.executeCount());
59         assertEquals(1, new DbQuery(session, "insert into GIRLS values(3, 'Monica', 'hacking')").executeUpdate());
60         assertEquals(3, query.executeCount());
61         assertEquals(0, query.getOpenResultSetCount());
62         assertEquals(0, DbQuery.totalOpenResultSetCount);
63         query.close();
64
65
66         // play with the query
67
68         sql = "select * from GIRLS where ID = :id";
69         query = new DbProfiledQuery(session, sql, new DbQueryMode().setDebug(true));
70         query.setInteger("id", 2);
71         ResultSet JavaDoc rs = query.execute();
72         assertEquals(1, query.getOpenResultSetCount());
73         assertEquals(1, DbQuery.totalOpenResultSetCount);
74
75         assertEquals("select * from GIRLS where ID = 2\nExecution time: ", query.getQueryString().substring(0, 49));
76         while (rs.next()) {
77             assertEquals(2, rs.getInt(1));
78             assertEquals("Sandra", rs.getString(2));
79             assertEquals("spying", rs.getString(3));
80         }
81         assertFalse(query.isClosed());
82         session.closeSession();
83         assertTrue(query.isClosed());
84         assertEquals(0, query.getOpenResultSetCount());
85         assertEquals(0, DbQuery.totalOpenResultSetCount);
86
87
88
89         // thread dbsession
90
91         DbSession dbts = new DbThreadSession(cp);
92         DbQuery q = new DbQuery("select count(*) from GIRLS");
93         assertEquals(3, q.executeCount());
94         dbts.closeSession();
95
96         assertNull(DbThreadSession.getCurrentSession());
97
98         // transaction example
99
100         DbSession session1 = new DbSession(cp);
101         DbSession session2 = new DbSession(cp);
102
103         session1.beginTransaction(new DbTransactionMode().setReadOnly(false));
104         query = new DbQuery(session1, "insert into GIRLS values(4, 'Jeniffer', 'fighting')");
105         assertEquals(1, query.executeUpdate());
106         query.close();
107
108         DbQuery query2 = new DbQuery(session2, "select count(*) from GIRLS");
109         assertEquals(0, query2.getOpenResultSetCount());
110         assertEquals(0, DbQuery.totalOpenResultSetCount);
111
112         rs = query2.execute();
113         if (rs.next()) {
114             // count before rollback (READ_UNCOMMITTED isolation level)
115 assertEquals(4, rs.getInt(1));
116         }
117         assertEquals(1, query2.getOpenResultSetCount());
118         assertEquals(1, DbQuery.totalOpenResultSetCount);
119
120 // // HSQLDB supports transactions at the READ_UNCOMMITTED level, also known
121 // // as level 0 transaction isolation. This means that during the lifetime of
122 // // a transaction, other connections to the database can see the changes made
123 // // to the data
124 //
125 session1.rollbackTransaction();
126
127         rs = query2.execute();
128         assertEquals(2, query2.getOpenResultSetCount());
129         assertEquals(2, DbQuery.totalOpenResultSetCount);
130         if (rs.next()) {
131             assertEquals(3, rs.getInt(1));
132         }
133
134         session2.closeSession();
135         assertEquals(0, query2.getOpenResultSetCount());
136         assertEquals(0, DbQuery.totalOpenResultSetCount);
137     
138         session1.closeSession();
139
140     }
141 }
142
Popular Tags