|
1
33 package smallsql.junit;
34
35 import java.sql.*;
36 import java.util.ArrayList  ;
37
38
44 public class TestOrderBy extends BasicTestCase {
45
46 static private boolean init;
47 private static final String table1 = "table_OrderBy1";
48 private static final String table2 = "table_OrderBy2";
49 private static final String table3 = "table_OrderBy3";
50 static private int valueCount;
51
52 public void init(){
53 if(init) return;
54 try{
55 Connection con = AllTests.getConnection();
56 dropTable( con, table1 );
57 dropTable( con, table2 );
58 dropTable( con, table3 );
59 Statement st = con.createStatement();
60 st.execute("create table " + table1 + "(v varchar(30), c char(30), nv nvarchar(30),i int, d float, r real, bi bigint, b boolean)");
61 st.execute("create table " + table2 + "(c2 char(30))");
62 st.execute("create table " + table3 + "(vc varchar(30), vb varbinary(30))");
63 st.close();
64
65 PreparedStatement pr = con.prepareStatement("INSERT into " + table1 + "(v,c,nv,i,d,r,bi,b) Values(?,?,?,?,?,?,?,?)");
66 PreparedStatement pr2= con.prepareStatement("INSERT into " + table2 + "(c2) Values(?)");
67 for(int i=150; i>-10; i--){
68 pr.setString( 1, String.valueOf(i));
69 pr.setString( 2, String.valueOf(i));
70 pr.setString( 3, String.valueOf( (char)i ));
71 pr.setInt ( 4, i );
72 pr.setDouble( 5, i );
73 pr.setFloat ( 6, i );
74 pr.setInt ( 7, i );
75 pr.setBoolean( 8, i == 0 );
76 pr.execute();
77 pr2.setString( 1, String.valueOf(i));
78 pr2.execute();
79 valueCount++;
80 }
81 pr.setObject( 1, null, Types.VARCHAR);
82 pr.setObject( 2, null, Types.VARCHAR);
83 pr.setObject( 3, null, Types.VARCHAR);
84 pr.setObject( 4, null, Types.VARCHAR);
85 pr.setObject( 5, null, Types.VARCHAR);
86 pr.setObject( 6, null, Types.VARCHAR);
87 pr.setObject( 7, null, Types.VARCHAR);
88 pr.setObject( 8, null, Types.VARCHAR);
89 pr.execute();
90 pr2.setObject( 1, null, Types.VARCHAR);
91 pr2.execute();
92 pr2.setString( 1, "");
93 pr2.execute();
94
95 pr.close();
96
97 pr = con.prepareStatement("INSERT into " + table3 + "(vc, vb) Values(?,?)");
98 pr.setString( 1, table3);
99 pr.setBytes( 2, table3.getBytes());
100 pr.execute();
101 pr.setString( 1, "");
102 pr.setBytes( 2, new byte[0]);
103 pr.execute();
104 pr.setString( 1, null);
105 pr.setBytes( 2, null);
106 pr.execute();
107
108 init = true;
109 }catch(Throwable e){
110 e.printStackTrace();
111 }
112 }
113
114
115 public void testOrderBy_char() throws Exception {
116 init();
117 Connection con = AllTests.getConnection();
118 Statement st = con.createStatement();
119 ResultSet rs;
120 String oldValue;
121
122 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by c");
123
124 assertTrue( rs.next() );
125
126 oldValue = rs.getString("c");
127 assertNull(oldValue);
128 assertTrue( rs.next() );
129 oldValue = rs.getString("c");
130
131 int count = 1;
132 while(rs.next()){
133 String newValue = rs.getString("c");
134 assertTrue( oldValue + "<" + newValue, oldValue.compareTo( newValue ) < 0 );
135 oldValue = newValue;
136 count++;
137 }
138 rs.close();
139 assertEquals( valueCount, count );
140 }
141
142
143 public void testOrderBy_varchar() throws Exception {
144 init();
145 Connection con = AllTests.getConnection();
146 Statement st = con.createStatement();
147 ResultSet rs;
148 String oldValue;
149
150 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by v");
151
152 assertTrue( rs.next() );
153
154 oldValue = rs.getString("v");
155 assertNull(oldValue);
156 assertTrue( rs.next() );
157 oldValue = rs.getString("v");
158
159 int count = 1;
160 while(rs.next()){
161 String newValue = rs.getString("v");
162 assertTrue( oldValue + "<" + newValue, oldValue.compareTo( newValue ) < 0 );
163 oldValue = newValue;
164 count++;
165 }
166 assertEquals( valueCount, count );
167 }
168
169
170 public void testOrderBy_varchar_asc() throws Exception {
171 init();
172 Connection con = AllTests.getConnection();
173 Statement st = con.createStatement();
174 ResultSet rs;
175 String oldValue;
176
177 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by v ASC");
178
179 assertTrue( rs.next() );
180
181 oldValue = rs.getString("v");
182 assertNull(oldValue);
183 assertTrue( rs.next() );
184 oldValue = rs.getString("v");
185
186 int count = 1;
187 while(rs.next()){
188 String newValue = rs.getString("v");
189 assertTrue( oldValue.compareTo( newValue ) < 0 );
190 oldValue = newValue;
191 count++;
192 }
193 rs.close();
194 assertEquals( valueCount, count );
195 }
196
197
198 public void testOrderBy_varchar_desc() throws Exception {
199 init();
200 Connection con = AllTests.getConnection();
201 Statement st = con.createStatement();
202 ResultSet rs;
203 String oldValue;
204
205 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by v desc");
206
207 assertTrue( rs.next() );
208 oldValue = rs.getString("v");
209
210 int count = 1;
211 while(oldValue != null && rs.next()){
212 String newValue = rs.getString("v");
213 if(newValue != null){
214 assertTrue( oldValue.compareTo( newValue ) > 0 );
215 count++;
216 }
217 oldValue = newValue;
218 }
219 assertNull(oldValue);
220 assertFalse( rs.next() );
221
222 assertEquals( valueCount, count );
223 }
224
225
226 public void testOrderBy_varchar_DescAsc() throws Exception {
227 init();
228 Connection con = AllTests.getConnection();
229 Statement st = con.createStatement();
230 ResultSet rs;
231 String oldValue;
232
233 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by v desc, i asc");
234
235 assertTrue( rs.next() );
236 oldValue = rs.getString("v");
237
238 int count = 1;
239 while(oldValue != null && rs.next()){
240 String newValue = rs.getString("v");
241 if(newValue != null){
242 assertTrue( oldValue.compareTo( newValue ) > 0 );
243 count++;
244 }
245 oldValue = newValue;
246 }
247 assertNull(oldValue);
248 assertFalse( rs.next() );
249
250 assertEquals( valueCount, count );
251 }
252
253
254 public void testOrderBy_varchar_GroupBy() throws Exception {
255 init();
256 Connection con = AllTests.getConnection();
257 Statement st = con.createStatement();
258 ResultSet rs;
259 String oldValue;
260
261 rs = st.executeQuery("SELECT first(v) cc FROM " + table1 + " Group By i ORDER by first(V)");
262
263 assertTrue( rs.next() );
264
265 oldValue = rs.getString("cc");
266 assertNull(oldValue);
267 assertTrue( rs.next() );
268 oldValue = rs.getString("cc");
269
270 int count = 1;
271 while(rs.next()){
272 assertTrue( oldValue.compareTo( rs.getString("cc") ) < 0 );
273 oldValue = rs.getString("cc");
274 count++;
275 }
276 assertEquals( valueCount, count );
277 }
278
279
280 public void testOrderBy_varchar_Join() throws Exception {
281 init();
282 Connection con = AllTests.getConnection();
283 Statement st = con.createStatement();
284 ResultSet rs;
285 String oldValue;
286
287 rs = st.executeQuery("SELECT * FROM " + table1 + " t1 Inner join "+table2+" t2 on t1.c=t2.c2 ORDER by v");
288
289 assertTrue( rs.next() );
290
291 oldValue = rs.getString("v");
292
293 int count = 1;
294 while(rs.next()){
295 assertTrue( oldValue.compareTo( rs.getString("v") ) < 0 );
296 oldValue = rs.getString("v");
297 count++;
298 }
299 assertEquals( valueCount, count );
300 }
301
302
303 public void testOrderBy_nvarchar() throws Exception {
304 init();
305 Connection con = AllTests.getConnection();
306 Statement st = con.createStatement();
307 ResultSet rs;
308 String oldValue;
309
310 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by nv");
311
312 assertTrue( rs.next() );
313
314 oldValue = rs.getString("nv");
315 assertNull(oldValue);
316 assertTrue( rs.next() );
317 oldValue = rs.getString("nv");
318
319 int count = 1;
320 while(rs.next()){
321 assertTrue( String.CASE_INSENSITIVE_ORDER.compare( oldValue, rs.getString("nv") ) <= 0 );
322 oldValue = rs.getString("nv");
323 count++;
324 }
325 assertEquals( valueCount, count );
326 }
327
328
329 public void testOrderBy_int() throws Exception {
330 init();
331 Connection con = AllTests.getConnection();
332 Statement st = con.createStatement();
333 ResultSet rs;
334 Integer oldValue;
335
336 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by i");
337
338 assertTrue( rs.next() );
339
340 oldValue = (Integer )rs.getObject("i");
341 assertNull(oldValue);
342 assertTrue( rs.next() );
343 oldValue = (Integer )rs.getObject("i");
344
345 int count = 1;
346 while(rs.next()){
347 assertTrue( oldValue.compareTo( (Integer )rs.getObject("i") ) < 0 );
348 oldValue = (Integer )rs.getObject("i");
349 count++;
350 }
351 assertEquals( valueCount, count );
352 }
353
354
355 public void test_function() throws Exception {
356 init();
357 Connection con = AllTests.getConnection();
358 Statement st = con.createStatement();
359 ResultSet rs;
360 int oldValue;
361
362 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by abs(i)");
363
364 assertTrue( rs.next() );
365
366 assertNull(rs.getObject("i"));
367 assertTrue( rs.next() );
368 oldValue = Math.abs( rs.getInt("i") );
369
370 int count = 1;
371 while(rs.next()){
372 int newValue = Math.abs( rs.getInt("i") );
373 assertTrue( oldValue <= newValue );
374 oldValue = newValue;
375 count++;
376 }
377 assertEquals( valueCount, count );
378 }
379
380
381 public void test_functionAscDesc() throws Exception {
382 init();
383 Connection con = AllTests.getConnection();
384 Statement st = con.createStatement();
385 ResultSet rs;
386 int oldValue;
387 int oldValue2;
388
389 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by abs(i) Asc, i desc");
390
391 assertTrue( rs.next() );
392
393 assertNull(rs.getObject("i"));
394 assertTrue( rs.next() );
395 oldValue = Math.abs( rs.getInt("i") );
396 oldValue2 = rs.getInt("i");
397
398 int count = 1;
399 while(rs.next()){
400 int newValue2 = rs.getInt("i");
401 int newValue = Math.abs( newValue2 );
402 assertTrue( oldValue <= newValue );
403 if(oldValue == newValue){
404 assertTrue( oldValue2 > newValue2 );
405 }
406 oldValue = newValue;
407 oldValue2 = newValue2;
408 count++;
409 }
410 assertEquals( valueCount, count );
411 }
412
413
414 public void testOrderBy_int_asc() throws Exception {
415 init();
416 Connection con = AllTests.getConnection();
417 Statement st = con.createStatement();
418 ResultSet rs;
419 Integer oldValue;
420
421 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by i Asc");
422
423 assertTrue( rs.next() );
424
425 oldValue = (Integer )rs.getObject("i");
426 assertNull(oldValue);
427 assertTrue( rs.next() );
428 oldValue = (Integer )rs.getObject("i");
429
430 int count = 1;
431 while(rs.next()){
432 assertTrue( oldValue.compareTo( (Integer )rs.getObject("i") ) < 0 );
433 oldValue = (Integer )rs.getObject("i");
434 count++;
435 }
436 assertEquals( valueCount, count );
437 }
438
439
440 public void testOrderBy_int_desc() throws Exception {
441 init();
442 Connection con = AllTests.getConnection();
443 Statement st = con.createStatement();
444 ResultSet rs;
445 Integer oldValue;
446
447 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by i Desc");
448
449 assertTrue( rs.next() );
450 oldValue = (Integer )rs.getObject("i");
451
452 int count = 1;
453 while(oldValue != null && rs.next()){
454 Integer newValue = (Integer )rs.getObject("i");
455 if(newValue != null){
456 assertTrue( oldValue.compareTo( newValue ) > 0 );
457 count++;
458 }
459 oldValue = newValue;
460 }
461 assertNull(oldValue);
462 assertFalse( rs.next() );
463 assertEquals( valueCount, count );
464 }
465
466
467 public void testOrderBy_double() throws Exception {
468 init();
469 Connection con = AllTests.getConnection();
470 Statement st = con.createStatement();
471 ResultSet rs;
472 Double oldValue;
473
474 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by d");
475
476 assertTrue( rs.next() );
477
478 oldValue = (Double )rs.getObject("d");
479 assertNull(oldValue);
480 assertTrue( rs.next() );
481 oldValue = (Double )rs.getObject("d");
482
483 int count = 1;
484 while(rs.next()){
485 assertTrue( oldValue.compareTo( (Double )rs.getObject("d") ) < 0 );
486 oldValue = (Double )rs.getObject("d");
487 count++;
488 }
489 assertEquals( valueCount, count );
490 }
491
492
493 public void testOrderBy_real() throws Exception {
494 init();
495 Connection con = AllTests.getConnection();
496 Statement st = con.createStatement();
497 ResultSet rs;
498 Float oldValue;
499
500 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by r");
501
502 assertTrue( rs.next() );
503
504 oldValue = (Float )rs.getObject("r");
505 assertNull(oldValue);
506 assertTrue( rs.next() );
507 oldValue = (Float )rs.getObject("r");
508
509 int count = 1;
510 while(rs.next()){
511 assertTrue( oldValue.compareTo( (Float )rs.getObject("r") ) < 0 );
512 oldValue = (Float )rs.getObject("r");
513 count++;
514 }
515 assertEquals( valueCount, count );
516 }
517
518
519 public void test_bigint() throws Exception {
520 init();
521 Connection con = AllTests.getConnection();
522 Statement st = con.createStatement();
523 ResultSet rs;
524 Long oldValue;
525
526 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by bi");
527
528 assertTrue( rs.next() );
529
530 oldValue = (Long )rs.getObject("bi");
531 assertNull(oldValue);
532 assertTrue( rs.next() );
533 oldValue = (Long )rs.getObject("bi");
534
535 int count = 1;
536 while(rs.next()){
537 assertTrue( oldValue.compareTo( (Long )rs.getObject("bi") ) < 0 );
538 oldValue = (Long )rs.getObject("bi");
539 count++;
540 }
541 assertEquals( valueCount, count );
542 }
543
544
545 public void test_bigint_withDoublicateValues() throws Exception {
546 init();
547 Connection con = AllTests.getConnection();
548 Statement st = con.createStatement();
549 ResultSet rs;
550 Long oldValue;
551
552 rs = st.executeQuery("SELECT bi/2 bi_2 FROM " + table1 + " ORDER by (bi/2)");
553
554 assertTrue( rs.next() );
555
556 oldValue = (Long )rs.getObject("bi_2");
557 assertNull(oldValue);
558 assertTrue( rs.next() );
559 oldValue = (Long )rs.getObject("bi_2");
560
561 int count = 1;
562 while(rs.next()){
563 Long newValue = (Long )rs.getObject("bi_2");
564 assertTrue( oldValue + "<="+newValue, oldValue.compareTo( newValue ) <= 0 );
565 oldValue = newValue;
566 count++;
567 }
568 assertEquals( valueCount, count );
569 }
570
571
572 public void test_boolean() throws Exception {
573 init();
574 Connection con = AllTests.getConnection();
575 Statement st = con.createStatement();
576 ResultSet rs;
577 boolean oldValue;
578
579 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by b");
580
581 assertTrue( rs.next() );
582
583 oldValue = rs.getBoolean("b");
584 assertFalse(oldValue);
585 assertTrue(rs.wasNull());
586 assertTrue( rs.next() );
587 oldValue = rs.getBoolean("b");
588 assertFalse(oldValue);
589 assertFalse(rs.wasNull());
590
591 int count = 1;
592 while(!oldValue && rs.next()){
593 oldValue = rs.getBoolean("b");
594 assertFalse(rs.wasNull());
595 count++;
596 }
597 while(oldValue && rs.next()){
598 oldValue = rs.getBoolean("b");
599 assertFalse(rs.wasNull());
600 count++;
601 }
602 assertFalse(rs.next());
603 assertEquals( valueCount, count );
604 }
605
606
607 public void testVarcharEmpty() throws Exception {
608 init();
609 Connection con = AllTests.getConnection();
610 Statement st = con.createStatement();
611 ResultSet rs;
612
613 rs = st.executeQuery("SELECT * FROM " + table3 + " ORDER by vc");
614
615 assertTrue( rs.next() );
616 assertNull( rs.getObject("vc") );
617
618 assertTrue( rs.next() );
619 assertEquals( "", rs.getObject("vc") );
620
621 assertTrue( rs.next() );
622 assertEquals( table3, rs.getObject("vc") );
623
624 assertFalse( rs.next() );
625 }
626
627
628 public void testVarbinaryEmpty() throws Exception {
629 init();
630 Connection con = AllTests.getConnection();
631 Statement st = con.createStatement();
632 ResultSet rs;
633
634 rs = st.executeQuery("SELECT * FROM " + table3 + " ORDER by vb");
635
636 assertTrue( rs.next() );
637 assertNull( rs.getObject("vb") );
638
639 assertTrue( rs.next() );
640 assertEqualsObject( "", new byte[0], rs.getObject("vb"), false );
641
642 assertTrue( rs.next() );
643 assertEqualsObject( "", table3.getBytes(), rs.getObject("vb"), false );
644
645 assertFalse( rs.next() );
646 }
647
648
649 public void test2Columns() throws Exception {
650 init();
651 Connection con = AllTests.getConnection();
652 Statement st = con.createStatement();
653 ResultSet rs = null;
654 String oldValue;
655
656 rs = st.executeQuery("SELECT * FROM " + table1+","+table2+" ORDER by v, c2");
657
658 assertTrue( rs.next() );
659 assertNull( rs.getObject("v") );
660 assertNull( rs.getObject("c2") );
661
662 assertTrue( rs.next() );
663 oldValue = rs.getString("c2");
664
665 int count = 1;
666 while(rs.next() && rs.getString("v") == null){
667 String newValue = rs.getString("c2");
668 assertTrue( oldValue.compareTo( newValue ) < 0 );
669 oldValue = newValue;
670 count++;
671 }
672 assertEquals( valueCount+1, count );
673
674 boolean isNext = true;
675 while(isNext){
676 String vValue = rs.getString("v");
677 assertNull( rs.getObject("c2") );
678
679 assertTrue( rs.next() );
680 oldValue = rs.getString("c2");
681 assertEquals( vValue, rs.getString("v") );
682
683 isNext = rs.next();
684 count = 1;
685 while(isNext && vValue.equals(rs.getString("v"))){
686 String newValue = rs.getString("c2");
687 assertTrue( oldValue.compareTo( newValue ) < 0 );
688 oldValue = newValue;
689 count++;
690 isNext = rs.next();
691 }
692 assertEquals( valueCount+1, count );
693 }
694 }
695
696
697
698 public void testOrderBy_Scollable() throws Exception {
699 init();
700 Connection con = AllTests.getConnection();
701 Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
702 ResultSet rs;
703 int count;
704
705 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by v");
706
707 rs.next();
709 rs.next();
710 rs.previous();
712 rs.last();
713 count = 0;
714 while(rs.previous()) count++;
715 assertEquals( valueCount, count );
716
717 rs.beforeFirst();
718 count = -1;
719 while(rs.next()) count++;
720 assertEquals( valueCount, count );
721
722 rs.beforeFirst();
723 count = -1;
724 while(rs.next()) count++;
725 assertEquals( valueCount, count );
726 }
727
728
729 public void testOrderBy_ScollableDesc() throws Exception {
730 init();
731 Connection con = AllTests.getConnection();
732 Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
733 ResultSet rs;
734 int count;
735
736 rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by i desc, d");
737
738 rs.next();
740 rs.next();
741 rs.previous();
743 rs.last();
744 count = 0;
745 while(rs.previous()) count++;
746 assertEquals( valueCount, count );
747
748 rs.beforeFirst();
749 count = -1;
750 while(rs.next()) count++;
751 assertEquals( valueCount, count );
752
753 rs.beforeFirst();
754 count = -1;
755 while(rs.next()) count++;
756 assertEquals( valueCount, count );
757 }
758
759
760 public void testOrderBy_Scollable2() throws Exception {
761 init();
762 Connection con = AllTests.getConnection();
763 Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
764 ResultSet rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER by v");
765
766
767 int colCount = rs.getMetaData().getColumnCount();
768 ArrayList result = new ArrayList ();
769 while(rs.next()){
770 Object [] row = new Object [colCount];
771 for(int i=0; i<colCount; i++){
772 row[i] = rs.getObject(i+1);
773 }
774 result.add(row);
775 }
776
777 int rowCount = result.size();
778 while(rs.previous()){
779 Object [] row = (Object [])result.get(--rowCount);
780 for(int i=0; i<colCount; i++){
781 assertEquals( "Difference in row:"+rowCount, row[i], rs.getObject(i+1));
782 }
783 }
784 assertEquals( "RowCount different between next and previous:"+rowCount, 0, rowCount);
785 }
786
787
788 public void testUnion() throws Exception {
789 init();
790 Connection con = AllTests.getConnection();
791 Statement st = con.createStatement();
792 ResultSet rs;
793 String oldValue;
794
795 rs = st.executeQuery("SELECT v, 5 as Const FROM " + table1 + " Union All Select vc, 6 From " + table3 + " ORDER by v");
796
797 assertRSMetaData(rs, new String []{"v", "Const"}, new int[]{Types.VARCHAR, Types.INTEGER});
798
799 assertTrue( rs.next() );
800 oldValue = rs.getString("v");
801 assertNull(oldValue);
802
803 assertTrue( rs.next() );
804 oldValue = rs.getString("v");
805 assertNull(oldValue);
806
807 assertTrue( rs.next() );
808 oldValue = rs.getString("v");
809
810 int count = 3;
811 while(rs.next()){
812 String newValue = rs.getString("v");
813 assertTrue( oldValue.compareTo( newValue ) < 0 );
814 oldValue = newValue;
815 count++;
816 }
817 assertEquals( valueCount+4, count );
818 }
819
820
821 }
822 |
Popular Tags |
Java API By Example, From Geeks To Geeks.