Archive

Posts Tagged ‘data’

Basic Hibernate @OneToOne @PrimaryKeyJoinColumn Example With Maven and MySQL

November 14, 2009 7 comments

Recently we had a story which involved improving one of our data models. The table for the model had grown quite wide and we wanted to improve normalization and performance. We wanted to move a few columns from our original table (Listing) to a new table with the primary key of the new table (ListingLocation) also being a foreign key to the primary key of the original table, our one-to-one relationship. I will try to detail how we accomplished this change using a simplified example. Source code is linked at the bottom of this post.

Here is the entity relationship diagram of the old, single table structure:
Old ER Diagram
And here is the entity relationship diagram of the new, two table structure:
New ER Diagram

As you can see, it is a very simple example of a very common relationship in the database. However, what we found when implementing this in Hibernate was not a simple as I had hoped.

To get started here is the SQL used to represent our new tables:

CREATE TABLE Listing
(
id BIGINT(20) NOT NULL AUTO_INCREMENT,
price DECIMAL(10,2),
PRIMARY KEY (id)
) TYPE = INNODB;


CREATE TABLE ListingLocation
(
listingID BIGINT(20) NOT NULL,
address VARCHAR(255),
PRIMARY KEY(listingID),
INDEX (listingID),
FOREIGN KEY (listingID) REFERENCES Listing (id)
) TYPE = INNODB;

I’ve used MySQL for this example because it is free and easy to setup. InnoDB table types have been because they allow foreign keys, which is crucial to this example.

Our old Listing entity was pretty basic; it looked something like this (imports & getters/setters excluded):

@Entity
@Table
public class Listing implements Serializable {
@Id
@GeneratedValue
private long id;
@Column(columnDefinition= "DECIMAL(10,2)")
private double price;
private String address;
...

In this case, creating a new instance of Listing and persisting it is very easy. When we split the entities, it becomes a little more complicated. Here is what our entities looked like after being split:

@Entity
@Table
public class Listing implements Serializable {
@Id
@GeneratedValue
private long id;
@Column(columnDefinition= "DECIMAL(10,2)")
private double price;
@OneToOne
@PrimaryKeyJoinColumn
private ListingLocation listingLocation;
...

and

@Entity
@Table
public class ListingLocation implements Serializable {
@Id
@Column(name = "listingID")
private Long id;
private String address;
...

The differences are not large, but there are a couple important points to note:

  • Adding ListingLocation to Listing with @OneToOne & @PrimaryKeyJoinColumn annotations tells Hibernate the Listing has a one-to-one mapping with ListingLocation by using the Primary Key as the join column.
  • Adding @Id & @Column(name = “listingID”) annotations to our id field in ListingLocation tells Hibernate that id is, well, an ID, but that column should not be called “id” in the DB, but “listingID” as that will help an observer see the relationship quickly without looking closely at the schema. Also, it is good to note that the @GeneratedValue is not on “id” in ListingLocation as it is in Listing as we want to specify exactly what goes in that field.

The biggest gripe I have with using the one-to-one relationship is that we can no longer save Listing only. Our REAL Listing entity is far more complex with several relationships, but this was the first one-to-one relationship with Hibernate. Previously we could do something like:

Listing listing = new Listing();
listing.setPrice(price);
listing.setAddress(address);
listing.setFoo(foo); // where foo is a @OneToMany annotated entity
...
session.save(listing);

Now, we must save Listing and ListingLocation separately like this:

ListingLocation listingLocation = new ListingLocation();
listingLocation.setAddress(address);
Listing listing = new Listing();
listing.setPrice(price);
listing.setListingLocation(listingLocation);
...
session.save(listing);
if (listing.getListingLocation() != null) {
listing.getListingLocation().setId(listing.getId());
session.save(listing.getListingLocation()); // save ListingLocation
}

I guess I’m just a bit spoiled, but I was hoping that this would bit more automatic, as it is with the one-to-many/many-to-one relationships.

I have written a small app that uses these two entities and inserts a row into each table, the link is available at the bottom of this post. The config (hibernate.cfg.xml) expects that you have MySQL running on 127.0.0.1 with a DB named ‘OneToOneDemo’ with a user named ‘root’ and a password of ‘password’; I have included the SQL (Setup-OneToOneDemo.sql) to setup the DB. Just extract the contents of the archive, navigate to the project directory, and from the command line/terminal run:

mvn clean compile exec:java -Dexec.mainClass=com.point2.onetoonedemo.App -e

You should see the following output:

@OneToOne @PrimaryKeyJoinColumn Hibernate Demo
----------------------------------------------
...
Hibernate:
insert into Listing (price) values (?)

Hibernate:
insert into ListingLocation (address, listingID) values (?, ?)
Saved listing ID: 1

Download Source Code (12 KB .ZIP)

If you have any questions, comments, or suggestions on how to do better accomplish one-to-one Hibernate mappings, I would love to hear about them. If you have any problems getting the code to compile and/or run, please let me know and I will make the necessary changes. Everything should just work, providing you modify the hibernate config or setup your DB. I had a difficult time finding complete and recent documentation on this subject so I hope this post and the maven project will help.

By: Damien Gabrielson

Creating Fixtures from Within Tests

February 3, 2009 Comments off

Django gives you a ‘dumpdata’ target which will create a fixture from all the records in your schema. For what we wanted, this was overkill. We had an existing unit test which was creating data in one test, and it was just the right amount of data for what we wanted. After searching through the Django codebase, it became clear that we could pass the objects we had in that test straight to the JSON serializer, and write the output to file. This ended up looking something like this:-

from django.core.serializers import json
serializer = json.Serializer()
objectsToSerialize =
MyModel.objects.filter(column='restriction')
with open('my_fixture.json','w') as f:
. f.write(serializer.serialize(objectsToSerialize, indent=4))

And that’s it! You can also spin up a shell, using:-

python manage.py shell

and load the data you want to create fixtures from if you don’t have any tests that create the data you want already.

By: Chris Tarttelin