sqlfunctionsintersystemstest.java

hibernate 开源框架的代码 jar包希望大家能喜欢

package org.hibernate.test.dialect.functional.cache;

import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import junit.framework.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import org.hibernate.Hibernate;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.ScrollableResults;
import org.hibernate.Transaction;
import org.hibernate.classic.Session;
import org.hibernate.dialect.Cache71Dialect;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.HSQLDialect;
import org.hibernate.dialect.InterbaseDialect;
import org.hibernate.dialect.MckoiDialect;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.Oracle9Dialect;
import org.hibernate.dialect.SybaseDialect;
import org.hibernate.dialect.TimesTenDialect;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.junit.functional.DatabaseSpecificFunctionalTestCase;
import org.hibernate.junit.functional.FunctionalTestClassTestSuite;
import org.hibernate.test.legacy.Blobber;
import org.hibernate.test.legacy.Broken;
import org.hibernate.test.legacy.Fixed;
import org.hibernate.test.legacy.Simple;
import org.hibernate.test.legacy.Single;

/**
 * Tests for function support on CacheSQL...
 *
 * @author Jonathan Levinson
 */
public class SQLFunctionsInterSystemsTest extends DatabaseSpecificFunctionalTestCase {

	private static final Logger log = LoggerFactory.getLogger(SQLFunctionsInterSystemsTest.class);

	public SQLFunctionsInterSystemsTest(String name) {
		super(name);
	}

	public String[] getMappings() {
		return new String[] {
				"legacy/AltSimple.hbm.xml",
				"legacy/Broken.hbm.xml",
				"legacy/Blobber.hbm.xml",
				"dialect/cache/TestInterSystemsFunctionsClass.hbm.xml"
		};
	}

	public static Test suite() {
		return new FunctionalTestClassTestSuite( SQLFunctionsInterSystemsTest.class );
	}

	public boolean appliesTo(Dialect dialect) {
		// all these test case apply only to testing InterSystems' CacheSQL dialect
		return dialect instanceof Cache71Dialect;
	}

	public void testDialectSQLFunctions() throws Exception {

		Session s = openSession();
		Transaction t = s.beginTransaction();

		Iterator iter = s.iterate("select max(s.count) from Simple s");

		if ( getDialect() instanceof MySQLDialect ) assertTrue( iter.hasNext() && iter.next()==null );

		Simple simple = new Simple();
		simple.setName("Simple Dialect Function Test");
		simple.setAddress("Simple Address");
		simple.setPay(new Float(45.8));
		simple.setCount(2);
		s.save(simple, new Long(10) );

		// Test to make sure allocating an specified object operates correctly.
		assertTrue(
			s.find("select new org.hibernate.test.legacy.S(s.count, s.address) from Simple s").size() == 1
		);

		// Quick check the base dialect functions operate correctly
		assertTrue(
			s.find("select max(s.count) from Simple s").size() == 1
		);
		assertTrue(
			s.find("select count(*) from Simple s").size() == 1
		);

		if ( getDialect() instanceof Cache71Dialect) {
			// Check Oracle Dialect mix of dialect functions - no args (no parenthesis and single arg functions
			java.util.List rset = s.find("select s.name, sysdate, floor(s.pay), round(s.pay,0) from Simple s");
			assertNotNull("Name string should have been returned",(((Object[])rset.get(0))[0]));
			assertNotNull("Todays Date should have been returned",(((Object[])rset.get(0))[1]));
			assertEquals("floor(45.8) result was incorrect ", new Integer(45), ( (Object[]) rset.get(0) )[2] );
			assertEquals("round(45.8) result was incorrect ", new Float(46), ( (Object[]) rset.get(0) )[3] );

			simple.setPay(new Float(-45.8));
			s.update(simple);

			// Test type conversions while using nested functions (Float to Int).
			rset = s.find("select abs(round(s.pay,0)) from Simple s");
			assertEquals("abs(round(-45.8)) result was incorrect ", new Float(46), rset.get(0));

			// Test a larger depth 3 function example - Not a useful combo other than for testing
			assertTrue(
				s.find("select floor(round(sysdate,1)) from Simple s").size() == 1
			);

			// Test the oracle standard NVL funtion as a test of multi-param functions...
			simple.setPay(null);
			s.update(simple);
			Double value = (Double) s.createQuery("select mod( nvl(s.pay, 5000), 2 ) from Simple as s where s.id = 10").list().get(0);
			assertTrue( 0 == value.intValue() );
		}

		if ( (getDialect() instanceof Cache71Dialect) ) {
			// Test the hsql standard MOD funtion as a test of multi-param functions...
			Double value = (Double) s.find("select MOD(s.count, 2) from Simple as s where s.id = 10" ).get(0);
			assertTrue( 0 == value.intValue() );
        }

        /*
        if ( (getDialect() instanceof Cache71Dialect) ) {
            // Test the hsql standard MOD funtion as a test of multi-param functions...
            Date value = (Date) s.find("select sysdate from Simple as s where nvl(cast(null as date), sysdate)=sysdate" ).get(0);
            assertTrue( value.equals(new java.sql.Date(System.currentTimeMillis())));
        }
        */

        s.delete(simple);
		t.commit();
		s.close();
	}

	public void testSetProperties() throws Exception {
		Session s = openSession();
		Transaction t = s.beginTransaction();
		Simple simple = new Simple();
		simple.setName("Simple 1");
		s.save(simple, new Long(10) );
		Query q = s.createQuery("from Simple s where s.name=:name and s.count=:count");
		q.setProperties(simple);
		assertTrue( q.list().get(0)==simple );
		//misuse of "Single" as a propertyobject, but it was the first testclass i found with a collection ;)
		Single single = new Single() { // trivial hack to test properties with arrays.
			String[] getStuff() { return (String[]) getSeveral().toArray(new String[getSeveral().size()]); }
		};

		List l = new ArrayList();
		l.add("Simple 1");
		l.add("Slimeball");
		single.setSeveral(l);
		q = s.createQuery("from Simple s where s.name in (:several)");
		q.setProperties(single);
		assertTrue( q.list().get(0)==simple );


		q = s.createQuery("from Simple s where s.name in (:stuff)");
		q.setProperties(single);
		assertTrue( q.list().get(0)==simple );
		s.delete(simple);
		t.commit();
		s.close();
	}

	public void testBroken() throws Exception {
		if (getDialect() instanceof Oracle9Dialect) return;
		Session s = openSession();
		Transaction t = s.beginTransaction();
		Broken b = new Fixed();
		b.setId( new Long(123));
		b.setOtherId("foobar");
		s.save(b);
		s.flush();
		b.setTimestamp( new Date() );
		t.commit();
		s.close();

		s = openSession();
		t = s.beginTransaction();
		s.update(b);
		t.commit();
		s.close();

		s = openSession();
		t = s.beginTransaction();
		b = (Broken) s.load( Broken.class, b );
		t.commit();
		s.close();

		s = openSession();
		t = s.beginTransaction();
		s.delete(b);
		t.commit();
		s.close();
	}

	public void testNothinToUpdate() throws Exception {
		Session s = openSession();
		Transaction t = s.beginTransaction();
		Simple simple = new Simple();
		simple.setName("Simple 1");
		s.save( simple, new Long(10) );
		t.commit();
		s.close();

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

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

	public void testCachedQuery() throws Exception {
		Session s = openSession();
		Transaction t = s.beginTransaction();
		Simple simple = new Simple();
		simple.setName("Simple 1");
		s.save( simple, new Long(10) );
		t.commit();
		s.close();

		s = openSession();
		t = s.beginTransaction();
		Query q = s.createQuery("from Simple s where s.name=?");
		q.setCacheable(true);
		q.setString(0, "Simple 1");
		assertTrue( q.list().size()==1 );
		assertTrue( q.list().size()==1 );
		assertTrue( q.list().size()==1 );
		q = s.createQuery("from Simple s where s.name=:name");
		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();
		q = s.createQuery("from Simple s where s.name=:name");
		q.setString("name", "Simple 2");
		q.setCacheable(true);
		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.setCacheable(true);
		q.setString(0, "Simple 1");
		assertTrue( q.list().size()==0 );
		assertTrue( q.list().size()==0 );
		t.commit();
		s.close();
	}

	public void testCachedQueryRegion() throws Exception {
		Session s = openSession();
		Transaction t = s.beginTransaction();
		Simple simple = new Simple();
		simple.setName("Simple 1");
		s.save( simple, new Long(10) );
		t.commit();
		s.close();

		s = openSession();
		t = s.beginTransaction();
		Query q = s.createQuery("from Simple s where s.name=?");
		q.setCacheRegion("foo");
		q.setCacheable(true);
		q.setString(0, "Simple 1");
		assertTrue( q.list().size()==1 );
		assertTrue( q.list().size()==1 );
		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 Cache71Dialect) {
			s.find("from Simple s where repeat('foo', 3) = 'foofoofoo'");
			s.find("from Simple s where repeat(s.name, 3) = 'foofoofoo'");
			s.find("from Simple s where repeat( lower(s.name), (3 + (1-1)) / 2) = 'foofoofoo'");
		}

		assertTrue(
			s.find("from Simple s where upper( s.name ) ='SIMPLE 1'").size()==1
		);
		if ( !(getDialect() instanceof HSQLDialect) ) {
			assertTrue(
				s.find("from Simple s 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) && !(getDialect() instanceof TimesTenDialect) ) { //My SQL has a funny concatenation operator
			assertTrue(
				s.find("from Simple s where lower( s.name || ' foo' ) ='simple 1 foo'").size()==1
			);
		}