Object Relational Mapping with JDBC

In this post, we will try to understand object relational mapping using the book store application. We will create the book object graph and persist into the relational database called bookstore. After that, we will retrieve the same object graph from relational database bookstore. Here we will understand the storing java objects to the relational database and fetch the data from the relational database to the Java objects using JDBC stands for Java Database Connectivity. It provides the set of API for accessing the relational database and storing the object graph in a relational database. These API provides to execute SQL statement and interact with any SQL compliant database.

While doing this exercise we will see what is the challenges facing in storing the object graph and retrieving the data from a relational database.

UML diagram of Bookstore application

In this bookstore application we have three java classes Book class, Publisher class, and Chapter class and the diagram shows the associations between them. A book has a publisher and can have multiple chapters. We have three relational database tables for bookstore application.
BOOK table, PUBLISHER table, and CHAPTER table. The relationship between these tables shown in below diagram. Tables are associated with each other with help of foreign keys.

Bookstore application object model

Bookstore application has three java classes Book, Publisher, and Chapter. There are simple java class called Plain Old Java Object.

package com.walking.techie.model;

import java.util.List;

public class Book {

    private String isbn;
    private String name;
    private Publisher publisher;
    private List<Chapter> chapters;

    public Book(String isbn, String name, Publisher publisher,
                List<Chapter> chapters) {
        this.isbn = isbn;
        this.name = name;
        this.publisher = publisher;
        this.chapters = chapters;
    }

    public Book() {
    }

    public String getIsbn() {
        return isbn;
    }

    public void setIsbn(String isbn) {
        this.isbn = isbn;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Publisher getPublisher() {
        return publisher;
    }

    public void setPublisher(Publisher publisher) {
        this.publisher = publisher;
    }

    public List<Chapter> getChapters() {
        return chapters;
    }

    public void setChapters(List<Chapter> chapters) {
        this.chapters = chapters;
    }


    @Override
    public String toString() {
        return "Book{" +
                "isbn='" + isbn + '\'' +
                ", name='" + name + '\'' +
                ", publisher=" + publisher +
                ", chapters=" + chapters +
                '}';
    }
}

package com.walking.techie.model;

public class Publisher {

    private String code;
    private String name;

    public Publisher(String code, String name) {
        this.code = code;
        this.name = name;
    }

    public Publisher() {
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Publisher{" +
                "code='" + code + '\'' +
                ", name='" + name + '\'' +
                '}';
    }
}

package com.walking.techie.model;

public class Chapter {

    private String title;
    private Integer chapterNumber;

    public Chapter(String title, Integer chapterNumber) {
        this.title = title;
        this.chapterNumber = chapterNumber;
    }

    public Chapter() {
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Integer getChapterNumber() {
        return chapterNumber;
    }

    public void setChapterNumber(Integer chapterNumber) {
        this.chapterNumber = chapterNumber;
    }

    @Override
    public String toString() {
        return "Chapter{" +
                "title='" + title + '\'' +
                ", chapterNumber=" + chapterNumber +
                '}';
    }
}

Bookstore application relational model

We are using MySQL database to store the object graph of bookstore application.

CREATE DATABASE bookstore;
USE bookstore;

CREATE TABLE PUBLISHER (CODE VARCHAR(4) NOT NULL,
PUBLISHER_NAME VARCHAR(100) NOT NULL,
PRIMARY KEY(CODE));

CREATE TABLE BOOK(ISBN VARCHAR(50) NOT  NULL,
BOOK_NAME VARCHAR(100) NOT NULL,
PUBLISHER_CODE VARCHAR(4) NOT NULL,
PRIMARY KEY(ISBN),
FOREIGN KEY(PUBLISHER_CODE) REFERENCES PUBLISHER(CODE));

CREATE TABLE CHAPTER(BOOK_ISBN VARCHAR(50) NOT NULL,
CHAPTER_NUM INT NOT NULL,
TITLE VARCHAR(100) NOT NULL,
PRIMARY KEY(BOOK_ISBN, CHAPTER_NUM),
FOREIGN KEY(BOOK_ISBN) REFERENCES BOOK(ISBN));

Tables created in bookstore database and their relationship as below diagram. In CHAPTER table both BOOK_ISBN, and CHAPTER_NUM defined as primary key that together will uniquely identify a chapter.

Database connection

We need mysql java connector to create database connection with MySQL database using JDBC driver.

package com.walking.techie.connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DataBaseConnection {
    private static Connection connection = null;

    public static Connection getDBConnection() throws ClassNotFoundException, SQLException {

        // Fully qualified name of Driver class
        Class.forName("com.mysql.jdbc.Driver");

        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/bookstore?useSSL=false", "root", "santosh");
        return connection;
    }
}

Persist Object Graph in Relational Database

Now we are all set to save object graph into relational database.

package com.walking.techie.service;

import com.walking.techie.model.Book;

public interface BookStore {
    public void persistObjectGraph(Book book);

    public Book retrieveObjectGraph(String isbn);
}

package com.walking.techie.service;


import com.walking.techie.connection.DataBaseConnection;
import com.walking.techie.model.Book;
import com.walking.techie.model.Chapter;
import com.walking.techie.model.Publisher;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class BookStoreService implements BookStore {
    @Override
    public void persistObjectGraph(Book book) {
        try {
            Connection connection = DataBaseConnection.getDBConnection();

            // store publisher details in PUBLISHER table
            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO PUBLISHER (CODE, PUBLISHER_NAME) VALUES (?,?)");
            preparedStatement.setString(1, book.getPublisher().getCode());
            preparedStatement.setString(2, book.getPublisher().getName());
            preparedStatement.executeUpdate();
            preparedStatement.close();

            // store book details in BOOK table
            preparedStatement = connection.prepareStatement("INSERT INTO BOOK (ISBN, BOOK_NAME, PUBLISHER_CODE) VALUES (?,?,?)");
            preparedStatement.setString(1, book.getIsbn());
            preparedStatement.setString(2, book.getName());
            preparedStatement.setString(3, book.getPublisher().getCode());
            preparedStatement.executeUpdate();
            preparedStatement.close();

            // store chapter details in CHAPTER table
            preparedStatement = connection.prepareStatement("INSERT INTO CHAPTER (BOOK_ISBN, CHAPTER_NUM, TITLE) VALUES (?,?,?)");
            for (Chapter chapter : book.getChapters()) {
                preparedStatement.setString(1, book.getIsbn());
                preparedStatement.setInt(2, chapter.getChapterNumber());
                preparedStatement.setString(3, chapter.getTitle());
                preparedStatement.executeUpdate();
            }
            preparedStatement.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Book retrieveObjectGraph(String isbn) {
        Book book = null;
        // fetch book detail from DB
        try {
            Connection connection = DataBaseConnection.getDBConnection();
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM BOOK, PUBLISHER WHERE BOOK.PUBLISHER_CODE=PUBLISHER.CODE AND BOOK.ISBN=?");
            preparedStatement.setString(1, isbn);
            ResultSet resultSet = preparedStatement.executeQuery();
            book = new Book();
            if (resultSet.next()) {
                book.setIsbn(resultSet.getString("ISBN"));
                book.setName(resultSet.getString("BOOK_NAME"));

                // setting publisher of Book
                Publisher publisher = new Publisher();
                publisher.setName(resultSet.getString("PUBLISHER_NAME"));
                publisher.setCode(resultSet.getString("CODE"));
                book.setPublisher(publisher);
            }
            // close resultset
            resultSet.close();
            preparedStatement.close();

            preparedStatement = connection.prepareStatement("SELECT * FROM CHAPTER WHERE CHAPTER.BOOK_ISBN = ?");
            preparedStatement.setString(1, isbn);
            resultSet = preparedStatement.executeQuery();
            List<Chapter> chapters = new LinkedList<>();
            while (resultSet.next()) {
                Chapter chapter = new Chapter();
                chapter.setChapterNumber(resultSet.getInt("CHAPTER_NUM"));
                chapter.setTitle(resultSet.getString("TITLE"));
                chapters.add(chapter);
            }
            //close result set
            resultSet.close();
            //close preparedStatement
            preparedStatement.close();
            book.setChapters(chapters);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return book;
    }
}

Book Application client

import com.walking.techie.model.Book;
import com.walking.techie.model.Chapter;
import com.walking.techie.model.Publisher;
import com.walking.techie.service.BookStoreService;

import java.util.LinkedList;
import java.util.List;

public class BookStoreClient {

    public static void main(String[] args) {
        BookStoreService service = new BookStoreService();


        // All Book information
        Publisher publisher = new Publisher("TECH", "Java Publications");
        Chapter chapter1 = new Chapter("Introduction of Hibernate", 1);
        Chapter chapter2 = new Chapter("Introduction of JPA and Hibernate", 2);
        List<Chapter> chapters = new LinkedList<>();
        chapters.add(chapter1);
        chapters.add(chapter2);
        Book book = new Book("123456789", "Java Persistence with Hibernate", publisher, chapters);
        // save book object graph in DB
        service.persistObjectGraph(book);

        // retrieve Book object graph from DB based on ISBN
        book = service.retrieveObjectGraph("123456789");
        // print book information on console
        System.out.println(book);
    }
}

Once you run the client Book object graph will save into DB and fetch the saved object graph using ISBN of book. Output will print on console like below.

Book{isbn='123456789', name='Java Persistence with Hibernate', publisher=Publisher{code='TECH', name='Java Publications'}, chapters=[Chapter{title='Introduction of Hibernate', chapterNumber=1}, Chapter{title='Introduction of JPA and Hibernate', chapterNumber=2}]}

Problem with using JDBC Directly

In this post we have discussed the object relational mapping using JBDC. We are facing problem when we directly using JDBC to persist or retrieve Java objects because of mismatch between object model and relational model.

In bookstore application, BOOK_ISBN column of CHAPTER table is foreign key reference to ISBN column of BOOK table but there is nothing referencing the Book class or from the Chapter class. In contrast, We have one to many association of book object having a list of chapter object.

This is a case of object-relational mismatch which focuses on the association between two classes or tables and their corresponding model. Now to handle the incompatibilities of these models we need to do some conversion or translation then we can retrieve and persist our object model. This is what we called object-relational mapping.

You can find the source code of the above bookstore application from GitHub