Java 17 Recipes

 How It Works
A database consists of organized data; that is, the data itself and a schema that provides 
data structures. Nowadays, most databases are organized in tables consisting of rows and 
columns. This is a natural way of organizing data, and you’re probably familiar with it 
through the use of spreadsheets. You can define the table characteristics independently 
of the actual data you’re going to store into it. A field is an individual data item within 
a table corresponding to the intersection of a row and a column. One or more columns 
can be specified as unique keys to identify each employee. For this purpose, you could 
use either one of the columns mentioned previously or the combination of first name, 
last name, and date of birth. The unique key used in preference over the others is called 
the primary key of a table.
Chapter 12 Working With Databases

---

430
A database management system (DBMS), such as MySQL, is a software package 
that lets you create, read, update, and delete (CRUD) both items of data and elements of 
the schema.
Therefore, when talking about a database, you need to distinguish between three 
aspects.
• The data it contains.
• The structure you impose on the data to CRUD it efficiently.
• The software that allows you to manipulate both the data itself and 
the database structure (the DBMS).
Working with a database means that you’re interacting with its DBMS. You can do 
that through a CLI, graphical user interfaces (GUIs) provided by the DBMS vendor and 
third parties, or programmatically through an API.
The DBMS can build an index for each key to retrieve the data more quickly. This 
slows down insertion and deletion of rows (i.e., new records) because the DBMS have to 
spend time updating the indexes. Most databases are more frequently interrogated than 
modified. Therefore, it usually pays to define indexes, at least those that can speed up the 
most common queries.
Structured Query Language (SQL) is the most widely used language to interact 
with DBMSs. Most DBMSs don’t support the whole SQL standard. Moreover, vendors 
sometimes add nonstandard elements that, in practice, prevent full portability across 
DBMSs. In general, regardless of whether we’re talking about database organization, 
table structure, or actual data, you’ll need to perform four CRUD operations. The 
corresponding SQL statements begin with a keyword that identifies the operation 
(e.g., INSERT, SELECT, UPDATE, or DELETE), followed when necessary by a keyword 
specifying on what type of entity the operation is to be performed (e.g., DATABASE, 
TABLE, or INDEX) and by additional elements. You use the SELECT statement for 
retrieving information.
You can create databases, tables, and indexes with the CREATE statement, update 
them with ALTER and delete them with DROP. Similarly, you can create and delete 
views with CREATE and DROP, but you cannot update them once you’ve created them. 
You use INSERT to create new rows within a table, and you use DELETE to delete them. 
The UPDATE statement lets you modify entire rows or one or more individual fields 
within them.
Chapter 12 Working With Databases

---

431
The statements that let you modify the structures are collectively referred to as 
Data Definition Language (DDL). Those that let you modify the content are called Data 
Manipulation Language (DML).
In many applications, the structure of databases, tables, indexes, and views, once 
initially defined, remains unchanged. Therefore, you’ll often need only the statements 
operating on rows and fields within your applications. In any case, you’ll certainly need 
SELECT, which you use to interrogate databases both in terms of their structure and the 
data they contain. Finally, to complete the list of statements you’re likely to need when 
developing applications, there are START TRANSACTION, COMMIT, and ROLLBACK, 
which you need to use transactions.
When you want to retrieve, update, or delete rows, you must identify them. You do 
this with the WHERE keyword followed by a .
In this chapter’s examples, we used MySQL as the DBMS of choice because, first, it’s 
available for free, and second, it’s the most widely used of the freely available DBMSs. 
As such, it has been proven to work reliably in all sorts of environments. However, in the 
end, you can develop Java applications that work with other traditional RDBMSs, such as 
Oracle Database or PostgreSQL.