---
title: Database Systems Lab 7
---
<h1 style='border: none'><center>Database Systems Lab 7</center></h1>
<h1 style='border: none'><center>JDBC Part 1</center></h1>
<h5><center>The Islamic University of Gaza<br>Engineering Faculty<br>Department of Computer Engineering</center></h5>
<h6>Authors: Usama R. Al Zayan & Rasha E. Kahil<span style="float:right">2023/03/24</span></h6>

---

## Expecteed outcomes
* Learning how to connect to the PostgreSQL database server from a Java program [Intellij].
* To be familiar with JDBC library statements to execute queries and updates.
* To build organized and meaningful outputs from resultsets and metadata.

## Lab 7: Time and Plan

| Tasks | Timing |
| -------- | -------- |
| Quiz 7 | 10 min |
| Task 1 | 80 min |

## Lab Tasks

Using the knowledge mentioned above, write a Java application that receives ‘super simple query’ commands from the user via command line (console or terminal), executes those commands on our university database, and shows the output.

| Query | Use |
| -------- | -------- |
| get student all | views all students in our university |
| get student count | shows how many students we have |
| get instructor all | views all instructors in our university |
| get instructor count | shows how many instructors we have |
| add student Usama Music | Insert student with name Usama and dept. name Music to our university |
| mod student id 00128 % name Tanaka | Updates the name of student who has the ID 00128 |
| mod instructor id 15151 % dept Finance | Updates the dept_name of instructor who has the ID 15151 |
| mod student id 00128 % cred 100 | Updates the tot_cred of student who has the ID 00128 |
| mod instructor id 15151 % salary 2000 | Updates the salary of instructor who has the ID 15151 |
| del student id 98345 | Delete the student who has the id 98345 from our university |
| del student name Usama | Delete the student whose name is Usama from our university |

### (More Questions)

1. Write a Java method that switches the current schema. The method firstly uses the method metadata.getSchemas() from the interface DatabaseMetaData to print a list of the schemas for the current database user, then asks the user to enter a schema number to connect to.
Add to our simple query language the command “switch schema” that calls your new method.

2. Add to our simple query language the command “add dept Sharia Quds 500000” that inserts a new department.

3. Add to our simple query language the command get student/instructor where "condition". Pass the condition as it is. this means a valid command is: get student where tot_cred > 100

4. Add to our simple query language the command “get courses_of student hala” that prints the list of courses a student named hala has taken.

5. Add to our simple query language the command “locate word” that takes a single word as argument, and finds all tuples in any relation in the database that contain that word as a substring in any column of that tuple. The output should be the entire tuple.
Your search should not be case sensitive and can match part of a word too, for example; the search word "Qu" matches a course named "Quantum Mechanics" so the tuple of that course in the course table shall appear. It also matches an instructor named "Queiroz" so his record from the table instructor shall appear in the results. And so on for the matched students “Harylquin for example”, departments, or any table in the schema.
You will need to use both SQL and MetaData in a clever way to do this.

###### tags: `Database Systems` `IUG` `Computer Engineering`

<center>End Of Lab 7</center>