|
1
34 package smallsql.junit;
35
36 import java.sql.*;
37
38
41 public class TestOther extends BasicTestCase {
42
43 public void testInsertSelect() throws Exception  {
44 Connection con = AllTests.getConnection();
45 try{
46 con.createStatement().execute("Create Table InsertSelect (i counter, v varchar(20))");
47 assertEqualsRsValue( new Integer (0), "Select count(*) from InsertSelect");
48
49 con.createStatement().execute("Insert Into InsertSelect(v) Values('qwert')");
50 assertEqualsRsValue( new Integer (1), "Select count(*) from InsertSelect");
51
52 con.createStatement().execute("Insert Into InsertSelect(v) Select v From InsertSelect");
53 assertEqualsRsValue( new Integer (2), "Select count(*) from InsertSelect");
54
55 con.createStatement().execute("Insert Into InsertSelect(v) (Select v From InsertSelect)");
56 assertEqualsRsValue( new Integer (4), "Select count(*) from InsertSelect");
57 }finally{
58 dropTable( con, "InsertSelect" );
59 }
60 }
61
62
63 public void testDistinct() throws Exception {
64 Connection con = AllTests.getConnection();
65 try{
66 con.createStatement().execute("Create Table TestDistinct (i counter, v varchar(20), n bigint, b boolean)");
67 assertRowCount( 0, "Select * From TestDistinct" );
68
69 con.createStatement().execute("Insert Into TestDistinct(v,b) Values('qwert1',true)");
70 con.createStatement().execute("Insert Into TestDistinct(v,b) Values('qwert2',true)");
71 con.createStatement().execute("Insert Into TestDistinct(v,b) Values('qwert1',true)");
72 con.createStatement().execute("Insert Into TestDistinct(v,b) Values('qwert2',true)");
73 con.createStatement().execute("Insert Into TestDistinct(v,b) Values('qwert1',false)");
74 assertRowCount( 5, "Select b,n,v From TestDistinct" );
75 assertRowCount( 2, "Select Distinct v From TestDistinct t1" );
76 assertRowCount( 3, "Select Distinct b,n,v From TestDistinct" );
77 assertRowCount( 3, "Select Distinct b,n,v,i+null,23+i-i,'asdf'+v From TestDistinct" );
78 assertRowCount( 5, "Select All b,n,v From TestDistinct" );
79 }finally{
80 dropTable( con, "TestDistinct" );
81 }
82 }
83
84
85 public void testConstantAndRowPos() throws Exception {
86 assertRowCount( 1, "Select 12, 'qwert'" );
87 }
88
89
90 public void testNoFromResult() throws Exception {
91 Connection con = AllTests.getConnection();
92
93 Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY );
94 ResultSet rs = st.executeQuery("Select 12, 'qwert' alias");
95
96 assertRSMetaData( rs, new String []{"col1", "alias"}, new int[]{Types.INTEGER, Types.VARCHAR });
97
98 assertTrue( rs.isBeforeFirst() );
99 assertFalse( rs.isFirst() );
100 assertFalse( rs.isLast() );
101 assertFalse( rs.isAfterLast() );
102
103 assertTrue( rs.next() );
104 assertFalse( rs.isBeforeFirst() );
105 assertTrue( rs.isFirst() );
106 assertTrue( rs.isLast() );
107 assertFalse( rs.isAfterLast() );
108
109 assertFalse( rs.next() );
110 assertFalse( rs.isBeforeFirst() );
111 assertFalse( rs.isFirst() );
112 assertFalse( rs.isLast() );
113 assertTrue( rs.isAfterLast() );
114
115 assertTrue( rs.previous() );
116 assertFalse( rs.isBeforeFirst() );
117 assertTrue( rs.isFirst() );
118 assertTrue( rs.isLast() );
119 assertFalse( rs.isAfterLast() );
120
121 assertFalse( rs.previous() );
122 assertTrue( rs.isBeforeFirst() );
123 assertFalse( rs.isFirst() );
124 assertFalse( rs.isLast() );
125 assertFalse( rs.isAfterLast() );
126
127 assertTrue( rs.first() );
128 assertFalse( rs.isBeforeFirst() );
129 assertTrue( rs.isFirst() );
130 assertTrue( rs.isLast() );
131 assertFalse( rs.isAfterLast() );
132
133 assertTrue( rs.last() );
134 assertFalse( rs.isBeforeFirst() );
135 assertTrue( rs.isFirst() );
136 assertTrue( rs.isLast() );
137 assertFalse( rs.isAfterLast() );
138 }
139
140
141 public void testInSelect() throws Exception {
142 Connection con = AllTests.getConnection();
143 try{
144 con.createStatement().execute("Create Table TestInSelect (i counter, v varchar(20), n bigint, b boolean)");
145 assertRowCount( 0, "Select * From TestInSelect WHere i In (Select i from TestInSelect)" );
146
147 con.createStatement().execute("Insert Into TestInSelect(v,b) Values('qwert1',true)");
148 assertRowCount( 1, "Select * From TestInSelect WHere i In (Select i from TestInSelect)" );
149
150 con.createStatement().execute("Insert Into TestInSelect(v,b) Values('qwert1',true)");
151 assertRowCount( 2, "Select * From TestInSelect WHere i In (Select i from TestInSelect)" );
152 assertRowCount( 1, "Select * From TestInSelect WHere i In (Select i from TestInSelect Where i>1)" );
153 assertRowCount( 1, "Select * From TestInSelect Where i IN ( 1, 1, 12345, 987654321)" );
154 assertRowCount( 2, "Select * From TestInSelect Where v IN ( null, '', 'qwert1', 'qwert1')" );
155 assertRowCount( 2, "Select * From TestInSelect Where v IN ( 'qwert1')" );
156 assertRowCount( 0, "Select * From TestInSelect Where '' IN ( 'qwert1')" );
157 assertRowCount( 2, "Select * From TestInSelect Where 'qwert1' IN ( 'qwert1', 'qwert2')" );
158 }finally{
159 dropTable( con, "TestInSelect" );
160 }
161 }
162
163
164 public void testSetTransaction() throws Exception {
165 Connection con = AllTests.getConnection();
166 try{
167 con.createStatement().execute("Set Transaction Isolation Level Read Uncommitted");
168 assertEquals( Connection.TRANSACTION_READ_UNCOMMITTED, con.getTransactionIsolation() );
169
170 con.createStatement().execute("Set Transaction Isolation Level Read Committed");
171 assertEquals( Connection.TRANSACTION_READ_COMMITTED, con.getTransactionIsolation() );
172
173 con.createStatement().execute("Set Transaction Isolation Level Repeatable Read");
174 assertEquals( Connection.TRANSACTION_REPEATABLE_READ, con.getTransactionIsolation() );
175
176 con.createStatement().execute("Set Transaction Isolation Level Serializable");
177 assertEquals( Connection.TRANSACTION_SERIALIZABLE, con.getTransactionIsolation() );
178
179 }finally{
180 con.setTransactionIsolation( Connection.TRANSACTION_READ_COMMITTED );
181 }
182 }
183
184
185 public void testCreateDropDatabases() throws Exception {
186 Connection con = DriverManager.getConnection("jdbc:smallsql");
187
188 Statement st = con.createStatement();
189 try{
190 st.execute("Create Database anyTestDatabase");
191 }catch(SQLException ex){
192 st.execute("Drop Database anyTestDatabase");
193 throw ex;
194 }
195 st.execute("Drop Database anyTestDatabase");
196 }
197
198
199 public void testManyColumns() throws Exception {
200 Connection con = AllTests.getConnection();
201 Statement st = con.createStatement();
202 dropTable( con, "ManyCols" );
203 StringBuffer buf = new StringBuffer ("Create Table ManyCols(");
204 for(int i=1; i<300; i++){
205 if(i!=1)buf.append(',');
206 buf.append("column").append(i).append(" int");
207 }
208 buf.append(')');
209
210 st.execute(buf.toString());
211 con.close();
212 con = AllTests.getConnection();
213 st = con.createStatement();
214 assertEquals(1,st.executeUpdate("Insert Into ManyCols(column260) Values(123456)"));
215 st.execute("Drop Table ManyCols");
216 }
217
218 }
219 |
Popular Tags |
Java API By Example, From Geeks To Geeks.