For the purpose of this lesson, you'll need to create a local MySQL database that you can connect to and run queries against.
Create a Test Database with MySQL Workbench
The simplest way to do this is to open MySQL Workbench, paste the following into a new query tab, and execute it by pressing the lightning bolt icon. You can also download the dump file here.
CREATE DATABASE IF NOT EXISTS `college`;
USE `college`;
--
-- Table structure for table `courses`
--
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`units` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `courses`
--
LOCK TABLES `courses` WRITE;
INSERT INTO `courses` VALUES (1,'computer science',3),(2,'literature',3),(3,'design',3);
UNLOCK TABLES;
--
-- Table structure for table `students`
--
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `students`
--
LOCK TABLES `students` WRITE;
INSERT INTO `students` VALUES (1,'example1','user1','[email protected]'),
(2,'example2','user2','[email protected]'),
(3,'example3','user3','[email protected]'),
(4,'example4','user4','[email protected]');
UNLOCK TABLES;
--
-- Table structure for table `students_courses`
--
DROP TABLE IF EXISTS `students_courses`;
CREATE TABLE `students_courses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `student_id_idx` (`student_id`),
KEY `course_id_idx` (`course_id`),
CONSTRAINT `course_id` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `student_id` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `students_courses`
--
LOCK TABLES `students_courses` WRITE;
INSERT INTO `students_courses` VALUES (1,1,1),(2,1,3),(3,2,3),(4,3,1),(5,3,2),(6,3,3),(7,4,1),(8,2,3);
UNLOCK TABLES;
By running this SQL script in MySQL Workbench, it will create a new schema named college and three new tables named courses, students, and students_courses, as well as a few rows of example data for each table.
Note that you have set up the primary keys for each table, as well as the foreign key constraints on the students_courses table, which has the foreign key student_id linked to the students table and the foreign key course_id linked to the courses table.
Take a moment to explore this database and all three tables that you have now created. Run a few queries, for example:
SELECT * FROM college.courses
And every other table. You can also experiment with this JOIN query:
SELECT s.first_name, s.last_name, c.name, c.units
FROM students s
JOIN students_courses sc
ON s.id = sc.student_id
JOIN courses c
ON sc.course_id = c.id
And this one:
SELECT c.name, s.email
FROM courses c
JOIN students_courses sc
ON c.id = sc.course_id
JOIN students s
ON sc.student_id = s.id
order by name
Now that you've got a test database in play with some tables and data, it's time to move ahead and connect to this DB from Java and start running some queries!
Summary: How to Connect a Java Test Database
- This lesson provides code to create a sample database using MySQL Workbench and a SQL script - please create this database on your local machine
- There are several sample queries provided to play with the database, including
SELECTqueryJOINquery