testorderby.java

java 数据库 功能强大 效率高 SmallSQL Database is a free DBMS library for the Java(tm) platform. It runs on

	

	public void testOrderBy_int_asc() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		Integer oldValue;
		
		rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER  by i Asc");
		
		assertTrue( rs.next() );
		
		oldValue = (Integer)rs.getObject("i");
		assertNull(oldValue);
		assertTrue( rs.next() );
		oldValue = (Integer)rs.getObject("i");
		
		int count = 1;
		while(rs.next()){
			assertTrue( oldValue.compareTo( (Integer)rs.getObject("i") ) < 0 );
			oldValue = (Integer)rs.getObject("i");
			count++;
		}
		assertEquals( valueCount, count );
	}
	

	public void testOrderBy_int_desc() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		Integer oldValue;
		
		rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER  by i Desc");
		
		assertTrue( rs.next() );
		oldValue = (Integer)rs.getObject("i");
		
		int count = 1;
		while(oldValue != null && rs.next()){
			Integer newValue = (Integer)rs.getObject("i");
			if(newValue != null){
				assertTrue( oldValue.compareTo( newValue ) > 0 );
				count++;
			}
			oldValue = newValue;
		}
		assertNull(oldValue);
		assertFalse( rs.next() );
		assertEquals( valueCount, count );
	}
	

	public void testOrderBy_double() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		Double oldValue;
		
		rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER  by d");
		
		assertTrue( rs.next() );
		
		oldValue = (Double)rs.getObject("d");
		assertNull(oldValue);
		assertTrue( rs.next() );
		oldValue = (Double)rs.getObject("d");
		
		int count = 1;
		while(rs.next()){
			assertTrue( oldValue.compareTo( (Double)rs.getObject("d") ) < 0 );
			oldValue = (Double)rs.getObject("d");
			count++;
		}
		assertEquals( valueCount, count );
	}
	

	public void testOrderBy_real() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		Float oldValue;
		
		rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER  by r");
		
		assertTrue( rs.next() );
		
		oldValue = (Float)rs.getObject("r");
		assertNull(oldValue);
		assertTrue( rs.next() );
		oldValue = (Float)rs.getObject("r");
		
		int count = 1;
		while(rs.next()){
			assertTrue( oldValue.compareTo( (Float)rs.getObject("r") ) < 0 );
			oldValue = (Float)rs.getObject("r");
			count++;
		}
		assertEquals( valueCount, count );
	}
	

	public void test_bigint() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		Long oldValue;
		
		rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER  by bi");
		
		assertTrue( rs.next() );
		
		oldValue = (Long)rs.getObject("bi");
		assertNull(oldValue);
		assertTrue( rs.next() );
		oldValue = (Long)rs.getObject("bi");
		
		int count = 1;
		while(rs.next()){
			assertTrue( oldValue.compareTo( (Long)rs.getObject("bi") ) < 0 );
			oldValue = (Long)rs.getObject("bi");
			count++;
		}
		assertEquals( valueCount, count );
	}
	

	public void test_bigint_withDoublicateValues() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		Long oldValue;
		
		rs = st.executeQuery("SELECT bi/2 bi_2 FROM " + table1 + " ORDER  by (bi/2)");
		
		assertTrue( rs.next() );
		
		oldValue = (Long)rs.getObject("bi_2");
		assertNull(oldValue);
		assertTrue( rs.next() );
		oldValue = (Long)rs.getObject("bi_2");
		
		int count = 1;
		while(rs.next()){
			Long newValue = (Long)rs.getObject("bi_2");
			assertTrue( oldValue + "<="+newValue, oldValue.compareTo( newValue ) <= 0 );
			oldValue = newValue;
			count++;
		}
		assertEquals( valueCount, count );
	}
	

	public void test_boolean() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		boolean oldValue;
		
		rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER  by b");
		
		assertTrue( rs.next() );
		
		oldValue = rs.getBoolean("b");
		assertFalse(oldValue);
		assertTrue(rs.wasNull());
		assertTrue( rs.next() );
		oldValue = rs.getBoolean("b");
		assertFalse(oldValue);		
		assertFalse(rs.wasNull());
		
		int count = 1;
		while(!oldValue && rs.next()){
			oldValue = rs.getBoolean("b");
			assertFalse(rs.wasNull());
			count++;
		}
		while(oldValue && rs.next()){
			oldValue = rs.getBoolean("b");
			assertFalse(rs.wasNull());
			count++;
		}
		assertFalse(rs.next());
		assertEquals( valueCount, count );
	}
	

	public void testVarcharEmpty() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		
		rs = st.executeQuery("SELECT * FROM " + table3 + " ORDER  by vc");
		
		assertTrue( rs.next() );		
		assertNull( rs.getObject("vc") );

		assertTrue( rs.next() );
		assertEquals( "", rs.getObject("vc") );
		
		assertTrue( rs.next() );
		assertEquals( table3, rs.getObject("vc") );
		
		assertFalse( rs.next() );
	}
	

	public void testVarbinaryEmpty() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		
		rs = st.executeQuery("SELECT * FROM " + table3 + " ORDER  by vb");
		
		assertTrue( rs.next() );		
		assertNull( rs.getObject("vb") );

		assertTrue( rs.next() );
		assertEqualsObject( "", new byte[0], rs.getObject("vb"), false );
		
		assertTrue( rs.next() );
		assertEqualsObject( "", table3.getBytes(), rs.getObject("vb"), false );
		
		assertFalse( rs.next() );
	}


	public void test2Columns() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs = null;
		String oldValue;

		rs = st.executeQuery("SELECT * FROM " + table1+","+table2+" ORDER  by v, c2");

		assertTrue( rs.next() );
		assertNull( rs.getObject("v") );
		assertNull( rs.getObject("c2") );
		
		assertTrue( rs.next() );
		oldValue = rs.getString("c2");

		int count = 1;
		while(rs.next() && rs.getString("v") == null){
			String newValue = rs.getString("c2");
			assertTrue( oldValue.compareTo( newValue ) < 0 );
			oldValue = newValue;
			count++;
		}
		assertEquals( valueCount+1, count );
		
		boolean isNext = true;
		while(isNext){
			String vValue = rs.getString("v");
			assertNull( rs.getObject("c2") );
		
			assertTrue( rs.next() );
			oldValue = rs.getString("c2");
			assertEquals( vValue, rs.getString("v") );

			isNext = rs.next();
			count = 1;
			while(isNext && vValue.equals(rs.getString("v"))){
				String newValue = rs.getString("c2");
				assertTrue( oldValue.compareTo( newValue ) < 0 );
				oldValue = newValue;
				count++;
				isNext = rs.next();
			}
			assertEquals( valueCount+1, count );
		}
	}

	

	public void testOrderBy_Scollable() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		ResultSet rs;
		int count;
		
		rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER  by v");
		
		//jetzt irgendwo in die Mitte scrollen
		rs.next();
		rs.next();
		rs.previous(); //dann soll der Zeiger nicht am Ende des bereits gefetchten stehen
		
		rs.last();
		count = 0;
		while(rs.previous()) count++;		
		assertEquals( valueCount, count );

		rs.beforeFirst();
		count = -1;
		while(rs.next()) count++;		
		assertEquals( valueCount, count );

		rs.beforeFirst();
		count = -1;
		while(rs.next()) count++;		
		assertEquals( valueCount, count );
	}

	
	public void testOrderBy_ScollableDesc() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		ResultSet rs;
		int count;
		
		rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER  by i desc, d");
		
		//jetzt irgendwo in die Mitte scrollen
		rs.next();
		rs.next();
		rs.previous(); //dann soll der Zeiger nicht am Ende des bereits gefetchten stehen
		
		rs.last();
		count = 0;
		while(rs.previous()) count++;		
		assertEquals( valueCount, count );

		rs.beforeFirst();
		count = -1;
		while(rs.next()) count++;		
		assertEquals( valueCount, count );

		rs.beforeFirst();
		count = -1;
		while(rs.next()) count++;		
		assertEquals( valueCount, count );
	}

	
	public void testOrderBy_Scollable2() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = st.executeQuery("SELECT * FROM " + table1 + " ORDER  by v");

		
		int colCount = rs.getMetaData().getColumnCount();
		ArrayList result = new ArrayList();
		while(rs.next()){
			Object[] row = new Object[colCount];
			for(int i=0; i<colCount; i++){
				row[i] = rs.getObject(i+1);
			}
			result.add(row);
		}
		
		int rowCount = result.size();
		while(rs.previous()){
			Object[] row = (Object[])result.get(--rowCount);
			for(int i=0; i<colCount; i++){
				assertEquals( "Difference in row:"+rowCount, row[i], rs.getObject(i+1));
			}
		}
		assertEquals( "RowCount different between next and previous:"+rowCount, 0, rowCount);
	}

	
	public void testUnion() throws Exception{
		init();
		Connection con = AllTests.getConnection();
		Statement st = con.createStatement();
		ResultSet rs;
		String oldValue;
		
		rs = st.executeQuery("SELECT v, 5 as Const FROM " + table1 + " Union All Select vc, 6 From " + table3 + " ORDER by v");
		
		assertRSMetaData(rs, new String[]{"v", "Const"}, new int[]{Types.VARCHAR, Types.INTEGER});
		
		assertTrue( rs.next() );		
		oldValue = rs.getString("v");
		assertNull(oldValue);
		
		assertTrue( rs.next() );		
		oldValue = rs.getString("v");
		assertNull(oldValue);
		
		assertTrue( rs.next() );
		oldValue = rs.getString("v");
		
		int count = 3;
		while(rs.next()){
			String newValue = rs.getString("v");
			assertTrue( oldValue.compareTo( newValue ) < 0 );
			oldValue = newValue;
			count++;
		}
		assertEquals( valueCount+4, count );
	}


}