==========================
Database Management & JDBC
==========================

:Author:   Nick Efford
:Contact:  N.D.Efford@leeds.ac.uk
:Status:   Draft
:Revised:  2017-09-14


This worksheet will introduce you to how databases are managed from within
IntelliJ IDEA and how they are accessed from Java programs via the
`JDBC API`_.

The `Apache Derby`_ database - which comes bundled with the JDK, branded as
'Java DB' - is used throughout.  However, please note that, aside from the
details of driver names and connection URLs, very little in this worksheet is
specific to any particular RDBMS.  Examples given here of how to connect, do
queries and extract results will work just the same with SQLite, MySQL, etc.
(Indeed, this is whole point of JDBC...)

For more information, please consult the Java Tutorial's `Lessons on JDBC`_
and the section of the IntelliJ online manual on `Working with the Database
Tool Window`_.


.. _JDBC API:
   http://docs.oracle.com/javase/8/docs/api/

.. _Apache Derby:
   http://db.apache.org/derby/

.. _Lessons on JDBC:
   https://docs.oracle.com/javase/tutorial/jdbc/index.html

.. _Working with the Database Tool Window:
   https://www.jetbrains.com/help/idea/2017.2/working-with-the-database-tool-window.html


Getting Started
===============

As first steps, you need to configure the database drivers in IntelliJ and
set up a suitable database.

#. Start IntelliJ and create a new Java project called ``JDBC``.  Click the
   :guilabel:`Database` tab to activate the Database tool window, then
   click on the Data Source Properties button, or press :kbd:`Alt+Enter`.

   On the Data Sources and Drivers dialog, select 'Derby (Embedded)' from the
   list of drivers on the left.   Under 'Driver files' on the right of the
   dialog, untick the option to 'Use Apache Derby [latest]', then click the
   green '+' button in the Additional Files section.

   Browse to where the JDK has been installed (on SoC PCs, this will be
   :file:`/usr/java/latest`), move down into the :file:`db/lib` subdirectory
   and select :file:`derby.jar`.  Click :guilabel:`OK` to dismiss the driver
   selection dialog, then :guilabel:`OK` again to dismiss the Data Sources
   and Drivers dialog.

   .. figure:: driver.png
      :scale: 80 %
      :align: center

#. Now let's create the database.  Click on the green '+' button in the
   top-left corner of the Database tool window or press :kbd:`Alt+Insert`,
   then choose :menuselection:`Data Source --> Derby (Embedded)`.  On the
   resulting dialog, specify the **full path** to a directory inside your
   JDBC project called :file:`books`.  The Name field should autofill as
   ``books``.

   Tick the 'Create database' checkbox next to the database path, then
   click :guilabel:`OK`.

   .. figure:: source.png
      :scale: 65 %
      :align: center

   If all is well, a new directory called :file:`books` should appear in
   the Project tool window and a new Data Source called 'books' should
   appear in the Database tool window.  You should also see a new code
   editor entitled 'books' appear in the centre of the UI, which you can
   ignore for now.

   .. note::

      There's no requirement to create the directory for an embedded database
      inside the project.  We do it here simply because it is neater.

      However, this does mean that if you are using version control in this
      project, you'll need to add :file:`books/` to your :file:`.gitignore`
      file.  You definitely *don't* want to have your database under
      version control!

      **Watch out for this when doing your project!**

#. Finally, let's populate the database.  Download the file
   :download:`setup.sql` and copy it into the top-level directory of
   the ``JDBC`` project, then open the file in the editor and examine it.
   (You may at this point see an 'SQL dialect is not configured' message
   at the top of the window; if so, click on the 'Change dialect' link
   and set the Project SQL Dialect to 'Derby' on the resulting dialog.)

   Running these SQL statements is easy; just right-click on the file and
   choose :menuselection:`Run 'setup.sql'`, or press :kbd:`Ctrl+Shift+F10`.
   Progress will be reported in the Run tool window.


Database Management
===================

#. Expand the Data Source entry in the Database tool window.  Under ``APP``,
   you should see tables called ``BOOK`` and ``PUBLISHER``.  If you expand
   these entries, you will see details of the columns in these tables.

#. Double-click on the ``PUBLISHER`` table.  A new panel will open in the
   centre of the UI, showing the contents of the table.  Do the same
   for ``BOOK``.

   .. figure:: booktable.png
      :scale: 65 %
      :align: center

#. Investigate database management options further by right-clicking on
   one of the tables listed in the Database tool window.  Notice how there
   are options to drop the table or dump its data to a file in various
   formats.

   Right-click on ``APP`` and try the :menuselection:`Diagrams --> Show
   Visualisation` option.  You should get a diagram like this:

   .. figure:: diagram.png
      :align: center

#. Tables can be edited from within IntelliJ.  Try this now by clicking on
   the ``PUBLISHER`` tab to view the ``PUBLISHER`` table.  Select the row for
   'Syngress' and click the '-' button above the table to delete the row.
   Click on the Submit button (the one with 'DB' above green upward-pointing
   arrow) to confirm the deletion.  The '8 rows' appearing above the table
   will change to '7 rows'.

   Now click the '+' button to add a new row.  A row will appear at the
   bottom of the table, containing nulls in each column.  If you see
   ``<null>`` in the ``ID`` column, double-click on it so that its value
   changes to ``<generated>``, then double-click on the null in the ``NAME``
   column, type a suitable publisher name and press the Enter key.

   .. figure:: addrow.png
      :align: center

   As before, use the Submit button to commit the change.  You should see
   the '7 rows' above the table change to '8 rows'.

#. Finally, try executing a query.  Click on the 'books' tab and begin
   typing an SQL query such as this:

   .. code-block:: sql

      SELECT * FROM BOOK WHERE PUB_YEAR > 2004

   As you type this, IntelliJ will offer to autocomplete statement elements
   for you.  Use the Tab key to do this, in the usual way.

   .. figure:: autocomplete.png
      :scale: 80 %
      :align: center

   When the query is complete, click the Execute button (the green triangle)
   in the top-left corner, or press :kbd:`Ctrl+Enter`.  You do not need
   to terminate the statement with a semicolon.

   Query results will be displayed in tabular form in a new Database Console
   tool window, at the bottom of the UI.


Connecting With JDBC
====================

#. Right-click on the :file:`src` folder of the ``JDBC`` project and choose
   :menuselection:`New --> Package`.  Specify a package name of
   ``comp2931.jdbc``.

#. Download :download:`DerbyUtils.java` and :download:`Query.java` and copy
   them into the :file:`src/comp2931/jdbc` directory.  ``DerbyUtils``
   contains some utility methods that simplify shutdown and error handling
   for Apache Derby databases, and ``Query`` is a partially complete program
   to perform a query using JDBC.  Double-click on the latter to open it
   in a code editor.

#. Start by trying to load a database driver explicitly.  Remove the
   ``TODO:`` from the first comment and add the following code beneath it:

   .. code-block:: java

      Class.forName(driver);
      System.out.println("Driver loaded.");

   IntelliJ will underline the first of these statements and complain about an
   unhandled exception.  To fix this, uncomment the first of the two
   ``catch`` blocks.  (To do this quickly, highlight the entire block, then
   press :kbd:`Ctrl+/`.)

#. Now add the code that connects to the database.  Remove the ``TODO:``
   from the second comment and add the following code beneath it:

   .. code-block:: java

      Connection connection = DriverManager.getConnection(url);
      System.out.println("Connection established.");

   As you type this, use :kbd:`Alt+Enter` when prompted to add the needed
   import statements.  Make sure the imports are from ``java.sql``, not
   from any other package!

   There will be another unhandled exception error at this point, which you
   can fix by uncommenting the second ``catch`` block.  Use :kbd:`Alt+Enter`
   again to accept the import of ``java.sql.SQLException``.

#. Find the 'Close connection' comment.  Remove the ``TODO:`` from the
   comment and add the following code beneath it:

   .. code-block:: java

      connection.close();
      System.out.println("Connection closed.");

#. In the Database tool window, disconnect from the database by clicking
   the Disconnect button (the red square).  The run ``Query`` by clicking
   on the green triangle in the margin of the editor window and choosing
   :menuselection:`R&un 'Query.main()'`.

   You should see "Failed to load JDBC driver!" displayed in the Run
   tool window.

   .. note::

      Although the Database tool window has been configured with the
      required database driver, these settings don't carry over to your
      own code, so you still need to add the driver to the project
      classpath in order to run the program successfully!

   To fix the problem, choose :menuselection:`File --> Project Structure`.
   On the resulting dialog, select 'Libraries' and click the '+' button.
   Choose 'Java' from the list of options and then browse to where the
   ``derby.jar`` file is located (see earlier).  Select the file and click
   :guilabel:`OK`, then click :guilabel:`OK` again to dismiss the Project
   Structure dialog.

   Rerun the program and this time you should see the following sequence
   of messages appearing in the Run tool window:

   .. code-block:: none

      Using driver org.apache.derby.jdbc.EmbeddedDriver
      Connection URL is jdbc:derby:books
      Driver loaded.
      Connection established.
      Connection clos/home/csunix/csc6ne/teaching/modules/2931/wstest/JDBCed.
      Shutdown completed successfully.

   .. note::

      If you see an error, it could be that the Database tool is still
      connected to the database.  Click the Disconnect button (a red square)
      at the top of the Database tool window, then try running the program
      again.


Querying With JDBC
==================

#. Remove ``TODO:`` from the remaining 'To Do' comment and add the following
   code beneath it:

   .. code-block:: java

      String sql = "SELECT * FROM BOOK WHERE PUB_YEAR > 2004";

      try (Statement statement = connection.createStatement()) {
         ResultSet results = statement.executeQuery(sql);
      }

   As you type in this code, use :kbd:`Alt+Enter` to accept IntelliJ's suggested
   imports for the ``Statement`` and ``ResultSet`` classes.

   This structure is known as a **try-with-resources block**.  It ensures
   that the resources associated with the ``Statement`` and ``ResultSet``
   objects are released when no longer needed.

#. The code above will perform the query, but does not extract the results.
   For that, you will need to add a loop like this to the try-with-resources
   block:

   .. code-block:: java

      while (results.next()) {
        String author = results.getString(2);
        String title = results.getString(3);
        int year = results.getInt(5);
        System.out.printf("%s (%d) %s%n", author, year, title);
      }

   Think of the ``ResultSet`` object as having a **cursor**, initially
   pointing before the start of the query results.  Calling the ``next``
   method advances the cursor to the next row of results, returning
   ``true`` if there are results available, ``false`` otherwise.  Methods
   such as ``getString`` and ``getInt`` can be used to extract strings,
   integers, etc, from the currently-referenced row.

   Notice the use of column indices as method arguments.  If you check the
   ``BOOK`` table in the Database tool window, you will see that author
   is the second column, title is the third column and year of publication
   is the fifth column.

#. Now run the program again.  You should see the following in the Run
   tool window:

   .. code-block:: none

      Using driver org.apache.derby.jdbc.EmbeddedDriver
      Connection URL is jdbc:derby:books
      Driver loaded.
      Connection established.
      Gollman D (2011) Computer Security (3rd ed.)
      Liang YD (2014) Introduction to Java Programming (10th ed.)
      Rubin KS (2013) Essential Scrum
      Seacord R (2006) Secure Coding in C and C++
      Shostack A (2014) Threat Modeling: Designing For Security
      Connection closed.
      Shutdown completed successfully.


Queries With Parameters
=======================

Supposed you wanted to query for books by author name and allow the user
to specify a search term that matches the name.  You might be tempted
to construct a query string like this:

.. code-block:: java

   String sql = "SELECT * FROM BOOK WHERE AUTHOR LIKE '" + search + "'";

where ``search`` is a ``String`` variable containing the user's input.

.. warning::

   **This is insecure and an extremely bad idea**.

   If the user can supply arbitrary content for ``search``, it will be
   possible for them to alter the SQL statement to suit their own purposes.
   This is known as **SQL injection**.  We will explore this and similiar
   vulnerabilities in COMP3911 next year.

   Instead of constructing the query by string concatenation, you should
   use **prepared statements**.

#. Modify the query code so that it looks like this. (The lines to be
   changed are highlighted.)

   .. code-block:: java
      :emphasize-lines: 1,3-5

      String sql = "SELECT * FROM BOOK WHERE AUTHOR LIKE ?";

      try (PreparedStatement statement = connection.prepareStatement(sql)) {
        String search = JOptionPane.showInputDialog("Search term");
        statement.setString(1, search);
        ResultSet results = statement.executeQuery();
        while (results.next()) {
          String author = results.getString(2);
          String title = results.getString(3);
          int year = results.getInt(5);
          System.out.printf("%s (%d) %s%n", author, year, title);
        }
      }

   Here, the ``?`` in the query string is a placeholder for user input.
   The value to be substituted for this placeholder is provided by calling
   ``setString`` on the ``PreparedStatement`` object.

   Because the SQL statement has already been parsed, it is not possible
   for the content of ``search`` to change its meaning, thereby avoiding
   the threat of SQL injection.

#. Try running the program again.  Enter a search term such as ``S%`` to
   find all the authors whose surnames begin with 'S'.


Updating a Table With JDBC
==========================

#. Download :download:`Update.java` and copy it into the directory
   :file:`src/comp2931/jdbc` of the project.  Then open it in the code
   editor.  Take a look at the first line inside the ``try`` block:

   .. code-block:: java

      System.setProperty("jdbc.driver", driver);

   This is a convenient alternative to the ``Class.forName`` approach seen
   in ``Query.java``. One advantage is that it simplifies exception
   handling, because it won't generate a `ClassNotFoundException`; another
   is that `property settings can be loaded from a file`_, or even passed
   in as a command line option to the JVM.

#. Remove the ``TODO:`` from the 'Insert data' comment, then add code
   underneath to specify an SQL statement that will insert a new row into
   the ``BOOK`` table:

   .. code-block:: java

      String sql = "INSERT INTO BOOK(AUTHOR, TITLE, PUB_ID, PUB_YEAR) VALUES (?,?,?,?)";

   Underneath this, add a try-with-resources block that creates a
   ``PreparedStatement`` object called ``statement``, using the SQL specified
   by the ``sql`` variable.

#. Inside the try-with-resources block, add code that will substitute values
   into ``statement`` and then execute the update on the database:

   .. code-block:: java

      statement.setString(1, "Reese G");
      statement.setString(2, "Java Database Best Practices");
      statement.setInt(3, 4);
      statement.setInt(4, 2003);
      int count = statement.executeUpdate();
      System.out.printf("Rows inserted: %d%n", count);

#. Beneath this block, add similar code that will insert another row.
   This time, use values of "Sperko R" for author, "Java Persistence for
   Relational Databases" for title, 2 for the publisher ID and 2003 for
   publication year.

#. Now run the program.  You should see output like this:

   .. code-block:: none

      Connection established.
      Rows inserted: 1
      Rows inserted: 1
      Connection closed.
      Shutdown completed successfully.

   Use the Database tool window to verify that the two new rows of data
   have indeed been inserted into ``BOOK``.

   .. note::

      When connecting to a remote database server to perform repeated
      insertions of data, performance can improve significantly if you
      execute those insertions as a **batch**.

      You can do this by repeatedly calling ``addBatch`` on your
      ``PreparedStatement`` object, after setting the values for each
      new row.  You then call ``executeBatch`` to execute all the
      accumulated insertions.


.. _property settings can be loaded from a file:
   https://docs.oracle.com/javase/tutorial/essential/environment/sysprop.html


Connecting Over The Network
===========================

Derby can act both as an embedded database, as in the exercises above, or
as a database server accessed over the network.

#. To access a Derby server from IntelliJ, you need to configure a new data
   source.  Click on the Data Source Properties button in the Database tool
   window to bring up the Data Sources and Drivers dialog and select 'Derby
   (Remote)' from the list of drivers on the left.

   Under 'Driver files' on the right of the dialog, untick the option to
   'Use Apache Derby [latest]', then click the green '+' button in the
   Additional Files section.

   Browse to where the JDK has been installed (on SoC PCs, this will be
   :file:`/usr/java/latest`), move down into the :file:`db/lib` subdirectory
   and select :file:`derbyclient.jar`.  (Note: this is different from
   the file used for the embedded database connection!)

   Click :guilabel:`OK` to dismiss the driver selection dialog, then
   :guilabel:`OK` again to dismiss the Data Sources and Drivers dialog.

   Now click on the green '+' button at the top of the Database tool window
   or press :kbd:`Alt+Insert`, then choose :menuselection:`Data Source -->
   Derby (Remote)`.  On the resulting dialog, add ``books`` to the end of
   the URL.  The complete URL should be

   .. code-block:: none

      jdbc:derby://localhost:1527/books

   Then click :guilabel:`OK`.  Don't worry if you see a connection error
   at this point; this will be fixed by running the server.

#. Download :download:`dbclasspath`, :download:`dbstart` and
   :download:`dbstop` into the top-level directory of your IntelliJ project.
   These shell scripts will simplify the business of running the database
   server.

   Click the :guilabel:`Terminal` tab to activate the Terminal tool window.
   In this window, set execute permissions on the scripts with

   .. code-block:: none

      chmod u+x db*

   Then enter the following commands to set up your classpath and run
   the server:

   .. code-block:: none

      export DERBY_HOME=/usr/java/latest/db
      ./dbclasspath
      ./dbstart &

   Note: this definition of ``DERBY_HOME`` should be correct for SoC PCs but
   you will need to modify it when running on your own PC.

   Now click on the newly-created data source in the Database tool window.
   It will probably be named ``books@localhost`` or similar.  Then click the
   Synchronize button or press :kbd:`Ctrl+Alt+Y`.  This will establish a
   connection with the server.  You should find that you are now able to
   view the contents of the ``BOOK`` and ``PUBLISHER`` tables, issue queries,
   etc - just as you did with the embedded database.

#. Now try modifying the ``Query`` program so that it connects to the
   database over the network rather than via the filesystem.

   First, you'll need to ensure that the network client driver is in the
   project classpath.  Choose :menuselection:`File --> Project Structure`.
   On the resulting dialog, select 'Libraries', then select the 'derby' entry
   created previously.  Click on the '+' button above the panel on the right
   or press :kbd:`Alt+Insert` to add a new library file.  Browse to where
   the Derby JAR files are located, select :file:`derbyclient.jar` and click
   :guilabel:`OK`.  Then click :guilabel:`OK` again to dismiss the Project
   Structure dialog.

   Next, change the definitions of the ``driver`` and ``url`` variables in
   the ``Query`` class to the following:

   .. code-block:: java

      String driver = "org.apache.derby.jdbc.ClientDriver";
      String url = "jdbc:derby://localhost:1527/books";

   If you run the ``Query`` program, you should find it functions just as
   it did before.

   .. note::

      Changing the program from using an embedded database to a database
      server was a simple matter of changing the name of the driver and the
      connection URL (and making sure that the required driver classes
      were available to the program).

      The same simple steps would be required if switching to a different
      RDBMS such as MySQL or PostgreSQL.  This level of abstraction is
      a key benefit of using JDBC.

#. Shutdown the database server by running the following command in the
   Terminal tool window:

   .. code-block:: none

      ./dbstop


Where Next?
===========

This worksheet has covered only the most basic uses of JDBC.  For example,
we have not covered transactions_.  There are also a number of more advanced
features provided by the API.  For example, you can `use RowSet instead
of ResultSet to process query results`_.  You can also `connect using a
DataSource object`_, which can provide facilities such as connection pooling
and more advanced transaction management.  You may wish to explore some of
these more advanced features when you do your group project.


.. _transactions:
   https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

.. _use RowSet instead of ResultSet to process query results:
   https://docs.oracle.com/javase/tutorial/jdbc/basics/rowset.html

.. _connect using a DataSource object:
   https://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html