Tutorial :Legacy mapping in Grails/GORM: One domain class and two tables in a 1:N-relationship



Question:

Let's say I have two tables employee and salary with a 1:N relationship (one salary can be associated with many employees).

In plain SQL the tables would be joined with:

SELECT e.id, e.name, s.salary FROM employee e, salary s WHERE s.id = e.salary_id AND e.id = 12345;  

Assuming the following GORM-powered domain class how do I map the legacy database structure to the class?

class Employee {     String name     int salary  }  

Clarification #1: I want only one domain class containing data from both tables. Adding another class is hence not an option.

Clarification #2: The question I'm trying to find an answer to is simply "how do I map two tables to one class using Grails/GORM"? If you believe that it is impossible to do so, then please state that clearly in your answer rather than trying to restate the question.


Solution:1

IMO it is not possible with plain Grails/GORM to join multiple tables and map them to one Domain class. As a workaround you could use a legacy XML hibernate mapping and leverage the join feature to achieve your desired goal. Of course you would loose a lot of the GORM goodies.


Solution:2

Your SQL example indicates there are two tables, Employee and Salary. This should also be reflected in your classes. So instead of one, you need two classes. The GORM mapping would then look like this.

class Employee {     String name     Salary salary  }    class Salary {     static hasMany = [ employees : Employee ]     int salary  }  

See http://www.grails.org/GORM+-+Defining+relationships


Solution:3

You could, instead of having salary and name as properties, have them as get* methods that actually run a query on both these tables.

granted, that isnt the grails way, and its strongly recommended that you do follow the grails way.


Solution:4

I don't fully understand the limitation on not being able to add another class if there are 2 tables in the database, but if you're looking to have a unified interface, would it work to delegate the methods to the Salary class?

Something like:

class Salary {      int amount  }    class Employee {      Salary _salary      String name        String toString() { name }        public Integer getSalary() {          return _salary?.amount      }      public void setSalary(Integer amount) {      // not quite sure of your business logic here, this is a guess          _salary = Salary.findByAmount(amount)          if (!_salary) {              _salary = new Salary(amount: amount)              _salary.save()          }      }  }      def e = new Employee(name:"willy loman", salary: 100)  e.save()  assert e.salary == 100  

It's also possible that you might be able to make what you're asking for work with a custom hibernate mapping file, but I'm not familiar enough with contorting hibernate in that manner to say for sure.

See the Custom User Type section of this page.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »