sqlloadertest.java

好东西,hibernate-3.2.0,他是一开元的树杖hibernate-3.2.0

//$Id: SQLLoaderTest.java 9238 2006-02-08 22:25:37Z steveebersole $
package org.hibernate.test.legacy;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Transaction;
import org.hibernate.classic.Session;
import org.hibernate.dialect.HSQLDialect;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.Oracle9Dialect;
import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.dialect.SQLServerDialect;
import org.hibernate.dialect.TimesTenDialect;
import org.hibernate.dialect.OracleDialect;


public class SQLLoaderTest extends LegacyTestCase {

	static int nextInt = 1;
	static long nextLong = 1;
	public SQLLoaderTest(String arg) {
		super(arg);
	}

	public void testTS() throws Exception {
		if (getDialect() instanceof Oracle9Dialect) return;
		Session session = openSession();
		Transaction txn = session.beginTransaction();
		Simple sim = new Simple();
		sim.setDate( new Date() );
		session.save( sim, new Long(1) );
		Query q = session.createSQLQuery("select {sim.*} from Simple {sim} where {sim}.date_ = ?", "sim", Simple.class);
		q.setTimestamp( 0, sim.getDate() );
		assertTrue ( q.list().size()==1 );
		session.delete(sim);
		txn.commit();
		session.close();
	}


	public void testFindBySQLStar() throws HibernateException, SQLException {
		Session session = openSession();
		session.delete("from Assignable");
		session.delete("from Category");
		session.delete("from Simple");
		session.delete("from A");

		Category s = new Category();
		s.setName(String.valueOf(nextLong++));
		session.save(s);

		Simple simple = new Simple();
		simple.init();
		session.save(simple, new Long(nextLong++));

		A a = new A();
		session.save(a);

		B b = new B();
		session.save(b);
		session.flush();

		session.createSQLQuery("select {category.*} from category {category}", "category", Category.class).list();
		session.createSQLQuery("select {simple.*} from Simple {simple}", "simple", Simple.class).list();
		session.createSQLQuery("select {a.*} from TA {a}", "a", A.class).list();

		session.connection().commit();
		session.close();

	}

	public void testFindBySQLProperties() throws HibernateException, SQLException {
			Session session = openSession();
			session.delete("from Category");

			Category s = new Category();
			s.setName(String.valueOf(nextLong++));
			session.save(s);

			s = new Category();
			s.setName("WannaBeFound");
			session.flush();

			Query query = session.createSQLQuery("select {category.*} from category {category} where {category}.name = :name", "category", Category.class);

			query.setProperties(s);
			//query.setParameter("name", s.getName());

			query.list();

			query = session.createSQLQuery("select {category.*} from category {category} where {category}.name in (:names)", "category", Category.class);
			String[] str = new String[] { "WannaBeFound", "NotThere" };
			query.setParameterList("names", str);
			
			query.uniqueResult();
			
			session.connection().commit();
			session.close();
			
			

		}

	public void testFindBySQLAssociatedObjects() throws HibernateException, SQLException {
		Session s = openSession();
		s.delete("from Assignable");
		s.delete("from Category");

		Category c = new Category();
		c.setName("NAME");
		Assignable assn = new Assignable();
		assn.setId("i.d.");
		List l = new ArrayList();
		l.add(c);
		assn.setCategories(l);
		c.setAssignable(assn);
		s.save(assn);
		s.flush();
		s.connection().commit();
		s.close();

		s = openSession();
		List list = s.createSQLQuery("select {category.*} from category {category}", "category", Category.class).list();
		list.get(0);
		s.connection().commit();
		s.close();
		
		if ( getDialect() instanceof MySQLDialect ) return;
		if ( getDialect() instanceof OracleDialect ) return; // todo : this fails on Oracle8 also

		s = openSession();

		Query query = s.getNamedQuery("namedsql");
		assertNotNull(query);
		list = query.list();
        assertNotNull(list);
		
		Object[] values = (Object[]) list.get(0);
		assertNotNull(values[0]);
		assertNotNull(values[1]);
		assertTrue("wrong type: " + values[0].getClass(), values[0] instanceof Category);
		assertTrue("wrong type: " + values[1].getClass(), values[1] instanceof Assignable);
		
		s.connection().commit();
		s.close();

	}

	public void testPropertyResultSQL() throws HibernateException, SQLException {
		
		if ( getDialect() instanceof MySQLDialect ) return;
			
		Session s = openSession();
		s.delete("from Assignable");
		s.delete("from Category");

		Category c = new Category();
		c.setName("NAME");
		Assignable assn = new Assignable();
		assn.setId("i.d.");
		List l = new ArrayList();
		l.add(c);
		assn.setCategories(l);
		c.setAssignable(assn);
		s.save(assn);
		s.flush();
		s.connection().commit();
		s.close();

		s = openSession();

		Query query = s.getNamedQuery("nonaliasedsql");
		assertNotNull(query);
		List list = query.list();
        assertNotNull(list);
		
		assertTrue(list.get(0) instanceof Category);
		
		s.connection().commit();
		s.close();

	}
	
	public void testFindBySQLMultipleObject() throws HibernateException, SQLException {
		Session s = openSession();
		s.delete("from Assignable");
		s.delete("from Category");
		s.flush();
		s.connection().commit();
		s.close();
		s = openSession();
		Category c = new Category();
		c.setName("NAME");
		Assignable assn = new Assignable();
		assn.setId("i.d.");
		List l = new ArrayList();
		l.add(c);
		assn.setCategories(l);
		c.setAssignable(assn);
		s.save(assn);
		s.flush();
		c = new Category();
		c.setName("NAME2");
		assn = new Assignable();
		assn.setId("i.d.2");
		l = new ArrayList();
		l.add(c);
		assn.setCategories(l);
		c.setAssignable(assn);
		s.save(assn);
		s.flush();

		assn = new Assignable();
		assn.setId("i.d.3");
		s.save(assn);
		s.flush();
		s.connection().commit();
		s.close();

		if ( getDialect() instanceof MySQLDialect ) return;

		s = openSession();
		List list = s.createSQLQuery("select {category.*}, {assignable.*} from category {category}, \"assign-able\" {assignable}", new String[] { "category", "assignable" }, new Class[] { Category.class, Assignable.class }).list();

		assertTrue(list.size() == 6); // crossproduct of 2 categories x 3 assignables
		assertTrue(list.get(0) instanceof Object[]);
		s.connection().commit();
		s.close();
	}

	public void testFindBySQLParameters() throws HibernateException, SQLException {
		Session s = openSession();
		s.delete("from Assignable");
		s.delete("from Category");
		s.flush();
		s.connection().commit();
		s.close();
		s = openSession();
		Category c = new Category();
		c.setName("Good");
		Assignable assn = new Assignable();
		assn.setId("i.d.");
		List l = new ArrayList();
		l.add(c);
		assn.setCategories(l);
		c.setAssignable(assn);
		s.save(assn);
		s.flush();
		c = new Category();
		c.setName("Best");
		assn = new Assignable();
		assn.setId("i.d.2");
		l = new ArrayList();
		l.add(c);
		assn.setCategories(l);
		c.setAssignable(assn);
		s.save(assn);
		s.flush();
		c = new Category();
		c.setName("Better");
		assn = new Assignable();
		assn.setId("i.d.7");
		l = new ArrayList();
		l.add(c);
		assn.setCategories(l);
		c.setAssignable(assn);
		s.save(assn);
		s.flush();

		assn = new Assignable();
		assn.setId("i.d.3");
		s.save(assn);
		s.flush();
		s.connection().commit();
		s.close();

		s = openSession();
		Query basicParam = s.createSQLQuery("select {category.*} from category {category} where {category}.name = 'Best'", "category", Category.class);
		List list = basicParam.list();
		assertEquals(1, list.size());

		Query unnamedParam = s.createSQLQuery("select {category.*} from category {category} where {category}.name = ? or {category}.name = ?", "category", Category.class);
		unnamedParam.setString(0, "Good");
		unnamedParam.setString(1, "Best");
		list = unnamedParam.list();
		assertEquals(2, list.size());

		Query namedParam = s.createSQLQuery("select {category.*} from category {category} where ({category}.name=:firstCat or {category}.name=:secondCat)", "category", Category.class);
		namedParam.setString("firstCat", "Better");
		namedParam.setString("secondCat", "Best");
		list = namedParam.list();
		assertEquals(2, list.size());

		s.connection().commit();
		s.close();
	}

	public void testEscapedJDBC() throws HibernateException, SQLException {
		if ( 
				getDialect() instanceof HSQLDialect || 
				getDialect() instanceof PostgreSQLDialect
		) return;

		Session session = openSession();
		session.delete("from A");
		A savedA = new A();
		savedA.setName("Max");
		session.save(savedA);

		B savedB = new B();
		session.save(savedB);
		session.flush();

		int count = session.createQuery("from A").list().size();
		session.close();

		session = openSession();

		Query query;
		if ( getDialect() instanceof OracleDialect ) {
			// Oracle8 does not support X/Open extension functions :)
			query = session.createSQLQuery("select identifier_column as {a.id}, clazz_discriminata as {a.class}, count_ as {a.count}, name as {a.name} from TA where upper(name) like upper('max')", "a", A.class);
		}
		else if( getDialect() instanceof TimesTenDialect) {
            // TimesTen does not permit general expressions (like UPPER) in the second part of a LIKE expression,
            // so we execute a similar test 
            query = session.createSQLQuery("select identifier_column as {a.id}, clazz_discriminata as {a.class}, count_ as {a.count}, name as {a.name} from TA where {fn ucase(name)} like 'MAX'", "a", A.class);
        } else {
            query = session.createSQLQuery("select identifier_column as {a.id}, clazz_discriminata as {a.class}, count_ as {a.count}, name as {a.name} from TA where {fn ucase(name)} like {fn ucase('max')}", "a", A.class);
        }
		List list = query.list();