How to model stored procedure records in Grails?

问题:

I need to call some stored procedures that return their own kinds of records, that are not directly mapped to tables or views.

I have used stored procedures in the past with groovy.sql.Sql and plain (unmodelled) Maps, but for this application I would like to model those records with something akin to domain classes, in order to define data types, data binding, validation, associations to other entities, and so on.

What is the best way to do so?

Should I model the stored procedure records as proper domain classes (entities) and then try to disable (or redefine) their database persistence? How?

Should I use non-domain POJOs where I selectively enable the features I need? (such as validation with @Validatable) How can I handle associations then? (Associations arise when the record returned from the SP contains a foreign key to some other persisted entity.)

回答1:

If you want data binding, validation and associations to be maintained in an easy way then you should go with the Domain approach.

To disable database persistence for a domain class you can add static mapWith = "none" to a domain class and a table won't be created for that domain class.

Sample Entity class:

@ToString
public class SPTest {

    Long idField

    User user

    GroupIntegrationKey integrationKey

    static constraints = {
    }

    static mapWith = "none"
}

Stored Procedure statement:

SELECT id AS idField, user_id AS user, key AS integrationKey FROM my_domain;

In order to map the result of the SP to the entity you can use result transformers.

Query query = session.createSQLQuery("CALL getSPData()");

List<Map> results = query.with {
    resultTransformer = AliasToEntityMapResultTransformer.INSTANCE
    list()
}

Now iterate over list and create a new entity object

List<MyDomain> list = results.collect {
    new MyDomain(it)
}

System.err.println(list)

Drawbacks:

1. You can not map identifier field
2. You would have to iterate over result again to create entity objects
3. You can not map hasMany relationships

If you want to go with pojo, then in that case you would have to create your own version of getters to get associated objects.