Object Relational Mapping

Lesson

A relational database requires data to be stored as rows in tables. How can we map objects in our code to this kind of structure?

The Object Relational Mapping (ORM) pattern abstracts away the database layer. It allows us to focus on creating / manipulating instances of classes such as Restaurant and delegate the work of mapping them to database fields to the ORM layer. In future lessons we'll look at how a commercial ORM framework makes this easy but let's start by trying to write this logic ourselves so we really understand what happens "under the hood".

Writing our own ORM requires us to implement methods to:

persist an instance to the database save()

locate matching rows and turn them into objects findAll() / findById

update a row in the database from the content of an object update()

delete one or more rows in the database deleteAll() / deleteById()

Let's start with a test - note that it is best practice for a test to avoid any network connections hence the use of an in-memory database. We use the setup / tear down specific test methods to initialise and close our database.

const { Restaurant } = require('../src/Restaurant')
const { Database } = require('sqlite3')
const db = new Database(':memory:');

describe('Restaurants', () => {
    beforeAll((done) => {

        db.run('CREATE TABLE IF NOT EXISTS restaurants(id INTEGER PRIMARY KEY, name TEXT, image TEXT);', ()=> {
            done();
        })

        // pass in the database that should be used
        Restaurant.setDatabase(db);

    })

    afterAll((done) => {
        db.close();
    })

    test('A Restaurant can be persisted in the database', async(done) => {
        // given
        const restaurant = new Restaurant('Boo Jangles', 'https://some.image.url')

        //when
        await restaurant.save();

        // then
        expect(restaurant.id).toBeGreaterThan(0)
        done()
    })

    test('A Restaurant can found in the database', async(done) => {
        // given
        const restaurant = new Restaurant('Boo Jangles', 'https://some.image.url')
        const savedRestaurant = await restaurant.save();

        // when
        let foundRestaurant = await Restaurant.findById(savedRestaurant.id);

        // then
        expect(foundRestaurant.id).toBeGreaterThan(0)
        done()
    })

    test('Multiple Restaurants can found in the database', async(done) => {
        // given
        await Restaurant.deleteAll();

        let restaurant = new Restaurant('Boo Jangles', 'https://some.image.url')
        await restaurant.save();
        restaurant = new Restaurant('Nandos', 'https://some.otherimage.url')
        await restaurant.save();

        // when
        let foundRestaurants = await Restaurant.findAll();

        // then
        expect(foundRestaurants.length).toBe(2)
        done()
    })

    // TODO - add more tests
})

public class RestaurantTest {

    private static Jdbi jdbi = Jdbi.create("jdbc:sqlite:restaurants.sqlite");

    @BeforeClass
    public static void setup() {
        jdbi.useHandle(handle -> {
            handle.execute("CREATE TABLE IF NOT EXISTS RESTAURANTS (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, imagelink TEXT)");
        });
    }

    @Test
    public void save() {
        // given
        Restaurant restaurant = new Restaurant("Boo Jangles", "https://some.image.url");

        // when
        Restaurant savedRestaurant = restaurant.save();

        // then
        assertTrue(savedRestaurant.getId() > 0);
    }

    @Test
    public void findById() {
        // given
        Restaurant restaurant = new Restaurant("Boo Jangles", "https://some.image.url");
        Restaurant savedRestaurant = restaurant.save();

        // when
        Restaurant foundRestaurant = Restaurant.findById(savedRestaurant.getId());

        // then
        assertTrue(foundRestaurant.getId() > 0);
    }

    @Test
    public void findAll() {
        // given
        Restaurant.deleteAll();

        Restaurant restaurant = new Restaurant("Boo Jangles", "https://some.image.url");
        Restaurant savedRestaurant = restaurant.save();
        restaurant = new Restaurant("Nandos", "https://some.otherimage.url");
        savedRestaurant = restaurant.save();

        // when
        List<Restaurant> foundRestaurants = Restaurant.findAll();

        // then
        assertTrue(foundRestaurants.size() == 2);

    }
}

The save() method is shown as an example below:

const { Database } = require('sqlite3')

class Restaurant {
    id;
    name;
    image;
    static db;

    constructor(name, image) {
        this.name = name;
        this.image = image;
    }

    /**
     * Set the database to use
     */
    static setDatabase(db) {
        Restaurant.db = db;
    }

    save() {

        const self = this; // required as 'this' is not visible within the run function

        try {
            return new Promise(function (resolve) {
                Restaurant.db.run(`INSERT INTO RESTAURANTS (name, image) VALUES (?, ?)`, [self.name, self.image], function (err) {
                    if (err) {
                        console.log(err.message);
                        throw err;
                    }
                    self.id = this.lastID; // set the object id to the inserted row id

                    resolve(self);
                })
            });
        } catch (err) {
            console.log(err)
        }
    }

public class Restaurant {

    private static Jdbi jdbi = Jdbi.create("jdbc:sqlite:restaurants.sqlite");

    private long id;
    private String name;
    private String imageLink;

    public Restaurant() {
    }

    public Restaurant(String name, String imageLink) {
        this.name = name;
        this.imageLink = imageLink;
    }

    public Restaurant save() {

        // using withHandle will auto close the db connection
        Long id = jdbi.withHandle(handle -> {

            return handle.createUpdate("INSERT INTO RESTAURANTS (name, imagelink) VALUES (?, ?)")
                    .bind(this.name, this.imageLink)
                    .executeAndReturnGeneratedKeys("id")
                    .mapTo(Long.class).findOnly();
        });

        this.id = id;

        return this;
    }

Assignment

Create code to represent the 3 classes below. There is no need at this stage to worry about relationships between classes.

UML Class Diagram showing Restaurant, Menu and MenuItem classes and attributes

Write a save() method to persist an instance of each class to the database.

Write a static findAll() method to select all rows from a table in the database and construct object instances to represent these rows and a static findById() method to select a specific row from a table in the database and construct an object instance to represent this row.

Write a static deleteAll() method to delete all rows in a specific table and a static deleteById() to delete a specific row

Write a static updateById() method to update the content of a row in the database

Run the unit test above to prove your code works as expected. Add extra test cases to ensure complete code coverage.

Commit your code into Github and share the link with your coach for review.

Assignment extension tasks

Object Relational Mapping - part 1

Learning Objectives

Pre-requisites

Lesson

Assignment

Assignment extension tasks

Additional resources