|
1
33 package smallsql.junit;
34
35 import java.math.BigDecimal  ;
36 import java.sql.*;
37
38
39
43 public class TestGroupBy extends BasicTestCase {
44
45 private static final String table1 = "table_GroupBy1";
46
47 private static final String STR_VALUE1 = "name1";
48 private static final String STR_VALUE2 = "name2";
49
50 private boolean init;
51 public TestGroupBy() {
52 super();
53 }
54
55 public TestGroupBy(String name) {
56 super(name);
57 }
58
59 public void init(){
60 if(init) return;
61 try{
62 Connection con = AllTests.getConnection();
63 dropTable( con, table1 );
64 Statement st = con.createStatement();
65 st.execute("create table " + table1 + "(name varchar(30), id int )");
66 st.close();
69 PreparedStatement pr = con.prepareStatement("INSERT into " + table1 + "(name, id) Values(?,?)");
70 pr.setString( 1, STR_VALUE1);
71 pr.setInt( 2, 1 );
72 pr.execute();
73 pr.setString( 1, STR_VALUE1);
74 pr.setInt( 2, 2 );
75 pr.execute();
76 pr.setString( 1, STR_VALUE1);
77 pr.setNull( 2, Types.INTEGER );
78 pr.execute();
79 pr.setString( 1, STR_VALUE2);
80 pr.setInt( 2, 1 );
81 pr.execute();
82
83 pr.close();
84
85 init = true;
86 }catch(Throwable e){
87 e.printStackTrace();
88 }
89 }
90
91 public void testTest() throws Exception {
92 init();
93 Connection con = AllTests.getConnection();
94 Statement st = con.createStatement();
95 ResultSet rs;
96
101
102 rs = st.executeQuery("Select count(id) FROM " + table1 + " Group By name");
103 while(rs.next()){
104 rs.getObject(1);
105 }
106
107 rs = st.executeQuery("Select count(*) FROM " + table1 + " Group By name");
108 while(rs.next()){
109 rs.getObject(1);
110 }
111
112 rs = st.executeQuery("Select count(*) FROM " + table1);
113 assertTrue(rs.next());
114 assertEquals( 4, rs.getInt(1));
115
116 rs = st.executeQuery("Select count(id) FROM " + table1);
117 assertTrue(rs.next());
118 assertEquals( 3, rs.getInt(1));
119
120 rs = st.executeQuery("Select count(*)+1 FROM " + table1);
121 assertTrue(rs.next());
122 assertEquals( 5, rs.getInt(1));
123 }
124
125 public void testCountWhere() throws Exception {
126 init();
127 assertEqualsRsValue( new Integer (0), "Select count(*) FROM " + table1 + " Where id=-1234");
128 }
129
130 public void testCountWherePrepare() throws Exception {
131 init();
132 Connection con = AllTests.getConnection();
133 PreparedStatement pr = con.prepareStatement("Select count(*) FROM " + table1 + " Where id=-1234");
134 for(int i=1; i<=3; i++){
135 ResultSet rs = pr.executeQuery();
136 assertTrue( "No row produce in loop:"+i, rs.next());
137 assertEquals( "loop:"+i, 0, rs.getInt(1));
138 }
139 }
140
141 public void testCountOrderBy() throws Exception {
142 init();
143 Connection con = AllTests.getConnection();
144 PreparedStatement pr = con.prepareStatement("Select count(*) FROM " + table1 + " Group By name Order By name DESC");
145 for(int i=1; i<=3; i++){
146 ResultSet rs = pr.executeQuery( );
147 assertTrue ( "loop:"+i, rs.next());
148 assertEquals( "loop:"+i, 1, rs.getInt(1));
149 assertTrue ( "loop:"+i, rs.next());
150 assertEquals( "loop:"+i, 3, rs.getInt(1));
151 }
152 }
153
154 public void testGroupByWithExpression() throws Exception {
155 init();
156 Connection con = AllTests.getConnection();
157 PreparedStatement pr = con.prepareStatement("Select sum(id), name+'a' as ColumnName FROM " + table1 + " Group By name+'a' Order By Name+'a'");
158 for(int i=1; i<=3; i++){
159 ResultSet rs = pr.executeQuery( );
160 assertTrue ( "loop:"+i, rs.next());
161 assertEquals( "loop:"+i, 3, rs.getInt(1));
162 assertTrue ( "loop:"+i, rs.next());
163 assertEquals( "loop:"+i, 1, rs.getInt(1));
164 assertEquals( "loop:"+i+" Alias name von Expression", "ColumnName", rs.getMetaData().getColumnName(2));
165 }
166 }
167
168 public void testComplex() throws Exception {
169 init();
170 Connection con = AllTests.getConnection();
171 PreparedStatement pr = con.prepareStatement("Select abs(sum(abs(3-id))+2) FROM " + table1 + " Group By name+'a' Order By 'b'+(Name+'a')");
172 for(int i=1; i<=3; i++){
173 ResultSet rs = pr.executeQuery( );
174 assertTrue ( "loop:"+i, rs.next());
175 assertEquals( "loop:"+i, 5, rs.getInt(1));
176 assertTrue ( "loop:"+i, rs.next());
177 assertEquals( "loop:"+i, 4, rs.getInt(1));
178 }
179 }
180
181 public void testWithNullValue() throws Exception {
182 init();
183 assertEqualsRsValue(new Integer (4), "Select count(*) FROM " + table1 + " Group By name+null" );
184 }
185
186 public void testSumInt() throws Exception {
187 init();
188 assertEqualsRsValue( new Integer (4), "Select sum(id) FROM " + table1);
189 }
190
191 public void testSumLong() throws Exception {
192 init();
193 assertEqualsRsValue( new Long (4), "Select sum(cast(id as BigInt)) FROM " + table1);
194 }
195
196 public void testSumReal() throws Exception {
197 init();
198 assertEqualsRsValue( new Float (4), "Select sum(cast(id as real)) FROM " + table1);
199 }
200
201 public void testSumDouble() throws Exception {
202 init();
203 assertEqualsRsValue( new Double (4), "Select sum(cast(id as double)) FROM " + table1);
204 }
205
206 public void testSumDecimal() throws Exception {
207 init();
208 assertEqualsRsValue( new BigDecimal ("4.00"), "Select sum(cast(id as decimal(38,2))) FROM " + table1);
209 }
210
211 public void testMaxInt() throws Exception {
212 init();
213 assertEqualsRsValue( new Integer (2), "Select max(id) FROM " + table1);
214 }
215
216 public void testMaxBigInt() throws Exception {
217 init();
218 assertEqualsRsValue( new Long (2), "Select max(cast(id as BigInt)) FROM " + table1);
219 }
220
221 public void testMaxString() throws Exception {
222 init();
223 assertEqualsRsValue( STR_VALUE2, "Select max(name) FROM " + table1);
224 }
225
226
227 public void testMaxTinyint() throws Exception {
228 init();
229 assertEqualsRsValue( new Integer (2), "Select max(convert(tinyint,id)) FROM " + table1);
230 }
231
232
233 public void testMaxReal() throws Exception {
234 init();
235 assertEqualsRsValue( new Float (2), "Select max(convert(real,id)) FROM " + table1);
236 }
237
238
239 public void testMaxFloat() throws Exception {
240 init();
241 assertEqualsRsValue( new Double (2), "Select max(convert(float,id)) FROM " + table1);
242 }
243
244
245 public void testMaxDouble() throws Exception {
246 init();
247 assertEqualsRsValue( new Double (2), "Select max(convert(double,id)) FROM " + table1);
248 }
249
250
251 public void testMaxMoney() throws Exception {
252 init();
253 assertEqualsRsValue( new java.math.BigDecimal ("2.0000"), "Select max(convert(money,id)) FROM " + table1);
254 }
255
256
257 public void testMaxNumeric() throws Exception {
258 init();
259 assertEqualsRsValue( new java.math.BigDecimal ("2"), "Select max(convert(numeric,id)) FROM " + table1);
260 }
261
262
263 public void testMaxDate() throws Exception {
264 init();
265 assertEqualsRsValue( java.sql.Date.valueOf("2345-01-23"), "Select max({d '2345-01-23'}) FROM " + table1);
266 }
267
268
269 public void testMaxTime() throws Exception {
270 init();
271 assertEqualsRsValue( java.sql.Time.valueOf("12:34:56"), "Select max({t '12:34:56'}) FROM " + table1);
272 }
273
274 public void testMaxTimestamp() throws Exception {
275 init();
276 assertEqualsRsValue( java.sql.Timestamp.valueOf("2345-01-23 12:34:56.123"), "Select max({ts '2345-01-23 12:34:56.123'}) FROM " + table1);
277 }
278
279
280 public void testMaxUniqueidentifier() throws Exception {
281 init();
282 assertEqualsRsValue( "12345678-3445-3445-3445-1234567890AB", "Select max(convert(uniqueidentifier, '12345678-3445-3445-3445-1234567890ab')) FROM " + table1);
283 }
284
285
286 public void testMaxOfNull() throws Exception {
287 init();
288 assertEqualsRsValue( null, "Select max(id) FROM " + table1 + " Where id is null");
289 }
290
291 public void testMin() throws Exception {
292 init();
293 assertEqualsRsValue( new Integer (1), "Select min(id) FROM " + table1);
294 }
295
296 public void testMinString() throws Exception {
297 init();
298 assertEqualsRsValue( STR_VALUE1, "Select min(name) FROM " + table1);
299 }
300
301 public void testMinOfNull() throws Exception {
302 init();
303 assertEqualsRsValue( null, "Select min(id) FROM " + table1 + " Where id is null");
304 }
305
306 public void testFirst1() throws Exception {
307 init();
308 assertEqualsRsValue( new Integer (1), "Select first(id) FROM " + table1);
309 }
310
311 public void testFirst2() throws Exception {
312 init();
313 assertEqualsRsValue( "name1", "Select first(name) FROM " + table1);
314 }
315
316 public void testLast1() throws Exception {
317 init();
318 assertEqualsRsValue( new Integer (1), "Select last(id) FROM " + table1);
319 }
320
321 public void testLast2() throws Exception {
322 init();
323 assertEqualsRsValue( "name2", "Select last(name) FROM " + table1);
324 }
325
326
327 public void testAvg() throws Exception {
328 init();
329 assertEqualsRsValue( new Integer (1), "Select avg(id) FROM " + table1);
330 }
331
332
333 public void testGroupBy() throws Exception {
334 init();
335 Connection con = AllTests.getConnection();
336 Statement st = con.createStatement();
337 ResultSet rs;
338 rs = st.executeQuery("Select name FROM " + table1 + " Group By name");
339 assertTrue(rs.next());
340 assertEquals( STR_VALUE1, rs.getObject(1) );
341 assertTrue(rs.next());
342 assertEquals( STR_VALUE2, rs.getObject(1) );
343
344 }
345
346
347
351 public void testViewWidthGroupBy() throws Exception {
352 init();
353 Connection con = AllTests.getConnection();
354 Statement st = con.createStatement();
355 try{
356 ResultSet rs;
357 st.execute("Create View qry" + table1 + " as Select name, name as name2, count(*) as count FROM " + table1 + " Group By name");
358 rs = st.executeQuery("Select * from qry" + table1);
359 assertEquals( "name", rs.getMetaData().getColumnLabel(1) );
360 assertEquals( "name2", rs.getMetaData().getColumnLabel(2) );
361 assertEquals( "count", rs.getMetaData().getColumnLabel(3) );
362 }finally{
363 st.execute("Drop View qry" + table1);
364 }
365 }
366
367
368 public void testCountNoRow() throws Exception {
369 init();
370
371 Connection con = AllTests.getConnection();
373 Statement st = con.createStatement();
374 st.execute("Delete FROM " + table1);
375 init = false;
376 assertEqualsRsValue( new Integer (0), "Select count(*) FROM " + table1);
377 }
378
379 }
380 |
Popular Tags |
Java API By Example, From Geeks To Geeks.