KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > hsqldb > test > TestLikePredicateOptimizations


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */
30
31
32 package org.hsqldb.test;
33
34 import java.sql.Connection JavaDoc;
35 import java.sql.PreparedStatement JavaDoc;
36 import java.sql.ResultSet JavaDoc;
37 import java.sql.Statement JavaDoc;
38
39 import junit.framework.TestCase;
40 import junit.framework.TestResult;
41
42 /**
43  * HSQLDB TestLikePredicate Junit test case. <p>
44  *
45  * @author boucherb@users
46  * @version 1.7.2
47  * @since 1.7.2
48  */
49 public class TestLikePredicateOptimizations extends TestBase {
50
51     public TestLikePredicateOptimizations(String JavaDoc name) {
52         super(name);
53     }
54
55     /* Implements the TestLikePredicate test */
56     public void test() throws Exception JavaDoc {
57
58         Connection JavaDoc conn = newConnection();
59         Statement JavaDoc stmt = conn.createStatement();
60         PreparedStatement JavaDoc pstmt;
61         ResultSet JavaDoc rs;
62         String JavaDoc sql;
63         int expectedCount;
64         int actualCount;
65
66         stmt.execute("drop table test if exists");
67
68         sql = "create table test(name varchar(255))";
69
70         stmt.execute(sql);
71
72         sql = "insert into test values(?)";
73         pstmt = conn.prepareStatement(sql);
74
75         for (int i = 0; i < 10000; i++) {
76             pstmt.setString(1, "name" + i);
77             pstmt.addBatch();
78         }
79
80         pstmt.executeBatch();
81
82         sql = "select count(*) from test where name = null";
83         rs = stmt.executeQuery(sql);
84
85         rs.next();
86
87         expectedCount = rs.getInt(1);
88         sql = "select count(*) from test where name like null";
89         pstmt = conn.prepareStatement(sql);
90         rs = pstmt.executeQuery();
91
92         rs.next();
93
94         actualCount = rs.getInt(1);
95
96         assertEquals("\"" + sql + "\"", expectedCount, actualCount);
97
98 // --
99 sql = "select count(*) from test where name = ''";
100         rs = stmt.executeQuery(sql);
101
102         rs.next();
103
104         expectedCount = rs.getInt(1);
105         sql = "select count(*) from test where name like ''";
106         pstmt = conn.prepareStatement(sql);
107         rs = pstmt.executeQuery();
108
109         rs.next();
110
111         actualCount = rs.getInt(1);
112
113         assertEquals("\"" + sql + "\"", expectedCount, actualCount);
114
115 // --
116 sql = "select count(*) from test where name is not null";
117         rs = stmt.executeQuery(sql);
118
119         rs.next();
120
121         expectedCount = rs.getInt(1);
122         sql = "select count(*) from test where name like '%'";
123         pstmt = conn.prepareStatement(sql);
124         rs = pstmt.executeQuery();
125
126         rs.next();
127
128         actualCount = rs.getInt(1);
129
130         assertEquals("\"" + sql + "\"", expectedCount, actualCount);
131
132 // --
133 sql = "select count(*) from test where left(name, 6) = 'name44'";
134         rs = stmt.executeQuery(sql);
135
136         rs.next();
137
138         expectedCount = rs.getInt(1);
139         sql = "select count(*) from test where name like 'name44%'";
140         pstmt = conn.prepareStatement(sql);
141         rs = pstmt.executeQuery();
142
143         rs.next();
144
145         actualCount = rs.getInt(1);
146
147         assertEquals("\"" + sql + "\"", expectedCount, actualCount);
148
149 // --
150 sql = "select count(*) from test where left(name,5) = 'name4' and right(name,1) = 5";
151         rs = stmt.executeQuery(sql);
152
153         rs.next();
154
155         expectedCount = rs.getInt(1);
156         sql = "select count(*) from test where name like 'name4%5'";
157         pstmt = conn.prepareStatement(sql);
158         rs = pstmt.executeQuery();
159
160         rs.next();
161
162         actualCount = rs.getInt(1);
163
164         assertEquals("\"" + sql + "\"", expectedCount, actualCount);
165
166 // --
167 stmt.execute("drop table test1 if exists");
168
169         sql = "CREATE TABLE test1 (col VARCHAR(30))";
170         pstmt = conn.prepareStatement(sql);
171
172         pstmt.execute();
173
174         sql = "INSERT INTO test1 (col) VALUES ('one')";
175         pstmt = conn.prepareStatement(sql);
176
177         pstmt.execute();
178
179         sql = "SELECT * FROM test1 WHERE ( col LIKE ? )";
180         pstmt = conn.prepareStatement(sql);
181
182         pstmt.setString(1, "one");
183
184         rs = pstmt.executeQuery();
185
186         rs.next();
187
188         String JavaDoc presult = rs.getString("COL");
189
190         sql = "SELECT * FROM test1 WHERE ( col LIKE 'one' )";
191         pstmt = conn.prepareStatement(sql);
192         rs = pstmt.executeQuery();
193
194         rs.next();
195
196         String JavaDoc result = rs.getString("COL");
197
198         assertEquals("\"" + sql + "\"", result, presult);
199     }
200
201     /* Runs TestLikePredicate test from the command line*/
202     public static void main(String JavaDoc[] args) throws Exception JavaDoc {
203
204         TestResult result;
205         TestCase test;
206         java.util.Enumeration JavaDoc failures;
207         int count;
208
209         result = new TestResult();
210         test = new TestLikePredicateOptimizations("test");
211
212         test.run(result);
213
214         count = result.failureCount();
215
216         System.out.println("TestLikePredicateOptimizations failure count: "
217                            + count);
218
219         failures = result.failures();
220
221         while (failures.hasMoreElements()) {
222             System.out.println(failures.nextElement());
223         }
224     }
225 }
226
Popular Tags