001: /*-------------------------------------------------------------------------
002: *
003: * Copyright (c) 2004-2005, PostgreSQL Global Development Group
004: *
005: * IDENTIFICATION
006: * $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/CursorFetchTest.java,v 1.12 2005/01/11 08:25:48 jurka Exp $
007: *
008: *-------------------------------------------------------------------------
009: */
010: package org.postgresql.test.jdbc2;
011:
012: import java.sql.*;
013:
014: import junit.framework.TestCase;
015:
016: import org.postgresql.test.TestUtil;
017:
018: /*
019: * Tests for using non-zero setFetchSize().
020: */
021: public class CursorFetchTest extends TestCase {
022: private Connection con;
023:
024: public CursorFetchTest(String name) {
025: super (name);
026: }
027:
028: protected void setUp() throws Exception {
029: con = TestUtil.openDB();
030: TestUtil.createTable(con, "test_fetch", "value integer");
031: con.setAutoCommit(false);
032: }
033:
034: protected void tearDown() throws Exception {
035: con.rollback();
036: con.setAutoCommit(true);
037: TestUtil.dropTable(con, "test_fetch");
038: TestUtil.closeDB(con);
039: }
040:
041: protected void createRows(int count) throws Exception {
042: PreparedStatement stmt = con
043: .prepareStatement("insert into test_fetch(value) values(?)");
044: for (int i = 0; i < count; ++i) {
045: stmt.setInt(1, i);
046: stmt.executeUpdate();
047: }
048: }
049:
050: // Test various fetchsizes.
051: public void testBasicFetch() throws Exception {
052: createRows(100);
053:
054: PreparedStatement stmt = con
055: .prepareStatement("select * from test_fetch order by value");
056: int[] testSizes = { 0, 1, 49, 50, 51, 99, 100, 101 };
057: for (int i = 0; i < testSizes.length; ++i) {
058: stmt.setFetchSize(testSizes[i]);
059: assertEquals(testSizes[i], stmt.getFetchSize());
060:
061: ResultSet rs = stmt.executeQuery();
062: assertEquals(testSizes[i], rs.getFetchSize());
063:
064: int count = 0;
065: while (rs.next()) {
066: assertEquals("query value error with fetch size "
067: + testSizes[i], count, rs.getInt(1));
068: ++count;
069: }
070:
071: assertEquals("total query size error with fetch size "
072: + testSizes[i], 100, count);
073: }
074: }
075:
076: // Similar, but for scrollable resultsets.
077: public void testScrollableFetch() throws Exception {
078: createRows(100);
079:
080: PreparedStatement stmt = con.prepareStatement(
081: "select * from test_fetch order by value",
082: ResultSet.TYPE_SCROLL_INSENSITIVE,
083: ResultSet.CONCUR_READ_ONLY);
084:
085: int[] testSizes = { 0, 1, 49, 50, 51, 99, 100, 101 };
086: for (int i = 0; i < testSizes.length; ++i) {
087: stmt.setFetchSize(testSizes[i]);
088: assertEquals(testSizes[i], stmt.getFetchSize());
089:
090: ResultSet rs = stmt.executeQuery();
091: assertEquals(testSizes[i], rs.getFetchSize());
092:
093: for (int j = 0; j <= 50; ++j) {
094: assertTrue("ran out of rows at position " + j
095: + " with fetch size " + testSizes[i], rs.next());
096: assertEquals("query value error with fetch size "
097: + testSizes[i], j, rs.getInt(1));
098: }
099:
100: int position = 50;
101: for (int j = 1; j < 100; ++j) {
102: for (int k = 0; k < j; ++k) {
103: if (j % 2 == 0) {
104: ++position;
105: assertTrue(
106: "ran out of rows doing a forward fetch on iteration "
107: + j + "/" + k + " at position "
108: + position
109: + " with fetch size "
110: + testSizes[i], rs.next());
111: } else {
112: --position;
113: assertTrue(
114: "ran out of rows doing a reverse fetch on iteration "
115: + j + "/" + k + " at position "
116: + position
117: + " with fetch size "
118: + testSizes[i], rs.previous());
119: }
120:
121: assertEquals("query value error on iteration " + j
122: + "/" + k + " with fetch size "
123: + testSizes[i], position, rs.getInt(1));
124: }
125: }
126: }
127: }
128:
129: public void testScrollableAbsoluteFetch() throws Exception {
130: createRows(100);
131:
132: PreparedStatement stmt = con.prepareStatement(
133: "select * from test_fetch order by value",
134: ResultSet.TYPE_SCROLL_INSENSITIVE,
135: ResultSet.CONCUR_READ_ONLY);
136:
137: int[] testSizes = { 0, 1, 49, 50, 51, 99, 100, 101 };
138: for (int i = 0; i < testSizes.length; ++i) {
139: stmt.setFetchSize(testSizes[i]);
140: assertEquals(testSizes[i], stmt.getFetchSize());
141:
142: ResultSet rs = stmt.executeQuery();
143: assertEquals(testSizes[i], rs.getFetchSize());
144:
145: int position = 50;
146: assertTrue("ran out of rows doing an absolute fetch at "
147: + position + " with fetch size " + testSizes[i], rs
148: .absolute(position + 1));
149: assertEquals("query value error with fetch size "
150: + testSizes[i], position, rs.getInt(1));
151:
152: for (int j = 1; j < 100; ++j) {
153: if (j % 2 == 0)
154: position += j;
155: else
156: position -= j;
157:
158: assertTrue(
159: "ran out of rows doing an absolute fetch at "
160: + position + " on iteration " + j
161: + " with fetchsize" + testSizes[i], rs
162: .absolute(position + 1));
163: assertEquals("query value error with fetch size "
164: + testSizes[i], position, rs.getInt(1));
165: }
166: }
167: }
168:
169: //
170: // Tests for ResultSet.setFetchSize().
171: //
172:
173: // test one:
174: // set fetchsize = 0
175: // run query (all rows should be fetched)
176: // set fetchsize = 50 (should have no effect)
177: // process results
178: public void testResultSetFetchSizeOne() throws Exception {
179: createRows(100);
180:
181: PreparedStatement stmt = con
182: .prepareStatement("select * from test_fetch order by value");
183: stmt.setFetchSize(0);
184: ResultSet rs = stmt.executeQuery();
185: rs.setFetchSize(50); // Should have no effect.
186:
187: int count = 0;
188: while (rs.next()) {
189: assertEquals(count, rs.getInt(1));
190: ++count;
191: }
192:
193: assertEquals(100, count);
194: }
195:
196: // test two:
197: // set fetchsize = 25
198: // run query (25 rows fetched)
199: // set fetchsize = 0
200: // process results:
201: // process 25 rows
202: // should do a FETCH ALL to get more data
203: // process 75 rows
204: public void testResultSetFetchSizeTwo() throws Exception {
205: createRows(100);
206:
207: PreparedStatement stmt = con
208: .prepareStatement("select * from test_fetch order by value");
209: stmt.setFetchSize(25);
210: ResultSet rs = stmt.executeQuery();
211: rs.setFetchSize(0);
212:
213: int count = 0;
214: while (rs.next()) {
215: assertEquals(count, rs.getInt(1));
216: ++count;
217: }
218:
219: assertEquals(100, count);
220: }
221:
222: // test three:
223: // set fetchsize = 25
224: // run query (25 rows fetched)
225: // set fetchsize = 50
226: // process results:
227: // process 25 rows. should NOT hit end-of-results here.
228: // do a FETCH FORWARD 50
229: // process 50 rows
230: // do a FETCH FORWARD 50
231: // process 25 rows. end of results.
232: public void testResultSetFetchSizeThree() throws Exception {
233: createRows(100);
234:
235: PreparedStatement stmt = con
236: .prepareStatement("select * from test_fetch order by value");
237: stmt.setFetchSize(25);
238: ResultSet rs = stmt.executeQuery();
239: rs.setFetchSize(50);
240:
241: int count = 0;
242: while (rs.next()) {
243: assertEquals(count, rs.getInt(1));
244: ++count;
245: }
246:
247: assertEquals(100, count);
248: }
249:
250: // test four:
251: // set fetchsize = 50
252: // run query (50 rows fetched)
253: // set fetchsize = 25
254: // process results:
255: // process 50 rows.
256: // do a FETCH FORWARD 25
257: // process 25 rows
258: // do a FETCH FORWARD 25
259: // process 25 rows. end of results.
260: public void testResultSetFetchSizeFour() throws Exception {
261: createRows(100);
262:
263: PreparedStatement stmt = con
264: .prepareStatement("select * from test_fetch order by value");
265: stmt.setFetchSize(50);
266: ResultSet rs = stmt.executeQuery();
267: rs.setFetchSize(25);
268:
269: int count = 0;
270: while (rs.next()) {
271: assertEquals(count, rs.getInt(1));
272: ++count;
273: }
274:
275: assertEquals(100, count);
276: }
277:
278: public void testSingleRowResultPositioning() throws Exception {
279: String msg;
280: createRows(1);
281:
282: int[] sizes = { 0, 1, 10 };
283: for (int i = 0; i < sizes.length; ++i) {
284: Statement stmt = con.createStatement(
285: ResultSet.TYPE_FORWARD_ONLY,
286: ResultSet.CONCUR_UPDATABLE);
287: stmt.setFetchSize(sizes[i]);
288:
289: // Create a one row result set.
290: ResultSet rs = stmt
291: .executeQuery("select * from test_fetch order by value");
292:
293: msg = "before-first row positioning error with fetchsize="
294: + sizes[i];
295: assertTrue(msg, rs.isBeforeFirst());
296: assertTrue(msg, !rs.isAfterLast());
297: assertTrue(msg, !rs.isFirst());
298: assertTrue(msg, !rs.isLast());
299:
300: msg = "row 1 positioning error with fetchsize=" + sizes[i];
301: assertTrue(msg, rs.next());
302:
303: assertTrue(msg, !rs.isBeforeFirst());
304: assertTrue(msg, !rs.isAfterLast());
305: assertTrue(msg, rs.isFirst());
306: assertTrue(msg, rs.isLast());
307: assertEquals(msg, 0, rs.getInt(1));
308:
309: msg = "after-last row positioning error with fetchsize="
310: + sizes[i];
311: assertTrue(msg, !rs.next());
312:
313: assertTrue(msg, !rs.isBeforeFirst());
314: assertTrue(msg, rs.isAfterLast());
315: assertTrue(msg, !rs.isFirst());
316: assertTrue(msg, !rs.isLast());
317:
318: rs.close();
319: stmt.close();
320: }
321: }
322:
323: public void testMultiRowResultPositioning() throws Exception {
324: String msg;
325:
326: createRows(100);
327:
328: int[] sizes = { 0, 1, 10, 100 };
329: for (int i = 0; i < sizes.length; ++i) {
330: Statement stmt = con.createStatement(
331: ResultSet.TYPE_FORWARD_ONLY,
332: ResultSet.CONCUR_UPDATABLE);
333: stmt.setFetchSize(sizes[i]);
334:
335: ResultSet rs = stmt
336: .executeQuery("select * from test_fetch order by value");
337: msg = "before-first row positioning error with fetchsize="
338: + sizes[i];
339: assertTrue(msg, rs.isBeforeFirst());
340: assertTrue(msg, !rs.isAfterLast());
341: assertTrue(msg, !rs.isFirst());
342: assertTrue(msg, !rs.isLast());
343:
344: for (int j = 0; j < 100; ++j) {
345: msg = "row " + j + " positioning error with fetchsize="
346: + sizes[i];
347: assertTrue(msg, rs.next());
348: assertEquals(msg, j, rs.getInt(1));
349:
350: assertTrue(msg, !rs.isBeforeFirst());
351: assertTrue(msg, !rs.isAfterLast());
352: if (j == 0)
353: assertTrue(msg, rs.isFirst());
354: else
355: assertTrue(msg, !rs.isFirst());
356:
357: if (j == 99)
358: assertTrue(msg, rs.isLast());
359: else
360: assertTrue(msg, !rs.isLast());
361: }
362:
363: msg = "after-last row positioning error with fetchsize="
364: + sizes[i];
365: assertTrue(msg, !rs.next());
366:
367: assertTrue(msg, !rs.isBeforeFirst());
368: assertTrue(msg, rs.isAfterLast());
369: assertTrue(msg, !rs.isFirst());
370: assertTrue(msg, !rs.isLast());
371:
372: rs.close();
373: stmt.close();
374: }
375: }
376:
377: // Test odd queries that should not be transformed into cursor-based fetches.
378: public void testInsert() throws Exception {
379: // INSERT should not be transformed.
380: PreparedStatement stmt = con
381: .prepareStatement("insert into test_fetch(value) values(1)");
382: stmt.setFetchSize(100); // Should be meaningless.
383: stmt.executeUpdate();
384: }
385:
386: public void testMultistatement() throws Exception {
387: // Queries with multiple statements should not be transformed.
388:
389: createRows(100); // 0 .. 99
390: PreparedStatement stmt = con
391: .prepareStatement("insert into test_fetch(value) values(100); select * from test_fetch order by value");
392: stmt.setFetchSize(10);
393:
394: assertTrue(!stmt.execute()); // INSERT
395: assertTrue(stmt.getMoreResults()); // SELECT
396: ResultSet rs = stmt.getResultSet();
397: int count = 0;
398: while (rs.next()) {
399: assertEquals(count, rs.getInt(1));
400: ++count;
401: }
402:
403: assertEquals(101, count);
404: }
405:
406: // if the driver tries to use a cursor with autocommit on
407: // it will fail because the cursor will disappear partway
408: // through execution
409: public void testNoCursorWithAutoCommit() throws Exception {
410: createRows(10); // 0 .. 9
411: con.setAutoCommit(true);
412: Statement stmt = con.createStatement();
413: stmt.setFetchSize(3);
414: ResultSet rs = stmt
415: .executeQuery("SELECT * FROM test_fetch ORDER BY value");
416: int count = 0;
417: while (rs.next()) {
418: assertEquals(count++, rs.getInt(1));
419: }
420:
421: assertEquals(10, count);
422: }
423:
424: public void testGetRow() throws SQLException {
425: Statement stmt = con.createStatement();
426: stmt.setFetchSize(1);
427: ResultSet rs = stmt
428: .executeQuery("SELECT 1 UNION SELECT 2 UNION SELECT 3");
429: int count = 0;
430: while (rs.next()) {
431: count++;
432: assertEquals(count, rs.getInt(1));
433: assertEquals(count, rs.getRow());
434: }
435: assertEquals(3, count);
436: }
437:
438: }
|