---
documentclass: scrreprt
title: CSCI 3410 - Database Systems
subtitle: Lecture Notes (Draft)
author: Clément Aubert
institute: Augusta University
papersize: letter
geometry: "vmargin=2cm"
bibliography: [ bib/bib.bib, bib/entry.bib ]
link-citations: true
lang: en
numbersections: true
mainfont: Linux Libertine O
keywords:
- Computer Science
- Database
- SQL
- Database Design
- Database Application
- NoSQL
header-includes:
- \usepackage{latex/packages}
include-in-header:
- anchors_bis.html
pandoc-numbering:
 problem:
    general:
      listing-title: List of Problems 
    standard:
        format-link-classic: 'Pb %n'
        format-link-title: 'Pb %n'
    latex:
        format-link-classic: 'Pb %n'
        format-link-title: 'Pb %n'
---

\newpage 

## A First Program

We will write and compile a simple java program that manipulates a simple database^[This program ows a lot to the one presented at <http://www.ntu.edu.sg/home/ehchua/programming/java/jdbc_basic.html>.].
Even if the creation and population of the database could have been done from within the program, we will do it as a preliminary step, using the C.L.I., to make our program simpler (and also because it generally match usage: schemas are usually created before the program is executed).

### The Database (`SQL`)

For this program, we will use the following database:

```{.sqlmysql .numberLines .includeLink  include=code/sql/HW_EBookshop.sql}
```

```{.default.numberlines}
MariaDB [HW_EBookshop]> SELECT * FROM BOOKS;
+----+-------------------------+--------------------------------+-------+------+
| ID | title                   | author                         | price | qty  |
+----+-------------------------+--------------------------------+-------+------+
|  1 | The Communist Manifesto | Karl Marx and Friedrich Engels | 11.11 |   11 |
|  2 | Don Quixote             | Miguel de Cervantes            | 22.22 |   22 |
|  3 | A Tale of Two Cities    | Charles Dickens                | 33.33 |   33 |
|  4 | The Lord of the Rings   | J. R. R. Tolkien               | 44.44 |   44 |
|  5 | Le Petit Prince         | Antoine de Saint-Exupéry       | 55.55 |   55 |
+----+-------------------------+--------------------------------+-------+------+
5 rows in set (0.00 sec)
```

You can copy and paste the code, then execute it, or use MySQL's batch mode: you can find the code previously given at `code/sql/HW_EBookshop.sql`, i.e., at <https://rocketgit.com/user/caubert/CSCI_3410/source/tree/branch/master/blob/notes/code/sql/HW_EBookshop.sql>.
Open a terminal (or command-line interpreter), navigate to the folder where you stored that file (using `cd`), and type

```{.bash}
mysql -u testuser -p < HW_EBookshop.sql
```

for linux, or (something like)

```{.bash}
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -u testuser -p < HW_EBookshop.sql
```

for Windows.
Refer to the [_Logging-In as testuser_](#dbms:testuserlogin) section if you forgot how to log-in to your database.

You just discovered MySQL's batch mode, that perform *series* of instructions from a file.
You can easily make sure that the database and the table were indeed created, and the values inserted, by logging the way you used to, and executing the usual commands.

### Executing Database Application

As we are about to see, a database application needs to be written following this order:

#. Load the API,
#. Try to open the connection (i.e., create `Connection` and `Statement` objects), using a try/catch statement, 
#. Perform the required actions on the database (using `Statement` object),
#. Close the connection.

and the program needs to load the driver (which is specific to DBMS) at execution time.


Of course, if the second step failed, then the program needs to exit gracefully, or to provide debugging information to the user.
The program we will obtain can (normally) be compiled, using something like `javac FirstProg.java`{.bash}  (or an equivalent command for windows).
But another refinment is needed when you want to execute it.
We need to set up the *driver* (or *connector*) to make the java `SQL` API and MySQL communicate. To do so, 

- Go to <https://dev.mysql.com/downloads/connector/j/>
- Select "Platform Independent",
- Click on "Download" in front of "Platform Independent (Architecture Independent), ZIP Archive"
- Look for the (somewhat hidden) "No thanks, just start my download."
- Download the file named "mysql-connector-java-***.zip", where `***` is the version number.
- Unzip the file, and locate the "mysql-connector-java-***.jar" file (normally, in the root folder).
- Copy that file in the same folder as where you intend to compile your program.

Once this is done and your program was compiled, you can execute it using (where you replace `***` with the actual number, of course, e.g. `8.0.22`):

```{.bash}
java -cp .:mysql-connector-java-***.jar FirstProg
```

in Linux, or

```{.bash}
java -cp .;mysql-connector-java-***.jar FirstProg
```

in Windows.
The `-cp` option lists the places where java should look for the class used in the program: we are explicitely asking java to use the `mysql-connector-java-***.jar` executable (the driver) to execute our `FirstProg` executable.

If we try to execute `FirstProg` without that flag, we obtain the following error message:

```{.bash}
$ java FirstProg
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/HW_EBOOKSHOP
at java.sql.DriverManager.getConnection(DriverManager.java:689)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at FirstProg.main(FirstProg.java:9)
```

### The Application Program (`java`)


```{.java .numberLines .includeLink  include=code/java/FirstProg.java}
```

A couple of comments:

- `java.sql.*`, whose documentation is at <https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html>, contains the following classes that we will use in this chapter:
    - `DriverManager`, used for managing a set of JDBC drivers,
    - `Connection`, used to make a connection with a database via `DriverManager` objects,
    - `Statement`, used to send basic `SQL` statements via `Connection` objects,
    - `ResultSet`, to retrieve and update the results of a query, returned by a `Statement` object,
    - `ResultSetMetadata`, to get information about a `ResultSet` object,
    - `SQLException`, a class of exceptions relative to `SQL`.

- Intuitively, a `Connection` is a bridge (the physical connection), and `Statement` is a lane (a symbolic, or logic, path on the bridge).
- In the string `"jdbc:mysql://localhost:3306/HW_EBOOKSHOP"`, 
    - `jdbc` is the protocol, 
    - `mysql` is the subprotocol, 
    - `localhost` is the url of the database,
    - `3306` is the port, and 
    - `HW_EBOOKSHOP` is the schema (that needs to already exist in this case).

- Note that `strSelect` does not end with `;` (it could, but does not have to).
- `next()` returns `true` if there is something left in the set of result, and move to the next line if it is the case. It ressembles what we would use to read from a file. If you try to use `getString` _before_ moving to the first row, you'll get an error like 
```
java.sql.SQLException: Before start of result set
```
Undeed, the cursor is "above" the first row of results when the `ResultSet` object is created.
- We could use `1`, `2`, and `3` instead of `"title"`, `"price"` and `"qty"` in the `while` loop: the `getString`, `getDouble` and `getInt` are overloaded, and have versions that take one integer as input, corresponding to the position of the attribute in the result set.


### The Result

If you store the program in `FirstProg.java`, compile it, with

```{.bash}
javac FirstProg.java
```

and then execute it, with 

```{.bash}
java -cp .:mysql-connector-java-***.jar FirstProg
```

(refer back to ["Executing Database Application"](#executing-database-application) for more details) then you should obtain:

```{.default}
The `SQL` query is: SELECT title, price, qty FROM BOOKS WHERE qty > 40
The records selected are:
The Lord of the Rings, 44.44, 44
Le Petit Prince, 55.55, 55
Total number of records = 2
```

Take the time to make sure you have the same result on your installation, and that you understand how the code works before moving on.

### A Variation

If you were to replace the body of `try` in the previous program with 

```{.java .numberLines dedent=6 .includeLink  include=code/java/FirstProgBis.java snippet=alternate}
```

You would obtain:

```{.default}
The records selected are:
1 ID,  The Communist Manifesto title,  Karl Marx and Friedrich Engels author,  11.11 price,  11 qty
2 ID,  Don Quixote title,  Miguel de Cervantes author,  22.22 price,  22 qty
3 ID,  A Tale of Two Cities title,  Charles Dickens author,  33.33 price,  33 qty
4 ID,  The Lord of the Rings title,  J. R. R. Tolkien author,  44.44 price,  44 qty
5 ID,  Le Petit Prince title,  Antoine de Saint-Exupéry author,  55.55 price,  55 qty
```

In that code, please note:

- the use of `ResultSetMetadata`,
- that we could "extract" the number of columns in the `ResultSet` using the `getColumncCount` method,
- that we used the `getString` method with integer input to read _all_ the data in the table, no matter its "original" data type.

Overall, this code would work equally well if the table had a different number of columns, as opposed to our first program.

## Mapping Datatypes

Note that in the previous code, we read everything as a string.
But, actually, `SQL` and `JAVA` datatypes can be mapped as follows:

`SQL` | `JAVA` |
:---: | :---:
`INTEGER` | `int`
`CHARACTER(n)` | `String`
`VARCHAR(n)` | `String`
`REAL` | `float`
`DOUBLE` | ` double`
`DECIMAL(t,d)` | `java.math.BigDecimal`
`DATE` | `java.sql.Date`
`BOOLEAN` | `boolean`
`BIT(1)` | `byte`

Remember that in `DECIMAL(t,d)` the `t` stands for the number of digits, the `d` for the precision.

However, we cannot always have a correspondance going the other way around (from Java to SQL): what would correspond to a reference variable?
To a private attribute?
This series of problems is called "object-relational impedance mismatch", it can be overcomed, but at a cost.
We will come back to this in the [Presentation of NoSQL](#presentation-of-nosql) Chapter.


## Differences Between `executeQuery`, `executeUpdate` and `execute`

Previously, we used `executeQuery` to send a SQL command to the DBMS.
This method is tailored for `SELECT`{.sqlmysql} statement, and it is not the only method we can use.

Name | `executeQuery` | `executeUpdate` | `execute` |
--- | --- | --- | ---
Used for | `SELECT` | `INSERT`, `UPDATE`, `DELETE` | Any type
Input Type | `string` | `string` | `string` 
Return Type | `ResultSet` | `int`, the number of rows affected by the querry | `boolean`, `true` if the query returned a `ResultSet`, `false` if the query returned an `int` or nothing

To retrieve the `ResultSet` obtained by an `execute` statement, you need to use `getResultSet` or `getUpdateCount`.
For more details, consult <https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html>.