Hi,
I was wondering if there is any work underway to make Hibernate generate human readable sql, that is, sql without all of those unecessary and weird names aliases for both fields and table names.

I understand having a unique way of addressing them is necessary to make the or-mapper work, but the current solution (at least with Hibernate 2.1.8, don't know about HIbernate 3) generated unecessary aliases that make the generated query harder to read...

Hello

have you tried using the opensource tool P6Spy http://www.p6spy.com/ ?

basically it is a sort of wrapper around your DB driver that logs every JDBC call, so you can not only read the query that's sent to your db , but also page through the result sets with actual replacement values.

The way I use it:
add the spy.jar to your classpath; then in your hibernate.properties, replace

hibernate.connection.driver_class org.postgresql.Driver

with

hibernate.connection.driver_class com.p6spy.engine.spy.P6SpyDriver

enter all JDBC connection details (driver, url, login, password etc...) in the spy.properties file provided by the tool.

et voila!

hope this helps,

Isabelle

IPhan,
thank for your message. I already know about p6spy, but it does not help in having human readable sql.

What I'm complaining about is the SQL generated. Have a look:

select stateimpl4_.name as x0_0_, workflowmo5_.name as x1_0_, workflowmo3_.CREATION_DATE as x2_0_, workflowmo3_.ACTIVE as x3_0_, workflowmo3_.LAST_VERSION as x4_0_ from CL_WORKFLOW_STAGE workflowst0_ inner join CL_STAGE_ACTIVITY activities1_ on workflowst0_.ID=activities1_.WORKFLOW_STAGE_ID inner join CL_ACTIVITY treatmenta2_ on activities1_.ACTIVITY_ID=treatmenta2_.ID, CL_WORKFLOW_MODEL_VERSION workflowmo3_, JBPM_NODE stateimpl4_, CL_WORKFLOW_MODEL workflowmo5_ where workflowst0_.STATE_ID=stateimpl4_.id and workflowmo3_.WORKFLOW_MODEL_ID=workflowmo5_.ID and ((treatmenta2_.ID=144 )and(stateimpl4_.definition=workflowmo3_.DEFINITION_ID and workflowst0_.STATE_ID=stateimpl4_.id)) order by workflowmo5_.name , workflowmo3_.CREATION_DATE , stateimpl4_.name

Do you find it easy to read? I don't, and many of the aliases used are not really needed, especially for tables since their names are not repeated (in this query, at least).

Hibernate 3.1, set hibernate.format_sql=true.

Christian,
thank you for your comment but... I cannot find any documentation on that parameter and I'm not using Hibernate 3.

So, a couple of questions:
* does format_sql avoid the generation of those ugly aliases? Format seems to imply just pretty prenting (maybe it's just my poor understanding of english)
* if so, would it be difficult to port it back to 2.1.8? (I'm not asking you to do it, just if it's possible and a time estimate)

The aliases are there for a reason, so No. Backporting is probably simple.

in hibernate3 Dialect has getMaxAliasLength default set to 10. If you make that number higher the more you will get of the alias names making it more "human" but still unique. This of course requires that your db can handle alias names larger than 10...

Sorry but I don't see the reason. Besides uniqueness, of course.
But for the fields it's not required if you are using positional access to the results, isn't it?
And for the tables, it's required only if there are two tables with the same name in the from clause, and in this particular case, and only for those tables, I have no objection to the use of aliases.

This doesn't address your issue with alias names, but it helps get it relatively readable for me very quickly. I've used it often.

http://home.comcast.net/~danmeany/sqlformatter.html

Hibernate doesn't use positional access. If you want to know more why aliases are generated, browse the source code.

As I stated in my first post, my problem is with the aliases. I can hand format the sql quite fast, what make the sql unreadable to me are the aliases.

then increase the number in the Dialect and they will get more readable.

Funny. You say that, then exactly state the reason ;)

:-)