How to use multiple primary keys

Question

I created database, for my android app, witch has 16 tables. I want to use ORMlite mapping. The problem is that I didn't find examples where you have composite id(Multiple primary keys). For example I have table:

CREATE  TABLE IF NOT EXISTS `Tourist_Guide`.`Cultural_activity` (
  `City_Id` INT NOT NULL ,
  `activity_Id` INT NOT NULL ,
  `Cultural_activity_Id` INT NOT NULL AUTO_INCREMENT ,
  `Name_Of_Cultural_activity` VARCHAR(30) NOT NULL ,
  PRIMARY KEY (`Cultural_activity_Id`, `City_Id`, `activity_Id`) ,
  INDEX `fk_Cultural_activity_activity1` (`City_Id` ASC, `activity_Id` ASC) ,
  CONSTRAINT `fk_Cultural_activity_activity1`
    FOREIGN KEY (`City_Id` , `activity_Id` )
    REFERENCES `Tourist_Guide`.`activity` (`City_Id` , `activity_Id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Can you, please, tell me how to map this table to class(how this class should look like), is that even possible?

Solution 1

Limitations

For simplicity, and to be able to have the same POCO class persisted in db4o, memcached, redis or on the filesystem (i.e. providers included in ServiceStack), each model must have a single primary key, by convention OrmLite expects it to be Id although you use [Alias("DbFieldName")] attribute it map it to a column with a different name or use the [PrimaryKey] attribute to tell OrmLite to use a different property for the primary key.

You can still SELECT from these tables, you will just be unable to make use of APIs that rely on it, e.g. Update or Delete where the filter is implied (i.e. not specified), all the APIs that end with ById, etc.

Workaround single Primary Key limitation

A potential workaround to support tables with multiple primary keys is to create an auto generated Id property that returns a unique value based on all the primary key fields, e.g:

public class OrderDetail
{
    public string Id { get { return this.OrderId + "/" + this.ProductId; } }

    

public int OrderId { get; set; }
    public int ProductId { get; set; }
    public decimal UnitPrice { get; set; }
    public short Quantity { get; set; }
    public double Discount { get; set; }
}

https://github.com/ServiceStack/ServiceStack.OrmLite/#limitations

Solution 2

You have to use the following annotation above each unique field:

@DatabaseField (uniqueCombo = true)

Here are the docs on uniqueCombo.

Solution 3

It is possible to generate an artificial ID field which consists of a composition of other fields. This can be done by settings the useSetGet Property in the @DatabaseField Annotation to true. This makes ORMLite call the getter and setter methods, instead of using reflection.

In your getter you could then return a composition of your fields.

This would look something like this in your example:

@DatabaseField(id=true, useGetSet=true)
private String id;

...

public String getId(){

    return culturalAcitivityId +"-" +cityId +"-" +activityId;

}
public void setId(String id){

    this.id = id;

}

http://ormlite.com/javadoc/ormlite-core/com/j256/ormlite/field/DatabaseField.html#useGetSet()

Comments

[sekula87]

I read this article, groups.google.com/group/ormlite-user/browse_thread/thread/…, so i got confused because: "Hrm. Ormlite doesn't currently support composite ids. Hibernate, for example, handles composite ids as embedded classes which I guess Ormlite would need to support to get this to work. "

[Zakaria]

If you look at the date, you should notice that discussion was in january 2011. But, the uniqueCombo was added three months later as you can see in the changelog (search = "uniqueCombo").

[Gray]

ORMLite still does not support compositie ids. It does support indexes that provide unique constraints however.

[Gray]

The post was about ORMLite the Java ORM library not service-stack.

[JanBo]

This solution can cause certain issues if your entity has a foreign collection. When updating foreign collection objects that dont eager/autorefresh their foreign parents, the foreign id will be set to null/0/default values because OrmLite will load them properly and set the id by using setId(), but when updating it will try to use getId which will "destroy" your foreign connection if the objects has been lazy loaded.

[ChRoNoN]

useSetGet still uses reflection, but instead of direct field access is uses getter and setter methods.