12.1 Basic Concepts

A database, at its simplest, is an application that keeps track of data in a structured manner. By this definition, you could think of a spreadsheet as a type of database?you work with data in rows, columns, and tables. You input data, and then add functions to perform sums and other calculations on the data. In the Java world, however, a database typically refers to a relational database. A relational database also stores data in rows and columns, but locates them within larger data components called tables. Additionally, relational databases allow keys between these tables, building the relationships for which the database type is named.

12.1.1 SQL

When learning about relational databases, you'll hear the term Structured Query Language (SQL), which is the language you will use to access a relational database. SQL works with any relational database and underlies the many GUI tools you will see for working with these types of databases.

To solidify these concepts, look at some examples. Consider a simple database table, as defined in Table 12-1.

Table 12-1. A simple database table













Much like a spreadsheet, the data in the database is stored in columns (ID, First, Last, Email) and rows (in this case, two rows of data). The ID column is commonly used when working with relational databases. It generally provides a unique ID for each row, used by other tables that may need to reference that row.

While an ID column is almost always used in relational databases, it is not required. Additionally, it sometimes exists but has a different name, such as row-id or identifier.

When communicating with a relational database, send SQL commands like:

SELECT ID, First, Last 
  FROM SimpleTable 

Essentially, the first line specifies the columns to select, the second line indicates which database table is desired, and the third line indicates a selection criterion. Using the sample data in Table 12-1 would return the first row of data. Here are several other simple SQL statements:

  FROM SimpleTable
 WHERE Last = 'Smith'

SELECT First, Last
  FROM SimpleTable
 WHERE Email LIKE '%netcom.com'
ORDER BY Last, First

The real strength of relational databases, however, lies in their ability to issue relational queries?in effect "stitching" together the data in two or more tables with a single query:

SELECT s.First, s.Last, p.PhoneNumber
  FROM SimpleTable s, PhoneNumberTable p
 WHERE p.PersonID = s.ID
   AND s.Last = 'Iverson'

For example, suppose you have a customers table and an orders table. You could issue a single query against both tables, asking for the most recent order for each customer:

SELECT c.first, c.last, o.description
  FROM customers c, orders o
 WHERE o.customerID = c.ID
GROUP BY o.customerID
HAVING MAX(o.orderDate)

An in-depth description of SQL is beyond the scope of this book, but it's a powerful, popular way to express data and queries against that data.

Having to learn another programming language (such as SQL) when you start working with Java can be daunting, but learning SQL is easier than writing your own database. SQL has also become an extremely popular language; even if you decide to switch to another programming language in the future, that new language will probably possess a mechanism for interacting with databases via SQL.