SQL in a Server Environment

CS157a

Chris Pollett

Dec 5, 2018

Outline

Three-Tiered Architecture
Application Programming with Databases
JDBC
In-Class Exercise
Prepared statements

Introduction

So far, we have been looking at how to use SQL to create, manipulate, and query relational databases only from within the database systems shell.
Often, we want to write C, Java, PHP, Python, etc. programs that do non database stuff such as draw web pages, manipulate GUIs, etc using information from a database.
As an example of how to do this, today we look at how one can execute SQL commands using Java as the host language in the above.

Three-Tier Architecture

One common architecture used for writing application programs for databases is the three-tier or three layer architecture seen above.
The roles of the different layers roughly correspond to the models, controllers and views that you may seen if you studied the MVC design pattern in CS151.
Namely,
1. We imagine in the above that the client is a web browser, or is a mobile app making a request to a web server for a HTML, XML, or JSON object.
2. The web server are processes that connect clients to the database system usually over the Internet, but possibly over a local connect. These serve web pages (HTML, XML, JSON) (the View in MVC) based on the particular request of the user.
3. The Application Server are processes that perform the "business logic" for the application program. For example, it might handle the logic of who has bought what. In MVC, this logic might correspond to controller classes. Applications servers might be implemented using servlet technology, fast CGI (common gateway interface), etc.
4. The Database Server run the processes of the DBMS and can perform queries and modification to the database at the request of the application servers. They are used to handle model requests in the MVC pattern.

Networking and Tiers

Processes in the different tiers in the three tier architecture may be on the same machine or may be on different machines.
In order to have a general framework, that works for all database application programs, we need to have a framework which can handle the case where processes in each layer are on different machines.
So we need to be able to have the client communicate over a network to the web server and the web server communicate with the application server, and more importantly for us, that the application server communicate with the database.
Communication over a network is done with a collections of layered protocols each having read and write interfaces to the layer one down.
Rather than give a whole networking class, here are roughly the layers we care about:
1. Physical Layer. This layer uses electrons/photons to transmit bits down a communication line.
2. Link/Host-to-Network Layer. This handles the error correction, etc needed to provide using the physical communication of frames (bounded length sequences of bits) between two machines or between a machine and a switch/router all on the same line. If more than one machine is on the same line a protocol such as Ethernet might might be used to say which machine is what.
3. Internet Layer. This handles communicating packets between two different machines over the internet. These machines are not assumed to be on the same line or local area network. We imagine the internet is being built out of machines connected to routers and routers connected to other routers and further machines. Machines in this network are identified by IP (Internet Protocol) addresses. For example, 173.13.143.73. Two common standard for these addresses are IPv4 and IPv6.
4. Transport Layer. This layers uses packets to fake having a direct connection (using TCP (Transfer Control Protocol)) between two processes on different machines over which they can communicate sequences of bytes. To say which process we are communicating between we use a two byte port number. For example, we might say we want to communicate with 80 on a machine to access its web server or port 3306 to access its Mysql server.
5. Application Layer. These the protocols that web servers, database servers, etc. use to communicate via connections from the transport layer.
So-called loopback interfaces can be used to allow us to communicate between two processes on the same machine using the above stack.

JDBC

Java Database Connectivity (JDBC) is the application programming interface (API) we use for the last homework to communicate between our Java application programs and a database.
It was first developed by Sun Microsystems in 1997.
The most recent version is JDBC 4.3.
Each database vendor supports their own communication protocols for communicating using TCP with instances of their DBMS.
I.e., DB2's protocol is not the same as Orcacle's, etc.
To allow one collection of Java classes to do database programming that will work with all vendors, JDBC makes use of driver classes for each vendor.
So to communicate with a Mysql database using Java, one needs to get the Mysql driver class for JDBC.
JDBC drivers come in different levels depending on how robust the interface with the given DBMS is.
The Mysql driver is a so-called level 4 driver which means the driver is written in Java only, not making use of C/C++.

Setting up a Simple Database for use with JDBC

We are now going to demonstrate a simple Java program which does a query on a Mysql database.
To set this up, we can use the mysql CLI to create a database test:
```
CREATE DATABASE test;
```

We then make a table my_table(a:INT, b:VARCHAR(20)) and insert some rows in it:

CREATE TABLE my_table (
   a INT,
   b VARCHAR(20)
);
INSERT INTO my_table VALUES
(1, "hi"), (2, "bye"), (3, "yo");

To keep things simple, we will use the Mysql database's root account to access the database from our program.
I used MAMP to install Mysql, and this set's up the root account with password root, and sets up Mysql so that it listens for application requests on port 3306.

JdbcTest.java

Below is a simple Java program to query the my_table we just created:

import java.sql.*;

class JdbcTest
{
    public static void main(String[] args)
    {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        try {
            conn = DriverManager.getConnection(
                "jdbc:mysql://localhost/test?" +
                "user=root&password=root");
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM my_table");
            while (rs.next()) {
                int a = rs.getInt("a");
                String b = rs.getString("b");
                System.out.println("a:" + a + "\tb:" + b);
            }
        } catch (SQLException ex) {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) { } // ignore
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { } // ignore
                stmt = null;
            }
        }
    }
}

Remarks

JdbcTest.java connects to the DBMS via the line:

conn = DriverManager.getConnection(
   jdbc:mysql://localhost/test?" +
   "user=root&password=root");

This connection string here says to contacts a mysql process running on the same machine (localhost) on the default port.
If we wanted to connect to a different machine on a different port, we would change localhost to hostname:port_number .
The string after the ? says the username and password to connect with.
To then perform a query, we then make an instance of Statement, and call its executeQuery method.
This returns a ResultSet object which encapsulates a cursor/pointer into the rows that would be returned by the query.
This object's next() method can be used to get the next row pointed to and advance one row.
We use the getInt(some_column_name), getString(some_column_name) methods of the ResultSet object to get the current values of the columns we want of the row the ResultSet cursor currently points to.
Database operations may fail so we use a try catch block for SQLException to do error handling.

Compiling, and Testing the Program

To compile our code, we can simply use the command:
```
javac JdbcTest.java
```
To run the code, we need to make sure that the mysql driver jar file is in our class path.
We can either set an environment variable for the path or explicitly mention this path in the command line when we run our program:
```
java -cp .:/Users/cpollett/Desktop/mysql-connector-java-8.0.13.jar JdbcTest
```
When run, given the database was set up as described, we should get:
```
a:1	b:hi
a:2	b:bye
a:3	b:yo
```

In-Class Exercise

Modify my_table so that it now has three columns my_table(a:INT, b:VARCHAR(20), c:CHAR(1))
Insert four rows of your choice into this table, each with a different a value.
Modify JdbcTest to handle this, modify JdbcTest so that only rows with a > 5 are returned.
Compile and post the output of your modified program to the Dec. 5 In-Class Exercise Thread.

PreparedStatements

Sometimes it is useful to do queries which depend on Java variables.
One easy way to do this is to dynamically alter the string which we pass to executeQuery. For example,
```
int a=5;
rs = stmt.executeQuery("SELECT * FROM my_table WHERE a=" + a);
```
This is called an ad hoc query and is both dangerous from a security point of view and will tend to be slow.

A better way to do this is to use a prepared statement. For example,

PrepararedStatement pstmt = conn.prepareStatement
    ("select * from EMPLOYEE where fname=? and salary=?");
pstmt.setString(1,"bob");
pstmt.setInt(2, 123);
ResultSet rs = pstmt.executeQuery();

In the above, the DBMS can actually "compile" the query and then reuse it when different values are provided for the ? components.

Insert, Update, Delete from Java

If we want to execute SQL DML or DDL commands from Java, rather than use executeQuery, we use executeUpdate:
```
String ssn  = "123456789";
stmt.executeUpdate("DELETE FROM EMPLOYEE WHERE SSN="+ssn);
```
(we could have used a prepared statement).