How to create a database schema in MySQL
If you plan to do database development with Java and MySQL, the first thing you’ll need to do is configure a database schema.
Technically speaking, the first thing to do before you configure a schema is actually install MySQL and the MySQL Workbench. But once those prerequisites are in place, the first database operation you’ll need to perform is schema creation.
Why is a database schema required?
In enterprise relational databases, schemas act as a top-level organizations structure. A schema typically maps to a single problem domain such as shipping or billing or user preferences. For cloud-native developers who follow the 12-Factor App guidelines, one schema typically maps to a single microservice.
Furthermore, most JDBC drivers require a schema name to create a JDBC connection to the database.
Schemas and the JDBC URL
The schema is part of the connection URL and the JDBC driver requires it at runtime to figure out which database tables are available for manipulation.
Without a database schema, your data-driven apps go nowhere.
Steps to create a schema in MySQL
Fortunately, it’s not hard to create a database schema in MySQL, especially if you have the MySQL Workbench tool at your disposal. Just follow these steps:
- Open the MySQL Workbench
- Click the Create Schema option
- Provide a schema name
- Click apply to create the MySQL scheme
The MySQL workbench executes the required SQL statement under the covers, and the schema is listed in the tool.
With the schema created, you can now provide any JDBC drivers, Hibernate config or JPA persistence.xml file the name of the database URL with the schema name attached. That allows your Java programs to connect to the database.
Once you’ve created the schema, you can then create database tables, add columns and rows, insert data and perform CRUD operations.
Your ability to manipulate relational data is only limited your knowledge of JPA, Hibernate and the JDBC APIs.