Skip to content

Lab 4-1: JDBC Startup

Build and Run the Lab

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

Instructions

Unit 4 Setup

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

    Build.xml Screenshot

  1. Run the init target to intialize the Unit 4 workspace.

    ant init

MariaDB Setup

  1. Create these files in the new dba directory.

  2. Log into mysql with this command. 

    sudo mysql

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

    Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 34
Server version: 10.5.29-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

  3. 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)

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

    exit

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

    mariadb -ustudent -p
    You should see something like this once you’ve logged in as student. 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.5.29-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

  6. Run this sql command.

    show databases;
    You'll see the database schema. 
    +--------------------+
| Database           |
+--------------------+
| information_schema |
| student            |
+--------------------+
3 rows in set (0.00 sec)

  7. Run this sql 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

  8. Run the sql 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

  1. 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.

  2. 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)

  3. 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

  1. 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.

  2. If you haven’t already, create a directory named project4 in your java112 directory.

  3. Create the following file and put in your projects/src/java112/project4 directory.

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

    • Use this script to run the file:
    #! /bin/sh

java -classpath lib/jdbc_lab.jar:/home/vscode/tomcat/lib/mariadb-java-client-3.5.6.jar java112.project4.JDBCSelectEmployees
  1. Make the run script executable. Remember chmod??!!

  2. 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

  1. 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

Database commands

Start MariaDB service: sudo service mariadb start
Login as student: mariadb -ustudent -p
Password: student

  • You can also check if your MariaDB service is running by using sudo service mariadb status.
  • You typically need to start up this service each time you open Codespaces.

Bonus

The JDBCSelectEmployees.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.

  1. 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.