Sunday, August 28, 2016

Hibernate JPA OneToOne association with common primary key

Imagine, that you need to create a movies database, which stores a movie details and a details on a book, on which the movie was done. A book details are optional, since a movie may be not based on a book. A movie details are: title, release date, country, genre, language. A book details are: title, author, country, language.

You might define a table MOVIES that contains details for both a movie and a book. If a movie is not done on a book, the book-related columns will be null. For relational database such model means a waste of space, since a data column takes space according to its data type, no matter if data is null.

The better and normalized model may be done with two tables:MOVIE and MOVIE_BOOK. The table MOVIE contains only columns with movie details and has a primary key ID, which identifies a movie. The table MOVIE_BOOK contains columns with book details and has a primary key MOVIE_ID. If a particular movie is not done on a book, only a record in the MOVIE is created. If a movie is done on a book, an associated record in MOVIE_BOOK is created; the value of the MOVIE_ID of the book is the foreign key to the ID of the MOVIE. Getting of a movie and a book details in a single query is reached with LEFT JOIN.

This model may be easily implemented with the Hibernate. The Hibernate ensures, that a movie and a relevant book will be assigned with the same primary key upon creation and will be left joined upon get. Let's see, how this should be done.

Movies DB schema and select query


DB schema:
 CREATE TABLE `MOVIES_DB`.`MOVIE` (  
  `ID` int(11) unsigned NOT NULL auto_increment,  
  `TITLE` varchar(60) NOT NULL,  
  `COUNTRY` varchar(30) NOT NULL,   
  `LANGUAGE` varchar(30) NOT NULL,   
  `GENRE` enum('Drama', 'Comedy', 'Action', 'Animation', 'Documentary', 'Romance', 'Crime')   
    CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,  
   `RELEASED_ON` datetime NULL,  
  CONSTRAINT `PRIMARY` PRIMARY KEY (`ID`)  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 CREATE TABLE `MOVIES_DB`.`MOVIE_BOOK` (  
  `MOVIE_ID` int(11) unsigned NOT NULL,  
  `TITLE` varchar(60) NULL,  
  `AUTHOR` varchar(60) NULL,  
  `COUNTRY` varchar(30) NOT NULL,   
  `LANGUAGE` varchar(30) NOT NULL,   
  CONSTRAINT `PRIMARY` PRIMARY KEY (`MOVIE_ID`),  
  CONSTRAINT `FK_MOVIE_ID` FOREIGN KEY (`MOVIE_ID`) REFERENCES `MOVIES_DB`.`MOVIE` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
Select query to get movies with books:
 SELECT * FROM MOVIES_DB.MOVIE LEFT JOIN MOVIES_DB.MOVIE_BOOK ON MOVIES_DB.MOVIE.ID=MOVIES_DB.MOVIE_BOOK.MOVIE_ID;

Java classes


The entity class DbMovie implements the table MOVIE model. Its id is generated automatically by DB:
 @Id  
 @GeneratedValue  
 @Column(name="ID")  
 public int getId() {  
      return id;  
 }  
The DbMovie embeds a book details of type DbMovieBook. The annotations @OneToOne and @PrimaryKeyJoinColumn with reference to the column "MOVIE_ID" implements the left join:
 @OneToOne(cascade=CascadeType.ALL)  
 @PrimaryKeyJoinColumn(referencedColumnName="MOVIE_ID")  
 public DbMovieBook getMovieBook() {  
     return movieBook;  
 }
The full implementation of the DbMovie:
 @Data  
 @EqualsAndHashCode(callSuper=false)  
 @Table(name="MOVIE")  
 @Entity  
 @Access(AccessType.PROPERTY)  
 public class DbMovie {  
      static enum MovieGenre {  
           Drama, Comedy, Action, Animation, Documentary, Romance, Crime  
      }  
      private int id;  
      private String title;  
      private String country;  
      private String language;  
      private MovieGenre genre;  
      private Date releasedOn;  
      private DbMovieBook movieBook;  
      @Id  
      @GeneratedValue  
      @Column(name="ID")  
      public int getId() {  
           return id;  
      }  
      @Column(name="TITLE", nullable=false)  
      public String getTitle() {  
           return title;  
      }  
      @Column(name="COUNTRY", nullable=false)  
      public String getCountry() {  
           return country;  
      }  
      @Column(name="LANGUAGE", nullable=false)  
      public String getLanguage() {  
           return language;  
      }  
      @Enumerated(EnumType.STRING)  
      @Column(name="GENRE")  
      public MovieGenre getGenre() {  
           return genre;  
      }  
      @Column(name="RELEASED_ON")  
      public Date getReleasedOn() {  
           return releasedOn;  
      }  
      @OneToOne(cascade=CascadeType.ALL)  
      @PrimaryKeyJoinColumn(referencedColumnName="MOVIE_ID")  
      public DbMovieBook getMovieBook() {  
           return movieBook;  
      }  
      @Override  
      public String toString() {  
           return "DbMovie [id=" + id + ", title=" + title + ", country=" + country + ", language=" + language + ", genre="  
                     + genre + ", releasedOn=" + releasedOn + ", movieBook=" + movieBook + "]";  
      }  
 }  

The entity class DbMovieBook implements the table MOVIE_BOOK model. It embeds a movie entity movie, which is referred in definition of the DbMovieBook id and definition of the join condition.
The DbMovieBook id is defined as a foreign key of auto-generated primary key of a movie entity:
 @Id  
 @GeneratedValue(generator="SharedPrimaryKeyGenerator")  
 @GenericGenerator(name="SharedPrimaryKeyGenerator",strategy="foreign",parameters = @Parameter(name="property", value="movie"))  
 @Column(name="MOVIE_ID", unique = true, nullable = false)  
 public int getMovieId() {  
      return movieId;  
 }  
The left join condition is defined with:
 @OneToOne  
 @PrimaryKeyJoinColumn  
 public DbMovie getMovie() {  
      return movie;  
 }
The toString implementation of the DbMovieBook should not print embedded movie, since this will cause the cyclic dependencies:
 @Override  
 public String toString() {  
      return "DbMovieBook [movieId=" + movieId + ", title=" + title + ", author=" + author + ", country=" + country  
                + ", language=" + language + "]";  
 }  

The full implementation of the DbMovieBook:
 import org.hibernate.annotations.GenericGenerator;  
 import org.hibernate.annotations.Parameter;  
 @Data  
 @EqualsAndHashCode(callSuper=false)  
 @Table(name="MOVIE_BOOK")  
 @Entity  
 @Access(AccessType.PROPERTY)  
 public class DbMovieBook{  
      private int movieId;  
      private String title;  
      private String author;  
      private String country;  
      private String language;  
      private DbMovie movie;  
      @Id  
      @GeneratedValue(generator="SharedPrimaryKeyGenerator")  
      @GenericGenerator(name="SharedPrimaryKeyGenerator",strategy="foreign",parameters = @Parameter(name="property", value="movie"))  
      @Column(name="MOVIE_ID", unique = true, nullable = false)  
      public int getMovieId() {  
           return movieId;  
      }  
      @OneToOne  
      @PrimaryKeyJoinColumn  
      public DbMovie getMovie() {  
           return movie;  
      }  
      @Column(name="TITLE", nullable=false)  
      public String getTitle() {  
           return title;  
      }  
      @Column(name="AUTHOR", nullable=false)  
      public String getAuthor() {  
           return author;  
      }  
      @Column(name="COUNTRY", nullable=false)  
      public String getCountry() {  
           return country;  
      }  
      @Column(name="LANGUAGE", nullable=false)  
      public String getLanguage() {  
           return language;  
      }  
      @Override  
      public String toString() {  
           return "DbMovieBook [movieId=" + movieId + ", title=" + title + ", author=" + author + ", country=" + country  
                     + ", language=" + language + "]";  
      }  
 }  

No comments :

About the author

My Photo
I trust only simple code and believe that code should be handsome. This is not a matter of technology, but professional approach, consolidated after years of software development. I enjoy to cause things working and feel very happy, when I manage to solve a problem.
Back to Top