Java Code Examples for org.hibernate.classic.Session#createSQLQuery()

private void componentTest(String sql) throws SQLException {
    Session session = openSession();
 
 Componentizable c = setupComponentData( session );
    
 Query q = session.createSQLQuery(sql, "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.flush();
 session.connection().commit();
 session.close();
}
 

public void testFindSimpleBySQL() throws Exception {
	if ( getDialect() instanceof MySQLDialect ) return;
	Session session = openSession();
	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", "category", Category.class);
	List list = query.list();

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

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

	if ( getDialect() instanceof MySQLDialect ) return;

	session = openSession();

	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", "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.connection().commit();
	session.close();
}
 

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 testFindBySQLDiscriminatedSameSession() throws Exception {
	Session session = openSession();
	session.delete("from A");
	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}", "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.connection().commit();
	session.close();
}
 

public void testCompositeIdId() throws HibernateException, SQLException {
 // issue HHH-21
    Session s = openSession();

    CompositeIdId id = new CompositeIdId();
    id.setName("Max");
    id.setSystem("c64");
    id.setId("games");

    s.save(id);
    s.flush();
    s.connection().commit();
    s.close();

    s = openSession();
    // having a composite id with one property named id works since the map used by sqlloader to map names to properties handles it.
    Query query = s.createSQLQuery("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=?", "c", CompositeIdId.class);
    query.setString(0, "c64");
    query.setString(1, "games");

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

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

    s = openSession();

    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.connection().commit();
    s.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 testEmbeddedCompositeProperties() throws HibernateException, SQLException {
   Session session = openSession();

   Single s = new Single();
   s.setId("my id");
   s.setString("string 1");
   session.save(s);
   session.flush();
   session.connection().commit();

   session.clear();

   Query query = session.createSQLQuery("select {sing.*} from Single {sing}", "sing", Single.class);

   List list = query.list();

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

   session.clear();

   query = session.createSQLQuery("select {sing.*} from Single {sing} where sing.id = ?", "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 = ?", "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 = ?", "sing", Single.class);
   query.setString(0, "my id");
   list = query.list();

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

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

}