Java 17 Recipes

Table 12-1. Recipes
Field Name
Length
Type
Constraints
id
-
int
primary key
not null
recipe_number
10
VarChar
not null
recipe_name
100
VarChar
not null
description
500
VarChar
Default null
Table 12-2. Publications
Field Name
Length
Type
Constraints
id
-
int
primary key
not null
book_title
500
VarChar
not null
publish_date
-
Date
Default null
publish_co
100
VarChar
Default null
Listing 
12-5
 shows the SQL script for the creation of the apressBooks database.
Chapter 12 Working With Databases

---

428
Listing 12-5. ApressBookDatabase_create.sql
01 DROP DATABASE IF EXISTS `apressBooks`;
02 CREATE DATABASE `apressBooks`;
03 CREATE TABLE `apressBooks`.`recipes` (
04 `id` int NOT NULL AUTO_INCREMENT,
05 `recipe_number` varchar(10) NOT NULL,
06 `recipe_name` varchar(100) NOT NULL,
07 `description` varchar(500) DEFAULT NULL,
08 PRIMARY KEY (`id`)
09 ) ;
10 insert into apressBooks.recipes values(1,
11 '12-1',
12 'Installing MySQL',
13 'Downloading and installation of a MySQL Database');
14 insert into apressBooks.recipes values(2,
15 '12-2',
16 'Connecting to a Database',
17 'DriverManager and DataSource Implementations');
18 insert into apressBooks.recipes values(3,
19 '12-3',
20 'Handling SQL Exceptions',
21 'Using SQLException');
22 insert into apressBooks.recipes values(4,
23 '12-4',
24 'Querying a Database and Retrieving Results',
25 'Obtaining and using data from a DBMS');
26 CREATE TABLE `apressBooks`.`publication` (
27 `id` int NOT NULL AUTO_INCREMENT,
28 `book_title` varchar(500) NOT NULL,
29 `publish_date` date DEFAULT NULL,
30 `publish_co` varchar(100) DEFAULT NULL,
31 PRIMARY KEY (`id`)
32 );
33 insert into apressBooks.publication values (
34 1,
Chapter 12 Working With Databases

---

429
35 'Java EE 17 Recipes',
36 date('2021-12-01'),
37 'APRESS');
38 insert into apressBooks.publication values (
39 2,
40 ‘Beginning Jakarta EE Web Development’,
41 date('2020-04-03'),
42 'APRESS');
Line 01 removes the database. The IF EXISTS option allows you to delete it only if it 
already exists. This option prevents the reported error when you use the creation script 
the first time if the database does not exist. The DROP statement deletes the database 
and the physical disk files, so you should have a backup of the database if you want to 
restore it in the future.
Line 02 creates a blank database named apressBooks.
Lines 03 to 09 create a table to store recipes named recipes.
Lines 10 to 25 populate the recipes table.
Lines 26 to 32 create a table named publication.
Lines 33 to 42 populate the publication table.
To execute the SQL script, you can use the command-line client you have just seen. 
You find the script in the software package for this chapter. Open the command-line 
client. Open ApressBookDatabase_create.sql with a text editor, copy everything and 
paste it onto the command-line client to create and populate the tables.