alvinalexander.com | career | drupal | java | mac | mysql | perl | scala | uml | unix  

Hibernate example source code file (SQLLoaderTest.java)

This example Hibernate source code file (SQLLoaderTest.java) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.

Java - Hibernate tags/keywords

a, a, assignable, assignable, b, category, category, jdbc, list, object, object, query, session, sql, sqlexception, test, util

The Hibernate SQLLoaderTest.java source code

//$Id: SQLLoaderTest.java 11383 2007-04-02 15:34:02Z steve.ebersole@jboss.com $
package org.hibernate.test.legacy;

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

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.dialect.HSQLDialect;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.dialect.TimesTenDialect;

import org.junit.Test;

import org.hibernate.testing.FailureExpected;
import org.hibernate.testing.SkipForDialect;
import org.hibernate.testing.TestForIssue;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertSame;
import static org.junit.Assert.assertTrue;

public class SQLLoaderTest extends LegacyTestCase {
	static int nextInt = 1;
	static long nextLong = 1;

	@Override
	public String[] getMappings() {
		return new String[] {
			"legacy/ABC.hbm.xml",
			"legacy/Category.hbm.xml",
			"legacy/Simple.hbm.xml",
			"legacy/Fo.hbm.xml",
			"legacy/SingleSeveral.hbm.xml",
			"legacy/Componentizable.hbm.xml",
            "legacy/CompositeIdId.hbm.xml"
		};
	}

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

	@Test
	public void testFindBySQLStar() throws HibernateException, SQLException {
		Session session = openSession();
		session.beginTransaction();
		for ( Object entity : session.createQuery( "from Assignable" ).list() ) {
			session.delete( entity );
		}
		for ( Object entity : session.createQuery( "from Category" ).list() ) {
			session.delete( entity );
		}
		for ( Object entity : session.createQuery( "from Simple" ).list() ) {
			session.delete( entity );
		}
		for ( Object entity : session.createQuery( "from A" ).list() ) {
			session.delete( entity );
		}

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

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

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

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

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

		session.getTransaction().commit();
		session.close();
	}

	@Test
	public void testFindBySQLProperties() throws HibernateException, SQLException {
		Session session = openSession();
		session.beginTransaction();
		for ( Object entity : session.createQuery( "from Category" ).list() ) {
			session.delete( entity );
		}

		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" )
				.addEntity( "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)" )
				.addEntity( "category", Category.class );
		String[] str = new String[] { "WannaBeFound", "NotThere" };
		query.setParameterList("names", str);
		query.uniqueResult();

		query = session.createSQLQuery( "select {category.*} from category {category} where {category}.name in :names" )
				.addEntity( "category", Category.class );
		query.setParameterList("names", str);
		query.uniqueResult();

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

		query = session.createSQLQuery( "select {category.*} from category {category} where {category}.name in :names" )
				.addEntity( "category", Category.class );
		query.setParameterList("names", str);
		query.uniqueResult();

		session.getTransaction().commit();
		session.close();
	}

	@Test
	public void testFindBySQLAssociatedObjects() throws HibernateException, SQLException {
		Session s = openSession();
		s.beginTransaction();
		for ( Object entity : s.createQuery( "from Assignable" ).list() ) {
			s.delete( entity );
		}
		for ( Object entity : s.createQuery( "from Category" ).list() ) {
			s.delete( entity );
		}

		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.getTransaction().commit();
		s.close();

		s = openSession();
		s.beginTransaction();
		List list = s.createSQLQuery( "select {category.*} from category {category}" ).addEntity( "category", Category.class ).list();
		list.get(0);
		s.getTransaction().commit();
		s.close();
		
		if ( getDialect() instanceof MySQLDialect ) {
			return;
		}

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

		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.getTransaction().commit();
		s.close();

	}

	@Test
	@SkipForDialect( MySQLDialect.class )
	public void testPropertyResultSQL() throws HibernateException, SQLException {
		Session s = openSession();
		s.beginTransaction();
		for ( Object entity : s.createQuery( "from Assignable" ).list() ) {
			s.delete( entity );
		}
		for ( Object entity : s.createQuery( "from Category" ).list() ) {
			s.delete( entity );
		}

		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.getTransaction().commit();
		s.close();

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

	}

	@Test
	public void testFindBySQLMultipleObject() throws HibernateException, SQLException {
		Session s = openSession();
		s.beginTransaction();
		for ( Object entity : s.createQuery( "from Assignable" ).list() ) {
			s.delete( entity );
		}
		for ( Object entity : s.createQuery( "from Category" ).list() ) {
			s.delete( entity );
		}
		s.getTransaction().commit();
		s.close();

		s = openSession();
		s.beginTransaction();
		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.getTransaction().commit();
		s.close();

		if ( getDialect() instanceof MySQLDialect ) {
			return;
		}

		s = openSession();
		s.beginTransaction();
		String sql = "select {category.*}, {assignable.*} from category {category}, \"assign-able\" {assignable}";

		List list = s.createSQLQuery( sql ).addEntity( "category", Category.class ).addEntity( "assignable", Assignable.class ).list();

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

	@Test
	public void testFindBySQLParameters() throws HibernateException, SQLException {
		Session s = openSession();
		s.beginTransaction();
		for ( Object entity : s.createQuery( "from Assignable" ).list() ) {
			s.delete( entity );
		}
		for ( Object entity : s.createQuery( "from Category" ).list() ) {
			s.delete( entity );
		}
		s.getTransaction().commit();
		s.close();

		s = openSession();
		s.beginTransaction();
		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.getTransaction().commit();
		s.close();

		s = openSession();
		s.beginTransaction();
		Query basicParam = s.createSQLQuery( "select {category.*} from category {category} where {category}.name = 'Best'" )
				.addEntity( "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 = ?" )
				.addEntity( "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)" )
				.addEntity( "category", Category.class);
		namedParam.setString("firstCat", "Better");
		namedParam.setString("secondCat", "Best");
		list = namedParam.list();
		assertEquals(2, list.size());
		s.getTransaction().commit();
		s.close();
	}

	@Test
	@SkipForDialect( { HSQLDialect.class, PostgreSQLDialect.class } )
	public void testEscapedJDBC() throws HibernateException, SQLException {
		Session session = openSession();
		session.beginTransaction();
		for ( Object entity : session.createQuery( "from A" ).list() ) {
			session.delete( entity );
		}
		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.getTransaction().commit();
		session.close();

		session = openSession();
		session.beginTransaction();

		Query query;
		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'" )
					.addEntity( "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')}" )
					.addEntity( "a", A.class );
        }
		List list = query.list();

		assertNotNull(list);
		assertEquals(1, list.size());
		session.getTransaction().commit();
		session.close();
	}

	@Test
	public void testDoubleAliasing() throws HibernateException, SQLException {
		Session session = openSession();
		session.beginTransaction();
		for ( Object entity : session.createQuery( "from A" ).list() ) {
			session.delete( entity );
		}
		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.getTransaction().commit();
		session.close();

		session = openSession();
		session.beginTransaction();
		String sql = "select a.identifier_column as {a1.id}, " +
				"    a.clazz_discriminata as {a1.class}, " +
				"    a.count_ as {a1.count}, " +
				"    a.name as {a1.name}, " +
				"    b.identifier_column as {a2.id}, " +
				"    b.clazz_discriminata as {a2.class}, " +
				"    b.count_ as {a2.count}, " +
				"    b.name as {a2.name} " +
				"from TA a, TA b " +
				"where a.identifier_column = b.identifier_column";
		Query query = session.createSQLQuery( sql ).addEntity( "a1", A.class ).addEntity( "a2", A.class );
		List list = query.list();

		assertNotNull(list);
		assertEquals(2, list.size());
		session.getTransaction().commit();
		session.close();
	}

	@Test
	public void testEmbeddedCompositeProperties() throws HibernateException, SQLException {
		Session session = openSession();
		session.beginTransaction();
		Single s = new Single();
		s.setId("my id");
		s.setString("string 1");
		session.save(s);
		session.getTransaction().commit();

		session = openSession();
		session.beginTransaction();

		SQLQuery query = session.createSQLQuery( "select {sing.*} from Single {sing}" ).addEntity( "sing", Single.class );
		List list = query.list();
		assertTrue(list.size()==1);

		session.clear();

		query = session.createSQLQuery( "select {sing.*} from Single {sing} where sing.id = ?" ).addEntity( "sing", Single.class );
	   	query.setString(0, "my id");
	   	list = query.list();
		assertTrue(list.size()==1);

		session.clear();

		query = session.createSQLQuery( "select s.id as {sing.id}, s.string_ as {sing.string}, s.prop as {sing.prop} from Single s where s.id = ?" )
			   .addEntity( "sing", Single.class );
		query.setString(0, "my id");
	   	list = query.list();
		assertTrue(list.size()==1);

		session.clear();

		query = session.createSQLQuery( "select s.id as {sing.id}, s.string_ as {sing.string}, s.prop as {sing.prop} from Single s where s.id = ?" )
			   .addEntity( "sing", Single.class );
		query.setString(0, "my id");
		list = query.list();

		assertTrue(list.size()==1);

		session.getTransaction().commit();
		session.close();
	}

	@Test
	@FailureExpected( jiraKey = "unknown" )
	public void testReturnPropertyComponentRename() throws HibernateException, SQLException {
		// failure expected because this was a regression introduced previously which needs to get tracked down.
		Componentizable componentizable = setupComponentData();
		
		Session session = openSession();
		session.beginTransaction();
		Query namedQuery = session.getNamedQuery("queryComponentWithOtherColumn");
		List list = namedQuery.list();
		
		assertEquals(1, list.size());
		assertEquals( "flakky comp", ( (Componentizable) list.get(0) ).getComponent().getName() );
		
		session.clear();
		session.delete(componentizable);
		session.getTransaction().commit();
		session.close();
	}
	
	@Test
	public void testComponentStar() throws HibernateException, SQLException {
	    componentTest("select {comp.*} from Componentizable comp");
	}
	
	@Test
	public void testComponentNoStar() throws HibernateException, SQLException {
	    componentTest("select comp.id as {comp.id}, comp.nickName as {comp.nickName}, comp.name as {comp.component.name}, comp.subName as {comp.component.subComponent.subName}, comp.subName1 as {comp.component.subComponent.subName1} from Componentizable comp");
	}

	private void componentTest(String sql) throws SQLException {
	    Componentizable c = setupComponentData();

		Session session = openSession();
		session.beginTransaction();
		SQLQuery q = session.createSQLQuery( sql ).addEntity( "comp", Componentizable.class );
	    List list = q.list();
	    assertEquals(list.size(),1);

	    Componentizable co = (Componentizable) list.get(0);
	    assertEquals(c.getNickName(), co.getNickName());
	    assertEquals(c.getComponent().getName(), co.getComponent().getName());
	    assertEquals(c.getComponent().getSubComponent().getSubName(), co.getComponent().getSubComponent().getSubName());

	    session.delete( co );
		session.getTransaction().commit();
	    session.close();
    }

	private Componentizable setupComponentData() throws SQLException {
		Session session = sessionFactory().openSession();
		session.beginTransaction();

		Componentizable c = new Componentizable();
	    c.setNickName("Flacky");
	    Component component = new Component();
	    component.setName("flakky comp");
	    SubComponent subComponent = new SubComponent();
	    subComponent.setSubName("subway");
        component.setSubComponent(subComponent);
	    
        c.setComponent(component);
        
        session.save(c);
		session.getTransaction().commit();
        session.clear();

		return c;
	}

	@Test
	@SkipForDialect( MySQLDialect.class )
    public void testFindSimpleBySQL() throws Exception {
		Session session = openSession();
		session.beginTransaction();
		Category s = new Category();
		s.setName(String.valueOf(nextLong++));
		session.save(s);
		session.flush();

		Query query = session.createSQLQuery( "select s.category_key_col as {category.id}, s.name as {category.name}, s.\"assign-able-id\" as {category.assignable} from {category} s" )
				.addEntity( "category", Category.class );
		List list = query.list();

		assertNotNull(list);
		assertTrue(list.size() > 0);
		assertTrue(list.get(0) instanceof Category);
		session.getTransaction().commit();
		session.close();
		// How do we handle objects with composite id's ? (such as Single)
	}

	@Test
	public void testFindBySQLSimpleByDiffSessions() throws Exception {
		Session session = openSession();
		session.beginTransaction();
		Category s = new Category();
		s.setName(String.valueOf(nextLong++));
		session.save(s);
		session.getTransaction().commit();
		session.close();

		if ( getDialect() instanceof MySQLDialect ) {
			return;
		}

		session = openSession();
		session.beginTransaction();

		Query query = session.createSQLQuery( "select s.category_key_col as {category.id}, s.name as {category.name}, s.\"assign-able-id\" as {category.assignable} from {category} s" )
				.addEntity( "category", Category.class );
		List list = query.list();

		assertNotNull(list);
		assertTrue(list.size() > 0);
		assertTrue(list.get(0) instanceof Category);

		// How do we handle objects that does not have id property (such as Simple ?)
		// How do we handle objects with composite id's ? (such as Single)
		session.getTransaction().commit();
		session.close();
	}

	@Test
	public void testFindBySQLDiscriminatedSameSession() throws Exception {
		Session session = openSession();
		session.beginTransaction();
		for ( Object entity : session.createQuery( "from A" ).list() ) {
			session.delete( entity );
		}
		A savedA = new A();
		session.save(savedA);

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

		Query query = session.createSQLQuery( "select identifier_column as {a.id}, clazz_discriminata as {a.class}, name as {a.name}, count_ as {a.count} from TA {a}" )
				.addEntity( "a", A.class );
		List list = query.list();

		assertNotNull(list);
		assertEquals(2, list.size());

		A a1 = (A) list.get(0);
		A a2 = (A) list.get(1);

		assertTrue((a2 instanceof B) || (a1 instanceof B));
		assertFalse(a1 instanceof B && a2 instanceof B);

		if (a1 instanceof B) {
			assertSame(a1, savedB);
			assertSame(a2, savedA);
		}
		else {
			assertSame(a2, savedB);
			assertSame(a1, savedA);
		}

		session.clear();
		List list2 = session.getNamedQuery("propertyResultDiscriminator").list();
		assertEquals(2, list2.size());
		
		session.getTransaction().commit();
		session.close();
	}

	@Test
	public void testFindBySQLDiscriminatedDiffSession() throws Exception {
		Session session = openSession();
		session.beginTransaction();
		for ( Object entity : session.createQuery( "from A" ).list() ) {
			session.delete( entity );
		}
		A savedA = new A();
		session.save(savedA);

		B savedB = new B();
		session.save(savedB);
		session.getTransaction().commit();
		int count = session.createQuery("from A").list().size();
		session.close();

		session = openSession();
		session.beginTransaction();
		Query query = session.createSQLQuery( "select identifier_column as {a.id}, clazz_discriminata as {a.class}, count_ as {a.count}, name as {a.name} from TA" )
				.addEntity( "a", A.class );
		List list = query.list();

		assertNotNull(list);
		assertEquals(count, list.size());
		session.getTransaction().commit();
		session.close();
	}

	@Test
	@TestForIssue( jiraKey = "HHH-21" )
    public void testCompositeIdId() throws HibernateException, SQLException {
        Session s = openSession();
		s.beginTransaction();
        CompositeIdId id = new CompositeIdId();
        id.setName("Max");
        id.setSystem("c64");
        id.setId("games");
        s.save(id);
		s.getTransaction().commit();
        s.close();

        s = openSession();
		s.beginTransaction();
        // having a composite id with one property named id works since the map used by sqlloader to map names to properties handles it.
		String sql = "select system as {c.system}, id as {c.id}, name as {c.name}, foo as {c.composite.foo}, bar as {c.composite.bar} from CompositeIdId where system=? and id=?";
		SQLQuery query = s.createSQLQuery( sql ).addEntity( "c", CompositeIdId.class );
        query.setString(0, "c64");
        query.setString(1, "games");

        CompositeIdId id2 = (CompositeIdId) query.uniqueResult();
        check(id, id2);

		s.getTransaction().commit();
        s.close();

        s = openSession();
		s.beginTransaction();
        CompositeIdId useForGet = new CompositeIdId();
        useForGet.setSystem("c64");
        useForGet.setId("games");
        // this doesn't work since the verification does not take column span into respect!
        CompositeIdId getted = (CompositeIdId) s.get(CompositeIdId.class, useForGet);
        check(id,getted);
		s.getTransaction().commit();
        s.close();
    }

    private void check(CompositeIdId id, CompositeIdId id2) {
        assertEquals(id,id2);
        assertEquals(id.getName(), id2.getName());
        assertEquals(id.getId(), id2.getId());
        assertEquals(id.getSystem(), id2.getSystem());
    }

}

Other Hibernate examples (source code examples)

Here is a short list of links related to this Hibernate SQLLoaderTest.java source code file:

... this post is sponsored by my books ...

#1 New Release!

FP Best Seller

 

new blog posts

 

Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.

A percentage of advertising revenue from
pages under the /java/jwarehouse URI on this website is
paid back to open source projects.