How to Create a Database and its Structure

Learn how to create a database in Java in this tutorial by Nick Samoylov, the author of Introduction to Programming.

A database server is a software system that maintains and manages the database. For the purposes of this tutorial, you can use PostgreSQL, a free, open source database server.

After the database server is installed, you can use its console for creating a database and its user with the corresponding permissions. There are many ways to structure the data store and the system of users with different levels of access.

CREATE and DROP the database and its user

Read the database instructions and create a java user and a javaintro database (or select any other name you prefer and use them instead in the provided code examples). Here is how to do it for PostgreSQL:

CREATE USER java SUPERUSER;
CREATE DATABASE javaintro OWNER java;

If you make a mistake and decide to start anew, you can delete the created user and database using the following statements:

DROP USER java;
DROP DATABASE javaintro;

You selected the SUPERUSER role for your user, but a good security practice recommends assigning such a powerful role to an administrator only. For an application, it is recommended that you create a user who cannot create or change the database itself—its tables and constraints—but can only manage data.

Also, it is a good practice to create another logical layer, called schema, which can have its own set of users and permissions. This way, several schemas in the same database could be isolated and each user (one of them is your application) can access only certain schemas. On an enterprise level, the common practice is to create synonyms for the database schema so that no application can access the original structure directly.

Now you can connect your application to the database.

CREATE, ALTER, and DROP table

The standard SQL statement for table creation looks like this:

CREATE TABLE tablename (
  column1 type1,
  column2 type2,
  column3 type3,
  ....
);

The limitations for a table name, column names, and types of values that can be used depend on the particular database. Here is an example of a command that creates table person in PostgreSQL:

CREATE TABLE person (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR NOT NULL,
  last_name VARCHAR NOT NULL,
  dob DATE NOT NULL
);

As you can see, you’ve made the dob (date of birth) column not nullable. This imposes a constraint on the Person Java class, which will represent the records of this table: its dob field cannot be null. Here’s how to create the Person class:

class Person {
  private String firstName, lastName;
  private LocalDate dob;
  public Person(String firstName, String lastName, LocalDate dob) {
    this.firstName = firstName == null ? "" : firstName;
    this.lastName = lastName == null ? "" : lastName;
    if(dob == null){
      throw new RuntimeException("Date of birth is null");
    }
    this.dob = dob;
  }
  public String getFirstName() { return firstName; }
  public String getLastName() { return lastName; }
  public LocalDate getDob() { return dob; }
}

You haven’t set the size of the columns of the VARCHAR type, thereby allowing those columns to store values of any length, while the integer type allows them to store numbers from 4,713 BC to 5,874,897 AD. NOT NULL was added because the column would be nullable by default, while it’s better to make sure that all the columns are populated for each record. Moreover, the Person class supports it by setting first and last names to empty String values if they are null, as the parameters of the Person constructor.

You also identified the id column as PRIMARY KEY, which indicates that this column uniquely identifies the record. The SERIAL keyword means that you’re asking the database to generate the next integer value every time a new record is added, so each record will have a unique integer number. Alternatively, you can make PRIMARY KEY from the combination of first_name, last_name, and dob:

CREATE TABLE person (
  first_name VARCHAR NOT NULL,
  last_name VARCHAR NOT NULL,
  dob DATE NOT NULL,
  PRIMARY KEY (first_name, last_name, dob)
);

But there is a chance that there can be two people who have the same name and who were born on the same day. So, it’s advisable to add another field and constructor to the Person class:

public class Person {
  private String firstName, lastName;
  private LocalDate dob;
  private int id;
  public Person(int id, String firstName, 
                                  String lastName, LocalDate dob) {
    this(firstName, lastName, dob);
    this.id = id;
  }   
  public Person(String firstName, String lastName, LocalDate dob) {
    this.firstName = firstName == null ? "" : firstName;
    this.lastName = lastName == null ? "" : lastName;
    if(dob == null){
      throw new RuntimeException("Date of birth is null");
    }
    this.dob = dob;
  }
  public String getFirstName() { return firstName; }
  public String getLastName() { return lastName; }
  public LocalDate getDob() { return dob; }
}

Use the constructor that accepts id for constructing an object based on the record in a database, while the other constructor can be used to create an object before inserting a new record.

Run the preceding SQL statement in a database console and create this table:

If necessary, the table can be deleted by the DROP command:

DROP table person;

The existing table can be changed using the ALTER command. For example, you can add an address column:

ALTER table person add column address VARCHAR;

If you are not sure whether such a column exists already, you can add IF EXISTS or IF NOT EXISTS:

ALTER table person add column IF NOT EXISTS address VARCHAR;

However this possibility only exists PostgreSQL 9.6 onwards.

Another important consideration of the database table creation is whether an index has to be added. An index is a data structure that helps accelerate the data search in the table without having to check every table record. An index can include one or more columns of a table. For example, the index for a primary key is created automatically. If you bring up the description of the table that you’ve created already, you will see the following:

You can also add any index if you think (and have proven it through experimentation) it will help the application's performance. For example, you can allow case-insensitive search by first and last name by adding the following index:

CREATE INDEX idx_names ON person ((lower(first_name), lower(last_name));

If the search speed improves, leave the index in place. If not, it can be removed:

drop index idx_names;

You can remove it since an index can have an overhead of additional writes and storage space.

You can also remove a column from a table:

ALTER table person DROP column address;

That’s it! If you found this tutorial interesting and would like to learn more about Java programming, you can explore Introduction to Programming. Filled with practical examples that will help you understand the nuances of object-oriented programming, the book is for anyone who wants to learn programming, and all you’ll need is a computer, an internet connection, and a cup of coffee!