jOOQ指南中使用的数据库

jOOQ指南中使用的数据库

本指南中使用的数据库将在本节中进行总结和创建

使用Oracle方言来创建

# 创建语言
CREATE TABLE language (
 id NUMBER(7) NOT NULL PRIMARY KEY,
 cd CHAR(2) NOT NULL,
 description VARCHAR2(50)
);

# 创建作者
CREATE TABLE author (
 id NUMBER(7) NOT NULL PRIMARY KEY,
 first_name VARCHAR2(50),
 last_name VARCHAR2(50) NOT NULL,
 date_of_birth DATE,
 year_of_birth NUMBER(7),
 distinguished NUMBER(1)
);

# 创建书籍
CREATE TABLE book (
 id NUMBER(7) NOT NULL PRIMARY KEY,
 author_id NUMBER(7) NOT NULL,
 title VARCHAR2(400) NOT NULL,
 published_in NUMBER(7) NOT NULL,
 language_id NUMBER(7) NOT NULL,
 
 CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author(id),
 CONSTRAINT fk_book_language FOREIGN KEY (language_id) REFERENCES language(id)
);

# 创建书店
CREATE TABLE book_store (
 name VARCHAR2(400) NOT NULL UNIQUE
);

# 创建书店的书
CREATE TABLE book_to_book_store (
 name VARCHAR2(400) NOT NULL,
 book_id INTEGER NOT NULL,
 stock INTEGER,
 
 PRIMARY KEY(name, book_id),
 CONSTRAINT fk_b2bs_book_store FOREIGN KEY (name) REFERENCES book_store (name) ON DELETE CASCADE,
 CONSTRAINT fk_b2bs_book FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE CASCADE
);

更多的实体、类型(例如UDT、ARRAY类型、ENUM类型等)、存储过程和包被引入到特定的示例中
除上述数据外，您还可以假设以下示例数据：

INSERT INTO language (id, cd, description) VALUES (1, 'en', 'English');
INSERT INTO language (id, cd, description) VALUES (2, 'de', 'Deutsch');
INSERT INTO language (id, cd, description) VALUES (3, 'fr', 'Fran?ais');
INSERT INTO language (id, cd, description) VALUES (4, 'pt', 'Português');
INSERT INTO author (id, first_name, last_name, date_of_birth , year_of_birth)
 VALUES (1 , 'George' , 'Orwell' , DATE '1903-06-26', 1903 );
INSERT INTO author (id, first_name, last_name, date_of_birth , year_of_birth)
 VALUES (2 , 'Paulo' , 'Coelho' , DATE '1947-08-24', 1947 );
INSERT INTO book (id, author_id, title , published_in, language_id)
 VALUES (1 , 1 , '1984' , 1948 , 1 );
INSERT INTO book (id, author_id, title , published_in, language_id)
 VALUES (2 , 1 , 'Animal Farm' , 1945 , 1 );
INSERT INTO book (id, author_id, title , published_in, language_id)
 VALUES (3 , 2 , 'O Alquimista', 1988 , 4 );
INSERT INTO book (id, author_id, title , published_in, language_id)
 VALUES (4 , 2 , 'Brida' , 1990 , 2 );
INSERT INTO book_store VALUES ('Orell Füssli');
INSERT INTO book_store VALUES ('Ex Libris');
INSERT INTO book_store VALUES ('Buchhandlung im Volkshaus');
INSERT INTO book_to_book_store VALUES ('Orell Füssli' , 1, 10);
INSERT INTO book_to_book_store VALUES ('Orell Füssli' , 2, 10);
INSERT INTO book_to_book_store VALUES ('Orell Füssli' , 3, 10);
INSERT INTO book_to_book_store VALUES ('Ex Libris' , 1, 1 );
INSERT INTO book_to_book_store VALUES ('Ex Libris' , 3, 2 );
INSERT INTO book_to_book_store VALUES ('Buchhandlung im Volkshaus', 3, 1 );

如下为jOOQ的学习大纲的思维导图：https://www.drawon.cn/template/details/656ac5efc083fb5228fc17f8