aspose file tools*
The moose likes Object Relational Mapping and the fly likes Get dialect from session (for character escaping) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Get dialect from session (for character escaping)" Watch "Get dialect from session (for character escaping)" New topic
Author

Get dialect from session (for character escaping)

Robert Garrido
Ranch Hand

Joined: Dec 11, 2008
Posts: 30
Hi all,

I need to get the dialect being used in order to make some special characters escape, I need to use different escape technique depending upon the dialect.

For example, for mysql I need to escape underscore like this:

query.replaceAll("_", "\\\\_")

for Oracle and HSQL I need to do like this:

query.replaceAll("_", "\\_")

That's why I need to infer the dialect from the org.hibernate.Session, but I just can't see how to do it with the available JavaDocs.

Thanks a lot!
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

It is counterintuative to try to do this, hence no method in the API.

Why do you need to escape charactes at all?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Robert Garrido
Ranch Hand

Joined: Dec 11, 2008
Posts: 30
Because I don't want to have special characters like underscore to be read as wildcards but literal characters, ex:

I have this data:

index word
1 stop
2 stap
3 st_p

If I put in my hibernate query "select * from data where word like '%st_p%' "

I only want to get the "st_p" result and not the three of them.

Currently I'm using MySQL, so the query that gets the right result looks like "select * from data where work like '%st\\\\_p%' "

In Oracle it would be "select * from data where work like '%st\\_p%' "

That's why I want to escape especial characters according to dialect.

Thanks!


Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Would it not be much easier to use parameter binding? This way you do not have to care about database-specific escape characters in your queries.
Robert Garrido
Ranch Hand

Joined: Dec 11, 2008
Posts: 30
Can you give me an example of what you mean? Please let me know how it would work for the given example.

Thanks
Robert Garrido
Ranch Hand

Joined: Dec 11, 2008
Posts: 30
By the way, I currently do this:

sqlQuery.setParameter(e.getKey(), e.getValue() != null ? e.getValue().replaceAll("_", "\\\\_") : null);
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18659
    
    8

And do you do that for every single query? You didn't post a query to go with that, which suggests that you do.
Robert Garrido
Ranch Hand

Joined: Dec 11, 2008
Posts: 30
Yes, I do that for every single query. I actually escape the parameters values when they are binded to the org.hibernate.SQLQuery.

My second post was to clarify whay it works different in Oracle and MySql.

However the first question still remains: How can I infer the dialect so I use different escaping in Oracle than MySql.

Thanks!
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Hmm - I'm guilty or reading your question too quickly. Now I think about it, underscore is in the ANSI standard, so a dialect will not escape it even with parameter binding. Finding the dialect is a non-starter, there is no API for this supplied by Hibernate. But finding the database is doable. You would need to interogate the database meta data and use a SQL query directly as appropriate.

I'm actually a little surprised HQL doesn't seem to include a cross-platform escape character (or some other mechanism) to escape underscore==wildcard behaviour.
Robert Garrido
Ranch Hand

Joined: Dec 11, 2008
Posts: 30
Paul Sturrock wrote:You would need to interogate the database meta data and use a SQL query directly as appropriate.


Any sample about how to do this? I only have the Session to get that metadata.

Cheers
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You can get a java.sql.Conneciton from the Session with the connection() method. From that you can get the DatabaseMetaData which should allow you to find out what database type you are on. Not perfect, but I can't think of another way of doing it (short of changing your data).
Robert Garrido
Ranch Hand

Joined: Dec 11, 2008
Posts: 30
Thanks a lot, that made the trick. However the connection() method is deprecated. Is there another way to get the connection/metadata?
Robert Garrido
Ranch Hand

Joined: Dec 11, 2008
Posts: 30
Thanks a lot. It's fixed now.
 
Don't get me started about those stupid light bulbs.
 
subject: Get dialect from session (for character escaping)