KickJava   Java API By Example, From Geeks To Geeks.

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


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.io.IOException JavaDoc;
35 import java.sql.Connection JavaDoc;
36 import java.sql.DriverManager JavaDoc;
37 import java.sql.ResultSet JavaDoc;
38 import java.sql.SQLException JavaDoc;
39 import java.sql.Statement JavaDoc;
40
41 import org.hsqldb.Trace;
42
43 import junit.framework.TestCase;
44
45 /**
46  * Test cases for HSQL aggregates and HAVING clause.
47  *
48  * @author Tony Lai
49  */
50
51 // fredt@users - modified to remove dependecy on DBUnit
52 public class TestGroupByHaving extends TestCase {
53
54     //------------------------------------------------------------
55 // Class variables
56 //------------------------------------------------------------
57 private static final String JavaDoc databaseDriver = "org.hsqldb.jdbcDriver";
58     private static final String JavaDoc databaseURL = "jdbc:hsqldb:mem:.";
59     private static final String JavaDoc databaseUser = "sa";
60     private static final String JavaDoc databasePassword = "";
61
62     //------------------------------------------------------------
63 // Instance variables
64 //------------------------------------------------------------
65 private Connection JavaDoc conn;
66     private Statement JavaDoc stmt;
67
68     //------------------------------------------------------------
69 // Constructors
70 //------------------------------------------------------------
71
72     /**
73      * Constructs a new SubselectTest.
74      */
75     public TestGroupByHaving(String JavaDoc s) {
76         super(s);
77     }
78
79     //------------------------------------------------------------
80 // Class methods
81 //------------------------------------------------------------
82 protected static Connection JavaDoc getJDBCConnection() throws SQLException JavaDoc {
83         return DriverManager.getConnection(databaseURL, databaseUser,
84                                            databasePassword);
85     }
86
87     protected void setUp() throws Exception JavaDoc {
88
89         super.setUp();
90
91         if (conn != null) {
92             return;
93         }
94
95         Class.forName(databaseDriver);
96
97         conn = getJDBCConnection();
98         stmt = conn.createStatement();
99
100         // I decided not the use the "IF EXISTS" clause since it is not a
101 // SQL standard.
102 try {
103
104 // stmt.execute("drop table employee");
105 stmt.execute("drop table employee if exists");
106         } catch (Exception JavaDoc x) {}
107
108         stmt.execute("create table employee(id int, "
109                      + "firstname VARCHAR(50), " + "lastname VARCHAR(50), "
110                      + "salary decimal(10, 2), " + "superior_id int, "
111                      + "CONSTRAINT PK_employee PRIMARY KEY (id), "
112                      + "CONSTRAINT FK_superior FOREIGN KEY (superior_id) "
113                      + "REFERENCES employee(ID))");
114         addEmployee(1, "Mike", "Smith", 160000, -1);
115         addEmployee(2, "Mary", "Smith", 140000, -1);
116
117         // Employee under Mike
118 addEmployee(10, "Joe", "Divis", 50000, 1);
119         addEmployee(11, "Peter", "Mason", 45000, 1);
120         addEmployee(12, "Steve", "Johnson", 40000, 1);
121         addEmployee(13, "Jim", "Hood", 35000, 1);
122
123         // Employee under Mike
124 addEmployee(20, "Jennifer", "Divis", 60000, 2);
125         addEmployee(21, "Helen", "Mason", 50000, 2);
126         addEmployee(22, "Daisy", "Johnson", 40000, 2);
127         addEmployee(23, "Barbara", "Hood", 30000, 2);
128     }
129
130     protected void tearDown() throws Exception JavaDoc {
131
132         super.tearDown();
133
134         // I decided not the use the "IF EXISTS" clause since it is not a
135 // SQL standard.
136 try {
137
138 // stmt.execute("drop table employee");
139 stmt.execute("drop table employee if exists");
140         } catch (Exception JavaDoc x) {}
141
142         if (stmt != null) {
143             stmt.close();
144
145             stmt = null;
146         }
147
148         if (conn != null) {
149             conn.close();
150
151             conn = null;
152         }
153     }
154
155     private void addEmployee(int id, String JavaDoc firstName, String JavaDoc lastName,
156                              double salary, int superiorId) throws Exception JavaDoc {
157
158         stmt.execute("insert into employee values(" + id + ", '" + firstName
159                      + "', '" + lastName + "', " + salary + ", "
160                      + (superiorId <= 0 ? "null"
161                                         : ("" + superiorId)) + ")");
162     }
163
164     /**
165      * Tests aggregated selection with a <b>GROUP_BY</b> clause. This is
166      * a normal use of the <b>GROUP_BY</b> clause. The first two employees
167      * do not have a superior, and must be grouped within the same group,
168      * according to <b>GROUP_BY</b> standard.
169      */
170     public void testAggregatedGroupBy() throws SQLException JavaDoc {
171
172         String JavaDoc sql = "select avg(salary), max(id) from employee "
173                      + "group by superior_id " + "order by superior_id " + "";
174         Object JavaDoc[][] expected = new Object JavaDoc[][] {
175             {
176                 new Double JavaDoc(150000), new Integer JavaDoc(2)
177             }, {
178                 new Double JavaDoc(42500), new Integer JavaDoc(13)
179             }, {
180                 new Double JavaDoc(45000), new Integer JavaDoc(23)
181             },
182         };
183
184         compareResults(sql, expected, 0);
185     }
186
187     /**
188      * Tests aggregated selection with a <b>GROUP_BY</b> clause and a
189      * <b>HAVING</b> clause.
190      * <p>
191      * This is a typical use of the <b>GROUP_BY</b> + <b>HAVING</b> clause.
192      * The first two employees are eliminated due to the <b>HAVING</b>
193      * condition.
194      * <p>
195      * This test uses aggregated function to eliminate first group.
196      */
197     public void testAggregatedGroupByHaving1() throws SQLException JavaDoc {
198
199         String JavaDoc sql = "select avg(salary), max(id) from employee "
200                      + "group by superior_id " + "having max(id) > 5 "
201                      + "order by superior_id " + "";
202         Object JavaDoc[][] expected = new Object JavaDoc[][] {
203             {
204                 new Double JavaDoc(42500), new Integer JavaDoc(13)
205             }, {
206                 new Double JavaDoc(45000), new Integer JavaDoc(23)
207             },
208         };
209
210         compareResults(sql, expected, 0);
211     }
212
213     /**
214      * Tests aggregated selection with a <b>GROUP_BY</b> clause and a
215      * <b>HAVING</b> clause.
216      * <p>
217      * This is a typical use of the <b>GROUP_BY</b> + <b>HAVING</b> clause.
218      * The first two employees are eliminated due to the <b>HAVING</b>
219      * condition.
220      * <p>
221      * This test uses <b>GROUP_BY</b> column to eliminate first group.
222      */
223     public void testAggregatedGroupByHaving2() throws SQLException JavaDoc {
224
225         String JavaDoc sql = "select avg(salary), max(id) from employee "
226                      + "group by superior_id "
227                      + "having superior_id is not null "
228                      + "order by superior_id " + "";
229         Object JavaDoc[][] expected = new Object JavaDoc[][] {
230             {
231                 new Double JavaDoc(42500), new Integer JavaDoc(13)
232             }, {
233                 new Double JavaDoc(45000), new Integer JavaDoc(23)
234             },
235         };
236
237         compareResults(sql, expected, 0);
238     }
239
240     /**
241      * Tests an unusual usage of the <b>HAVING</b> clause, without a
242      * <b>GROUP BY</b> clause.
243      * <p>
244      * Only one row is returned by the aggregate selection without a
245      * <b>GROUP BY</b> clause. The <b>HAVING</b> clause is applied to the
246      * only returned row. In this case, the <b>HAVING</b> condition is
247      * satisfied.
248      */
249     public void testHavingWithoutGroupBy1() throws SQLException JavaDoc {
250
251         String JavaDoc sql = "select avg(salary), max(id) from employee "
252                      + "having avg(salary) > 1000 " + "";
253         Object JavaDoc[][] expected = new Object JavaDoc[][] {
254             {
255                 new Double JavaDoc(65000), new Integer JavaDoc(23)
256             },
257         };
258
259         compareResults(sql, expected, 0);
260     }
261
262     /**
263      * Tests an unusual usage of the <b>HAVING</b> clause, without a
264      * <b>GROUP BY</b> clause.
265      * <p>
266      * Only one row is returned by the aggregate selection without a
267      * <b>GROUP BY</b> clause. The <b>HAVING</b> clause is applied to the
268      * only returned row. In this case, the <b>HAVING</b> condition is
269      * NOT satisfied.
270      */
271     public void testHavingWithoutGroupBy2() throws SQLException JavaDoc {
272
273         String JavaDoc sql = "select avg(salary), max(id) from employee "
274                      + "having avg(salary) > 1000000 " + "";
275         Object JavaDoc[][] expected = new Object JavaDoc[][]{};
276
277         compareResults(sql, expected, 0);
278     }
279
280     /**
281      * Tests an invalid <b>HAVING</b> clause that contains columns not in
282      * the <b>GROUP BY</b> clause. A SQLException should be thrown.
283      */
284     public void testInvalidHaving() throws SQLException JavaDoc {
285
286         String JavaDoc sql = "select avg(salary), max(id) from employee "
287                      + "group by lastname "
288                      + "having (max(id) > 1) and (superior_id > 1) " + "";
289         Object JavaDoc[][] expected = new Object JavaDoc[][]{};
290
291         compareResults(sql, expected, -Trace.NOT_IN_AGGREGATE_OR_GROUP_BY);
292     }
293
294     //------------------------------------------------------------
295 // Helper methods
296 //------------------------------------------------------------
297 private void compareResults(String JavaDoc sql, Object JavaDoc[][] rows,
298                                 int errorCode) throws SQLException JavaDoc {
299
300         ResultSet JavaDoc rs = null;
301
302         try {
303             rs = stmt.executeQuery(sql);
304
305             assertTrue("Statement <" + sql + "> \nexpecting error code: "
306                        + errorCode, (0 == errorCode));
307         } catch (SQLException JavaDoc sqlx) {
308             if (sqlx.getErrorCode() != errorCode) {
309                 sqlx.printStackTrace();
310             }
311
312             assertTrue("Statement <" + sql + "> \nthrows wrong error code: "
313                        + sqlx.getErrorCode() + " expecting error code: "
314                        + errorCode, (sqlx.getErrorCode() == errorCode));
315
316             return;
317         }
318
319         int rowCount = 0;
320         int colCount = rows.length > 0 ? rows[0].length
321                                        : 0;
322
323         while (rs.next()) {
324             assertTrue("Statement <" + sql + "> \nreturned too many rows.",
325                        (rowCount < rows.length));
326
327             Object JavaDoc[] columns = rows[rowCount];
328
329             for (int col = 1, i = 0; i < colCount; i++, col++) {
330                 Object JavaDoc result = null;
331                 Object JavaDoc expected = columns[i];
332
333                 if (expected == null) {
334                     result = rs.getString(col);
335                     result = rs.wasNull() ? null
336                                           : result;
337                 } else if (expected instanceof String JavaDoc) {
338                     result = rs.getString(col);
339                 } else if (expected instanceof Double JavaDoc) {
340                     result = new Double JavaDoc(rs.getString(col));
341                 } else if (expected instanceof Integer JavaDoc) {
342                     result = new Integer JavaDoc(rs.getInt(col));
343                 }
344
345                 assertEquals("Statement <" + sql
346                              + "> \nreturned wrong value.", columns[i],
347                                  result);
348             }
349
350             rowCount++;
351         }
352
353         assertEquals("Statement <" + sql
354                      + "> \nreturned wrong number of rows.", rows.length,
355                          rowCount);
356     }
357
358     //------------------------------------------------------------
359 // Main program
360 //------------------------------------------------------------
361 public static void main(String JavaDoc[] args) throws IOException JavaDoc {
362
363 // junit.swingui.TestRunner.run(TestGroupByHaving.class);
364 junit.textui.TestRunner.run(TestGroupByHaving.class);
365     }
366 }
367
Popular Tags