sqlfunctionstest.java

介绍了hibernate的入门有一些基本常用的事例

//$Id: SQLFunctionsTest.java,v 1.7 2005/02/23 03:38:47 oneovthafew Exp $
package org.hibernate.test.legacy;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import junit.framework.Test;
import junit.framework.TestSuite;
import junit.textui.TestRunner;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.ScrollableResults;
import org.hibernate.Transaction;
import org.hibernate.classic.Session;
import org.hibernate.dialect.DB2Dialect;
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.OracleDialect;
import org.hibernate.dialect.SybaseDialect;
import org.hibernate.dialect.TimesTenDialect;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.test.TestCase;

public class SQLFunctionsTest extends TestCase {

	private static final Log log = LogFactory.getLog(SQLFunctionsTest.class);

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

	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 OracleDialect) {
			// 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(), trunc(s.pay), round(s.pay) 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("trunc(45.8) result was incorrect ", new Float(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)) 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 trunc(round(sysdate())) from Simple s").size() == 1
			);

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

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

		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 DB2Dialect) {
			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(