sorted by: recent | see : popular

Content Tagged with tag4sree + SQL

I found myself trying to fix a bug in the nHibernate source today.With nHibernate its a pleasure to have a lot of unit tests to make sure you (hopefully) haven't broken anything with your changes. I did what i always do, checked out the nHibernate trunk ran all the tests and no failures. Then i messed around with the source made some changes and wanted to make sure all tests still passed. However in the middle of my fiddling i had dropped the nHibernate database used for unittests and recreated it, but that shouldn't be a problem. nHibernate creates tables needed during its unit testing so all is good.

Then a test failed, and this test had nothing to do with the things i worked on, so i went to investigate the failing testcase called NHibernate.Test.SubselectFetchTest.SubselectFetchFixture.SubselectFetchWithLimit the testcase tries to fetch 2 rows from the database out of tree added and do various asserts on their children to check that collection fetching using the subselect fetch strategy is working. The assertion that failed was that a wrong number of child elements were on one of the parents, and a little investigation showed that the 2 rows returned was not the ones the test expected. To reproduce the error a test like this could be written:

[Test]
public void WrongElementReturnedFromLimitQuery()
{
	//Save three objects to the database with different names
	ISession s = OpenSession();
	Parent p1 = new Parent("foo");
	Parent p2 = new Parent("bar");
	Parent p3 = new Parent("aaa");
	s.Save(p1);
	s.Save(p2);
	s.Save(p3);

	s.Flush();

	IList<Parent> parents = s.CreateQuery("from Parent order by Name desc")
				.SetMaxResults(2)
				.List<Parent>();
	Assert.Equal("foo", parents[0].Name); //Fails expected "foo" was "aaa"
	Assert.Equal("bar", parents[1].Name); // Fails expected "bar" was "foo"
}

I was a bit puzzled at first, these sort of queries in nHibernate is pretty well tested and as i said i had not changed anything related to this, but to be sure i quickly reverted my changes and ran the tests again. And it still failed... Then i suddenly realised what was wrong and why this test suddenly failed. Take a moment and give me your guess on why... 

Yep, you guessed it. My new database running the tests were created using danish collation for sorting and in denmark aa is the same as the danish charecter å which is last in the alphabeth.

I found myself trying to fix a bug in the nHibernate source today.With nHibernate its a pleasure to have a lot of unit tests to make sure you (hopefully) haven't broken anything with your changes. I did what i always do, checked out the nHibernate trunk ran all the tests and no failures. Then i messed around with the source made some changes and wanted to make sure all tests still passed. However in the middle of my fiddling i had dropped the nHibernate database used for unittests and recreated it, but that shouldn't be a problem. nHibernate creates tables needed during its unit testing so all is good.

Then a test failed, and this test had nothing to do with the things i worked on, so i went to investigate the failing testcase called NHibernate.Test.SubselectFetchTest.SubselectFetchFixture.SubselectFetchWithLimit the testcase tries to fetch 2 rows from the database out of tree added and do various asserts on their children to check that collection fetching using the subselect fetch strategy is working. The assertion that failed was that a wrong number of child elements were on one of the parents, and a little investigation showed that the 2 rows returned was not the ones the test expected. To reproduce the error a test like this could be written:

[Test]
public void WrongElementReturnedFromLimitQuery()
{
	//Save three objects to the database with different names
	ISession s = OpenSession();
	Parent p1 = new Parent("foo");
	Parent p2 = new Parent("bar");
	Parent p3 = new Parent("aaa");
	s.Save(p1);
	s.Save(p2);
	s.Save(p3);

	s.Flush();

	IList<Parent> parents = s.CreateQuery("from Parent order by Name desc")
				.SetMaxResults(2)
				.List<Parent>();
	Assert.Equal("foo", parents[0].Name); //Fails expected "foo" was "aaa"
	Assert.Equal("bar", parents[1].Name); // Fails expected "bar" was "foo"
}

I was a bit puzzled at first, these sort of queries in nHibernate is pretty well tested and as i said i had not changed anything related to this, but to be sure i quickly reverted my changes and ran the tests again. And it still failed... Then i suddenly realised what was wrong and why this test suddenly failed. Take a moment and give me your guess on why... 

Yep, you guessed it. My new database running the tests were created using danish collation for sorting and in denmark aa is the same as the danish charecter å which is last in the alphabeth.

I found myself trying to fix a bug in the nHibernate source today.With nHibernate its a pleasure to have a lot of unit tests to make sure you (hopefully) haven't broken anything with your changes. I did what i always do, checked out the nHibernate trunk ran all the tests and no failures. Then i messed around with the source made some changes and wanted to make sure all tests still passed. However in the middle of my fiddling i had dropped the nHibernate database used for unittests and recreated it, but that shouldn't be a problem. nHibernate creates tables needed during its unit testing so all is good.

Then a test failed, and this test had nothing to do with the things i worked on, so i went to investigate the failing testcase called NHibernate.Test.SubselectFetchTest.SubselectFetchFixture.SubselectFetchWithLimit the testcase tries to fetch 2 rows from the database out of tree added and do various asserts on their children to check that collection fetching using the subselect fetch strategy is working. The assertion that failed was that a wrong number of child elements were on one of the parents, and a little investigation showed that the 2 rows returned was not the ones the test expected. To reproduce the error a test like this could be written:

[Test]
public void WrongElementReturnedFromLimitQuery()
{
	//Save three objects to the database with different names
	ISession s = OpenSession();
	Parent p1 = new Parent("foo");
	Parent p2 = new Parent("bar");
	Parent p3 = new Parent("aaa");
	s.Save(p1);
	s.Save(p2);
	s.Save(p3);

	s.Flush();

	IList<Parent> parents = s.CreateQuery("from Parent order by Name desc")
				.SetMaxResults(2)
				.List<Parent>();
	Assert.Equal("foo", parents[0].Name); //Fails expected "foo" was "aaa"
	Assert.Equal("bar", parents[1].Name); // Fails expected "bar" was "foo"
}

I was a bit puzzled at first, these sort of queries in nHibernate is pretty well tested and as i said i had not changed anything related to this, but to be sure i quickly reverted my changes and ran the tests again. And it still failed... Then i suddenly realised what was wrong and why this test suddenly failed. Take a moment and give me your guess on why... 

Yep, you guessed it. My new database running the tests were created using danish collation for sorting and in denmark aa is the same as the danish charecter å which is last in the alphabeth.

Most of the time Hibernate “just works”. Therefore when it is not that easy is comes as somewhat of a surprise. Hibernate documentation says it is easy to use SQL from within Hibernate, but lets try it.

We can do it with using named SQL queries, or simply by embedding our SQL instructions directly in the source code.

Embedding example : SQLCooperationTest.java

public void runQuery(){ 
39       SQLQuery q = TestUtilities.getHSession().createSQLQuery
                       ( "SELECT street, zip FROM sql_addresses"); 
40       q.addScalar( "street", Hibernate.STRING); 
41       q.addScalar( "zip", Hibernate.STRING); 
42       printResults( q ); 
43     }

Named SQL call example: SQLCooperationTest.java

 
46    
47     public void runNamedQuery(){ 
48       SQLQuery q1 = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" ); 
49       printResults( q1 ); 
50     }

and the definition of the named query is: sql.hbm.xml

5     
6    <hibernate-mapping package="com.sourcelabs.hibernate.bhw.bags" > 
7     
8    <sql-query name="select_address"> 
9      <return-scalar column="street" type="java.lang.String"/> 
10     <return-scalar column="zip" type="java.lang.String"/> 
11     select street, zip from sql_addresses 
12   </sql-query> 
13   </hibernate-mapping>

as we can see in both cases we need to specify aliases and types for the query before it can be executed. If we do not do it then we will get exception like this:

  Caused by: org.hibernate.QueryException: addEntity() or addScalar() must be called on a sql query
   before executing the query. [SELECT street, zip FROM sql_addresses]
    at org.hibernate.impl.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:169)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:140)
    at com.sourcelabs.hibernate.bhw.sql.SQLCooperationTest.printResults(SQLCooperationTest.java:52)
    at com.sourcelabs.hibernate.bhw.sql.SQLCooperationTest.runQuery(SQLCooperationTest.java:43)
    at com.sourcelabs.hibernate.bhw.sql.SQLCooperationTest$$FastClassByCGLIB$$79fd0338.
    invoke(<generated>)
    at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept
    (Cglib2AopProxy.java:635)
    at com.sourcelabs.hibernate.bhw.sql.SQLCooperationTest$$EnhancerByCGLIB$$511db1d7.
    runQuery(<generated>)

This default behavior is somehow odd and counterintuitive because H can return an array of object by default, but for unclear reasons H does not do that. Another potentially useful feature could be returning an array of maps. And again there is enough information at runtime to make intelligent mapping of columns to keys and values.

Hibernate perhaps could learn something from iBatis. If you need to use complex and/or RDBMS specific SQL extensively throughout then you might consider using Hibernate and iBatis together.

I don’t understand why that is a bad thing and why you would want to mix in two different ORM tools in your application. Just always use aliases, and if it isn’t to an object already mapped then include the types.