nativesqlqueriestest.java

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

package org.hibernate.test.sql.hand.query;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import junit.framework.Test;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.test.sql.hand.Organization;
import org.hibernate.test.sql.hand.Person;
import org.hibernate.test.sql.hand.Employment;
import org.hibernate.test.sql.hand.Product;
import org.hibernate.test.sql.hand.Order;
import org.hibernate.test.sql.hand.Dimension;
import org.hibernate.test.sql.hand.SpaceShip;
import org.hibernate.test.sql.hand.Speech;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;
import org.hibernate.junit.functional.FunctionalTestCase;
import org.hibernate.junit.functional.FunctionalTestClassTestSuite;
import org.hibernate.transform.DistinctRootEntityResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.transform.AliasToEntityMapResultTransformer;

/**
 * Tests of various features of native SQL queries.
 *
 * @author Steve Ebersole
 */
public class NativeSQLQueriesTest extends FunctionalTestCase {

	public NativeSQLQueriesTest(String x) {
		super( x );
	}

	public String[] getMappings() {
		return new String[] { "sql/hand/query/NativeSQLQueries.hbm.xml" };
	}

	public void configure(Configuration cfg) {
		super.configure( cfg );
		cfg.setProperty( Environment.GENERATE_STATISTICS, "true" );
	}

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

	protected String getOrganizationFetchJoinEmploymentSQL() {
		return "SELECT org.ORGID as {org.id}, " +
		       "        org.NAME as {org.name}, " +
		       "        emp.EMPLOYER as {emp.key}, " +
		       "        emp.EMPID as {emp.element}, " +
		       "        {emp.element.*}  " +
		       "FROM ORGANIZATION org " +
		       "    LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER";
	}

	protected String getOrganizationJoinEmploymentSQL() {
		return "SELECT org.ORGID as {org.id}, " +
		       "        org.NAME as {org.name}, " +
		       "        {emp.*}  " +
		       "FROM ORGANIZATION org " +
		       "    LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER";
	}

	protected String getEmploymentSQL() {
		return "SELECT * FROM EMPLOYMENT";
	}

	protected String getEmploymentSQLMixedScalarEntity() {
		return "SELECT e.*, e.employer as employerid  FROM EMPLOYMENT e" ;
	}

	protected String getOrgEmpRegionSQL() {
		return "select {org.*}, {emp.*}, emp.REGIONCODE " +
		       "from ORGANIZATION org " +
		       "     left outer join EMPLOYMENT emp on org.ORGID = emp.EMPLOYER";
	}

	protected String getOrgEmpPersonSQL() {
		return "select {org.*}, {emp.*}, {pers.*} " +
		       "from ORGANIZATION org " +
		       "    join EMPLOYMENT emp on org.ORGID = emp.EMPLOYER " +
		       "    join PERSON pers on pers.PERID = emp.EMPLOYEE ";
	}

	public void testFailOnNoAddEntityOrScalar() {
		// Note: this passes, but for the wrong reason.
		//      there is actually an exception thrown, but it is the database
		//      throwing a sql exception because the SQL gets passed
		//      "un-processed"...
		Session s = openSession();
		s.beginTransaction();
		try {
			String sql = "select {org.*} " +
			             "from organization org";
			s.createSQLQuery( sql ).list();
			fail( "Should throw an exception since no addEntity nor addScalar has been performed." );
		}
		catch( HibernateException he) {
			// expected behavior
		}
		finally {
			s.getTransaction().rollback();
			s.close();
		}
	}

	public void testManualSynchronization() {
		Session s = openSession();
		s.beginTransaction();

		sfi().getStatistics().clear();

		// create an Organization...
		Organization jboss = new Organization( "JBoss" );
		s.persist( jboss );

		// now query on Employment, this should not cause an auto-flush
		s.createSQLQuery( getEmploymentSQL() ).list();
		assertEquals( 0, sfi().getStatistics().getEntityInsertCount() );

		// now try to query on Employment but this time add Organization as a synchronized query space...
		s.createSQLQuery( getEmploymentSQL() ).addSynchronizedEntityClass( Organization.class ).list();
		assertEquals( 1, sfi().getStatistics().getEntityInsertCount() );

		// clean up
		s.delete( jboss );
		s.getTransaction().commit();
		s.close();
	}

	public void testSQLQueryInterface() {
		Session s = openSession();
		Transaction t = s.beginTransaction();
		Organization ifa = new Organization("IFA");
		Organization jboss = new Organization("JBoss");
		Person gavin = new Person("Gavin");
		Employment emp = new Employment(gavin, jboss, "AU");

		s.persist(ifa);
		s.persist(jboss);
		s.persist(gavin);
		s.persist(emp);

		List l = s.createSQLQuery( getOrgEmpRegionSQL() )
				.addEntity("org", Organization.class)
				.addJoin("emp", "org.employments")
				.addScalar("regionCode", Hibernate.STRING)
				.list();
		assertEquals( 2, l.size() );

		l = s.createSQLQuery( getOrgEmpPersonSQL() )
				.addEntity("org", Organization.class)
				.addJoin("emp", "org.employments")
				.addJoin("pers", "emp.employee")
				.list();
		assertEquals( l.size(), 1 );

		t.commit();
		s.close();

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

		l = s.createSQLQuery( "select {org.*}, {emp.*} " +
			       "from ORGANIZATION org " +
			       "     left outer join EMPLOYMENT emp on org.ORGID = emp.EMPLOYER, ORGANIZATION org2" )
		.addEntity("org", Organization.class)
		.addJoin("emp", "org.employments")
		.setResultTransformer(new DistinctRootEntityResultTransformer())
		.list();
		assertEquals( l.size(), 2 );

		t.commit();
		s.close();

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

		s.delete(emp);
		s.delete(gavin);
		s.delete(ifa);
		s.delete(jboss);

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

	public void testResultSetMappingDefinition() {
		Session s = openSession();
		Transaction t = s.beginTransaction();
		Organization ifa = new Organization("IFA");
		Organization jboss = new Organization("JBoss");
		Person gavin = new Person("Gavin");
		Employment emp = new Employment(gavin, jboss, "AU");

		s.persist(ifa);
		s.persist(jboss);
		s.persist(gavin);
		s.persist(emp);

		List l = s.createSQLQuery( getOrgEmpRegionSQL() )
				.setResultSetMapping( "org-emp-regionCode" )
				.list();
		assertEquals( l.size(), 2 );

		l = s.createSQLQuery( getOrgEmpPersonSQL() )
				.setResultSetMapping( "org-emp-person" )
				.list();
		assertEquals( l.size(), 1 );

		s.delete(emp);
		s.delete(gavin);
		s.delete(ifa);
		s.delete(jboss);

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

	public void testScalarValues() throws Exception {
		Session s = openSession();
		Transaction t = s.beginTransaction();

		Organization ifa = new Organization( "IFA" );
		Organization jboss = new Organization( "JBoss" );

		Serializable idIfa = s.save( ifa );
		Serializable idJBoss = s.save( jboss );

		s.flush();

		List result = s.getNamedQuery( "orgNamesOnly" ).list();
		assertTrue( result.contains( "IFA" ) );
		assertTrue( result.contains( "JBoss" ) );

		result = s.getNamedQuery( "orgNamesOnly" ).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		Map m = (Map) result.get(0);
		assertEquals( 2, result.size() );
		assertEquals( 1, m.size() );
		assertTrue( m.containsKey("NAME") );

		t.commit();
		s.close();

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

		Iterator iter = s.getNamedQuery( "orgNamesAndOrgs" ).list().iterator();
		Object[] o = ( Object[] ) iter.next();
		assertEquals( o[0], "IFA" );
		assertEquals( ( ( Organization ) o[1] ).getName(), "IFA" );
		o = ( Object[] ) iter.next();
		assertEquals( o[0], "JBoss" );
		assertEquals( ( ( Organization ) o[1] ).getName(), "JBoss" );

		t.commit();
		s.close();

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

		// test that the ordering of the results is truly based on the order in which they were defined
		iter = s.getNamedQuery( "orgsAndOrgNames" ).list().iterator();
		Object[] row = ( Object[] ) iter.next();
		assertEquals( "expecting non-scalar result first", Organization.class, row[0].getClass() );
		assertEquals( "expecting scalar result second", String.class, row[1].getClass() );
		assertEquals( ( ( Organization ) row[0] ).getName(), "IFA" );
		assertEquals( row[1], "IFA" );
		row = ( Object[] ) iter.next();
		assertEquals( "expecting non-scalar result first", Organization.class, row[0].getClass() );
		assertEquals( "expecting scalar result second", String.class, row[1].getClass() );
		assertEquals( ( ( Organization ) row[0] ).getName(), "JBoss" );
		assertEquals( row[1], "JBoss" );
		assertFalse( iter.hasNext() );

		t.commit();
		s.close();

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

		iter = s.getNamedQuery( "orgIdsAndOrgNames" ).list().iterator();
		o = ( Object[] ) iter.next();
		assertEquals( o[1], "IFA" );
		assertEquals( o[0], idIfa );
		o = ( Object[] ) iter.next();
		assertEquals( o[1], "JBoss" );
		assertEquals( o[0], idJBoss );

		s.delete( ifa );
		s.delete( jboss );
		t.commit();
		s.close();
	}

	public void testMappedAliasStrategy() {
		Session s = openSession();
		Transaction t = s.beginTransaction();
		Organization ifa = new Organization("IFA");
		Organization jboss = new Organization("JBoss");