Create Database

UML

The examples in this tutorial require the following four entity types:

• BookStore, Book, Author: Demonstrate various associations including one-to-many, many-to-one, many-to-many.

• TreeNode: Demonstrates infinite recursion (self-associated properites), include recursive queries and recursive saving.

Create SQL File

This tutorial uses MySQL. Create a new SQL file jimmer-demo.sql with the following code:

create database jimmer_demo;
use jimmer_demo;

create table book_store(
    id bigint unsigned not null auto_increment primary key, 
    name varchar(50) not null,
    website varchar(100)
) engine=innodb;

... 

create table book (
    id bigint unsigned not null auto_increment primary key,
    name varchar(50) not null,  
    edition integer not null,
    price numeric(10, 2) not null,
    store_id bigint unsigned  
) engine=innodb;   

...

create table author(
   id bigint unsigned not null auto_increment primary key,
   first_name varchar(25) not null,
   last_name varchar(25) not null,
   gender char(1) not null, 
   created_time datetime not null,
   modified_time datetime not null
) engine=innodb;

...

/*
Many-to-many relationship between entities requires join table in ORM implementation.

This table stores many-to-many mapping between books and authors. It is a join table, 
not an entity table, so no corresponding entity in the UML diagram above.  
*/
create table book_author_mapping(
    book_id bigint unsigned not null,
    author_id bigint unsigned not null
) engine=innodb;

...

create table tree_node(
    node_id bigint unsigned not null auto_increment primary key,
    name varchar(20) not null,
    parent_id bigint unsigned  
) engine=innodb;

...

insert into book_store(id, name) values
    (1, 'O''REILLY'),
    (2, 'MANNING')
;

insert into book(id, name, edition, price, store_id) values
    (1, 'Learning GraphQL', 1, 50, 1),
    (2, 'Learning GraphQL', 2, 55, 1),
    (3, 'Learning GraphQL', 3, 51, 1),

    (4, 'Effective TypeScript', 1, 73, 1),
    (5, 'Effective TypeScript', 2, 69, 1),
    (6, 'Effective TypeScript', 3, 88, 1),

    (7, 'Programming TypeScript', 1, 47.5, 1),
    (8, 'Programming TypeScript', 2, 45, 1),
    (9, 'Programming TypeScript', 3, 48, 1),

    (10, 'GraphQL in Action', 1, 80, 2),
    (11, 'GraphQL in Action', 2, 81, 2),
    (12, 'GraphQL in Action', 3, 80, 2)
;

insert into author(id, first_name, last_name, gender, created_time, modified_time) values
    (1, 'Eve', 'Procello', 'F', now(), now()),
    (2, 'Alex', 'Banks', 'M', now(), now()),
    (3, 'Dan', 'Vanderkam', 'M', now(), now()),
    (4, 'Boris', 'Cherny', 'M', now(), now()),
    (5, 'Samer', 'Buna', 'M', now(), now())
;

insert into book_author_mapping(book_id, author_id) values
    (1, 1),
    (2, 1),
    (3, 1),

    (1, 2),
    (2, 2),
    (3, 2),

    (4, 3),
    (5, 3),
    (6, 3),

    (7, 4),
    (8, 4),
    (9, 4),

    (10, 5),
    (11, 5),
    (12, 5)
;

insert into tree_node(
    node_id, name, parent_id
) values
    (1, 'Home', null),
        (2, 'Food', 1),
            (3, 'Drinks', 2),
                (4, 'Coca Cola', 3),
                (5, 'Fanta', 3),
            (6, 'Bread', 2),
                (7, 'Baguette', 6),
                (8, 'Ciabatta', 6),
        (9, 'Clothing', 1),
            (10, 'Woman', 9),
                (11, 'Casual wear', 10),
                    (12, 'Dress', 11),
                    (13, 'Miniskirt', 11),
                    (14, 'Jeans', 11),
                (15, 'Formal wear', 10),
                    (16, 'Suit', 15),
                    (17, 'Shirt', 15),
            (18, 'Man', 9),
                (19, 'Casual wear', 18),
                    (20, 'Jacket', 19),
                    (21, 'Jeans', 19),
                (22, 'Formal wear', 18),
                    (23, 'Suit', 22),
                    (24, 'Shirt', 22)
;

This SQL file shows:

• Entity tables - book_store, book, author, tree_node map to entities in UML
• Join table - book_author_mapping represents many-to-many mapping between Book and Author

note

This is a basic database structure. More fields will be added in later tutorials.

See https://github.com/babyfish-ct/jimmer-examples/blob/main/env-with-cache/maxwell/jimmer-demo.sql for full SQL.

Create Database

Install Docker. Open a command line, navigate to the SQL file directory, and run:

docker run \ 
    --name jimmer-demo-mysql \
    ...
    mysql \
    --lower_case_table_names=1
    
docker cp jimmer-demo.sql jimmer-demo-mysql:/var/jimmer-demo.sql

docker exec jimmer-demo-mysql /bin/sh -c \
        'mysql -uroot -p123456 </var/jimmer-demo.sql'