CS 377 - Database Systems
JDBC Project

Due: see class web page

0. Preparation

Execute the following commands to get a copy of the prepared files into your project directory:

mkdir ~/cs377/JDBC cp /home/cs377001/Handouts/JDBC/JDBC.java ~/cs377/JDBC

1. Assignment

JDBC application

Select a database
Execute an SQL query on the selected database
Display the selected tuples in formatted columns with column title

Provide 4 functions on the selected tuples:

Find the maximun on the values in a given column
Find the minimum on the values in a given column
Find the average on the numerical values in a given column
Find the median on the values in a given column

The project is done by extending the prepared program file JDBC.java with the above functionalities

The prepared program file JDBC.java contains TODO labels in the following places where you must:

Location 1: shared variable definition

Marked as follows

/* ********************************************************* TODO 1: Define you SHARED program variables here ********************************************************* */

Below this marked location, you must define the shared variables that you used in your program

A shared variable is:

Shared variable = a variable that you used in multiple (different) methods in your Java program
(If you use a variable only inside one method, then you must define that variable as a local variable inside that method and not as a shared variable !!!)

Location 2: activate the buttons

Marked as follows:

/* ********************************************************** TODO 2: You must make OTHER buttons active yourself.... ********************************************************** */

Below this marked location, you must write Java statements to:

Activate the buttons in the GUI

Location 3: call back classes for the buttons

Marked as follows:

/* =============================================================== TODO 3: ***** Write OTHER "call back" classes here for other buttons =============================================================== */

Below this marked location, you must write Java class that contains "call back" methods that will be run (executed) when a button is pressed

You must write one call back class per button
The call back class that you write for a specific button will depend on the function of the button !!!

In the prepared file JDBC.java, I have written code to activate the SELECT button

(And in class, I have discuss how to write call back code)

1.1 How to Compile and Run your GUI application

compile

run

JDBC program

javac -cp ".:/home/cs377001/lib/mysql-connector-java.jar" JDBC.java java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" JDBC

Note:

You must run the JDBC program on a Lab Machine !!!

2. The skeleton GUI for the project

prepared files

Java statements

graphical application

GUI

The GUI looks similar (but "less professional") than the cs377-sql query tool that you used in the SQL project

In this project, you will learn to implement the cs377-sql query tool by yourself....

3. Functionality

6 buttons

Select
Execute
Minimum
Maximum
Average
Median

These 6 buttons are highlighted in yellow in this figure:

How to work on this project:

Work on the buttons in the following order:
Finish the buttons SELECT and EXECUTE first before working on the function buttons !!!

For each button, you must:

Define the necessary shared variables in TODO 1 area
Make button active in the TODO 2 area
(I already did it for the SELECT button in the prepared program)
Write the actionPerformed( ) method in the call back class for the button in the TODO 3 area

Functionality of the main method and the call back methods for the buttons:

Main method:

SELECT:

When pressed, the JDBC application will make a connection to holland.mathcs.emory.edu and use the database specified in the DBName variable defined in the prepared program file

Note:

You can press the SELECT button multiple times (so you can work with different databases !!!)

Suppose the user click on the Select button the second time:

The JDBC program has already connected to a database before (when the user clocks the first time !!!)
The JDBC program must first close the current JDBC connection before making a 2nd (new) connection !!!

Error handling:

EXECUTE:

When pressed, the JDBC application will:

Execute the query in the Input variable defined in the prepared program file
Print the result tuples in a specific format in the Output JTextArea variable defined in the prepared program

The output format is sepcified in the "Output format requirements" section in this homework handout (see below)

Error handling:

If the SELECT button was pressed without having previously selected a database, then:
Handle this by defining a boolean DatabaseSelected variable and set this variable to true when user has selected a database

Use the following construc to execute a query (so you can handle queries that contain an (syntax) error):

try { .... // (Other program statements omitted for brevity rset = stmt.executeQuery( ... ); // Execute query // When this is run, program will raise an exception // if the query contains a syntax error // Then it will jump to the catch segment ... } catch (Exception e) { String errMsg = e.getMessage( ); // Write statement here to display the "errMsg" string // in the outpyt window }

Minimum:

When pressed, the JDBC application will compute the minimum over the value in the column given by the Column variable in the prepared program
The minimum found is display in the MinText JTextField variable in the prepared program
Note:

You must use the meta data technique from project 1 to process the correct data type.

To find the data type (= meta data) of the i-th column, use:

if (metaData.getColumnType(i) == java.sql.Types.DECIMAL || metaData.getColumnType(i) == java.sql.Types.INTEGER ) { // process column data as float } else { // process column data as string }

Maximum:

When pressed, the JDBC application will compute the maximum over the value in the column given by the Column variable in the prepared program
The maximum found is display in the MaxText JTextField variable in the prepared program
Note:

Average:

When pressed, the JDBC application will compute the average over a numercal value in the column given by the Column variable in the prepared program
The average found is display in the AvgText JTextField variable in the prepared program

Note:

The first column is column 1 !!!
Display the error message col # err if the column number exceeds the number of columns
Display the error message Not num if the values in the column is not numeric (integer, decimal or float)

Median:

When pressed, the JDBC application will compute the median over the value in the column given by the Column variable in the prepared program
The media found is display in the MedianText JTextField variable in the prepared program
Note:

3.1. Output formatting requirements

The following are required output format of the GUI:

String attributes are to be left-aligned. If the attribute value is NULL, left-align the string "NULL".
Example:
```
    LNAME
    --------------
    Smith
    Jones
    NULL
```
Numeric attributes are to be right-aligned. If the attribute value is NULL, right-align the string "NULL".
Example:
```
    SALARY
    ------------
        50000.00
       120000.00
            NULL
```

The width of column:

If the attribute type is a number (integer. float or decimal), set the column width to 10
If the attribute type is CHAR, set the column width to maximum of the value returned by getColumnDisplaySize() and 6.

Displaying the attribute name (= the title of the column):

If the attribute name (= coulum title) is too long, you must truncate it to the width of the attribute column.
If a string is too short, you must "pad" it with SPACE at the end.

The number of decimal digit displayed for a Numeric attribute column is equal to the value returned by the getScale() method.
There is one space separating columns from each other.

Test output

I have created a test relation called JDBCTest in the in the spjDB database to test the display qualify of your graphical application

Do the following to test your GUI application:

1. Select the spjDB database 2. Execute this query: select * from JDBCTest

The output must look like this:

name address salary dnum really_lon ================================================================ John 123 Decatur Rd 50000.1000 5 NULL Mary 543 Fishburn Rd 40000.9500 NULL abcde NULL 765 West Lane NULL 1 0123456789 David NULL 60500.5000 0 NULL Peter 890 Oxford Dr 30510.5600 3 klm Jane 672 Turn lane 22540.3500 2 xyz Jack 444 Watts Dr 83501.5990 3 NULL William 123 ABC lane 46500.2500 2 abc

3.2 Computing the minimun, maximum, average and median

Rules when you compute the minimun, maximum, average and median :

When you compute the minimun, maximum, average and median , you must skip the null values
The median is the (N+1)/2 element
If N is even, round (N+1)/2 down

Formatting:

4. Hints on computing minimum, maximum, average & medium

For some operations, you need to store the attribute values in an array.

To store the attribute value, do:

Use last() and getRow() to find the number of tuple values that you need to store
(see: click here)
Create an array of that many elements to store the values
Make sure you use the correct type of array !!!

You may need to read the selected tuples again:

You will need to determine the data type of a given column:

Use the column index value in the "Column" JTextField to obtain the column index.

Then call getColumnType(columnIndex):

The type of the column is DECIMAL if getColumnType() returns the value Types.DECIMAL
The type of the column is INTEGER if getColumnType() returns Types.INTEGER
The type of the column is STRING if getColumnType() returns Types.CHAR

Applicablity criteria:

You can compute the max, min and median value for any data type.
You can only compute the average for a numeric data types

Simplifcation:

To simplify the program, you can use getDouble() to obtain all numerical values.
(I.e., treat integer as a double)

5. Hints on formatting the output

Formatting strings can be done by appending (padding) spaces.
Use the length() method to find the length of the string.
The display size of the column is obtained from the meta data

Format the numbers according to the values obtained from getColumnDisplaySize() and getScale().

Note:

If getColumnDisplaySize() > 10, then reduce the column width to 10
(We do not have numeric data of more than 10 digits).

The number of decimal places displayed must be equal to the value returned by getScale().

The Java class java.text.DecimalFormat can help you format numbers.

For example, the following program shows you how to format a double number:

import java.text.DecimalFormat; class TestFormat { public static void main(String args[]) { double myNumber = 1234.5678; DecimalFormat form = new DecimalFormat("0.00"); String s; s = form.format(myNumber).toString(); System.out.println(s); } }

will print:

    1234.57

The format string "0.00" will produce an output with 2 decimal places. If you use the format string "0.000", it will print 1234.568.

The string that you obtained does not have the correct length. You will still need to pad the (numberic) string in front with the exact number of spaces to align the string to the right !

6. Grading Criteria

too time consuming

look in your code

grade

visual inspection

by running some queries and see what works and what does not.
We will also inspect alignments

Point assignments to the project:

Error handling: 10%

When there is a error, the Java program must print an appropriate error message in the Output JTextArea window
It must not exit !!!
Errors that need to be handled with error messages in the Output window:

The Select button: 10 %

Warning:

The Select button will connect to the MySQL database server and use the Database given in the Database box.
If your Select button does not work, we cannot test the other functionality and you will get 0 as grade !!!

Reason:

You have seen many examples in class how to connect to the database.
So failure to implement the Select button is equivalent to not understanding the JDBC material at all !!!

The EXECUTE button is worth 40 % - distributed as follows:

20% for executing the query correctly (regardless how the result is displayed).

10% for displaying String attributes properly.

Test:

select NAME, ADDRESS from JDBCTest Output: left aligned name address ---------- -------------------- John 123 Decatur Rd Mary 543 Fishburn Rd NULL 765 West Lane David NULL

10% for displaying numeric attributes properly.

Test:

select SALARY, DNUM from JDBCTest Output: right aligned salary dnum ===================== 50000.1000 5 40000.9500 NULL NULL 1 60500.5000 0 ... ...

NOTE: you should also try if the following queries work (because they will be tested too):

       select * from supplier

       select * from part

The Max button (must work for all data types): 5 %
The Min button (must work for all data types): 5 %
The Average button (must only work for numerical data types): 10 %
The Median button (must work for all data types): 20 %

7. Turn in

If your program consists of a single Java program file, then do the following:

Turn in your (one and only) Java source file as "JDBC" using the command:

/home/cs377001/turnin JDBC.java JDBC

If your program consists of multiple Java program files, then do the following:

Turn in your main program source file (the one that contains the main method) as "JDBC" using the command:
```
             /home/cs377001/turnin    JDBC.java   JDBC
```
Turn in all your other Java source files as "JDBC-x" where "x" is any one digit number.
For example:
```
             /home/cs377001/turnin OTHER_PROG.java JDBC-1
```

Extension request

To request an extension for the JDBC project, you must execute the following command before the deadline of the homework:
You request will be successful if you have not exceeded the maximum number of "free" (no-questions-asked) requests allowed

Statement of Policy on Computer Assignments

Students will be graded partially on the basis of their programming assignments. These programming assignments are to be treated as examinations, and are expected to be your individual work. While discussions with other students in the course may be permitted or encouraged by your instructor, you should write your program yourself. The mathlab representatives are available to explain error messages, discuss briefly technical details with which you may not be familiar, and give short suggestions as to how you might detect logic errors. The reps should not, however be asked to write part or all of your program. Your instructor (and any teaching assistants assigned to the course) will be glad to help you to the extent that he or she feels reasonable.

Submissions based on other students solutions in prior offerings of the course specifically violate these guidelines, as do submissions prepared with the help of an outside "tutor".

You should take precautions to protect the confidentiality of your work: preserve the secrecy of your password, do not make files or directories sharable, pick up your printouts promptly and dispose of printouts where they will not tempt other students. All work should be done either in the class directory of your ITD account (preferred) or in your "priv" directory (only if you do not have a class directory).

All submissions should include a comment statement near the top of the program of the form:

THIS CODE IS MY OWN WORK, IT WAS WRITTEN WITHOUT CONSULTING A TUTOR OR CODE WRITTEN BY OTHER STUDENTS - your name

Cases of apparent plagiarism or collusion will be referred to the Honor Council.