|
1
5 package org.h2.test.db;
6
7 import java.sql.*;
8
9 import org.h2.test.TestBase;
10
11 public class TestBigResult extends TestBase {
12 public void test() throws Exception  {
13 if(config.memory) {
14 return;
15 }
16 testLimitBufferedResult();
17 testOrderGroup();
18 }
19
20 private void testLimitBufferedResult() throws Exception {
21 deleteDb("bigResult");
22 Connection conn = getConnection("bigResult");
23 Statement stat = conn.createStatement();
24 stat.execute("DROP TABLE IF EXISTS TEST");
25 stat.execute("CREATE TABLE TEST(ID INT)");
26 for(int i=0; i<200; i++) {
27 stat.execute("INSERT INTO TEST(ID) VALUES("+i+")");
28 }
29 stat.execute("SET MAX_MEMORY_ROWS 100");
30 ResultSet rs;
31 rs = stat.executeQuery("select id from test order by id limit 10 offset 85");
32 for(int i=85; rs.next(); i++) {
33 check(i, rs.getInt(1));
34 }
35 rs = stat.executeQuery("select id from test order by id limit 10 offset 95");
36 for(int i=95; rs.next(); i++) {
37 check(i, rs.getInt(1));
38 }
39 rs = stat.executeQuery("select id from test order by id limit 10 offset 105");
40 for(int i=105; rs.next(); i++) {
41 check(i, rs.getInt(1));
42 }
43 conn.close();
44 }
45
46 private void testOrderGroup() throws Exception {
47 deleteDb("bigResult");
48 Connection conn = getConnection("bigResult");
49 Statement stat = conn.createStatement();
50 stat.execute("DROP TABLE IF EXISTS TEST");
51 stat.execute("CREATE TABLE TEST(" + "ID INT PRIMARY KEY, "
52 + "Name VARCHAR(255), "
53 + "FirstName VARCHAR(255), "
54 + "Points INT,"
55 + "LicenseID INT)");
56 int len = getSize(10, 5000);
57 PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?, ?, ?)");
58 for(int i=0; i<len; i++) {
59 prep.setInt(1, i);
60 prep.setString(2, "Name " + i);
61 prep.setString(3, "First Name " + i);
62 prep.setInt(4, i*10);
63 prep.setInt(5, i*i);
64 prep.execute();
65 }
66 conn.close();
67 conn = getConnection("bigResult");
68 stat = conn.createStatement();
69 stat.setMaxRows(len + 1);
70 ResultSet rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
71 for(int i=0; i<len; i++) {
72 rs.next();
73 check(i, rs.getInt(1));
74 check("Name " + i, rs.getString(2));
75 check("First Name " + i, rs.getString(3));
76 check(i*10, rs.getInt(4));
77 check(i*i, rs.getInt(5));
78 }
79
80 stat.setMaxRows(len + 1);
81 rs = stat.executeQuery("SELECT * FROM TEST WHERE ID >= 1000 ORDER BY ID");
82 for(int i=1000; i<len; i++) {
83 rs.next();
84 check(i, rs.getInt(1));
85 check("Name " + i, rs.getString(2));
86 check("First Name " + i, rs.getString(3));
87 check(i*10, rs.getInt(4));
88 check(i*i, rs.getInt(5));
89 }
90
91 stat.execute("SET MAX_MEMORY_ROWS 2");
92 rs = stat.executeQuery("SELECT Name, SUM(ID) FROM TEST GROUP BY NAME");
93 while(rs.next()) {
94 rs.getString(1);
95 rs.getInt(2);
96 }
97
98 conn.setAutoCommit(false);
99 stat.setMaxRows(0);
100 stat.execute("SET MAX_MEMORY_ROWS 0");
101 stat.execute("CREATE TABLE DATA(ID INT, NAME VARCHAR_IGNORECASE(255))");
102 prep = conn.prepareStatement("INSERT INTO DATA VALUES(?, ?)");
103 for(int i=0; i<len; i++) {
104 prep.setInt(1, i);
105 prep.setString(2, ""+i/200);
106 prep.execute();
107 }
108 Statement s2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
109 rs = s2.executeQuery("SELECT NAME FROM DATA");
110 rs.last();
111 conn.setAutoCommit(true);
112
113 rs = s2.executeQuery("SELECT NAME FROM DATA ORDER BY ID");
114 while(rs.next()) {
115 }
117
118 conn.close();
119 }
120
121
122 }
123
124 |
Popular Tags |
Java API By Example, From Geeks To Geeks.