TestTransactions


1   /* =============================================================
2    * SmallSQL : a free Java DBMS library for the Java(tm) platform
3    * =============================================================
4    *
5    * (C) Copyright 2004-2006, by Volker Berlin.
6    *
7    * Project Info:  http://www.smallsql.de/
8    *
9    * This library is free software; you can redistribute it and/or modify it 
10   * under the terms of the GNU Lesser General Public License as published by 
11   * the Free Software Foundation; either version 2.1 of the License, or 
12   * (at your option) any later version.
13   *
14   * This library is distributed in the hope that it will be useful, but 
15   * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY 
16   * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public 
17   * License for more details.
18   *
19   * You should have received a copy of the GNU Lesser General Public
20   * License along with this library; if not, write to the Free Software
21   * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, 
22   * USA.  
23   *
24   * [Java is a trademark or registered trademark of Sun Microsystems, Inc. 
25   * in the United States and other countries.]
26   *
27   * ---------------
28   * TestTransactions.java
29   * ---------------
30   * Author: Volker Berlin
31   * 
32   * Created on 05.08.2004
33   */
34  package smallsql.junit;
35  import java.sql.*;
36  
37  /**
38   * @author Volker Berlin
39   */
40  public class TestTransactions extends BasicTestCase {
41  
42      
43      public void testCreateTable() throws Exception  {
44          Connection con = AllTests.getConnection();
45          try{            
46              con.setAutoCommit(false);
47              con.createStatement().execute("create table transactions (ID  INTEGER NOT NULL, Name VARCHAR(100), FirstName VARCHAR(100), Points INTEGER, LicenseID INTEGER, PRIMARY KEY(ID))");
48              con.commit();
49  
50              Connection con2 = AllTests.createConnection();
51              con2.setAutoCommit(false);
52              
53              
54              PreparedStatement pr = con2.prepareStatement("insert into transactions (id,Name,FirstName,Points,LicenseID) values (?,?,?,?,?)");
55              pr.setInt(      1, 0 );
56              pr.setString(   2, "Pilot_1" );
57              pr.setString(   3, "Herkules" );
58              pr.setInt(      4, 1 );
59              pr.setInt(      5, 1 );
60              pr.addBatch();
61              pr.executeBatch();
62  
63              assertRowCount( 0, "Select * from transactions");
64              con2.commit();
65              assertRowCount( 1, "Select * from transactions");
66              
67              con2.close();
68          }finally{
69              try{
70                  con.createStatement().execute("Drop Table transactions");
71              }catch(Throwable   e){e.printStackTrace();}
72              con.setAutoCommit(true);
73          }
74      }
75      
76  
77  
78      
79      public void testCommit() throws Exception  {
80          Connection con = AllTests.getConnection();
81          try{            
82              con.setAutoCommit(false);
83              con.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
84              assertRowCount( 0, "Select * from transactions");
85  
86              con.createStatement().execute("Insert Into transactions(v) Values('qwert')");
87              assertRowCount( 1, "Select * from transactions");
88  
89              con.createStatement().execute("Insert Into transactions(v) Select v From transactions");
90              assertRowCount( 2, "Select * from transactions");
91  
92              con.createStatement().execute("Insert Into transactions Select * From transactions");
93              assertRowCount( 4, "Select * from transactions");
94              
95              con.commit();
96              assertRowCount( 4, "Select * from transactions");
97              
98          }finally{
99              try{
100                 con.createStatement().execute("Drop Table transactions");
101             }catch(Throwable   e){e.printStackTrace();}
102             con.setAutoCommit(true);
103         }
104     }
105     
106 
107     /**
108      * In the table there is already one row that is commited.
109      */
110     public void testCommitWithOneCommitRow() throws Exception  {
111         Connection con = AllTests.getConnection();
112         try{            
113             con.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
114             assertRowCount( 0, "Select * from transactions");
115 
116             con.createStatement().execute("Insert Into transactions(v) Values('qwert')");
117             assertRowCount( 1, "Select * from transactions");
118 
119             con.setAutoCommit(false);
120             con.createStatement().execute("Insert Into transactions(v) Select v From transactions");
121             assertRowCount( 2, "Select * from transactions");
122 
123             con.createStatement().execute("Insert Into transactions (Select * From transactions)");
124             assertRowCount( 4, "Select * from transactions");
125             
126             con.commit();
127             assertRowCount( 4, "Select * from transactions");
128             
129         }finally{
130             try{
131                 con.createStatement().execute("Drop Table transactions");
132             }catch(Throwable   e){e.printStackTrace();}
133             con.setAutoCommit(true);
134         }
135     }
136     
137 
138     public void testRollback() throws Exception  {
139         Connection con = AllTests.getConnection();
140         try{            
141             con.setAutoCommit(false);
142             con.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
143             assertRowCount( 0, "Select * from transactions");
144 
145             con.createStatement().execute("Insert Into transactions(v) Values('qwert')");
146             assertRowCount( 1, "Select * from transactions");
147 
148             con.createStatement().execute("Insert Into transactions(v) Select v From transactions");
149             assertRowCount( 2, "Select * from transactions");
150 
151             con.createStatement().execute("Insert Into transactions(v) (Select v From transactions)");
152             assertRowCount( 4, "Select * from transactions");
153             
154             con.rollback();
155             assertRowCount( 0, "Select * from transactions");
156             
157         }finally{
158             try{
159                 con.createStatement().execute("Drop Table transactions");
160             }catch(Throwable   e){e.printStackTrace();}
161             con.setAutoCommit(true);
162         }
163     }
164 
165 
166     /**
167      * In the table there is already one row that is commited.
168      */
169     public void testRollbackWithOneCommitRow() throws Exception  {
170         Connection con = AllTests.getConnection();
171         try{            
172             con.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
173             assertRowCount( 0, "Select * from transactions");
174 
175             con.createStatement().execute("Insert Into transactions(v) Values('qwert')");
176             assertRowCount( 1, "Select * from transactions");
177 
178             con.setAutoCommit(false);
179             con.createStatement().execute("Insert Into transactions(v) Select v From transactions");
180             assertRowCount( 2, "Select * from transactions");
181 
182             con.createStatement().execute("Insert Into transactions(v) (Select v From transactions)");
183             assertRowCount( 4, "Select * from transactions");
184             
185             con.rollback();
186             assertRowCount( 1, "Select * from transactions");
187             
188         }finally{
189             try{
190                 con.createStatement().execute("Drop Table transactions");
191             }catch(Throwable   e){e.printStackTrace();}
192             con.setAutoCommit(true);
193         }
194     }
195 
196 
197     private void testInsertRow_Last(Connection con, boolean callLastBefore) throws Exception  {
198         try{            
199             con.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
200             assertRowCount( 0, "Select * from transactions");
201 
202             con.createStatement().execute("Insert Into transactions(v) Values('qwert')");
203 
204             ResultSet rs = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
205                                 .executeQuery("Select * from transactions Where 1=0");
206             
207             if(callLastBefore) rs.last();
208             rs.moveToInsertRow();
209             rs.updateString("v", "qwert2");
210             rs.insertRow();
211             
212             rs.last();
213             assertEquals("qwert2", rs.getString("v"));
214             assertFalse( rs.next() );
215             assertTrue( rs.previous() );
216             assertEquals("qwert2", rs.getString("v"));
217             
218             rs.beforeFirst();
219             assertTrue( rs.next() );
220             assertEquals("qwert2", rs.getString("v"));
221             assertFalse( rs.next() );
222 
223         }finally{
224             try{
225                 con.createStatement().execute("Drop Table transactions");
226             }catch(Throwable   e){e.printStackTrace();}
227         }
228     }
229     
230     
231     public void testInsertRow_Last() throws Exception  {
232         Connection con = AllTests.getConnection();
233         testInsertRow_Last(con, false);
234         testInsertRow_Last(con, true);
235         con.setAutoCommit(false);
236         testInsertRow_Last(con, false);
237         con.setAutoCommit(true);
238         con.setAutoCommit(false);
239         testInsertRow_Last(con, true);
240         con.setAutoCommit(true);
241     }
242 
243     
244     public void testInsertAndUpdate() throws Exception  {
245         Connection con = AllTests.getConnection();
246         try{            
247             con.setAutoCommit(false);
248             con.createStatement().execute("Create Table transactions ( v varchar(20))");
249             assertRowCount( 0, "Select * from transactions");
250 
251             assertEquals( 1, con.createStatement().executeUpdate("Insert Into transactions(v) Values('qwert')") );
252             assertEqualsRsValue("qwert", "Select * from transactions");
253             assertEqualsRsValue(new Integer  (1), "Select count(*) from transactions");
254             
255             assertEquals( 1, con.createStatement().executeUpdate("Update transactions set v='qwert2'") );
256             assertEqualsRsValue("qwert2", "Select * from transactions");
257             assertEqualsRsValue(new Integer  (1), "Select count(*) from transactions");
258             
259             Savepoint savepoint = con.setSavepoint();
260             
261             assertEquals( 1, con.createStatement().executeUpdate("Update transactions set v='qwert 3'") );
262             assertEqualsRsValue("qwert 3", "Select * from transactions");
263             assertEqualsRsValue(new Integer  (1), "Select count(*) from transactions");
264 
265             con.rollback( savepoint );
266             
267             con.commit();
268             assertEqualsRsValue("qwert2", "Select * from transactions");
269             assertEqualsRsValue(new Integer  (1), "Select count(*) from transactions");
270         }finally{
271             try{
272                 con.createStatement().execute("Drop Table transactions");
273             }catch(Throwable   e){e.printStackTrace();}
274             con.setAutoCommit(true);
275         }
276     }
277     
278     
279     /**
280      * If there was insert a row within the ResultSet that not map WHERE than you scroll to this row.
281      * If there an Insert outsite the ResultSet that not map the WHERE then you can't scroll this row.
282      * @throws Exception
283      */
284     public void testInsertRow_withWrongWhere() throws Exception  {
285         Connection con = AllTests.getConnection();
286         try{            
287             con.setAutoCommit(false);
288             con.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
289             assertRowCount( 0, "Select * from transactions");
290 
291             con.createStatement().execute("Insert Into transactions(v) Values('qwert')");
292 
293             ResultSet rs = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
294                                 .executeQuery("Select * from transactions Where 1=0");
295             
296             rs.moveToInsertRow();
297             rs.updateString("v", "qwert2");
298             rs.insertRow();
299             
300             rs.beforeFirst();
301             assertTrue( rs.next() );
302             assertEquals("qwert2", rs.getString("v"));
303             assertFalse( rs.next() );
304         }finally{
305             try{
306                 con.createStatement().execute("Drop Table transactions");
307             }catch(Throwable   e){e.printStackTrace();}
308             con.setAutoCommit(true);
309         }
310     }
311     
312     
313 
314     /**
315      * A row that was inserted and commited with a valid WHERE expression should not count 2 times.
316      */
317     public void testInsertRow_withRightWhere() throws Exception  {
318         Connection con = AllTests.getConnection();
319         try{            
320             con.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
321             assertRowCount( 0, "Select * from transactions");
322 
323             con.createStatement().execute("Insert Into transactions(v) Values('qwert2')");
324 
325             ResultSet rs = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
326                                 .executeQuery("Select * from transactions Where v = 'qwert'");
327             
328             rs.moveToInsertRow();
329             rs.updateString("v", "qwert");
330             rs.insertRow();
331             
332             rs.beforeFirst();
333             assertTrue( rs.next() );
334             assertEquals("qwert", rs.getString("v"));
335             assertFalse( rs.next() );
336         }finally{
337             try{
338                 con.createStatement().execute("Drop Table transactions");
339             }catch(Throwable   e){e.printStackTrace();}
340         }
341     }
342     
343     
344     public void testReadUncommited() throws Exception  {
345         Connection con1 = AllTests.getConnection();
346         Connection con2 = AllTests.createConnection();
347         try{        
348             con2.setTransactionIsolation( Connection.TRANSACTION_READ_UNCOMMITTED );
349             con1.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
350             assertRowCount( 0, "Select * from transactions");
351 
352             con1.setAutoCommit(false);
353             con1.createStatement().execute("Insert Into transactions(v) Values('qwert2')");
354 
355             ResultSet rs2 = con2.createStatement().executeQuery("Select count(*) from transactions");
356             assertTrue( rs2.next() );
357             assertEquals( 1, rs2.getInt(1) );
358         }finally{
359             try{
360                 con1.createStatement().execute("Drop Table transactions");
361             }catch(Throwable   e){e.printStackTrace();}
362             con1.setAutoCommit(true);
363             con2.close();
364         }
365     }
366 
367     
368     public void testReadCommited() throws Exception  {
369         Connection con1 = AllTests.getConnection();
370         Connection con2 = AllTests.createConnection();
371         try{        
372             con2.setTransactionIsolation( Connection.TRANSACTION_READ_COMMITTED );
373             con1.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
374             assertRowCount( 0, "Select * from transactions");
375 
376             con1.setAutoCommit(false);
377             con1.createStatement().execute("Insert Into transactions(v) Values('qwert2')");
378 
379             ResultSet rs2 = con2.createStatement().executeQuery("Select count(*) from transactions");
380             assertTrue( rs2.next() );
381             assertEquals( 0, rs2.getInt(1) );
382         }finally{
383             try{
384                 con1.createStatement().execute("Drop Table transactions");
385             }catch(Throwable   e){e.printStackTrace();}
386             con1.setAutoCommit(true);
387             con2.close();
388         }
389     }
390 
391 
392     public void testReadWriteLock() throws Exception  {
393         Connection con1 = AllTests.getConnection();
394         Connection con2 = AllTests.createConnection();
395         try{        
396             con1.createStatement().execute("Create Table transactions (i counter, v varchar(20))");
397             con1.createStatement().execute("Insert Into transactions(v) Values('qwert1')");
398 
399             con1.setAutoCommit(false);
400             con1.createStatement().execute("Update transactions Set v = 'qwert'");
401             
402             long time = System.currentTimeMillis();
403             try{
404                 con2.createStatement().executeQuery("Select count(*) from transactions");
405             }catch(SQLException e){
406                 //TODO error nummer test
407             }       
408             assertTrue("Wait time to small", System.currentTimeMillis()-time>=5000);
409         }finally{
410             try{
411                 con1.createStatement().execute("Drop Table transactions");
412             }catch(Throwable   e){e.printStackTrace();}
413             con1.setAutoCommit(true);
414             con2.close();
415         }
416     }
417 
418 
419 }
420
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags