-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
vulps
 Post subject: having encoding problem in hql queries where clause
PostPosted: Mon Jul 18, 2005 3:16 pm 
Newbie

Joined: Wed Jun 01, 2005 1:40 pm
Posts: 7
Hi ,
In hql queries in where clause there is a problem, it converts Turkish characters to"?" . I modified hibernate auction sample and got same problem. I can insert,update beans with Turkish chars without problem (configured in hibernate.properties using characterEncoding param). Only problem occurs in where clause of hql queries . I wrote details below.


hibernate version:3.05

mapping
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping
   package="org.hibernate.auction">

   <class name="User" table="AuctionUser" lazy="true">
      <comment>Users may bid for or sell auction items.</comment>
      
      <id name="id">
         <generator class="native"/>
      </id>
      
      <natural-id mutable="true">
         <property name="userName"
               length="10"/>
      </natural-id>
      
      <property name="password"
            not-null="true"
            length="15"
            column="`password`"/>
      
      <property name="email"/>
      
      <component name="name">
         <property name="firstName"
               length="50"
               not-null="true"/>
         <property name="initial"
               column="`initial`"/>
         <property name="lastName"
               length="50"
               not-null="true"/>
      </component>

   </class>
</hibernate-mapping>


code
Code:
public class Main {

   private SessionFactory factory;

   /**
    * Demonstrates HQL projection/aggregation
    */
   public void searchByUserName(String username) throws Exception {
      Session s = factory.openSession();
      Transaction tx=null;
      try {
         tx = s.beginTransaction();

         List user = s.createQuery(
            " select user.userName, user.email, user.password "
            + " from User as user "
            + " where user.userName = '" + username + "'"
            )
            .setMaxResults(100)
            .list();

         System.out.println("for username " + username + " matching results:" + user.size());

         tx.commit();
      }
      catch (Exception e) {
         if (tx!=null) tx.rollback();
         throw e;
      }
      finally {
         s.close();
      }
   }

   public void createTestUsers() throws Exception {
      System.out.println("Setting up some test data");

      Session s = factory.openSession();
      Transaction tx = s.beginTransaction();

      User seller = new User();
      seller.setUserName("Kesinleş");
      seller.setName( new Name("ıİÇ窺ĞğÜü", new Character('T'), "Öö testing") );
      seller.setEmail("someone@here.org");
      seller.setPassword("******");
      s.save(seller);
      
   }

   static User mainSeller;

   public static void main(String[] args) throws Exception {

      final Main test = new Main();

      Configuration cfg = new Configuration()
         .addClass(User.class)
         .setProperty(Environment.HBM2DDL_AUTO, "create");
      cfg.setProperty("hibernate.show_sql", "true");

      test.factory = cfg.buildSessionFactory();
      test.createTestUsers();
      test.searchByUserName("Kesinleş");
      test.searchByUserName("8859-1");
      test.factory.close();

   }
}




DB : postgresql 8.0 and mysql 4.1


Debug level Hibernate log excerpt:
Code:
Debug   10.285   false   org.hibernate.impl.SessionImpl   opened session at timestamp: 4594529587707904
Debug   10.245   false   org.hibernate.impl.SessionImpl   after transaction completion
Debug   10.205   false   org.hibernate.jdbc.JDBCContext   after transaction completion
Debug   10.165   false   org.hibernate.connection.DriverManagerConnectionProvider   returning connection to pool, pool size: 1
Debug   10.135   false   org.hibernate.jdbc.ConnectionManager   closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
Debug   10.095   false   org.hibernate.impl.SessionImpl   closing session
Debug   10.055   false   org.hibernate.impl.SessionImpl   after transaction completion
Debug   10.014   false   org.hibernate.jdbc.JDBCContext   after transaction completion
Debug   9.974   false   org.hibernate.transaction.JDBCTransaction   committed JDBC Connection
Debug   9.944   false   org.hibernate.impl.SessionImpl   before transaction completion
Debug   9.894   false   org.hibernate.jdbc.JDBCContext   before transaction completion
Debug   9.854   false   org.hibernate.impl.SessionImpl   automatically flushing session
Debug   9.814   false   org.hibernate.transaction.JDBCTransaction   commit
Debug   9.764   false   org.hibernate.engine.PersistenceContext   initializing non-lazy collections
Debug   9.724   false   org.hibernate.jdbc.AbstractBatcher   closing statement
Debug   9.684   false   org.hibernate.jdbc.AbstractBatcher   about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
Debug   9.644   false   org.hibernate.jdbc.AbstractBatcher   about to close ResultSet (open ResultSets: 1, globally: 1)
Debug   9.594   false   org.hibernate.loader.Loader   done processing result set (0 rows)
Debug   9.544   false   org.hibernate.loader.Loader   processing result set
Debug   9.504   false   org.hibernate.jdbc.AbstractBatcher   about to open ResultSet (open ResultSets: 0, globally: 0)
Debug   9.464   false   org.hibernate.jdbc.AbstractBatcher   preparing statement
Debug   9.424 false   org.hibernate.SQL   select user0_.userName as col_0_0_, user0_.email as col_1_0_, user0_."password" as col_2_0_ from h3tr.AuctionUser user0_ where user0_.userName='Kesinle?' limit ?
Debug   9.384   false   org.hibernate.jdbc.AbstractBatcher   about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
Debug   9.333   false   org.hibernate.hql.ast.ErrorCounter   throwQueryException() : no errors
Debug   9.303   false   org.hibernate.hql.ast.QueryTranslatorImpl   SQL: select user0_.userName as col_0_0_, user0_.email as col_1_0_, user0_."password" as col_2_0_ from h3tr.AuctionUser user0_ where user0_.userName='Kesinle?'
Debug   9.263   false   org.hibernate.hql.ast.QueryTranslatorImpl   HQL:  select user.userName, user.email, user.password  from org.hibernate.auction.User as user  where user.userName = 'Kesinleş'
Debug   9.203   false   org.hibernate.hql.ast.ErrorCounter   throwQueryException() : no errors
Debug   9.163   false   org.hibernate.hql.ast.AST   --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT'  querySpaces (h3tr.AuctionUser)
    +-[SELECT_CLAUSE] SelectClause: '{select clause}'
    |  +-[DOT] DotNode: 'user0_.userName' {propertyName=userName,dereferenceType=4,propertyPath=userName,path=user.userName,tableAlias=user0_,className=org.hibernate.auction.User,classAlias=user}
    |  |  +-[ALIAS_REF] IdentNode: 'user0_.id' {alias=user, className=org.hibernate.auction.User, tableAlias=user0_}
    |  |  \-[IDENT] IdentNode: 'userName' {originalText=userName}
    |  +-[SELECT_COLUMNS] SqlNode: ' as col_0_0_'
    |  +-[DOT] DotNode: 'user0_.email' {propertyName=email,dereferenceType=4,propertyPath=email,path=user.email,tableAlias=user0_,className=org.hibernate.auction.User,classAlias=user}
    |  |  +-[ALIAS_REF] IdentNode: 'user0_.id' {alias=user, className=org.hibernate.auction.User, tableAlias=user0_}
    |  |  \-[IDENT] IdentNode: 'email' {originalText=email}
    |  +-[SELECT_COLUMNS] SqlNode: ' as col_1_0_'
    |  +-[DOT] DotNode: 'user0_."password"' {propertyName=password,dereferenceType=4,propertyPath=password,path=user.password,tableAlias=user0_,className=org.hibernate.auction.User,classAlias=user}
    |  |  +-[ALIAS_REF] IdentNode: 'user0_.id' {alias=user, className=org.hibernate.auction.User, tableAlias=user0_}
    |  |  \-[IDENT] IdentNode: 'password' {originalText=password}
    |  \-[SELECT_COLUMNS] SqlNode: ' as col_2_0_'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[user], fromElementByTableAlias=[user0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'h3tr.AuctionUser user0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=user,role=null,tableName=h3tr.AuctionUser,tableAlias=user0_,colums={,className=org.hibernate.auction.User}}
    \-[WHERE] SqlNode: 'where'
       \-[EQ] SqlNode: '='
          +-[DOT] DotNode: 'user0_.userName' {propertyName=userName,dereferenceType=4,propertyPath=userName,path=user.userName,tableAlias=user0_,className=org.hibernate.auction.User,classAlias=user}
          |  +-[ALIAS_REF] IdentNode: 'user0_.id' {alias=user, className=org.hibernate.auction.User, tableAlias=user0_}
          |  \-[IDENT] IdentNode: 'userName' {originalText=userName}
          \-[QUOTED_STRING] LiteralNode: ''Kesinle?''

Debug   9.123   false   org.hibernate.hql.antlr.HqlSqlBaseWalker   query() >> end, level = 1
Debug   9.083   false   org.hibernate.hql.ast.JoinProcessor   Using FROM fragment [h3tr.AuctionUser user0_]
Debug   9.043   false   org.hibernate.hql.ast.HqlSqlWalker   processQuery() :  ( SELECT ( {select clause} ( user0_.userName user0_.id userName ) ( user0_.email user0_.id email ) ( user0_."password" user0_.id password ) ) ( FromClause{level=1} h3tr.AuctionUser user0_ ) ( where ( = ( user0_.userName user0_.id userName ) 'Kesinle?' ) ) )
Debug   9.043   false   org.hibernate.hql.antlr.HqlSqlBaseWalker   query() : finishing up , level = 1
Debug   9.033   false   org.hibernate.hql.ast.FromReferenceNode   Resolved :  user.userName -> user0_.userName
Debug   8.993   false   org.hibernate.hql.ast.DotNode   getDataType() : userName -> org.hibernate.type.StringType@5a2eaa
Debug   8.953   false   org.hibernate.hql.ast.FromReferenceNode   Resolved :  user -> user0_.id
Debug   8.923   false   org.hibernate.hql.ast.FromReferenceNode   Resolved :  user.password -> user0_."password"
Debug   8.923   false   org.hibernate.hql.ast.DotNode   getDataType() : password -> org.hibernate.type.StringType@5a2eaa
Debug   8.923   false   org.hibernate.hql.ast.FromReferenceNode   Resolved :  user -> user0_.id
Debug   8.913   false   org.hibernate.hql.ast.FromReferenceNode   Resolved :  user.email -> user0_.email
Debug   8.913   false   org.hibernate.hql.ast.DotNode   getDataType() : email -> org.hibernate.type.StringType@5a2eaa
Debug   8.913   false   org.hibernate.hql.ast.FromReferenceNode   Resolved :  user -> user0_.id
Debug   8.913   false   org.hibernate.hql.ast.FromReferenceNode   Resolved :  user.userName -> user0_.userName
Debug   8.913   false   org.hibernate.hql.ast.DotNode   getDataType() : userName -> org.hibernate.type.StringType@5a2eaa
Debug   8.893   false   org.hibernate.hql.ast.FromReferenceNode   Resolved :  user -> user0_.id
Debug   8.853   false   org.hibernate.hql.ast.FromElement   FromClause{level=1} :  org.hibernate.auction.User (user) -> user0_
Debug   8.783   false   org.hibernate.hql.antlr.HqlSqlBaseWalker   query() << begin, level = 1
Debug   8.693   false   org.hibernate.hql.ast.ErrorCounter   throwQueryException() : no errors
Debug   8.693   false   org.hibernate.hql.ast.AST   --- HQL AST ---
\-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  +-[FROM] 'from'
    |  |  \-[RANGE] 'RANGE'
    |  |     +-[DOT] '.'
    |  |     |  +-[DOT] '.'
    |  |     |  |  +-[DOT] '.'
    |  |     |  |  |  +-[IDENT] 'org'
    |  |     |  |  |  \-[IDENT] 'hibernate'
    |  |     |  |  \-[IDENT] 'auction'
    |  |     |  \-[IDENT] 'User'
    |  |     \-[ALIAS] 'user'
    |  \-[SELECT] 'select'
    |     +-[DOT] '.'
    |     |  +-[IDENT] 'user'
    |     |  \-[IDENT] 'userName'
    |     +-[DOT] '.'
    |     |  +-[IDENT] 'user'
    |     |  \-[IDENT] 'email'
    |     \-[DOT] '.'
    |        +-[IDENT] 'user'
    |        \-[IDENT] 'password'
    \-[WHERE] 'where'
       \-[EQ] '='
          +-[DOT] '.'
          |  +-[IDENT] 'user'
          |  \-[IDENT] 'userName'
          \-[QUOTED_STRING] ''Kesinle?''

Debug   8.643   false   org.hibernate.hql.ast.QueryTranslatorImpl   parse() - HQL:  select user.userName, user.email, user.password  from org.hibernate.auction.User as user  where user.userName = 'Kesinleş'
Debug   8.552   false   org.hibernate.engine.QueryParameters   named parameters: {}
Debug   8.532   false   org.hibernate.impl.SessionImpl   find:  select user.userName, user.email, user.password  from User as user  where user.userName = 'Kesinleş'
Debug   8.512   false   org.hibernate.jdbc.ConnectionManager   running Session.finalize()
Debug   8.472   false   org.hibernate.transaction.JDBCTransaction   current autocommit status: false
Debug   8.472   false   org.hibernate.connection.DriverManagerConnectionProvider   using pooled JDBC connection, pool size: 0
Debug   8.472   false   org.hibernate.connection.DriverManagerConnectionProvider   total checked-out connections: 0
Debug   8.462   false   org.hibernate.jdbc.ConnectionManager   opening JDBC connection
Debug   8.462   false   org.hibernate.transaction.JDBCTransaction   begin


if you read the line starting with Debug 8.532 hql is : select user.userName, user.email, user.password from User as user where user.userName = 'Kesinleş' . At hqls where clause there is a Turkish char (similar to s in case you cant see properly). And above the line that starts with [b]Debug 8.693[b] in Ast trees last part Turkish chars converted to ? .

Should i add an issue to jira?
Thanks
Selim
Top
 Profile  
 
Myk
 Post subject:
PostPosted: Mon Jul 18, 2005 5:06 pm 
Senior
Senior

Joined: Sun Jan 04, 2004 2:46 pm
Posts: 147
First check your MySQL table is configured to support unicode characters.

Code:
dev.mysql.com/doc/mysql/en/charset-unicode.html


Then change your query so that you are using a bound parameter for username rather than concatenating it in the query. The jdbc driver probably will not be able to detect you are using a unicode character if it is not in an explicit parameter.

Then read this ( but you may not need to do anything explicit )

Code:
http://dev.mysql.com/doc/connector/j/en/cj-character-sets.html


If you still have trouble you should try executing the query using a raw jdbc PreparedStatement and get it to work like that. Then hibernate should just work on top of the settings you work out for that.

Hope this helps.
Top
 Profile  
 
vulps
 Post subject:
PostPosted: Tue Jul 19, 2005 2:43 am 
Newbie

Joined: Wed Jun 01, 2005 1:40 pm
Posts: 7
Problem is not mysql or jdbc driver. I have same problem for Posgresql too. And i can insert update records using Turkish charset (iso8859-9, latin5).

The problem is if i wrote something using hql

select stock.ratio, stock.price, stock.name from Stock as stock where stock.name = :lookfor

and passing named parameter lookfor as "kapalı" the last char is Turkish char ( i without dot ) the query converted to sql like this

select .................... where stock.name = 'kapal?'

If you look at the debug messages and read what i wrote below it you can see that only where part of hql query corrupted while converting to sql.

Selim
Top
 Profile  
 
vulps
 Post subject:
PostPosted: Wed Jul 20, 2005 2:59 am 
Newbie

Joined: Wed Jun 01, 2005 1:40 pm
Posts: 7
Is the topic, message too detailed?

Should i have been titled it "Bug in hibernate" to make someone care of it?

I think i dont ask stupid mapping question or stupid usage without reading any of hibernate docs. I have been using hibernate nearly 2 years and i solved many problems searching on wiki, forum.

Today we have serious problem. We cant use our native language (Latin5, iso8859-9, Turkish) in hql queries where clauses. I posted topic with every detail as asked in "How to ask for help".

If the topic too detailed, someone should revise and update Hibernates "How to ask for help" documentation.

Thanks
Selim
Top
 Profile  
 
mistretzu
 Post subject:
PostPosted: Thu Oct 04, 2007 8:02 am 
Newbie

Joined: Wed Oct 03, 2007 10:15 am
Posts: 5
I have the same problem with characters like ì, â, Ä, etc...

When I perform a HQL select query with where clause that contains some of that character hibernate doesn't find the record.

String hql = "from User where upper(username) = :uname";
Query query = session.createQuery(hql).setParameter("uname", user.getUsername().toUpperCase());

Hibernate version is 3.2.5.ga
The database is Sybase 9.0.2, database' char set is UTF-8, database connection char set is UTF-8, I have specified in hibernate.cfg.xml:

<property name="hibernate.connection.CharSet">utf8</property>
<property name="hibernate.connection.characterEncoding">utf8</property>
<property name="hibernate.connection.useUnicode">true</property>

But none of this help.

Using a DBVisualizer tool I can see the record with correct characters and if I perform the select query that fails with hibernate, here it works.

Before posting I searched the forum and I found a dozen of similar problems, but no solution.

Any one know how to fix this?
Top
 Profile  
 
mistretzu
 Post subject:
PostPosted: Thu Oct 04, 2007 11:35 am 
Newbie

Joined: Wed Oct 03, 2007 10:15 am
Posts: 5
I don't know, but I have never found help on programing forums.

Here is the question to my problem:

There is an inconsistence between SQL upper() function and java String.toUpperCase() method.

The correct code:

String hql = "from User where upper(username) = upper(:uname)";
Query query = session.createQuery(hql).setParameter("uname", user.getUsername());
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.