A lesson on connecting a Java application to a back-end database through the Java Database Connectivity (JDBC).

Java Database Connectivity (JDBC): Lesson

Up to this point, we have still been operating strictly in the Java language within our application. Now that we have firmly established a connection with our database and successfully created a statement manager, it is time to begin blending SQL into our program.

As a reminder, SQL is the language of relational databases. The term "relation" is just a fancy word for a table and how the data across multiple tables can be related to each other. All data in a relational database is stored inside these tables, broken down into columns and rows.

Inside `CustomerDaoService`, most of our methods are waiting on some preliminary data manipulation and then the injection of the actual SQL statements to execute "queries" or commands on the database. While this isn't a SQL-focused lesson, we will make sure you walk away with a functional program and the ability to store your application's data into a persistent data source.

As it stands, we currently have an entirely empty database, "MYSTERY_BUSINESS.db", without even a table to its name, just an empty shell, like a variable that has been declared but not initialized. In SQL, we use the command "CREATE TABLE" followed by the table name and then the column names and data types. Let's look at our `Customer` model and how it will translate into a table in SQLite:
![Java to SQLite Datatype Conversion](https://github.com/leftskidlow/images/blob/main/java_to_sqlite_converstion.png?raw=true)


Executing SQL Through JDBC

Congratulations! You now know how to successfully implement the Java Database Connectivity framework. This API provides you with a deep dive into the connection between the front and back ends of software engineering, now you're truly a full-stack developer!

You'll find that many of the popular and more robust data storage solutions are just as easy to use as SQLite with JDBC, and we encourage you to test your skills across many options. The conformance requirements to JDBC standards ensure all connections follow similar syntax and workflow!

Let's go over a few of the key features you implemented throughout this lesson:
- The five-layered structure of JDBC implementation
- Downloading third-party JDBC drivers
- Specifying multiple classpaths for our program to include at runtime
- Building code in line with a design principle: MVVM and Data Access Objects
- Verifying and Registering JDBC drivers with the `DriverManager`
- Establishing a `Connection` to a database and managing the opening and closing of resources in a responsible way
- Creating a `Statement` in JDBC to manage our SQL injections to the database
- Converting Java data types to SQL and vice versa in order to save objects to a database and then retrieve them out of the same database
- Creating a `ResultSet` from a SQL query and how to iterate through the rows and columns of the `ResultSet`

We truly only scraped the surface capabilities of the JDBC API, and we encourage you to expand your knowledge by diving into the documentation and practicing on your own time. Here is an example of where you can take your newfound skills and practice in your own programs:

> Take a look at the `Blob` data type in SQL, which is a large binary object data type, essentially a stream of binary data. This powerful data type, when combined with your I/O skills from Learn Java and Serialization skills from Intermediate Java can be used to create a simple and powerful full-stack solution for applications with a small to medium-sized user base.


Review

The **Java Database Connectivity** (JDBC) framework is an API provided by Oracle, the parent company of the Java programming language, and is included in the JDK when you install it on your computer. This API provides a simple and consistent way to connect our Java applications to a relational database in order to persist our data.

**Persistence**, or _the ability of your data to live outside your application_, is a crucial part of software development, especially as your programs grow in size and number of users. 

There are a number of different storage solutions available to a developer and JDBC provides a standard way to connect and interface with many of these options. This lesson, if you are following along both here and on your personal machine, will make exclusive reference to the SQLite database, a small and portable database available free of charge at [SQLite.org](https://www.sqlite.org). If you would like more information on installing this database on your computer, see our article about it here: [What is SQLite?](https://www.codecademy.com/article/what-is-sqlite).

JDBC allows programmers to operate directly on the database by allowing the language of databases, Structured Query Language (SQL), to be written directly into your Java files. If this is your first time hearing of SQL, we strongly suggest you review our [SQL for JDBC](https://www.codecademy.com/courses/learn-advanced-java/articles/sql-for-jdbc) article, as this lesson will assume a basic understanding of the necessary SQL commands for table creation and data manipulation.

All in all, between your application and the database, five layers exist in the JDBC environment:
###### 1. Your Application
  - All the logic, user interface, and data contained in your Java files.

###### 2. JDBC API
  - This is a set of classes and supporting files that provide the framework for the connection to a database.

###### 3. JDBC Driver Manager
  - The JDBC driver manager, normally referenced by importing and calling the class `DriverManager` class acts as the interface between you the programmer, and the actual drivers for the database.

###### 4. JDBC Drivers
  - This is the specific code that translates the commands sent via the JDBC into the language and syntax that the designated database works with. Most database applications, MySQL, SQLite, and Postgres for example, each provide JDBC drivers that can be downloaded from their websites.

###### 5. Databases
  - These are the data storage objects used to persist data throughout your application.


As we progress through the next few exercises we will navigate and implement JDBC solutions to link all five layers together into a useable data storage solution that you can add to your toolkit as a professional software engineer.

Introduction to JDBC

Now that we have connected to the database, it is time to begin communicating with it.

JDBC provides a very straightforward class to take care of executing static SQL queries, the `Statement` interface. A `Statement` is generated from a call to the `Connection` method, `.createStatement()`.

```java
Connection connection = DriverManager.getConnection(url);
Statement statement = connection.createStatement();
```

Once a `Statement` object is created it can be used to execute SQL queries and return a `ResultSet` object, which is essentially a Java representation of a table from the database. The `ResultSet` can then be used in the logic of your program. 

These `Statement` objects, just like `Connection` objects, also need to be closed when you are finished using them and are best included inside the same `try-with-resources` block that a `Connection` is opened in.

There is also an extension of `Statement` called `PreparedStatement` that is used to execute a precompiled SQL statement multiple times and with greater efficiency. As your program interacts more and more with the database you may find that a `PreparedStatement` might improve your performance.

For now, let's set up our `Statement` objects that we will use in each of our remaining `CustomerDaoService` methods.


Creating a Statement Manager

A `ResultSet` is all good and dandy, but what we really need back into our program is `Customer` objects! In the previous exercise, we used the `.getString()` method to pull data from a column and convert it to a string for use in printing. The `ResultSet` class has a wide range of other `get` methods that will return nearly any data from a column if of course that data type can be converted to the requested type.

Our `Customer` class stores its `ID` as an integer and the rest of the properties are strings, making our implementation fairly easy so, we'll stick to just two methods: `.getInt()` and `.getString()`. Here are some of the other options available:
- `.getBoolean()`
- `.getArray()`
- `.getTime()`
- `.getDouble()`
- `.getBlob()`

> There is a generic `.getObject()` method as well that returns the default Java object type corresponding to the column's SQL type, following the mapping for built-in types specified in the JDBC specification.

Let's get to creating our `Customer` objects from information stored inside a SQL database!


Creating a Customer from a ResultSet

Everything we've done so far has revolved around pushing data to our database, an important skill, but only half of the story.

Let's take a look at a very common data persistence scenario, loading a user's previous data when an app is closed and reopened at a later date.

- Just prior to a user exiting an app and the process is killed, the application executes a save function that pushes the user's current state to a database.
- When the application is reopened in the future, a splash screen is shown, along with a loading bar
- During this time the application is sending a request back to the database to load that user's saved state, moving the loading bar along as data is received.
- When the splash screen disappears it then appears to the user that the app never forgot where the user left it, when in fact all the information came from a separate database.

Through our JDBC implementation, we can produce similar results, but we need to now learn how to retrieve information back from the database.

In JDBC, the data that is sent back to the Java application from a database comes in the form of a `ResultSet` and is returned by a query on the database from a `Statement` object. This `ResultSet` object, just like `Connection` and `Statement` needs to be closed when finished, meaning it belongs with the other resources in our `try-with-resources` block.

A `ResultSet` object is a collection of rows and columns that represent a table from the database. There is a "cursor" that points to the current row, from which we can access columns by the column's index (which, unlike other Java data structures, begins with index 1).

```java
Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery("SQL QUERY");
```

You may have noticed in the code example that we are now calling the `.executeQuery()` method instead of the `.executeUpdate()` method, the difference being that a query returns a `ResultSet`.

Once we have injected the SQL into the database and received the result as a `ResultSet`, we can use regular ol' Java code to work through the results and present them to the user.

Let's see how this looks inside Mystery Business!


Operating on a ResultSet

In order to keep our project organized, we are going to spend some time refactoring it. As we start expanding the scope of our program to bring in outside resources and communicate across a variety of networks and mediums, a little organization can go a long way.

Let's assume, for this lesson's purposes, that we are an independent developer that has been asked to create a simple Java application. It should be able to process customer data for a yet-to-be-named business and also store that customer information to permanent storage via a SQLite database.

For this project, we are going to follow the MVVM, or Model-View-ViewModel, architecture. Essentially we are going to organize all of our objects (called models) into one package, our views (the user interface) into another package, and the ViewModels (the business logic that interacts with models, UI, and services) into another. We will also have a separate package for our data access classes, called `services`, where our data access object (the class that accesses the database) lives.

Let's review our project environment:
 - `project` -top level package
   - `models` - holds object representations (customers)
   - `views` - holds user interface files (we don't have UI yet)
   - `viewmodels` - holds business logic that uses models and interacts with other parts of the program such as UI and external resources
   - `services` - provides the `viewmodels` with a mode of communicating with external resources


Design Principles: MVVM

- Database Created... ✅
- Table Created... ✅
- ~~Alderaan System targeted~~... ✅
- List of Customers Saved to Table... ❌

As you can see our ~~death star~~, database, is not quite fully operational yet. Let's keep working toward making this ~~weapon system~~ JDBC connection perform some useful work. Creating databases and tables is all good and well but our core functionality is actually turning our Java data into data that we can store in our tables and then retrieving it back out of the database at a later date.

This is done with the SQL `INSERT INTO` statement. We `INSERT` a set of values, in the same order as the columns of our table, `INTO` our table. We will continue to use the `Statement` interface to implement this SQL command, although we challenge you to browse the [PreparedStatement Documentation](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) to see how you can implement a more efficient solution on your own.

Let's take a look at a sample insert statement in SQLite:

```sql
INSERT INTO CUSTOMERS
  VALUES (1010, "Jack", "Delangey", "jack@mysterybusiness.com", "555-867-5309");
```
We don't want to hardcode values into our SQL expression like the example above though, we want our `CustomerDaoService` to contain the logic to substitute each of these values for the properties of our `Customer` class. The whole command will be concatenated together into a String that we can execute on our database using our `Statement` object.


Storing a Customer List

The first layer we will set to tackle is the bottom-most, the JDBC Drivers. These are third-party files that allow a specific database to extend JDBC classes and interfaces so that they work with that database. While SQL is the industry standard for relational database communication, its implementation is not standardized across all databases. Some databases only support certain SQL statements, some format their SQL differently, and some still may rely on SQL and some other programming language to implement their features. This variation among database providers means that each database product will have its own drivers, similar to each printer manufacturer having its own drivers. JDBC drivers are typically maintained in a repository service like [Maven](https://mvnrepository.com/open-source).

Once downloaded, the driver needs to be added to the [classpath](https://docs.oracle.com/javase/tutorial/essential/environment/paths.html) of your program. The classpath is the location where the JVM looks for `.class` files for your application when the program is run. Many IDEs have a process to do this automatically, but we will stick to adding the classpath variable through the command line interface manually.

We'll also be using SQLite and the [SQLite JDBC Driver](https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc) as our backend database. Once it is downloaded, the next step is to register the driver with the `DriverManager` class. This class is responsible for managing all the connected JDBC drivers and ensuring the necessary files are available for a successful connection. Since the 2006 release of JDBC 4.0, any driver that is placed as a classpath variable is automatically registered but since we are adding the classpath manually, it will be good practice to run through the steps to verify the driver was loaded properly. We do this by checking to see if the class exists in the program build (this occurs at runtime, not compile time) using the `Class.forName("Class Name")` method.

Let's go ahead and get started! In the IDE on the right, you will see that we have already uploaded a copy of the latest SQLite driver (the `.jar` file) and have created a `project` folder that contains the file **ConnectionTest.java**.


JDBC Drivers

Now that we have verified that our drivers are present and restructured our project into a common design protocol, it is time to make the connection to our database.

The default setup of a SQLite database requires no username and password, therefore to connect to the database all we need to do is point the Java program in the right direction. To get our program to connect to a database we need to start using classes from the JDBC API, specifically the `DriverManager` and `Connection` classes.

The [`DriverManager`](https://docs.oracle.com/javase/8/docs/api/java/sql/DriverManager.html) class is the basic service used to load and manage specific JDBC drivers and to reach out to the database to obtain an initial connection.

The [`Connection`](https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html) interface manages a session with a specific database. All SQL statements and results are returned and handled within the context of a single connection. Along with managing SQL execution, a `Connection` can also provide database schema information such as overall design and specific table layouts.

The `DriverManager` has a static method that attempts to establish the initial connection and return the connection back to the user in the form of a `Connection` object. The `.getConnection()` method can either accept simply a database location such as our SQLite database, or a location, username, and password to access more secure databases. Database connections are costly to both local memory and network bandwidth and it is very important to close this resource as soon as you are finished with it.

Database locations, typically in the form of a URL, follow a generic pattern, especially when using a JDBC connection. For example, the address for our SQLite database is: `jdbc:sqlite:mysteryBusiness.db`.
- The first part refers to the type of service used to connect to the database, in our case `jdbc`.
- The second part is the type or vendor of the database. We are using `sqlite` but if you are using a MySQL database it would be `mysql` or on a Microsoft SQL Server it would be `sqlserver`.
- The last part is the path to the database and any extra parameters such as usernames and passwords. Once again, our SQLite database is fairly barebones and requires only the location in the file system of the database.

Connections are also subject to failure, which can lead to other errors in our program. For this reason, a failed connection will throw a `SQLException` and needs to be wrapped in a `try-catch-finally` block or a `try-with-resources` block, the latter being preferred because it automatically closes any open resources.

```java
try (put resources you want to open here) {
  // Do something with the resources here
} catch (exceptions here) {
  // Do something when the try block fails, typically alert the user
}
```

**Note:** _SQLite will create a database with the database name you give it if you request to open a database that doesn't exist. This can make it appear like you are connected to the proper database even though you are not. Using a username/password will help eliminate this problem, as well as double-checking your code._
