CMR - Complex Link Possible?

[jcowgar]

Well, not really a complex link, but more than just a simple one field to one field.

I have many ejbs that need to store notes therefore I created a notes ejb that contains:

id int
type char
link_id int
note string

Now, type is the problem here. When say a patient add's a note, it adds: (type='p', link_id=(patient's id say, 2039), note='howdy')

But when a claim ejb adds a note, it adds: (type='c', link_id(provider's id, for conversation sake, the same as the patient_id, 2039), note='provider note')

Each ejb has it's own id that simply increments, therefore each ejb could and will have a id that is shared with another ejb. The id's are unique inside the table, not the db. Therefore, in SQL the way I would do a query to find notes for a patient:

SELECT * FROM notes WHERE type='p' AND linkId=2039;

Can I do that with the CMR in JBoss some how?

Jeremy Cowgar
[e-mail]

[jcowgar]

Guess I asked a hard one?

Jeremy Cowgar

[jboynes]

Without changing the database schema, I don't think you can use CMR for this. The relationship depends on additional data (the 'p' or 'c' literal for 'type') which isn't available to JBossCMP. It's the same thing that prevents you adding a fk constraint in the database.

If you can change the schema, there are a couple of options:

Option 1, use a relation-table mapping, which will require an additional table for each relation (patient->note, provider->note). This just adds to your existing schema (e.g. if you have compatibility issues).

Option 2, have separate tables for each note type (patient_note, provider_note), making the 'type' field unnecessary. This would allow a simple foreign-key CMR (and permit fk constraints in the db if that's important to you) but would require you to introduce EJBs for the different note types - Patient->PatientNote, Provider->ProviderNote (of course, they can share implementation).