The Structured Query Language in Java Database Programming

JDBC lets you communicate with databases using SQL, which is the command language for essentially all modern relational databases. Desktop databases usually have a GUI that lets users manipulate the data directly, but server-based databases are accessed purely through SQL.

The JDBC package can be thought of as nothing more than an API for com­municating SQL statements to databases. We will briefly introduce SQL in this section. If you have never seen SQL before, you might not find this ma­terial sufficient. If so, turn to one of the many learning resources on the topic; we recommend Learning SQL by Alan Beaulieu (O’Reilly, 2009) or the online book Learn SQL The Hard Way at http://sqt.tearncodethehardway.org.

You can think of a database as a bunch of named tables with rows and columns. Each column has a column name. Each row contains a set of related data.

As an example database for this book, we use a set of database tables that describe a collection of classic computer science books (see Tables 5.1 through 5.4).

Figure 5.3 shows a view of the Books table. Figure 5.4 shows the result of joining this table with the Publishers table. The Books and the Publishers tables each contain an identifier for the publisher. When we join both tables on the publisher code, we obtain a query result made up of values from the joined tables. Each row in the result contains the information about a book, together with the publisher name and web page URL. Note that the publisher names and URLs are duplicated across several rows because we have several books with the same publisher.

The benefit of joining tables is avoiding unnecessary duplication of data in the database tables. For example, a naive database design might have had columns for the publisher name and URL right in the Books table. But then the database itself, and not just the query result, would have many duplicates of these entries. If a publisher’s web address changed, all entries would need to be updated. Clearly, this is somewhat error-prone. In the relational model, we distribute data into multiple tables so that no information is unnecessarily duplicated. For example, each publisher’s URL is contained only once in the publisher table. If the information needs to be combined, the tables are joined.

In the figures, you can see a graphical tool to inspect and link the tables. Many vendors have tools to express queries in a simple form by connecting column names and filling information into forms. Such tools are often called query by example (QBE) tools. In contrast, a query that uses SQL is written out in text, using SQL syntax, for example:

SELECT Books.Title, Books.Publisher_Id, Books.Price, Publishers.Name, Publishers.URL

FROM Books, Publishers

WHERE Books.Publisher_Id = Publishers.Publisher_Id

In the remainder of this section, you will learn how to write such queries. If you are already familiar with SQL, just skip this section.

By convention, SQL keywords are written in capital letters, although this is not necessary.

The SELECT statement is quite flexible. You can simply select all rows in the Books table with the following query:

SELECT * FROM Books

The FROM clause is required in every SQL SELECT statement. It tells the database which tables to examine to find the data.

You can choose the columns that you want:

SELECT ISBN, Price, Title

FROM Books

You can restrict the rows in the answer with the WHERE clause:

SELECT ISBN, Price, Title

FROM Books

WHERE Price <= 29.95

Be careful with the “equals” comparison. SQL uses = and <>, rather than == or != as in the Java programming language, for equality testing.

The WHERE clause can also use pattern matching by means of the LIKE operator. The wildcard characters are not the usual * and ?, however. Use a % for zero or more characters and an underscore for a single character. For example,

SELECT ISBN, Price, Title

FROM Books

WHERE Title NOT LIKE ‘%n_x%’

excludes books with titles that contain words such as Unix or Linux.

Note that strings are enclosed in single quotes, not double quotes. A single quote inside a string is represented by a pair of single quotes. For example,

SELECT Title

FROM Books

WHERE Title LIKE ‘%”%’

reports all titles that contain a single quote.

You can select data from multiple tables:

SELECT * FROM Books, Publishers

Without a WHERE clause, this query is not very interesting. It lists all combinations of rows from both tables. In our case, where Books has 20 rows and Publishers has 8 rows, the result is a set of rows with 20 x 8 entries and lots of duplica­tions. We really want to constrain the query to say that we are only interested in matching books with their publishers:

SELECT * FROM Books, Publishers

WHERE Books.Publisher Id = Publishers.Publisher Id

This query result has 20 rows, one for each book, because each book has one publisher in the Publisher table.

Whenever you have multiple tables in a query, the same column name can occur in two different places. That happened in our example. There is a col­umn called Publisher_Id in both the Books and the Publishers tables. When an am­biguity would otherwise result, you must prefix each column name with the name of the table to which it belongs, such as Books.Publisher_Id.

You can use SQL to change the data inside a database as well. For example, suppose you want to reduce by $5.00 the current price of all books that have “C++” in their title:

UPDATE Books

SET Price = Price – 5.00

WHERE Title LIKE ‘%C++%’

Similarly, to delete all C++ books, use a DELETE query:

DELETE FROM Books

WHERE Title LIKE ‘%C++%’

SQL comes with built-in functions for taking averages, finding maximums and minimums in a column, and so on, which we do not discuss here.

Typically, to insert values into a table, you can use the INSERT statement:

INSERT INTO Books

VALUES (‘A Guide to the SQL Standard’, ‘0-201-96426-0’, ‘0201’, 47.95)

You need a separate INSERT statement for every row being inserted in the table.

Of course, before you can query, modify, and insert data, you must have a place to store data. Use the CREATE TABLE statement to make a new table. Specify the name and data type for each column. For example,

CREATE TABLE Books

(

Title CHAR(60),

ISBN CHAR(13),

Publisher_Id CHAR(6),

Price DECIMAL(10,2)

)

Table 5.5 shows the most common SQL data types.

In this book, we do not discuss the additional clauses, such as keys and constraints, that you can use with the CREATE TABLE statement.

Source: Horstmann Cay S. (2019), Core Java. Volume II – Advanced Features, Pearson; 11th edition.

Leave a Reply

Your email address will not be published. Required fields are marked *