REVERT TO CONSOLE

This is a variation on this article. Having never been a big fan of writing XML, I’ll gladly opt for the XML-less version. The first example online I found has some formatting issues and just seems a little hard to follow.

My step by step example follows after the jump. Call it the “Grails extended many-to-many step-by-step guide using the grails console, for dummies”.

Let’s start with a very simple example:

A person has one or more characteristics. A characteristic can be applied to more than one person. A PersonCharacteristic relationship has an assignedValue.

Here’s what I did:

grails create-domain-class person

grails create-domain-class characteristic

grails create-domain-class personCharacteristic

First I fleshed out Person:

class Person {

	static hasMany = [characteristics:PersonCharacteristic]

	String firstName
	String lastName

	List characteristics() {
		return characteristics.collect{it.characteristic}
	}

	List addCharacteristic(Characteristic characteristic) {
		return addCharacteristic(characteristic, new Integer(1))
	}

	List addCharacteristic(Characteristic characteristic, Integer assignedValue) {
		PersonCharacteristic.link(this, characteristic, assignedValue)
		return characteristics()
	}

	List removeFromCharacteristics(Characteristic characteristic) {
		PersonCharacteristic.unlink(this, characteristic)
		return characteristics()
	}

}

and Characteristic is pretty similar

class Characteristic {

	static hasMany = [people:PersonCharacteristic,employers:EmployerCharacteristic]

	String name
	String description
	Integer systemWeight = new Integer(1)

	List people() {
		return people.collect{it.person}
	}

	List employers() {
		return employers.collect{it.employer}
	}

	List addToEmployers(Employer employer) {
		EmployerCharacteristic.link(employer, this)
		return employers()
	}

	List addToPeople(Person person) {
		PersonCharacteristic.link(person, this)
		return people()
	}

	List removeFromEmployers(Employer employer) {
		EmployerCharacteristic.unlink(employer, this)
		return employers()
	}

	List removeFromPeople(Person person) {
		PersonCharacteristic.unlink(person, this)
		return people()
	}
}

and finally, PersonCharacteristic

class PersonCharacteristic {

	def Person person
	def Characteristic characteristic
	def Integer assignedValue

	static PersonCharacteristic link(Person person, Characteristic characteristic) {

		return link(person, characteristic, new Integer(1))
	}

	static PersonCharacteristic link(Person person, Characteristic characteristic, Integer assignedValue ) {
		def pc = PersonCharacteristic.findByPersonAndCharacteristic(person, characteristic)
		if (!pc) {
			pc = new PersonCharacteristic(userWeight:1)
			pc.person = person
			pc.characteristic = characteristic
			pc.assignedValue = assignedValue
			person?.addToCharacteristics(pc)
			characteristic?.addToPeople(pc)
			pc.save()
		}
		return pc
	}

	static void unlink(Person person, Characteristic characteristic) {
		def pc = PersonCharacteristic.findByPersonAndCharacteristic(person, characteristic)
		if (pc) {
			person?.removeFromCharacteristics(pc)
			characteristic?.removeFromPeople(pc)
			pc.delete()
		}
	}
}

Note: if you want an explanation of this class look here and read the section The Many-to-Many map.

Now, with any luck we’ll be able to do this with the console…

(I would assume you know this but just in case… go to your project base dir and type…

grails console

Now here are some people and characteristics. This is the easy part.

def a = new Person(firstName: "Jeff", lastName: "Revert")
a.save()
def b = new Person(firstName: "Priyatam", lastName: "Console")
b.save()
def c = new Characteristic(name: "Stupid", description: "slow of mind")
c.save()
def d = new Characteristic(name: "Handsome", description: "a pleasing appearance")
d.save()

you should see this output if you’re following along correctly:

Result: Characteristic : 2

But why take my word for it? Test it!

def jeff = Person.findByFirstName('Jeff')
def priyatam = Person.findByFirstName('Priyatam')
def stupid = Characteristic.findByName('Stupid')
def handsome = Characteristic.findByName('Handsome')
if(jeff) {
    println "Found Jeff"
}
if(priyatam) {
    println "Found Priyatam"
}
if(stupid) {
    println "Found Stupid"
}
if(handsome) {
    println "Found Handsome"
}

You should see this output:

Found Jeff
Found Priyatam
Found Stupid
Found Handsome

Next we need to add our characteristics to our people.

def person = Person.findByFirstName('Jeff')
def characteristic = Characteristic.findByName('Stupid')
person.addCharacteristic(characteristic)

and the resulting output:

Result: [Characteristic : 1]

I should be set with my many to many relationship!

Not convinced? I know how you feel…

One problem with Grails default database config is that you can’t easily go there and check out the schema. Well, I don’t know how to anyway, and I’m guessing I’m not alone. In my real world job I always check out the database. It’s like a security blanket, only with a penchant for evil. Anyway, it’s not that HSQLDB can’t provide it, it’s that the config isn’t running an in-memory database capable of connecting to. So change the development datasource url to this:

url = "jdbc:hsqldb:hsql://localhost:9001/devDB"

Open a new shell, go to your grails install directory, and run this:

java -cp ./lib/hsqldb-1.8.0.5.jar org.hsqldb.Server -database.0 mem:devDB -dbname.0 devDB

This will run an in memory database server that you can connect to with your favorite database exploring app. Now fire up the grails console again, and let’s add this stuff again.
After adding the people and characteristics, and adding the PersonCharacteristic, check out the tables. I see PERSON, CHARACTERISTIC, and PERSON_CHARACTERISTIC.

select * from person_characteristic

and this shows I’ve got a PERSON_CHARACTERISTIC table with an ID, VERSION, CHARACTERISTIC_ID, PERSON_ID, and ASSIGNED_VALUE columns with values 1,0,1,1,1. So you get exactly what you’d expect. Not bad!