|
1
34 package smallsql.junit;
35
36 import java.sql.*;
37
38
39
43 public class TestStatement extends BasicTestCase {
44
45 private static boolean init;
46
47
48 protected void setUp() throws Exception  {
49 if(init) return;
50 Connection con = AllTests.getConnection();
51 Statement st = con.createStatement();
52 dropTable( con, "statement");
53 st.execute("Create Table statement (c varchar(30), i counter)");
54 init = true;
55 }
56
57
58 public void testBatchUpate() throws Exception {
59 Connection con = AllTests.getConnection();
60 Statement st = con.createStatement();
61
62 assertEquals("Result Length wrong", 0, st.executeBatch().length );
63 st.clearBatch();
64 st.addBatch("Bla Bla");
65 try {
66 st.executeBatch();
67 } catch (BatchUpdateException ex) {
68 assertEquals("Result Length wrong",1,ex.getUpdateCounts().length);
69 }
70 st.clearBatch();
71 int count = 10;
72 for(int i=1; i<=count; i++){
73 st.addBatch("Insert Into statement(c) Values('batch"+i+"')");
74 }
75 int[] result = st.executeBatch();
76 assertEquals("Result Length wrong", count, result.length);
77 for(int i=0; i<count; i++){
78 assertEquals("Update Count", 1, result[i]);
79 }
80 assertRowCount(10, "Select * From statement");
81 }
82
83
84 public void testMultiValues() throws Exception {
85 Connection con = AllTests.getConnection();
86 Statement st = con.createStatement();
87 assertEquals("Update Count:", 10, st.executeUpdate("Insert Into statement(c) Values('abc1'),('abc2'),('abc3'),('abc4'),('abc5'),('abc6'),('abc7'),('abc8'),('abc9'),('abc10')"));
88 }
89
90
91 public void testMaxRows() throws Exception {
92 Connection con = AllTests.getConnection();
93 Statement st = con.createStatement();
94 st.setMaxRows(5);
95 ResultSet rs = st.executeQuery("Select * From statement");
96 assertEquals("Statement.getResultSet", rs, st.getResultSet());
97 assertRowCount(5,rs);
98 assertRowCount(4,"Select top 4 * From statement");
99
100 PreparedStatement pr = con.prepareStatement("Select * From statement");
101 pr.setMaxRows(6);
102 rs = pr.executeQuery();
103 assertEquals("PreparedStatement.getResultSet", rs, pr.getResultSet());
104 assertRowCount(6,rs);
105
106 pr.setMaxRows(3);
107 rs = pr.executeQuery();
108 assertRowCount(3,rs);
109
110 pr.setMaxRows(4);
111 rs = pr.executeQuery();
112 assertRowCount(4,rs);
113 }
114
115
116 public void testMoreResults() throws Exception {
117 Connection con = AllTests.getConnection();
118 Statement st = con.createStatement();
119
120 ResultSet rs = st.executeQuery("Select * From statement");
121 assertEquals( "getResultSet()", rs, st.getResultSet() );
122 assertEquals( "getUpdateCount()", -1, st.getUpdateCount() );
123 assertFalse( st.getMoreResults() );
124 try{
125 rs.next();
126 fail("ResultSet should be closed");
127 }catch(SQLException ex){
128 assertSQLException("01000", 0, ex);
129 }
130 assertNull( "getResultSet()", st.getResultSet() );
131 assertEquals( "getUpdateCount()", -1, st.getUpdateCount() );
132
133
134 rs = st.executeQuery("Select * From statement");
135 assertEquals( "getResultSet()", rs, st.getResultSet() );
136 assertEquals( "getUpdateCount()", -1, st.getUpdateCount() );
137 assertFalse( st.getMoreResults(Statement.KEEP_CURRENT_RESULT) );
138 assertTrue(rs.next());
139 assertNull( "getResultSet()", st.getResultSet() );
140 assertEquals( "getUpdateCount()", -1, st.getUpdateCount() );
141
142
143 int count = st.executeUpdate("Update statement set c = c");
144 assertTrue( "Update Erfolgreich", count>0 );
145 assertNull( "getResultSet()", st.getResultSet() );
146 assertEquals( "getUpdateCount()", count, st.getUpdateCount() );
147 assertFalse( st.getMoreResults() );
148 assertNull( "getResultSet()", st.getResultSet() );
149 assertEquals( "getUpdateCount()", -1, st.getUpdateCount() );
150 }
151
152
153 public void testGetConnection() throws Exception {
154 Connection con = AllTests.getConnection();
155 Statement st = con.createStatement();
156
157 assertEquals(con, st.getConnection() );
158 }
159
160
161 public void testFetch() throws Exception {
162 Connection con = AllTests.getConnection();
163 Statement st = con.createStatement();
164
165 st.setFetchDirection(ResultSet.FETCH_FORWARD);
166 assertEquals( st.getFetchDirection(), ResultSet.FETCH_FORWARD);
167
168 st.setFetchDirection(ResultSet.FETCH_REVERSE);
169 assertEquals( st.getFetchDirection(), ResultSet.FETCH_REVERSE);
170
171 st.setFetchSize(123);
172 assertEquals( st.getFetchSize(), 123);
173 }
174
175
176 public void testGeneratedKeys() throws Exception {
177 Connection con = AllTests.getConnection();
178 Statement st = con.createStatement();
179 ResultSet rs;
180
181 st.execute("Insert Into statement(c) Values('key1')", Statement.NO_GENERATED_KEYS);
182 try{
183 st.getGeneratedKeys();
184 fail("NO_GENERATED_KEYS");
185 }catch(SQLException ex){
186 assertSQLException("01000", 0, ex);
187 }
188 assertEquals("UpdateCount", 1, st.getUpdateCount());
189 assertNull("getResultSet", st.getResultSet());
190
191 st.execute("Insert Into statement(c) Values('key2')", Statement.RETURN_GENERATED_KEYS);
192 rs = st.getGeneratedKeys();
193 assertNotNull("RETURN_GENERATED_KEYS", rs);
194 assertEquals("ColumnCount",1,rs.getMetaData().getColumnCount());
195 assertEquals("ColumnCount","i",rs.getMetaData().getColumnName(1));
196 assertRowCount(1,rs);
197
198 assertEquals(1,st.executeUpdate("Insert Into statement(c) Values('key3')", Statement.RETURN_GENERATED_KEYS));
199 rs = st.getGeneratedKeys();
200 assertNotNull("RETURN_GENERATED_KEYS", rs);
201 assertEquals("ColumnCount",1,rs.getMetaData().getColumnCount());
202 assertEquals("ColumnCount","i",rs.getMetaData().getColumnName(1));
203 assertRowCount(1,rs);
204
205 st.execute("Insert Into statement(c) Values('key4')", new int[]{2,1});
206 rs = st.getGeneratedKeys();
207 assertNotNull("RETURN_GENERATED_KEYS", rs);
208 assertEquals("ColumnCount",2,rs.getMetaData().getColumnCount());
209 assertRowCount(1,rs);
210
211 assertEquals(1,st.executeUpdate("Insert Into statement(c) Values('key5')", new int[]{2}));
212 rs = st.getGeneratedKeys();
213 assertNotNull("RETURN_GENERATED_KEYS", rs);
214 assertEquals("ColumnCount",1,rs.getMetaData().getColumnCount());
215 assertEquals("ColumnCount","i",rs.getMetaData().getColumnName(1));
216 assertRowCount(1,rs);
217
218 st.execute("Insert Into statement(c) Values('key6')", new String []{"c","i"});
219 rs = st.getGeneratedKeys();
220 assertNotNull("RETURN_GENERATED_KEYS", rs);
221 assertEquals("ColumnCount",2,rs.getMetaData().getColumnCount());
222 assertRowCount(1,rs);
223
224 assertEquals(1,st.executeUpdate("Insert Into statement(c) Values('key7')", new String []{"i"}));
225 rs = st.getGeneratedKeys();
226 assertNotNull("RETURN_GENERATED_KEYS", rs);
227 assertEquals("ColumnCount",1,rs.getMetaData().getColumnCount());
228 assertEquals("ColumnCount","i",rs.getMetaData().getColumnName(1));
229 assertRowCount(1,rs);
230 }
231
232
233 public void testGeneratedKeysWithPrepare() throws Exception {
234 Connection con = AllTests.getConnection();
235 ResultSet rs;
236
237 PreparedStatement pr = con.prepareStatement("Insert Into statement(c) Values('key1')", Statement.NO_GENERATED_KEYS);
238 pr.execute();
239 try{
240 pr.getGeneratedKeys();
241 fail("NO_GENERATED_KEYS");
242 }catch(SQLException ex){
243 assertSQLException("01000", 0, ex);
244 }
245 assertEquals("UpdateCount", 1, pr.getUpdateCount());
246 assertNull("getResultSet", pr.getResultSet());
247 pr.close();
248
249 pr = con.prepareStatement("Insert Into statement(c) Values('key2')", Statement.RETURN_GENERATED_KEYS);
250 pr.execute();
251 rs = pr.getGeneratedKeys();
252 assertNotNull("RETURN_GENERATED_KEYS", rs);
253 assertEquals("ColumnCount",1,rs.getMetaData().getColumnCount());
254 assertEquals("ColumnCount","i",rs.getMetaData().getColumnName(1));
255 assertRowCount(1,rs);
256
257 pr = con.prepareStatement("Insert Into statement(c) Values('key3')", Statement.RETURN_GENERATED_KEYS);
258 assertEquals(1,pr.executeUpdate());
259 rs = pr.getGeneratedKeys();
260 assertNotNull("RETURN_GENERATED_KEYS", rs);
261 assertEquals("ColumnCount",1,rs.getMetaData().getColumnCount());
262 assertEquals("ColumnCount","i",rs.getMetaData().getColumnName(1));
263 assertRowCount(1,rs);
264
265 pr = con.prepareStatement("Insert Into statement(c) Values('key4')", new int[]{2,1});
266 pr.execute();
267 rs = pr.getGeneratedKeys();
268 assertNotNull("RETURN_GENERATED_KEYS", rs);
269 assertEquals("ColumnCount",2,rs.getMetaData().getColumnCount());
270 assertRowCount(1,rs);
271
272 pr = con.prepareStatement("Insert Into statement(c) Values('key5')", new int[]{2});
273 assertEquals(1,pr.executeUpdate());
274 rs = pr.getGeneratedKeys();
275 assertNotNull("RETURN_GENERATED_KEYS", rs);
276 assertEquals("ColumnCount",1,rs.getMetaData().getColumnCount());
277 assertEquals("ColumnCount","i",rs.getMetaData().getColumnName(1));
278 assertRowCount(1,rs);
279
280 pr = con.prepareStatement("Insert Into statement(c) Values('key6')", new String []{"c","i"});
281 pr.execute();
282 rs = pr.getGeneratedKeys();
283 assertNotNull("RETURN_GENERATED_KEYS", rs);
284 assertEquals("ColumnCount",2,rs.getMetaData().getColumnCount());
285 assertRowCount(1,rs);
286
287 pr = con.prepareStatement("Insert Into statement(c) Values('key7')", new String []{"i"});
288 assertEquals(1,pr.executeUpdate());
289 rs = pr.getGeneratedKeys();
290 assertNotNull("RETURN_GENERATED_KEYS", rs);
291 assertEquals("ColumnCount",1,rs.getMetaData().getColumnCount());
292 assertEquals("ColumnCount","i",rs.getMetaData().getColumnName(1));
293 assertRowCount(1,rs);
294 }
295
296
297 public void testResultSetType() throws Exception {
298 Connection con = AllTests.getConnection();
299 Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
300
301 assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, st.getResultSetType());
302 assertEquals(ResultSet.CONCUR_UPDATABLE, st.getResultSetConcurrency());
303
304 ResultSet rs = st.executeQuery("Select * From statement");
305
306 assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, rs.getType());
307 assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
308 }
309
310
311 public void testOther() throws Exception {
312 Connection con = AllTests.getConnection();
314 Statement st = con.createStatement();
315
316 st.clearWarnings();
318 assertNull(st.getWarnings());
319
320 st.setQueryTimeout(5);
323 assertEquals("QueryTimeout", 5, st.getQueryTimeout() );
324
325 st.setMaxFieldSize(100);
326 assertEquals("MaxFieldSize", 100, st.getMaxFieldSize() );
327 }
328
329
330 public void testTruncate() throws Exception {
331 Connection con = AllTests.getConnection();
332 Statement st = con.createStatement();
333
334 st.execute("Truncate table statement");
335 assertRowCount(0, "Select * From statement");
336 }
337 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.