Featured Article: Internationalized data in Hibernate

Discussions

News: Featured Article: Internationalized data in Hibernate

  1. Developers sometimes have to deal with the internationalization of data. Gavin King discusses his preferred solution to the problem of using internationalized reference data as UI labels. He documents the implementation of a real example.
    We've seen a few people using internationalized reference data where labels displayed in the user interface depend upon the user's language. It's not immediately obvious how to deal with this in Hibernate, and I've been meaning to write up my preferred solution for a while now.

    ... implementation ...

    Notice that this implementation is very efficient, we never need to join to the Label table in our queries - we never need to query that table at all, except at startup time to initialize the cache. A potential problem is keeping the cache up to date if the Label data changes. If you use Hibernate to implement the Label cache, there's no problem. If you implement it in your application, you will need to manually refresh the cache when data changes.
    Read Gavin King in Internationalized data in Hibernate

    Threaded Messages (36)

  2. A potential problem is keeping the cache up to date if the Label data changes. If you use Hibernate to implement the Label cache, there's no problem. If you implement it in your application, you will need to manually refresh the cache when data changes.
    Since you mention it, I have always wanted a persistence library that would address this problem: notifications from the database to the middle tier. I think that to keep this simple and efficient you probably need vendor-specific code. I think jConnect already supports this. I wonder what Oracle can do. There is probably a PL/SQL package for this ..
  3. Yes, Oracle can do it[ Go to top ]

    I wonder what Oracle can do. There is probably a PL/SQL package for this ..
    Yes, triggered Oracle java stored procedures can send messages via RMI, SOAP, etc. "up" to the application, and yes, it is unfortunately a proprietary solution.
  4. Yes, Oracle can do it[ Go to top ]

    Oracle java stored procedures can send messages via RMI, SOAP, etc. "up" to the application, and yes, it is unfortunately a proprietary solution.
    Excuse me, but are you saying that only Oracle boasts java stored procedures?
    When I worked with the Informix ORDBMS, five years ago, they also allowed java stored procedures. So please explain where the "proprietary" part is.

    Thanks (in advance),
    Avi.
  5. Yes, Oracle can do it[ Go to top ]

    The proprietary part is that every RDBMS has a very different interpretation of what a Java SP is and how it can be integrated. For example, in some you have to write a "wrapper" in the native procedural language of the database for the input and return parameters of the procedure. There are much more differences than you might think and the environment is usally very restrictive.
  6. Yes, Oracle can do it[ Go to top ]

    The proprietary part is that every RDBMS has a very different interpretation of what a Java SP is and how it can be integrated. For example, in some you have to write a "wrapper" in the native procedural language of the database for the input and return parameters of the procedure. There are much more differences than you might think and the environment is usally very restrictive.
    As I understand this standard was SQLj.
  7. SQLj is a way of integrating SQL with Java on the client side - as an alternative to JDBC. It does not say anything about how you write or integrate stored procedures.
    In fact, theoretically, a stored procedure may be written with the help either of JDBC or SQLj, as it is itself a client to the database. But again this does not alleviate the problem with the missing standard for SP integration, parameter passing etc.

    Christian
  8. SQLj is a way of integrating SQL with Java on the client side - as an alternative to JDBC. It does not say anything about how you write or integrate stored procedures.In fact, theoretically, a stored procedure may be written with the help either of JDBC or SQLj, as it is itself a client to the database. But again this does not alleviate the problem with the missing standard for SP integration, parameter passing etc.Christian
    It was about server side and SP too, It can be interesting to know more about it, but SQLj site is down. Oracle droped SQLj and it is dead, probably nobody need this integration anyway.
  9. BTW it must be possible to make it alive. Hibernate is a defacto standard, it can "fix" SQLj, new JDK features can help too.

    +1
  10. BTW it must be possible to make it alive. Hibernate is a defacto standard, it can "fix" SQLj, new JDK features can help too.+1
    Sorry, but neither Hibernate nor OJB nor TopLink nor any other O/R tool is capable of doing this because the *DB engine* has to be the one originating messages based on triggered stored procedures (i.e. for every insert, update, delete, etc). Remember, triggers live in the DB engine, not in the O/R tool.
      
    The way that Oracle pulls this off is that it bolted a JVM to its engine, so triggers can fire off java stored procedures that can send messages up to the application. Christian is correct in that this is done very differently by other DB vendors, if at all. It's doubtful that *any* official or defacto Java standard will change this any time soon.

    Some developers deal with this by having triggered stored procs update "flagging" tables that the application periodically polls to see what's changed. This works well (across most DB engines) if you don't need immediate, event-driven change notification, but you're still depending on a vendor-specific implementation (i.e. the creation of triggers and stored procs is not in the SQL-92 spec, so each vendor does it differently, if at all).

    Scott
  11. Remember, triggers live in the DB engine, not in the O/R tool. The way that Oracle pulls this off is that it bolted a JVM to its engine, so triggers can fire off java stored procedures that can send messages up to the application.
    Why do you think O/R tool can not live in DB engine too ? It must be a good way for web applications ( web server and RDBMS are in the same process ) Probably it is a good idea to implement this kind of server on SF.
     Christian is correct in that this is done very differently by other DB vendors, if at all.
    Yes, SQLJ is not popular.
  12. This is short SQLJ standard description:

    http://www.service-architecture.com/database/articles/sqlj.html
    Part 0: Embedded SQL in Java.
     This is source code preprocessor.
    Part 1: SQL routines using Java.
     This is server side stored procedures implemented as static JAVA methods.
    Part 2: SQL types using Java.
     This is O/R mapping.
  13. You know, the concepts and ideas of SQLJ are excellent. Get the programming and the data language closer together, great, I'm all for it. What sucks are the implementations and some parts of the specification. It is dead now (without support from Oracle, and they really tried...), only DB2 is left. IBM is somewhat slower and will certainly come to the same conclusion in the next 3 years. Having said that, the time is not yet here for a next try. Call me when it is :)
  14. Yes, I think time for SQLJ will be after EJB 3 become "complex", it will be very interesting to see EJB 4 :) But I do not have big problems with my home made "persistence" at this time too.
  15. Yes, Oracle can do it[ Go to top ]

    I am postgresql fan so I must add my 2 cents too.
    PostgreSQL supports SQL command for this use case http://www.postgresql.org/docs/7.4/static/sql-listen.html
    It means you can use JDBC for this kind of messaging. It is not any kind of standard, but it is a usefull feature and I think it must be possible to implement abstraction for this kind of platform specific feature in hibernate too.
  16. why not load all the labels[ Go to top ]

    I was working on this issue just a few weeks ago. I decided to load all the description (in this case) for all the languages at on time. So, I have my Object contains a map of desciptions with language as key (one-to-many). In practice, one application may only support limited number of languages. So loading multiple "description" shall not be a problem.

    Melvin
  17. why not load all the labels[ Go to top ]

    I mean...

    create table Category (
        category_id bigint not null primary key,
        discription bigint, ----changed line
        parent_category_id foreign key references(category),
        discription foreign key references(attribute) -- added line
    )

    create table attribute(ID);
    create table lang_attribute(id, att_id, lang_id, value)

    then category could have a map of attribute values (language dependent label)
  18. A practical solution[ Go to top ]

    The suggestion to use three tables, Category, attribute and attribute_lang is just the solution I was looking for. Thanks.
  19. some other approaches[ Go to top ]

    we have a localizeddata object that all objects like category, menu nodes extend. localized data has essential members in hashmap that need to be localized, ie name, description. The hashmap contains Locale -> localized data and each localized object has it since they extend it.
    Is that possible to make the setting of values from DB automatic, that is a join with label table and make hibernate build the localized data?
  20. Why not use resource bundles?[ Go to top ]

    How is the proposed approach better than having a message ID be a string which is treated as a property name, and using ResourceBundles to handle internationalization? This seems a little better in the following respects:

    1. A little easier during development, as you are only changing text files, though this is offset somewhat in webapps if you need to keep reloading the webapp to pick up the changed bundles.
    2. It's easier to send the files off for translation.
    3. Language variants such as variants of German may not need the entire set of messages to be translated, since the runtime will search for messages up the I18N hierarchy.

    Just my $0.02...

    Krasna Halopti
  21. yes, resource bundles ![ Go to top ]

    This article looks like reinventing the wheel.

    Why not to use resource bundles, indeed ?

    Works so well with Struts.
  22. I use Resource bundles for this use case too (It implements cache and checks "lastmodified" itself). But it is meaningfull to store text in database if this text is more data than resource too, it is more portable.
  23. Why not use resource bundles?[ Go to top ]

    2. It's easier to send the files off for translation.3. Language variants such as variants of German may not need the entire set of messages to be translated, since the runtime will search for messages up the I18N hierarchy.Just my $0.02...Krasna Halopti
    agreed. In switzerland applications often need to support 3 or 4 languages (german, italian, english, french). have the translators (often its one for each language!) work with simple text files is more convenient than a database solution.

    pascal
  24. Why not use resource bundles?[ Go to top ]

    I think putting them in the database has a lot of merits, as one guy mentioned its good if you need the localized text to appear in SQL reports. I've used a similar approach to the article before (without using a User Type though - too tricky for me yet).

    If you change the language field in the Label table to be a Locale field, then you can even implement your own ResourceBundle type functionality where you say search for fr_CH first, then fr, then default to en, using the most specific match you can. Thankfully Hibernate translates a Locale object into a String column for you :)

    It would be easy enough to give the translators an Excel format to do the translations in, then create update/insert statements from this to put the translations into the DB too...

    Regards,

    Mike.
  25. 2. It's easier to send the files off for translation.3. Language variants such as variants of German may not need the entire set of messages to be translated, since the runtime will search for messages up the I18N hierarchy.Just my $0.02...Krasna Halopti
    agreed. In switzerland applications often need to support 3 or 4 languages (german, italian, english, french). have the translators (often its one for each language!) work with simple text files is more convenient than a database solution.pascal
    We have simple text files which serve as the base for the translators. And during the build process, these text files get loaded into the DB. So, you have the convenience of simple text files(for translators, etc) and DB access for developers.

    BR,
    ~A
  26. Resource bundles[ Go to top ]

    In my opinion, resource bundles are not suitable if you need to update objects in the database which need to have some fields localized.
    Suppose you are working on a catalog, and you want to present the description of your item in different languages;
    the item can be updated, inserted, deleted from a web console.
    In such a case you would need to store some information in the db, some other in the properties file, update the record in the db and upload the new properties file... it seems to me much easier to have everything in the db.
    I think Properties files are good for interfaces, not for "dynamic data".

    Of course, my 2 euro cents
    Bye
    Enrico
  27. DB vs. ResourceBundles[ Go to top ]

    In our projects we've always looked at both and often use the database to store internationalized strings. The key questions for us are:
    1) will the text be changed?
    2) will reports need to have the text on them?
    If the text needs to be changed, it's very hard to do in resource bundles. First off you need to comb the filesystem looking for files that might be a resource bundle, then you need to read entire files and rewrite them to update one string (ok, you can always do some funky magic to make this more efficient, but that increases the complexity of your code). A final problem when editing data is concurrent edits - it can be challenging to lock a file and it's very hard to 'version' file data so you know when someone else has commited a change during the time you were editing.
    If the text needs to be a part of a report, the text needs to be in a database. I havn't seen too many RDBMs which support SELECT * FROM UserTable, c:\resource.properties WHERE ...
  28. DB vs. ResourceBundles[ Go to top ]

    IMHO, ResourceBundles and other localization mechanisms (such as those built into your web framework) should be used for UI strings, and the static parts of reports.

    Databases should be used for... data! Now there's a revolutionary concept.

    I think the question that needs to be asked is "who will maintain these strings?" Labels in the GUI should be maintained by the translators employed by the development team, and should not be changed by end users. Application
    data such as product descriptions to go in a catalogue should be maintained by end users.

    As for it being difficult to version file data - have you never heard of CVS??
  29. DB vs. ResourceBundles[ Go to top ]

    IMHO, ResourceBundles and other localization mechanisms (such as those built into your web framework) should be used for UI strings, and the static parts of reports.Databases should be used for... data!
    I completely agree. In the example given above about e.g. product descriptions in multiple languages, this is part of the application's data and is best stored in the DB. The case for resource bundles is strongest with strings used in the UI, which will typically not end up in SQL query results.
  30. I believe internationalization is a cross-cutting concern, i.e. you need it in multiple tiers. Perhaps one should design internationalization support as part of an aspect-oriented framework?
  31. Hibernate BOF Chat @ JavaRSS.com[ Go to top ]

    Hibernate BOF Chat @ JavaRSS.com

    JavaRSS BOF is a moderated chat on a selected subject with other JavaRSS friends. In the chat you can learn, share and ask questions on the subject. The chat is for a max of 1 hour.

    Subject/Time: Hibernate -- Tue Jul 13 2004 - 12 PM to 1 PM (US/Central)
    -- (17:00 to 18:00 Tue Jul 13 2004 -GMT/UTC).

    Visit http://JavaRSS.com/BOF for more details....

    Thanks!
    Jay
    http://JavaRSS.com
  32. UserType?[ Go to top ]

    What is a UserType? It contains strange methods: returnedClass(), nullSafeGet(), isMutable() and so on. But it doesn't implement a interface as I would expect it. So what is a UserType? Is it something which only exists in the Hibernate world?
  33. UserType[ Go to top ]

    What is a UserType? It contains strange methods: returnedClass(), nullSafeGet(), isMutable() and so on. But it doesn't implement a interface as I would expect it. So what is a UserType? Is it something which only exists in the Hibernate world?
    Oops, yes, it is meant to implement net.sf.hibernate.UserType. ;-)
  34. Inserting/Updating Category[ Go to top ]

    This looks like a good solution, but how do you go about updating labels referenced by a category and inserting new categories from hibernate? As I understand in order to be able to update a Category through hibernate (i.e. ... category.setDescription("foreign description"); categoryManager.update(category); ... ) The nullSafeSet method of UserType will be executed with a prepared statement holding something like "update category set name = ? where id = ?", a value of "foreign description" and an index of 0. From this, the method would need to locate the current label code for the category object referenced by id (this means parsing the sql query and executing native sql!) and update -or- insert a new record into the Label table. I think the user type would also have to differentiate inserts from updates (so it could create new Labels for new Categories). This all seems super cumbersome to me. Can anyone think of a better way to update/insert categories through hibernate in the context of the example case proposed by Gavin?
  35. Deleting Category[ Go to top ]

    Well, you should not use a plain String to represent the localized string, but some sort of a holder containing the ID and the String value (or map of all localized String values, one value for each locale/language). That's what I do and it works very well. The user type then uses the ID to see whether its nullSafeSet needs to do an insert, or update. The only trouble I am currently working on is the removal of localized strings when their parent entity (Category) is removed. I cannot think of any way to do this removal from within the user type. One probably needs to implement an interceptor whose onDelete method would check the properties of the deleted entity and for all "localized string" properties it would perform a manual clean up of data stored in external tables - not nice, but it should work. Or is there a better solution? Jan
  36. Re: Deleting Category[ Go to top ]

    I am also at the same point and am about to use onDelete to cleanup labels. Did you find another solution for this? Or did that do the job.
  37. onDelete[ Go to top ]

    Been a long time since I posted on this topic; we actually ended up using a hibernate listener to take care of string cleanup.