more readable generated SQL?

pzoio · **Posted:** Tue Aug 30, 2005 6:22 am

How can I get more readable generated SQL?

Turning on SQL logging, I get code which looks like this

select project0_.project_id as project1_, project0_.project_name as project2_16_, project0_.project_description as project3_16_, project0_.commission_datetime as commission4_16_ ... from project project0_

The problem is that the alias names are nonsensical from a user point of view . When I run the same select in a SQL viewer, you see these aliases as the column headers.

Is there any way to control generation of column aliases?

I'm using Hibernate version: 3.0.5

mjstein6300 · **Joined:** Tue Jun 28, 2005 4:33 pm **Posts:** 21

I've started using an additional project with hibernate, P6spy (http://www.p6spy.org). It logs all database activity, so you get the hibernate prepared statement, as well as the actual SQL the database receives. Made it real easy to solve some constraint violations I was having.

sven · **Posted:** Tue Aug 30, 2005 3:48 pm

With the latest version of Hibernate (3.1) you are able to format the SQL output in another way.

hibernate.properties

Code:

hibernate.format_sql true

As far as I know you don't have any direct influence on the aliases.

Best regards
Sven

max · **Posted:** Tue Aug 30, 2005 4:07 pm

overide Dialect.getMaxAliasLength() and increase the number - will make the aliases longer and in most cases more readable

etienne · **Posted:** Tue Aug 30, 2005 4:43 pm

This could be nice if it would be configurable in hibernate.cfg.xml

Code:

   public int getMaxAliasLength() {
      return 10;
   }

Etienne.

max · **Posted:** Wed Aug 31, 2005 12:15 am

well it isn't yet ;)

if it is found usefull and people ask for it then maybe (put it in jira if you want to)

pzoio · **Posted:** Wed Aug 31, 2005 3:46 am

Thanks for your replies ... which could be helpful.

It would be nice if you could configure the alias naming strategy. At the moment it seems to be hard-coded into the source.

My workaround is to post process the SQL by removing the alias part from the column names

Code:

protected String getModified(String origSQL)
{

    String sql = origSQL.toLowerCase();

    int fromIndex = sql.indexOf("from");
    sql = sql.substring(0, fromIndex);

    StringBuffer t = new StringBuffer(sql.length());

    int limit = 0; // index of current ?
    int base = 0; // index of previous ?

    while ((limit = sql.indexOf("as", limit)) != -1)
    {

        String substring = origSQL.substring(base, limit-1);
        t.append(substring);

        limit = sql.indexOf(",", limit);
        
        if (limit == -1) break;
        base = limit;

    }

    t.append(origSQL.substring(base));
    return t.toString();

}

A bit of a hack but it seems to work so far, although I suspect I may run into problems later down the line with more advanced queries