Chapter 14 SQLite Databases
Defining a Schema
Before you create a database, you have to decide what will be in that database. CriminalIntent stores a
single list of crimes, so you will define one table named crimes (Figure 14.1).
Figure 14.1 The crimes table
People do this kind of thing in a lot of different ways in the programming world. They are all trying
to achieve the same thing: to DRY up their code. DRY means “Don’t Repeat Yourself” and refers to a
rule of thumb when writing a program: If you write something down, write it down in one authoritative
place. That way, instead of repeating yourself all over the place, you are always referring to the one
authoritative place for that information.
Doing this with databases can be involved. There are even complex tools called object-relational
mappers (ORMs) that let you use your model objects (like Crime) as your One True Definition. In this
chapter, you will take the simpler route of defining a simplified database schema in Java code that says
what your table is named and what its columns are.
Start by creating a class to put your schema in. You will call this class CrimeDbSchema, but in the
Create New Class dialog, enter database.CrimeDbSchema. This will put the CrimeDbSchema.java file
in its own database package, which you will use to organize all your database-related code.
Inside CrimeDbSchema, define an inner class called CrimeTable to describe your table.
Listing 14.1 Defining CrimeTable (CrimeDbSchema.java)
public class CrimeDbSchema {
public static final class CrimeTable {
public static final String NAME = "crimes";
}
}
The CrimeTable class only exists to define the String constants needed to describe the moving pieces
of your table definition. The first piece of that definition is the name of the table in your database,
CrimeTable.NAME.
Next, describe the columns.
Listing 14.2 Defining your table columns (CrimeDbSchema.java)
public class CrimeDbSchema {
public static final class CrimeTable {
public static final String NAME = "crimes";
public static final class Cols {
public static final String UUID = "uuid";
public static final String TITLE = "title";
public static final String DATE = "date";
public static final String SOLVED = "solved";
}
}
}