CODEWITHSUNDEEP
sqlhibernategrails
Ian Durkan
Ian Durkan

Reputation: 1232

Storing maps in Hibernate/GORM

I'm having trouble understanding how to represent some information I'm dealing with using Hibernate in a Grails application. It's really more of a SQL problem though.

I have a number of Items to store in a database, from a JSON file. Each Item has a map of legality values. A legality map looks like this:

{
    "form2003": "legal",
    "form2007": "legal",
    "form2008": "banned",
    "form2009": "restricted"
    "form2013": "legal"
}

or this

{
    "form2003": "legal",
    "form2004": "legal",
    "form2005": "legal",
    "form2007": "restricted",
    "form2008": "banned",
    "form2009": "banned"
}

Keys range from "form2001" to "form2013", while values can be 'banned' 'restricted' or 'legal'. A fourth status is indicated implictly by the lack of an explicit status.

Right now I'm representing Items in their own table/domain type, with a parallel Legalities table; Items have a one-to-one relationship with Legalities. I have several other table/domain classes similar to Legalities, each with their own one-to-one relationship with Item.

The Legalities domain class I have now basically looks like this

class Legalities {
    static belongsTo = [item: Item]

    String form2000
    String form2001
    ...
    String form2013

    static constraints = {
        form2000(nullable: true)
        ...
        form2013(nullable: true)
    }
}

I need to be able to efficiently find every Item where a given field, like form2010, has a given value. This seems like it should be straightforward, with a HQL query like this:

"from Item as i where i.legalities.form2010 = \'legal\'"

The thing is, Grails and Hibernate won't let me parameterize queries on field names, only on field values!! The field name comes from a form so I end up having to try to sanitize the field name myself and assemble the query string manually. Is there some other way I'm supposed to be structuring the information here? Or am I running into a deficiency of Hibernate?

Upvotes: 1

Views: 91

Answers (1)

the baldheadedguy
the baldheadedguy

Reputation: 183

Try using criteria to create your queries like so:

def getLegalitiesForForm(formNum, formVal) {
    String formField = "form20${formNum}"

    return Legalities.createCriteria().get {
        eq formField, formVal
    }
}

http://grails.org/doc/2.3.4/guide/GORM.html#criteria

Upvotes: 1

Related Questions