sqlfunctionstest.java

人力资源管理系统 系统把几乎所有与人力资源相关的数据统一管理

		assertTrue( q.list().size()==1 );
		q = s.createQuery("from Simple s where s.name=:name");
		q.setCacheRegion("foo");
		q.setCacheable(true);
		q.setString("name", "Simple 1");
		assertTrue( q.list().size()==1 );
		simple = (Simple) q.list().get(0);
		
		q.setString("name", "Simple 2");
		assertTrue( q.list().size()==0 );
		assertTrue( q.list().size()==0 );
		simple.setName("Simple 2");
		assertTrue( q.list().size()==1 );
		assertTrue( q.list().size()==1 );
		t.commit();
		s.close();		

		s = openSession();
		t = s.beginTransaction();
		s.update( simple, new Long(10) );
		s.delete(simple);
		t.commit();
		s.close();		

		s = openSession();
		t = s.beginTransaction();
		q = s.createQuery("from Simple s where s.name=?");
		q.setCacheRegion("foo");
		q.setCacheable(true);
		q.setString(0, "Simple 1");
		assertTrue( q.list().size()==0 );
		assertTrue( q.list().size()==0 );
		t.commit();
		s.close();		
	}
	
	public void testSQLFunctions() throws Exception {
		Session s = openSession();
		Transaction t = s.beginTransaction();
		Simple simple = new Simple();
		simple.setName("Simple 1");
		s.save(simple, new Long(10) );
		
		if ( getDialect() instanceof DB2Dialect) {
			s.find("from s in class Simple where repeat('foo', 3) = 'foofoofoo'");
			s.find("from s in class Simple where repeat(s.name, 3) = 'foofoofoo'");
			s.find("from s in class Simple where repeat( lower(s.name), 3 + (1-1) / 2) = 'foofoofoo'");
		}
		
		assertTrue(
			s.find("from s in class Simple where upper( s.name ) ='SIMPLE 1'").size()==1
		);
		assertTrue(
			s.find("from s in class Simple where not( upper( s.name ) ='yada' or 1=2 or 'foo'='bar' or not('foo'='foo') or 'foo' like 'bar' )").size()==1
		);
		if ( !(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof SybaseDialect) && !(getDialect() instanceof MckoiDialect) && !(getDialect() instanceof InterbaseDialect) ) { //My SQL has a funny concatenation operator
			assertTrue(
				s.find("from s in class Simple where lower( s.name || ' foo' ) ='simple 1 foo'").size()==1
			);
		}
		if ( (getDialect() instanceof SybaseDialect) ) {
			assertTrue(
				s.find("from s in class Simple where lower( s.name + ' foo' ) ='simple 1 foo'").size()==1
			);
		}
		if ( (getDialect() instanceof MckoiDialect) ) {
			assertTrue(
				s.find("from s in class Simple where lower( concat(s.name, ' foo') ) ='simple 1 foo'").size()==1
			);
		}
		
		Simple other = new Simple();
		other.setName("Simple 2");
		other.setCount(12);
		simple.setOther(other);
		s.save( other, new Long(20) );
		//s.find("from s in class Simple where s.name ## 'cat|rat|bag'");
		assertTrue(
			s.find("from s in class Simple where upper( s.other.name ) ='SIMPLE 2'").size()==1
		);
		assertTrue(
			s.find("from s in class Simple where not ( upper( s.other.name ) ='SIMPLE 2' )").size()==0
		);
		assertTrue(
			s.find("select distinct s from s in class Simple where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2").size()==1
		);
		assertTrue(
			s.find("select s from s in class Simple where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2 order by s.other.count").size()==1
		);
		Simple min = new Simple();
		min.setCount(-1);
		s.save(min, new Long(30) );
		if ( ! (getDialect() instanceof MySQLDialect) && ! (getDialect() instanceof HSQLDialect) ) { //My SQL has no subqueries
			assertTrue(
				s.find("from s in class Simple where s.count > ( select min(sim.count) from sim in class Simple )").size()==2
			);
			t.commit();
			t = s.beginTransaction();
			assertTrue(
				s.find("from s in class Simple where s = some( select sim from sim in class Simple where sim.count>=0 ) and s.count >= 0").size()==2
			);
			assertTrue(
				s.find("from s in class Simple where s = some( select sim from sim in class Simple where sim.other.count=s.other.count ) and s.other.count > 0").size()==1
			);
		}
		
		Iterator iter = s.iterate("select sum(s.count) from s in class Simple group by s.count having sum(s.count) > 10");
		assertTrue( iter.hasNext() );
		assertTrue( new Integer(12).equals( iter.next() ) );
		assertTrue( !iter.hasNext() );
		if ( ! (getDialect() instanceof MySQLDialect) ) {
			iter = s.iterate("select s.count from s in class Simple group by s.count having s.count = 12");
			assertTrue( iter.hasNext() );
		}
		
		s.iterate("select s.id, s.count, count(t), max(t.date) from s in class Simple, t in class Simple where s.count = t.count group by s.id, s.count order by s.count");
		
		Query q = s.createQuery("from s in class Simple");
		q.setMaxResults(10);
		assertTrue( q.list().size()==3 );
		q = s.createQuery("from s in class Simple");
		q.setMaxResults(1);
		assertTrue( q.list().size()==1 );
		q = s.createQuery("from s in class Simple");
		assertTrue( q.list().size()==3 );
		q = s.createQuery("from s in class Simple where s.name = ?");
		q.setString(0, "Simple 1");
		assertTrue( q.list().size()==1 );
		q = s.createQuery("from s in class Simple where s.name = ? and upper(s.name) = ?");
		q.setString(1, "SIMPLE 1");
		q.setString(0, "Simple 1");
		q.setFirstResult(0);
		assertTrue( q.iterate().hasNext() );
		q = s.createQuery("from s in class Simple where s.name = :foo and upper(s.name) = :bar or s.count=:count or s.count=:count + 1");
		q.setParameter("bar", "SIMPLE 1");
		q.setString("foo", "Simple 1");
		q.setInteger("count", 69);
		q.setFirstResult(0);
		assertTrue( q.iterate().hasNext() );
		q = s.createQuery("select s.id from s in class Simple");
		q.setFirstResult(1);
		q.setMaxResults(2);
		iter = q.iterate();
		int i=0;
		while ( iter.hasNext() ) {
			assertTrue( iter.next() instanceof Long );
			i++;
		}
		assertTrue(i==2);
		q = s.createQuery("select all s, s.other from s in class Simple where s = :s");
		q.setParameter("s", simple);
		assertTrue( q.list().size()==1 );
		
		
		q = s.createQuery("from s in class Simple where s.name in (:name_list) and s.count > :count");
		HashSet set = new HashSet();
		set.add("Simple 1"); set.add("foo");
		q.setParameterList( "name_list", set );
		q.setParameter("count", new Integer(-1) );
		assertTrue( q.list().size()==1 );
		
		ScrollableResults sr = s.createQuery("from Simple s").scroll();
		sr.next();
		sr.getLong(0);
		Long lid = (Long) sr.get(0, Hibernate.LONG);
		assertEquals( lid, s.getIdentifier( sr.get( 0, Hibernate.entity(Simple.class) ) ) );
		sr.close();
		
		s.delete(other);
		s.delete(simple);
		s.delete(min);
		t.commit();
		s.close();
		
	}
	
	public void testBlobClob() throws Exception {
		
		if ( 
			getDialect() instanceof MySQLDialect || 
			getDialect() instanceof PostgreSQLDialect || 
			getDialect() instanceof HSQLDialect ||
			getDialect() instanceof OracleDialect
		) return;
		
		Session s = openSession();
		Blobber b = new Blobber();
		b.setBlob( Hibernate.createBlob( "foo/bar/baz".getBytes() ) );
		b.setClob( Hibernate.createClob("foo/bar/baz") );
		s.save(b);
		//s.refresh(b);
		//assertTrue( b.getClob() instanceof ClobImpl );
		s.flush();
		s.refresh(b);
		//b.getBlob().setBytes( 2, "abc".getBytes() );
		b.getClob().getSubString(2, 3);
		//b.getClob().setString(2, "abc");
		s.flush();
		s.connection().commit();
		s.close();
		
		s = openSession();
		b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
		Blobber b2 = new Blobber();
		s.save(b2);
		b2.setBlob( b.getBlob() );
		b.setBlob(null);
		//assertTrue( b.getClob().getSubString(1, 3).equals("fab") );
		b.getClob().getSubString(1, 6);
		//b.getClob().setString(1, "qwerty");
		s.flush();
		s.connection().commit();
		s.close();
		
		s = openSession();
		b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
		b.setClob( Hibernate.createClob("xcvfxvc xcvbx cvbx cvbx cvbxcvbxcvbxcvb") );
		s.flush();
		s.connection().commit();
		s.close();
		
		s = openSession();
		b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
		assertTrue( b.getClob().getSubString(1, 7).equals("xcvfxvc") );
		//b.getClob().setString(5, "1234567890");
		s.flush();
		s.connection().commit();
		s.close();
		
		
		/*InputStream is = getClass().getClassLoader().getResourceAsStream("jdbc20.pdf");
		s = sessionsopenSession();
		b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
		System.out.println( is.available() );
		int size = is.available();
		b.setBlob( Hibernate.createBlob( is, is.available() ) );
		s.flush();
		s.connection().commit();
		ResultSet rs = s.connection().createStatement().executeQuery("select datalength(blob_) from blobber where id=" + b.getId() );
		rs.next();
		assertTrue( size==rs.getInt(1) );
		rs.close();
		s.close();
		
		s = sessionsopenSession();
		b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
		File f = new File("C:/foo.pdf");
		f.createNewFile();
		FileOutputStream fos = new FileOutputStream(f);
		Blob blob = b.getBlob();
		byte[] bytes = blob.getBytes( 1, (int) blob.length() );
		System.out.println( bytes.length );
		fos.write(bytes);
		fos.flush();
		fos.close();
		s.close();*/

	}
	
	
	public String[] getMappings() {
		return new String[] { 
			"AltSimple.hbm.xml", 
			"Broken.hbm.xml", 
			"Blobber.hbm.xml" 
		};
	}
	
	public static Test suite() {
		return new TestSuite(SQLFunctionsTest.class);
	}

	public static void main(String[] args) throws Exception {
		TestRunner.run( suite() );
	}

}