|
1
30
31
32 package org.hsqldb.test;
33
34 import java.sql.Connection  ;
35 import java.sql.PreparedStatement ;
36 import java.sql.ResultSet ;
37 import java.sql.Statement ;
38
39 import junit.framework.TestCase;
40 import junit.framework.TestResult;
41
42
49 public class TestINPredicateParameterizationAndCorrelation extends TestBase {
50
51 public TestINPredicateParameterizationAndCorrelation(String name) {
52 super(name);
53 }
54
55
56 public void test() throws Exception {
57
58 Connection conn = newConnection();
59 Statement stmt = conn.createStatement();
60 PreparedStatement pstmt;
61 ResultSet rs;
62 int actualCount;
63 int expectedCount;
64 String sql;
65
66 stmt.execute("drop table test if exists");
67
68 sql = "create table test(id int)";
69
70 stmt.execute(sql);
71
72 sql = "insert into test values(?)";
73 pstmt = conn.prepareStatement(sql);
74
75 for (int i = 0; i < 10; i++) {
76 pstmt.setInt(1, i);
77 pstmt.addBatch();
78 }
79
80 pstmt.executeBatch();
81
82 sql = "select count(*) from test where id in(?,?)";
83 pstmt = conn.prepareStatement(sql);
84
85 pstmt.setInt(1, 0);
86 pstmt.setInt(2, 9);
87
88 rs = pstmt.executeQuery();
89
90 rs.next();
91
92 expectedCount = 2;
93 actualCount = rs.getInt(1);
94 sql = "\"select count(*) from test where id in(0,9)\"";
95
96 assertEquals(sql, expectedCount, actualCount);
97
98 sql = "select count(*) from test a, test b where 0 in(a.id, b.id)";
99 rs = stmt.executeQuery(sql);
100
101 rs.next();
102
103 expectedCount = rs.getInt(1);
104 sql = "select count(*) from test a, test b where ? in (a.id, b.id)";
105 pstmt = conn.prepareStatement(sql);
106
107 pstmt.setInt(1, 0);
108
109 rs = pstmt.executeQuery();
110
111 rs.next();
112
113 actualCount = rs.getInt(1);
114 sql = "\"select count(*) from test a, test b where 0 in (a.id, b.id)\"";
115
116 assertEquals(sql, expectedCount, actualCount);
117
118 try {
119 sql = "select count(*) from test a, test b where ? in(?, b.id)";
120 pstmt = conn.prepareStatement(sql);
121
122 assertTrue("expected exception preparing \"" + sql + "\"", false);
123 } catch (Exception e) {
124
125 assertTrue(e.toString(), true);
127 }
128
129 try {
130 sql = "select count(*) from test a, test b where a.id in(?, ?)";
131 pstmt = conn.prepareStatement(sql);
132 } catch (Exception e) {
133 assertTrue("unexpected exception preparing \"" + sql + "\":" + e,
134 false);
135 }
136
137 sql = "select count(*) from "
138 + "(select * from test where id in (1,2)) a,"
139 + "(select * from test where id in (3,4)) b "
140 + "where a.id < 2 and b.id < 4";
141 rs = stmt.executeQuery(sql);
142
143 rs.next();
144
145 expectedCount = rs.getInt(1);
146 sql = "select count(*) from "
147 + "(select * from test where id in (?,?)) a,"
148 + "(select * from test where id in (?,?)) b "
149 + "where a.id < ? and b.id < ?";
150 pstmt = conn.prepareStatement(sql);
151
152 pstmt.setInt(1, 1);
153 pstmt.setInt(2, 2);
154 pstmt.setInt(3, 3);
155 pstmt.setInt(4, 4);
156 pstmt.setInt(5, 2);
157 pstmt.setInt(6, 4);
158
159 rs = pstmt.executeQuery();
160
161 rs.next();
162
163 actualCount = rs.getInt(1);
164
165 assertEquals("row count: ", expectedCount, actualCount);
166 }
167
168
169 public static void main(String [] args) throws Exception {
170
171 TestResult result;
172 TestCase test;
173 java.util.Enumeration failures;
174 int count;
175
176 result = new TestResult();
177 test = new TestINPredicateParameterizationAndCorrelation("test");
178
179 test.run(result);
180
181 count = result.failureCount();
182
183 System.out.println(
184 "TestINPredicateParameterizationAndCorrelation failure count: "
185 + count);
186
187 failures = result.failures();
188
189 while (failures.hasMoreElements()) {
190 System.out.println(failures.nextElement());
191 }
192 }
193 }
194 |
Popular Tags |
Java API By Example, From Geeks To Geeks.