Handling Database Tables with no Primary key in Spring MVC and Hibernate

If you are building your web application in Java, you might be using Spring MVC with Hibernate. Both these Frameworks are extremely powerful and lets you, the developer to focus on building the application itself rather than to worry about many other factors required to manage and maintain the project dependencies.

You might have designed a database model, and might have tables without a primary key. Tables which map multiple tables (a.k.a Mapping tables) , usually do not have a primary key. While this seems normal, there are situations when you will have to insert, or update values into the table and you will find it difficult to do so. Why ?  That’s because without a key, there exists an ambiguity as to which row to update or delete. Fortunately, there is an easy way to solve this problem.

This is gonna be a long post. So, brace yourselves. I will try my best to make my point, but if you have any queries/ suggestions, feel free to let me know in the comments below. So, lets begin.

The answer to this issue is to use an Embedded Entity which provides a key to the persistent entity without a primary key. And these are the two annotations that will be required: @Embeddable and @Embedded .

Consider two persistent entities called Car.java and Color.java . These two have a Primary Key each and represent the tables “CAR” and “COLOR” tables in the “VEHICLE” database/schema.

Car.java:

package com.example.entities;

@Entity
@Table(name = “CAR”, catalog = “VEHICLE”)
public class Car implements java.io.Serializable {

private int carId;

private String carName;

@Id

@Column(name = “CAR_ID”, unique = true, nullable = false)
public int getId(){
return this.colorId;
}
public void setId(int colorId){
this.colorId = colorId;
}

public void setCarName(String carName){

this.carName = carName;
}
@Column(name = “CAR_NAME”, nullable = false)
public String getCarName(){
return this.CarName;
}

}

Color.java:

package com.example.entities;
@Entity
@Table(name = “COLOR”, catalog = “VEHICLE”)
public class Color implements java.io.Serializable{

private int colorID;
private String colorName;

@Id

@Column(name = “COLOR_ID”, unique = true, nullable = false)
public int getId(){
return this.colorId;
}
public void setId(int colorId){
this.colorId = colorId;
}

@Column(name = “COLOR_NAME”, nullable = false)

public String getColor(){
return this.colorName;
}
public void setColor(String colorName){
this.colorName = colorName;
}

}

For these two entities let there be another mapping entity called “CarColor.java”.This is the representation of the mapping table between “CAR” and “COLOR”. As this is a mapping table, it does not have any primary key. In Spring Hibernate scenario , the enity CarColor.java would be like:

CarColor.java

package com.example.entities;

@Entity
@Table(name = “CAR_COLOR”, catalog = “VEHICLE”)
public class CarColor implements java.io.Serializable{

private Car car;
private Color color;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = “CAR_NAME”, nullable = false, insertable = false, updatable = false)
public Car getCar(){
return this.car;
}
public void setCar(Car car){
this.car = car;
}

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = “COLOR_NAME”, nullable = false, insertable = false, updatable = false)
public COLOR getColor(){
return this.color;
}
public void setColor(Color color){
this.color = color;
}
}

This above example is correct, but as you would have seen, No primary key is there. So, if there are scenarios where you want to identify a record uniquely, you will find yourself in a bit of trouble. But, there is a  way around it. If you have auto generated the entities, then this workaround is automatically implemented. If not, yo can do it yourself. Here is what needs to be done:

adding another entity called CarColorId.java. This class is NOT a persistent class. But this can be used to uniquely identify each record in the CarColor.java   table. Here is the new Implementation of CarColor.java and also CarColorId.java .

CarColor.java

package com.example.entities;

@Entity
@Table(name = “CAR_COLOR”, catalog = “VEHICLE”)
public class CarColor implements java.io.Serializable{

//The following variable is of the type CarColorId
private CarColorId id;
private Car car;
private Color color;

@EmbeddedId
@AttributeOverrides({
@AttributeOverride(name = “carId”, column = @Column(name = “CAR_ID”, nullable = false)),
@AttributeOverride(name = “colorId”, column = @Column(name = “COLOR_ID”, nullable = false))})
public CarColorId getId() {
return this.id;
}

public void setId(CarColorId id) {
this.id = id;
}

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = “CAR_NAME”, nullable = false, insertable = false, updatable = false)
public Car getCar(){
return this.car;
}

public void setCar(Car car){
this.car = car;
}

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = “COLOR_NAME”, nullable = false, insertable = false, updatable = false)
public COLOR getColor(){
return this.color;
}

public void setColor(Color color){
this.color = color;
}

}

CarColorId.java

package com.example.entities;

@Embeddable
public class CarColorId implements java.io.Serializable{

private int carId;
private int colorId;

@Column(name = “COLOR_ID”, nullable = false)
public int getColorId(){
return this.colorId;
}
public void setColorId(int colorId){
this.colorId = colorId;
}
@Column(name = “CAR_ID”, nullable = false)
public int getCarId(){
return this.colorId;
}
public void setCarId(int colorId){
this.colorId = colorId;
}

public int hashCode() {
int result = 17;

result = 37 * result + this.getCarId();
result = 37 * result + this.getColorId();
return result;
}

}

Let’s analyse the new things that have been included in the above two classes.  Firstly, you would have observed two new annotations :  @Embeddable and @EmbeddedId . Here embeddable is used to indicate an entity that isn’t a persistent class but has persistent objects within them and these persistent objects can be used to gain an identity to the persistent class to which it forms an ID. @EmbeddedId is used to represent the object of the embeddable class.

Also, there is method in carColorId called hashCode() . hashcode() are methods are the ones which  gives an integer value for a given object. So, the hashvalue of an object always remains same and a carefully created hashCode() is the one which gives a unique identity to an object of the persistent class. So, in our example, class CarColorId gives a unique Identity to our persistent class CarColor.  

To perform operations on the CarColor table, one can find and compare objects of the type CarColor by making use of the hashCode() and using that value as a value that provides uniqueness to the objects  of that class.

As an example, If you want to update an object of type CarColor, then first fetch the objects that match the criteria of car name and/or car color . Then apply the hashcode() on that object to check it matches the object that has to be updated. If it does, then perform the update operation.

So, this is it from me. Hope that you have found what you have been looking for. Feel free to comment regarding any questions or suggestions.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s