Lab 4-1: JDBC Startup

Tip

Use the command $ ant build_jdbc_lab to compile and build the class.
Use the script $ ./runJDBClab.sh to run the class. You will need to create the runJDBClab.sh script. Instructions below.

Instructions

Unit 4 Setup

Update your build.xml file for project 4 by uncommenting the project 4 property line:

Run the init target to intialize the Unit 4 workspace.
```
ant init
```

mySQL Setup

Create these files in the new dba directory.
- create_database.sql
- create_employees_table.sql

Log into mysql with this command. The password is ‘student’.

mysql -uroot -p

You should see something like this once you’ve logged in.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Create the ‘student’ user and database with this command.

source dba/create_database.sql;

You'll see that one row was affected

Query OK, 1 row affected (0.05 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

Log out of mysql, we want to do everything from now on as the student user.
```
exit
```

Log back in as student. The password is ‘student’.

mysql -ustudent -p

You should see something like this once you’ve logged in as student.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Run this mysql command.

show databases;

You'll see the database schema.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| student            |
+--------------------+
3 rows in set (0.00 sec)

Run this mysql command. This will switch us to be in the student database.
```
use student;
```
You'll get a message that the database has changed.
```
Database changed
```
Run the mysql command: show tables;
```
show tables;
```
The result is showing us that there are no tables in this database.
```
Empty set (0.00 sec)
```

Create DB Tables

Run the SQL in the downloaded file create_employees_table.sql with this command.
```
source dba/create_employees_table.sql;
```
The first time you run this command you will see the following error at the top of the results because the table doesn’t exist.
```
ERROR 1051 (42S02): Unknown table 'employees'
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

...
```
The table now contains 50 records.

Run this command again.

show tables;

We have a new table! View a diagram of the table here.

+-------------------+
| Tables_in_student |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

Run this query: select * from employees;, you should see this:

select * from employees;

You'll see all 50 rows.

+--------+------------+-----------+-------------+-------+------+----------+
| emp_id | first_name | last_name | ssn         | dept  | room | phone    |
+--------+------------+-----------+-------------+-------+------+----------+
|    101 | Joe        | Coyne     | 111-22-3333 | HR    | 125  | 555-3726 |
|    102 | Fred       | Hensen    | 222-33-4444 | Eng   | 126  | 555-3727 |
|    103 | Ethyl      | Roselle   | 333-44-5555 | Admin | 127  | 555-3728 |
|    104 | Barney     | Curry     | 444-55-6666 | IT    | 128  | 555-3729 |

...

|    147 | Jeff       | Sager     | 487-54-5149 | Admin | 408  | 555-3772 |
|    148 | Steve      | Stone     | 156-16-1321 | IT    | 409  | 555-3773 |
|    149 | Nick       | Mathis    | 487-32-4981 | HR    | 410  | 555-3774 |
|    150 | Carrie     | Ramirez   | 657-54-9813 | Eng   | 411  | 555-3775 |
+--------+------------+-----------+-------------+-------+------+----------+
50 rows in set (0.00 sec)

If you see your rows then proceed with the next step.

Add Files & Directories

Check to see if the following file is in your tomcat/lib directory. If not download this file and place it in the it in the tomcat/lib directory.
- mysql-connector-java-8.0.16.jar
If you haven’t already, create a directory named project4 in your java112 directory.
Create the following file and put in your projects/src/java112/project4 directory.
- View JDBCSelectEmployees.java
- Download JDBCSelectEmployees.java

Create the a script file named runJDBClab.sh and place it in your projects directory.

If you are using codespaces, use this script for the file:

#! /bin/sh

java -classpath lib/jdbc_lab.jar:/home/vscode/tomcat/lib/mysql-connector-java-8.0.16.jar java112.project4.JDBCSelectEmployees

If you are using a VM, use this script for the file:

#! /bin/sh

java -classpath lib/jdbc_lab.jar:/home/student/tomcat/lib/mysql-connector-java-8.0.16.jar java112.project4.JDBCSelectEmployees

Make the run script executable. Remember chmod??!!

Build the lab by running this command: ant build_jdbc_lab.

Buildfile: /home/student/Dropbox/projects/build.xml

clean:
    [delete] Deleting directory /home/student/Dropbox/projects/build
    [delete] Deleting directory /home/student/Dropbox/projects/dist

init:
    [mkdir] Created dir: /home/student/Dropbox/projects/build
    [mkdir] Created dir: /home/student/Dropbox/projects/build/classes
    [mkdir] Created dir: /home/student/Dropbox/projects/dist
    [mkdir] Created dir: /home/student/Dropbox/projects/build/WEB-INF
    [mkdir] Created dir: /home/student/Dropbox/projects/build/META-INF
    [mkdir] Created dir: /home/student/Dropbox/projects/build/WEB-INF/classes
    [mkdir] Created dir: /home/student/Dropbox/projects/build/WEB-INF/lib

compile_jdbc_lab:
    [javac] Compiling 4 source files to /home/student/Dropbox/projects/build/classes
    [javac] Note: /home/student/Dropbox/projects/src/java112/employee/Search.java uses unchecked or unsafe operations.
    [javac] Note: Recompile with -Xlint:unchecked for details.

build_jdbc_lab:
        [jar] Building jar: /home/student/Dropbox/projects/lib/jdbc_lab.jar

BUILD SUCCESSFUL
Total time: 0 seconds

Execute Select Statement

Run the lab with this command: ./runJDBClab.sh. If you see this output, then you’re done!

$ ./runJDBClab.sh
queryString: SELECT emp_id, first_name, last_name FROM employees WHERE last_name like 'Smith%'

 Row: 120 Mike Smith
 Row: 121 Alan Smith
 Row: 122 Brian Smith
 Row: 128 Brenda Smithson

 Column count : 3
 Column 1 name : emp_id
 Column 1 type : INT
 Column 1 label name : emp_id

Bonus

The DBCSelectEmployees.java file lacks friendly error messages and does not incorporate try-with-resources. Update this file by adding the friendly error messages and implementing try-with-resources.

Screenshots

To receive credit for this lab save a screenshot in the projects/screenshots/week12 directory.

lab1-db-setup.png: The command line output showing the results from running the ./runJDBClab.sh script.

Add, commit, and push and verify all work, including screenshots are visible in GitHub.

Rubric

All of the following must be satisfied to achieve a "Met" status

Screenshot clearly shows the correct database output.
Code and screenshots are properly named and saved in the correct directory.
All lab steps have been accurately and appropriately implemented.
Code adheres to the course coding standards.
Commit messages are concise, atomic, and effectively describe the change(s) made.
JavaDoc documentation is properly implemented, providing clarity and understanding of the code's functionality and usage.
External sources (websites, classmates, AI tools, etc), if utilized, are referenced and documented within the code as comments.