HQL with no "select", 2 "tables"..

themax · **Posted:** Fri Dec 12, 2003 10:34 am

Assuming I run a HQL query like the following, what format does the data get returned from the Session.find() method? I am assuming it is a Collection of 2 element arrays "arr[2]" containing the contents of "CDB_PORTFOLIO" in arr[0], and arr [1] containing the "portfoliotocustomers" result?

Code:

from   CDB_PORTFOLIO in class com.kbcam.core.entity.Portfolio , 
   portfoliotocustomers in CDB_PORTFOLIO.portfoliotocustomers.elements 
where portfoliotocustomers.customerid = ?   
order by description asc

Note: 2 "tables" in the from clause, and no "select cdb_portfolio" clause.

dimas · **Posted:** Fri Dec 12, 2003 11:27 am

Quote:

I am assuming it is a Collection of 2 element arrays "arr[2]" containing the contents of "CDB_PORTFOLIO" in arr[0], and arr [1] containing the "portfoliotocustomers" result?

Correct.

I have no idea why may you need this knowledge. It is much like arithmetic operations and parenthesis. If you are not sure about operation pecedence in "a << b * 2 + 1", its is usually much easier to put parenthesis and make expression evaluate exactly the way want instead of guessing.
In your case - it cost nothing to add "select CDB_PORTFOLIO, portfoliotocustomers" at the beginning of your query and sleep safe.

themax · **Posted:** Fri Dec 12, 2003 12:18 pm

... which is exactly what I've done (after getting an exception before I included it), but it never hurts to know, because I (or someone else) is going to need that information at some point.

I could answer the question myself, but then, who gains from that except for me?? No-one. Putting the question on the group means that someone else will find it with a google search, and if that happens just once, then it was worth the effort: both yours and mine.

I, for my part, have no idea why you comment on my motive for asking the question in the first place. If I wanted analysis, I'd go see a therapist. Instead, I come to this group for answers to specific technical questions, nothing more, nothing less.

gavin · **Posted:** Fri Dec 12, 2003 9:39 pm

Note that your query should be wriiten as:

Code:

from Portfolio portfolio
join portfolio.portfoliotocustomers customer
where customer.customerid = ?   
order by description asc

in Hibernate 2.x

themax · **Posted:** Mon Dec 15, 2003 5:31 am

Thanks.. I'll need to go back and do some tidy-up.. thats one more task for the list.

themax · **Posted:** Tue Dec 16, 2003 11:38 am

When I switch the HQL to the 2.* format I get an error that I did not get when I used the Hibernate 1.* style HQL.

The (2.*) HQL:

Code:

select CDB_PORTFOLIO 
from com.kbcam.core.entity.Portfolio CDB_PORTFOLIO 
join CDB_PORTFOLIO.portfoliotocustomers portfoliotocustomers  
where portfoliotocustomers.customerid = ?   
order by CDB_PORTFOLIO.description asc

The resultant SQL (from show_sql=true in hibernate.properties)

Code:

select portfoli0_.portfolioid as portfolioid, 
   portfoli0_.currencyid as currencyid, 
   portfoli0_.managerstaffid as managers3_, 
   portfoli0_.portfoliostructureid as portfoli4_, 
   portfoli0_.schemetypeid as schemety5_, 
   portfoli0_.portfoliotypeid as portfoli6_, 
   portfoli0_.schememandatetypeid as schemema7_, 
   portfoli0_.portfoliostatusid as portfoli8_, 
   portfoli0_.description as descript9_ 
from CDB_PORTFOLIO portfoli0_ 
inner join CDB_PORTFOLIO_TO_CUSTOMER portfoli1_ on portfoli0_.portfolioid=portfoli1_.portfolioid 
where (portfoli1_.customerid=? ) 
order by  portfoli0_.description asc

The error:

Code:

Incorrect syntax near 'inner'.

I'm using the JConnect driver for Sybase against a Sybase 11.9.2 database on NT.

Should the SQL syntax above work with most JDBC2 drivers (or have I ballsed it up)??

I'm not familiar with using the "inner join" mechanism in the SQL above.. this was not being produced when I used the old 1.* style HQL.

gavin · **Posted:** Tue Dec 16, 2003 9:11 pm

I have no idea what could possibly be wrong here. Perhaps you need to upgrade Sybase?

This SQL is, of course, perfect.

themax · **Posted:** Wed Dec 17, 2003 5:09 am

Hm.. the sql works on MSSQL, so I'd imagine u're right: Sybase does not recognise the ANSI syntax.

I can't very well throw out Sybase 11.9.2 as a DBMS because its our core DBMS! Its in the process of being upgraded to 12.5, but thats months away from completion.. so...

Whats the impact of staying on the Hibernate 1.* syntax HQL, rather than upgrading to the 2.* syntax (which generates the "inner joins")? What sort of half-life is version 1.* HQL currently expected to have?

gavin · **Posted:** Wed Dec 17, 2003 5:23 am

If you have a look at OracleDialect, you will see how it is possible to use a different strategy for rendering join SQL (the JoinFragment interface). You should be able to create a custom Dialect. It should not be very difficult.

themax · **Posted:** Wed Dec 17, 2003 5:33 am

I've verified that its an issue on Sybase ASE 11.9.2, but the syntax does work correctly on Sybase ASE 12.5, so longer term, an upgrade will solve the issue. Shorter term, I'll take your suggestion and see what happens with it (in a few days time!)

Again, many thanks.

Don't u sleep?? :)

Colm

gavin · **Posted:** Wed Dec 17, 2003 5:40 am

Sleep?? Its 8.38 pm in Melbourne. (In the middle of summer - its been 36 degrees all week.)

themax · **Posted:** Wed Dec 17, 2003 12:23 pm

It works.. I have a new Sybase Dialect ("Sybase11_9_2Dialect"), and a new JoinFragment (Sybase11_9_2JoinFragment), which solves the issue on 11.9.2 using the HQL 2.* syntax. Sybase ASE 12.5 databases can be used via the existing SybaseDialect without issue.

Gavin, u might recognise some of the code (OracleDialect!).. but feel free to include this in the code base anyway! Its only a matter of time before other 11.9.2 users start reporting the same issues with the 2.* syntax that I encountered, and there are still quite a few 11.9.2 databases out there.

Code:

public class Sybase11_9_2Dialect extends SybaseDialect  {
   public Sybase11_9_2Dialect() {
      super();
   }

   public JoinFragment createOuterJoinFragment() {
         return new Sybase11_9_2JoinFragment();
   }

}

Code:

public class Sybase11_9_2JoinFragment extends JoinFragment {

   private StringBuffer afterFrom = new StringBuffer();
   private StringBuffer afterWhere = new StringBuffer();

   public void addJoin(String tableName, String alias, String[] fkColumns, String[] pkColumns, int joinType)
    {
    addCrossJoin(tableName, alias);


    for ( int j=0; j<fkColumns.length; j++)
        {
        //full joins are not supported.. yet!
        if (joinType==JoinFragment.FULL_JOIN )
           {
           throw new UnsupportedOperationException();
           }
        afterWhere.append(" and ")
            .append( fkColumns[j] )
            .append( " " );
        if (joinType==LEFT_OUTER_JOIN )
            afterWhere.append("*");
        afterWhere.append('=');
        if (joinType==RIGHT_OUTER_JOIN )
            afterWhere.append("*");

        afterWhere
            .append (" ")
            .append(alias)
            .append(StringHelper.DOT)
            .append( pkColumns[j] );
        }

   }

   public String toFromFragmentString() {
      return afterFrom.toString();
   }

   public String toWhereFragmentString() {
      return afterWhere.toString();
   }

   public void addJoins(String fromFragment, String whereFragment) {
      afterFrom.append(fromFragment);
      afterWhere.append(whereFragment);
   }

   public JoinFragment copy() {
      Sybase11_9_2JoinFragment copy = new Sybase11_9_2JoinFragment();
      copy.afterFrom = new StringBuffer( afterFrom.toString() );
      copy.afterWhere = new StringBuffer( afterWhere.toString() );
      return copy;
   }

   public void addCondition(String alias, String[] columns, String condition) {
      for ( int i=0; i<columns.length; i++ ) {
         afterWhere.append(" and ")
            .append(alias)
            .append(StringHelper.DOT)
            .append( columns[i] )
            .append(condition);
      }
   }

   public void addCrossJoin(String tableName, String alias) {
      afterFrom.append(StringHelper.COMMA_SPACE)
         .append(tableName)
         .append(' ')
         .append(alias);
   }

   public void addCondition(
      String alias,
      String[] fkColumns,
      String[] pkColumns) {

         throw new UnsupportedOperationException();

   }

   public void addCondition(String condition) {
      afterWhere.append(condition);
   }

}

themax · **Posted:** Tue Dec 30, 2003 7:16 am

Of course, since upgrading to Hibernate 2.1.1 (I was on Hibernate 2.0.3), the following is needed to complete the code from my previous post, because of changes to the JoinFragment class:

Code:

 public void addFromFragmentString(String fromFragmentString) {
      afterFrom.append(fromFragmentString);
   }

max · **Posted:** Tue Dec 30, 2003 11:34 am

could you submit this as a patch at the jira ?

themax · **Posted:** Tue Dec 30, 2003 11:42 am

I sure could.. Can you point me at the style guidelines (etc), so that I can tidy it up a bit first, or does that happen as part of the review process?