6.7 Special Topic: SQL

For this topic, we look at database accessibility within Java. Java includes an entire package named java.sql that implements common database functionality. This functionality is common to all database engines, but does not include the connectivity to a database.

You are not required to know SQL or anything about databases.

6.7.1 What's a database

A database is a structured approach to storing information. Something as simple as a CSV file can be a database; however, when we refer to databases, we tend to think of database engines.

• A database engine manages many databases.
• A database is comprised of tables.
• Tables contain information where rows are different observations, and columns are variables.

For example, a database on employees may consist of:

• A table (Users) containing personal information, e.g. firstname (String), lastname (String), emailid (String), date of birth (date), date of hire (date), unique id (integer), etc.
• A table (Departments) containing information on departments, e.g. department name (String), address (String), unique id (integers), etc.
• A relational table (Users_Departments) linking Users to Departments
• A table (Worktime) containing information about each day's work for each employee, e.g. day (date), employee id (integer), hour worked (double), payrate (float), etc.
• etc.

Database engines have very complex facilities to query the data. As opposed to figuring out what index some employee is in the array, the queries are formed in a more natural way, along the lines of: "Give me the employee with id 8", or "Give me all employees that have been at the university since 1970". Of course, this is not very structured language, and there are strict rules as to the the language used.

There are several companies that produce database engines. Here are only a few:

• DB2 (IBM): large-scale
• Oracle: large-scale
• MySQL: mid-scale
• Microsoft Access: small-scale

Each of these databases accepts queries in a standardized language (SQL: Structured Query Language). SQL statements to select an employee row from our database above would include:

SELECT * FROM Users WHERE emailid LIKE "ah297";

or

SELECT firstname,lastname from Users WHERE emailid LIKE "ah297";

While the query language is standardized, each database engine (Server) has a different way of communicating with the client. The connection is made through a network interface, but the connection point (ports), the data sent back and forth, encryption if available, etc. are different. For that reason, special Java packages exist for each database engine, that take care of the connection layer.

6.7.2 MySQL

For our purposes, we will be using MySQL. MySQL is a very successful and widely used database engine, especially for small- to medium-sized companies.

Below is the package that is required for MySQL to be used in a Java Application:

mysql.jar

6.7.3 Compiling

To compile a Java source file, e.g. DBTest.java, while including the mysql.jar package, the following javac command is required:

javac -classpath .:mysql.jar DBTest.java

6.7.4 Running

To run Java code (DBTest) while including the mysql.jar package, the following java command is used:

java -classpath .:mysql.jar DBTest

6.7.5 Test Data

The database we will be working with has the following parameters for location, name, etc.:

• Hostname: led.cs.columbia.edu
• Port: 3306
• Database Name: w31013
• Username: w31013
• Password: (given in class)

This database is very restricted and only allows you to view data, and not change it or delete it.

The database contains only one table with information about residents in East Campus. There are 690 records that fit into the following table schema:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned |      | PRI | NULL    | auto_increment |
| cunixid     | tinytext            |      | MUL |         |                |
| timestamp   | bigint(20) unsigned |      |     | 0       |                |
| uni         | tinytext            |      |     |         |                |
| name        | tinytext            |      |     |         |                |
| firstname   | tinytext            |      | MUL |         |                |
| middlename  | tinytext            |      |     |         |                |
| lastname    | tinytext            |      | MUL |         |                |
| title       | tinytext            |      |     |         |                |
| affiliation | tinytext            |      |     |         |                |
| dept        | tinytext            |      |     |         |                |
| email       | tinytext            |      |     |         |                |
| mailaddr    | tinytext            |      |     |         |                |
| mailaddr2   | tinytext            |      |     |         |                |
| residence   | tinytext            |      |     |         |                |
| phone       | tinytext            |      |     |         |                |
| phone2      | tinytext            |      |     |         |                |
+-------------+---------------------+------+-----+---------+----------------+

6.7.6 Standard Usage

The following is a template that can be used to load the MySQL driver, connect to the database, and begin working with the dataset:

DBTest.java

There are four methods that act as "helper" methods for commonly used functionality:

• private Connection openDBConnection()
• private void closeDBConnection(Connection cx)
• private Statement openStatement(Connection cx)
• private void closeStatement(Statement st)

The code for accessing the database, issuing a query, and going through the returned data row by row, has been placed in the constructor.

Note: You will need to enter the actual password as a parameter to openDBConnection(..)!

Connection cx = openDBConnection(FILL_THIS_IN);     Statement st = openStatement(cx);     try {       String query = "";       boolean brc = st.execute(query);       ResultSet rs = st.getResultSet();       while (rs.next()) {         // each row       }     } catch (Exception e) {       e.printStackTrace();     }     closeStatement(st);     closeDBConnection(cx);

This snipped of code uses the helper functions to open a connection to the database and create a statement, then issue a query, obtain the result set of data, iterate over that data, and finally close the connection.

6.7.7 Retrieving data from rows

When iterating over result data rows, every row acts as an object from which we can obtain each field. Depending on how many and which columns we request in the query, a row will contain the appropriate fields for it.

For example, if the requested fields are of types STRING, INT, STRING, FLOAT in that order, then these fields can be retrieved from each row as:

getString(1) getInt(2) getString(3) getFloat(4)

The indices passed as parameters indicate the column index, which corresponds to the column in the query.

6.7.8 Example 1

In this example, we will select the cunixid column from the users table and print it out, line by line. We will use the above file as a template, and change only the code in the constructor.

The SQL query for this example is:

SELECT cunixid FROM users

try {       String query = "SELECT cunixid FROM users";       boolean brc = st.execute(query);       ResultSet rs = st.getResultSet();       while (rs.next()) {         System.out.println(rs.getString(1));       }     } catch (Exception e) {       e.printStackTrace();     }

Sample output:

... kxc1 laf2019 lpg2006 mg2162 mgd2010 mk2243 nf186 npa2002 ot2006 pag2014 ...

When iterating over the resulting data rows, the example prints out the only field that is available for each row: the cunixid. Because cunixid is a string (text), and because it is the first column in the query ( NOT the actual table), we reference it by column index 1, and hence: rs.getString(1) .

6.7.9 Example 2

Let's select all of firstname, lastname, cunixid, and phone. The SQL query is:

SELECT firstname,lastname,cunixid,phone from users

try {       String query = "SELECT firstname,lastname,cunixid,phone from users";       boolean brc = st.execute(query);       ResultSet rs = st.getResultSet();       while (rs.next()) {         System.out.println(rs.getString(1) + " " + rs.getString(2) + ": " + rs.getString(3) + " , " + rs.getString(4));       }     } catch (Exception e) {       e.printStackTrace();     }

Sample output:

... Phung Nguyen: ptn2101 , +1 212-853-5114 Vikram Ashok: vva2101 , +1 212-853-4923 Tian Wang: tjw2005 , +1 212-853-5194 Brendan Cross: bec2105 , +1 212-853-4966 Harold Jensen Iii: hfj2101 , +1 212-853-4866 Jason Wagner: jhw2104 , +1 212-853-4972 Jerilyn Grote: jrg2115 , +1 212-853-5372 Jason Lambert: jrl2119 , +1 212-853-5026 ...

You could change this example to print out the entire table.

6.7.10 Example 3

Select students' names and department that are in SEAS. The field affilition contains that information. Undergraduate student in SEAS have an affiliation of "Fu Foundatn Schl Of Engineering & Applied Science:Ugrad"

SELECT firstname,lastname,dept from users where affiliation like 'Fu Foundatn Schl Of Engineering & Applied Science:Ugrad';

try {       String query = "SELECT firstname,lastname,dept from users where affiliation like 'Fu Foundatn Schl Of Engineering & Applied Science:Ugrad'";       boolean brc = st.execute(query);       ResultSet rs = st.getResultSet();       while (rs.next()) {         System.out.println(rs.getString(1) + " " + rs.getString(2) + ": " + rs.getString(3));       }     } catch (Exception e) {       e.printStackTrace();     }

Sample output:

... Jeffrey Nickerson: Mechanical Engineering Kipp Edick: Civil Engineering & Engineering Mechanics Seth Karpinski: Mechanical Engineering Colleen Brasser: Industrial Engineering & Operations Research Henry Selvitella: Mechanical Engineering Jeffrey Dunn: Chemical Engineering Patrick Sargent: Computer Science Yaa Boadi-Aboagye: Electrical Engineering Noah Shier: Mechanical Engineering ...

6.7.11 Example 4

Example 3, only sorted by department name, then lastname, then firstname. Print out lastname first:

SELECT firstname,lastname,dept from users where affiliation like 'Fu Foundatn Schl Of Engineering & Applied Science:Ugrad';

try {       String query = "SELECT firstname,lastname,dept from users where affiliation like 'Fu Foundatn Schl Of Engineering & Applied Science:Ugrad' order by dept,lastname,firstname";       boolean brc = st.execute(query);       ResultSet rs = st.getResultSet();       while (rs.next()) {         System.out.println(rs.getString(2) + " " + rs.getString(1) + ": " + rs.getString(3));       }     } catch (Exception e) {       e.printStackTrace();     }

Sample output:

... Tabry Mark: Computer Science Uy Jonathan: Computer Science Yung Norman: Computer Science Chea Angie: Earth & Environ. Engineering Skorik Alexandra: Earth & Environ. Engineering Boadi-Aboagye Yaa: Electrical Engineering Chiang Edward: Electrical Engineering ...