mscharhag, Programming and Stuff;

A blog about programming and software development topics, mostly focused on Java technologies including Java EE, Spring and Grails.

Posts tagged with Hibernate

  • Thursday, 23 January, 2014

    Using database views in Grails

    This post is a quick explanation on how to use database views in Grails.

    For an introduction I tried to summarize what database views are. However, I noticed I cannot describe it better than it is already done on Wikipedia. Therefore I will just quote the Wikipedia summary of View (SQL) here:

    In database theory, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated from data in the database, dynamically when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view.
    (Wikipedia)

    Example
    Let's assume we have a Grails application with the following domain classes:

    class User {
      String name
      Address address
      ...
    }

     

    class Address {
      String country
      ...
    }

    For whatever reason we want a domain class that contains direct references to the name and the country of an user. However, we do not want to duplicate these two values in another database table. A view can help us here.

    Creating the view
    At this point I assume you are already using the Grails database-migration plugin. If you don't you should clearly check it out. The plugin is automatically included with newer Grails versions and provides a convenient way to manage databases using change sets.

    To create a view we just have to create a new change set:

    changeSet(author: '..', id: '..') {
      createView("""
          SELECT u.id, u.name, a.country
          FROM user u
          JOIN address a on u.address_id = a.id
        """, viewName: 'user_with_country')
    }

    Here we create a view named user_with_country which contains three values: user id, user name and country.

    Creating the domain class
    Like normal tables views can be mapped to domain classes. The domain class for our view looks very simple:

    class UserWithCountry {
      String name
      String country
      
      static mapping = {
        table 'user_with_country'
        version false
      }
    } 

    Note that we disable versioning by setting version to false (we don't have a version column in our view).

    At this point we just have to be sure that our database change set is executed before hibernate tries to create/update tables on application start. This is typically be done by disabling the table creation of hibernate in DataSource.groovy and enabling the automatic migration on application start by setting grails.plugin.databasemigration.updateOnStart to true. Alternatively this can be achieved by manually executing all new changesets by running the dbm-update command.

    Usage
    Now we can use our UserWithCountry class to access the view:

    Address johnsAddress = new Address(country: 'england')
    User john = new User(name: 'john', address: johnsAddress)
    john.save(failOnError: true)
    
    assert UserWithCountry.count() == 1
    
    UserWithCountry johnFromEngland = UserWithCountry.get(john.id)
    assert johnFromEngland.name == 'john'
    assert johnFromEngland.country == 'england'

    Advantages of views
    I know the example I am using here is not the best. The relationship between User and Address is already very simple and a view isn't required here. However, if you have more sophisticated data structures views can be a nice way to hide complex relationships that would require joining a lot of tables. Views can also be used as security measure if you don't want to expose all columns of your tables to the application.

  • Saturday, 4 January, 2014

    Using Hibernate Filters in Grails

    The Grails Hibernate Filters plugin makes it possible to use Hibernate Filters with GORM. Hibernate Filters provide additional restriction rules that can be applied to classes and collections. The Hibernate Filters plugin makes it possible to use this functionality with GORM inside Grails applications. Let's look at an example to see how filters can help us.

    Assume we have the following Grails domain class:

    class User {
      String username
      boolean locked
    }

    The locked flag indicates if an User has been locked by an administrator for some reason. Assume that most parts of our application should treat locked users as they would not exist. To accomplish this we could add an additional condition that takes care of the locked flag to all queries that are used to retrieve User objects from the database. However, this would not be a good solution for various reasons (think of DRY, what if we have more than one flag?). Hibernate filters can be a nice help in this situation.

    Plugin installation
    To install the Hibernate Filters Plugin in a Grails application we have to do two things:

    Add the plugin dependency to BuildConfig.groovy:

    compile ":hibernate-filter:0.3.2" 

    Add the following configuration property to our dataSource definition:

    dataSource {
      ...
      configClass = HibernateFilterDomainConfiguration
    }

    Filter configuration
    Now we can add filters to our domain class using the static hibernateFilters property:

     

    class User {
      String username
      boolean locked
      static hibernateFilters = {
        lockedFilter(condition: 'locked=0', default: true)
      }
    }

    Here we define a filter with the name lockedFilter. The filter is enabled by default which means that the condition will be always applied when we query the database for User objects.

    // returns only users that are not locked
    List users = User.findAll() 
    
    // returns null if the user is locked
    User john = User.findByUsername('John') 

    However there is one exception: User.get(id) will return the User object even if the user for the given id has been locked. To apply the filter when retrieving a User by id we have to use  User.findById(id).

    In some situations we might need all users, even the locked ones (for example in the administration interface). Here we can use the withoutHibernateFilter method to disable our filter:

    User.withoutHibernateFilter('lockedFilter') {
      // get all users, including locked users
      List allUsers = User.findAll()
    }

    Filtering collections
    Hibernate Filters can also be used to filter collections. Assume users are able to upload images in our application. These images are saved in a collection in our User domain class:

    class User {
      ...
      static hasMany = [images: Image]
    }

     

    class Image {
      boolean visible
      static belongsTo = [user: User]
    }

    Administrators are able to hide inappropriate images by setting the visible property of Image to false. Images with the visible flag set to false should not be used anywhere (similar to locked users). The problem we face here is that user.getImages() returns all images of a given user by default.

    Filters can help us here again:

    class User {
      ...
      static hasMany = [images: Image]
      static hibernateFilters = {
        ...
        imagesVisibleFilter(collection: 'images', condition: 'visible=1', default: true)
      }
    }

    Here we define a filter named imagesVisibleFilter that is applied to the images collection by default. The filter condition will be applied whenever the images of a given user are retrieved from the database. If we now access the images collection using user.getImages() we only get visible images.

    Conclusion
    The Hibernate Filters plugin provides some nice and easy utilities for adding additional restrictions to domain classes and collections. The possibility to enable filters by default is a nice improvement compared to standard Hibernate in which filters need to be enabled manually for each Session instance.

    You can find the example project I created while writing this post on GitHup.

  • Tuesday, 24 September, 2013

    Trouble with MySQL’s lower() function

    If you are using MySQL 5.6.x with Hibernate and your query results don’t show up in the expected order it is possible that you run into this MySQL bug.

    Today we ran into this issue with GORM (which uses Hibernate). A simple findAll() returned the results in invalid order:

    List users = User.findAll([max: 10, offset: 0, sort: "name", order: "asc"]

    Other than expected the resulting list of users was not ordered by the users name.

    When looking at the query generated by hibernate we saw the following order by clause:

    ... order by lower(this_.name) asc limit ?

    After testing this query in different variations against different MySQL versions, we noticed that the lower() function does not work as intended in MySQL 5.6.11. Shortly after that, we found the bug ticket mentioned above.