---
title: Database Systems Pre-Lab 7
---
<h1 style='border: none'><center>Database Systems Pre-Lab 7</center></h1>
<h2 style='border: none'><center>JDBC Part I</center></h2>
<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/04/16</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 |

# Intellij
In this pre-lab, we are going to use Intellij, please read this [file](https://1drv.ms/b/s!Al-K2wk2I_QDgY0hd1JBQAZjXnHMSQ?e=MEZtcR) for more information about installation and getting a free copy.

# JDBC

$JDBC$ is the core API of Java that provides a standard interface to SQL-databases in our case postgreSQL.

# Connecting to our postgreSQL server

In this part we will show how to set up a Java environment, download PostgreSQL JDBC driver, and connect to the PostgreSQL database server.

## Setting up Java development environment

To develop a Java program, you need to have JDK installed on your computer. If you follow the IntelliJ download and installation instructions, you won't need to do anything else. Otherwise You need to download JDK 8. (any later JDK version will also work)

Practically, to develop java applications, you need to have a good IDE. There are many good IDEs available for free such as IntelliJ IDEA Community Edition, Eclipse, NetBeans, etc.

In our lab, we will use the IntelliJ IDE.
You will be able to follow the lab using any other IDE on your responsibility.

## Download PostgreSQL JDBC Driver

1. Create a JavaFX app by selecting JavaFX from the left menu(Generators), Then next, next and Create.


2. To ensure that the project was created correctly, a green check mark will appear next to the word Sync, after finishing downloading the necessary libraries.


3. Open the `pom.xml` file and add the following lines as shown in the image.
The following lines download the PostgreSQL JDBC Driver library and add it to the project.

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version>
</dependency>    


4. Press the button as shown in the following image. (Load Maven Changes)


5. To ensure that the library has been downloaded, the red text will disappear from the added lines and a green check mark will appear next to the word Sync, after downloading the library.


## Try to Connect to PostgreSQL from java

Now you need to prepare the information for your postgreSQL server.
* Address of the PostgreSQL database server e.g, `loaclhost`.
* Database name e.g, `UNI`.
* Username e.g, `postgres`.
* User password e.g, `admin`.

For this information, you can construct the PostgreSQL JDBC connection string by using the following format:

jdbc:postgresql://<database_host>:<port>/<database_name>

The `<port>` is optional. [by default it will be PostgreSQL standard port number (5432)]
In our example, the connection string is:

jdbc:postgresql://localhost/UNI

To make it easier, we can define the attributes of the App class for storing connection string, user, and password:

private final String url = "jdbc:postgresql://localhost/UNI1";
private final String user = "postgres";
private final String password = "admin";

**WE can establish connection in multiple ways.**
We can add the name of the schema that we want to directly connect to in the url in this format:

private final String url = "jdbc:postgresql://localhost/UNI1?currentSchema=uni-large";

For JDBC to be allowed to connect from java application to specific DBMS - PostgreSQL in our context -, we use its `JDBC driver`. So, a `JDBC driver` is a small piece of software that allows JDBC to connect to specified databases. You call the `getConnection` method of the `DriverManager` class. This method returns a `Connection` object.

The following `connect()` method connects to the PostgreSQL database server and returns a `Connection` object.

public Connection connect() {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(url, user, password);
        System.out.println("Connected to the PostgreSQL server successfully.");
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return conn;
}

You need to add the following lines to the `module-info.java` file.

requires java.sql;
requires org.postgresql.jdbc;
requires java.naming;


The complete program for connecting to PostgreSQL database server is as follows:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class HelloApplication {
    private Connection con;
    //information of your posgreSQL server
    private final String url = "jdbc:postgresql://localhost/UNI1";
    private final String user = "postgres";
    private final String password = "admin";

    public static void main(String[] args) {
        HelloApplication app = new HelloApplication();
        app.connect();
    }

    //Connect to the PostgreSQL database
    //returns a Connection object
    public Connection connect() {
        try {
            con = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to the PostgreSQL server successfully.");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return con; // will return Connection object
    }
}

Run the java code from the green triangle as shown.

So make sure to change the database server information to the information for your server.
And then run the code, you should get this, if the connection was established successfully.


## Connection using Data Source

PostgreSQL provides two implementations of DataSource which an application can use directly. One implementation performs connection pooling, while the other simply provides access to database connections through the DataSource interface without any pooling.

In this lab we will take a look at the simple data source connection without pooling.

Here is an example of connection using simple data source:

import java.sql.Connection;
import java.sql.SQLException;
import org.postgresql.ds.PGSimpleDataSource;


public class HelloApplication {
    private Connection con;
    public static void main(String[] args) {
        HelloApplication app = new HelloApplication();
        app.connect();
    }

    //Connect to the PostgreSQL database using PGSimpleDataSource
    public Connection connect() {
        PGSimpleDataSource source = new PGSimpleDataSource();
        source.setServerName("localhost");
        source.setDatabaseName("UNI1");
        source.setUser("postgres");
        source.setPassword("admin");
        source.setCurrentSchema("public");

        try {
            con = source.getConnection();
            System.out.println("Connected to server successfully");
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return con;  // will return Connection object

    }

}

# JDBC API

With the help of JDBC API, we can connect to a database, query it, add data, update data and even delete data from it very easily. New JDBC releases have features geared towards both the `client-side` (namely the package `java.sql`) and the `server-side` (`javax.sql`). Our focus now is on the `client-side` package.

In this part of the pre-lab we will walk you through the steps of querying data from a table, inserting a row into a table, updating existing data in a table, and deleting data from a table. Together, we will build an interactive command line program that understands our new ‘super simple’ queries😊 and executes them on our sample university database.

# Reading General Database Metadata

JDBC DatabaseMetaData is an interface from the java.sql package. DatabaseMetaData is generally provided by the Database application vendor in order to allow database programmers to know meta information about the current database, current database driver, current DBMS, and even current system. This interface is a very useful tool for programmers who need to explore the database.

## Example: Reading General Database Metadata

public Connection connect() {
    PGSimpleDataSource source = new PGSimpleDataSource();
    source.setServerName("localhost");
    source.setDatabaseName("UNI1");
    source.setUser("postgres");
    source.setPassword("admin");
    source.setCurrentSchema("uni-space");

    Connection con = null;
    try {
        con = source.getConnection();

        DatabaseMetaData metadata = con.getMetaData();
        System.out.println("DBMS Name: " + metadata.getDatabaseProductName());
        System.out.println("DBMS Version: " + metadata.getDatabaseProductVersion());
        System.out.println("Logged User: " + metadata.getUserName());
        System.out.println("JDBC Driver: " + metadata.getDriverName());
        System.out.println("Driver Version: " + metadata.getDriverVersion());
        System.out.println("Server URL: " + metadata.getURL());
        System.out.println("Max connections: " + metadata.getMaxConnections());

    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    return con;  // will return Connection object

}

**Output:**

:::info
DBMS Name: PostgreSQL
DBMS Version: 15.1
Logged User: postgres
JDBC Driver: PostgreSQL JDBC Driver
Driver Version: 42.6.0
Server URL: jdbc:postgresql://localhost/postgres
Max connections: 8192
:::

# JDBC Statements

Today, we will use two statement types in our application; `java.sql`. Statement and `java.sql.PreparedStatement`.

## `java.sql.Statement`

We use this statement interface for general-purpose access to our database. It is useful when using static SQL statements at runtime. The Statement interface cannot accept parameters and doesn’t contain parameters setters.

### Creating a `java.sql.Stamenet`

Before you can use a Statement object to execute an SQL statement, you need to create it using the Connection's `createStatement()` method, as in the following:

String query = "Select max(salary) from instructor;";
Statement stmt = con.createStatement();
stmt.execute(query);

Note that this method doesn’t take a string query as parameter.

### Giving parameters to a Statement

When a programmer who is using the statement interface needs to take input parameters at runtime, his only option is to use string concatenations to construct the query statement, for example

String query = "Select count(distinct "+ attr +" ) from "+ table +" ;";

Using this way to receive input parameters exposes the program to a very famous and destructive vulnerability that can exploited by attackers: $“SQL injection”$. If a programmer wants to use this string concatenation way, it is very important for him to implement countermeasures such as using regular expressions to validate inputs. Or he can avoid this headache and use PreparedStatement instead.

## `java.sql.PreparedStatement`

$PreparedStatement$ is a more sophisticated version of the Statement interface. PreparedStatement allows the programmer to create precompiled query statements with place holders for parameters. PreparedStatement can also be used with an SQL statement with no parameter. PreparedStatement is the correct choice when you plan to use the same SQL statement many times. If you do so, it does not compile the query every time you use it. Instead, it compiles the query only once then it uses the compiled version every time that statement is executed.

### Creating a `java.sql.PreparedStamenet`

Unlike Statement, PreparedStatement is given an SQL statement when it is created. You need to create one using the Connection's PrepareStatement (String) method, as in the following:

String query = "UPDATE student SET tot_cred = ? WHERE id = ?";
PreparedStatement ptmt = con.prepareStatement(query);

This ptmt object contains the query that is prepared to receive parameters. Now we pass the parameter to ptmt object. If we at some point change our mind, we can easily change those parameters before the execution.

ptmt.setString(2,"2203");
ptmt.setInt(1,80);
ptmt.setString(2,"2223");

Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the **same statement and supply it with different values each time you wish to execute it**.

# Executing JDBC SQL statements

To execute one of the JDBC statements we mentioned earlier, we have the following options:

## Execution methods

### `executeUpdate()`

To update data in the database. `executeUpdate` is used to execute the `UPDATE`, `INSERT`, `DELETE` queries. `executeUpdate` returns the number of rows affected by the `INSERT`, `UPDATE`, or `DELETE` statement.

### `executeQuery()`

`executeQuery` is used to execute the `SELECT` queries.

### `execute()`

If you do not know ahead of time whether the SQL statement will be a `SELECT` or an `UPDATE`/ `INSERT`/ `DELETE`, then you can use this method. It will return true if the SQL query was a `SELECT`, or `false` if it was an `UPDATE`, `INSERT`, or `DELETE` statement.

**Be careful:** if you use a `PreparedStatement`, then all of the above execution methods will cause a runtime error if you pass a query string as a parameter. You must write `pStmt.execute()` not `pStmt.execute(qry:String)`.

## Execution Results

### `UpdateCount`

If `executeUpdate()` returns without throwing an exception, it returns the number of rows affected by the SQL statement (an `INSERT` typically affects one row, but an `UPDATE` or `DELETE` statement can affect more).

### `ResultSet`

* `JDBC ResultSet` is an interface of `java.sql` package. It is a table of data representing a database `SELECT` query result, which is obtained by calling the `executeQuery()` method on statements.
* A `ResultSet` object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The `next()` method moves the cursor to the next row. The `next()` method returns `false` when there are no more rows in the `ResultSet` object. Because of that it can be used in a while loop to iterate through the result set.
* The default `ResultSet` object is not updateable therefore the cursor moves **only forward** from the first row to the last row only once.
* `ResultSet` provides `getXXX()` methods to get data from each row iteration. Here `XXX` represents `datatypes`. You also need to specify only the column name or index number (starting at 1) in getter parameters.

### `ResultSetMetaData`

$Metadata$ means data about data. If you wish to get the metadata of a table, like total number of columns, column names, column types etc. then you can use `ResultSetMetaData`. `ResultSetMetaData` is useful because it provides methods to get metadata from the `ResultSet` object.

#### Geting the `ResultSetMetaData` object

The `getMetaData()` method of `ResultSet` interface returns the object of `ResultSetMetaData`.

#### Commonly used methods of `ResultSetMetaData` interface:

1. `getColumnCount()` : int
2. `getColumnName(int index)` : String
3. `getColumnTypeName(int index)` : String
4. `getTableName(int index)` : String

## Statement instance results methods

After we talked about results, I’d like to tell that each of statement and `preparedStatement` instances has many result methods that initially returns the default `{-1, null, false}` values, but after you call any execute method on it, the result methods will return meaningful values. What the advantage of that? It is important in the case of using “`execute()`” which returns a `boolean` value that indicates the query type, if the statement was a `SELECT` query, you can retrieve the results by calling the `getResultSet()` method on that statement instance (`statement` or `preparedStatement`). If the `statement` was an `UPDATE`, `INSERT`, or `DELETE` statement, you can retrieve the affected rows count by calling `getUpdateCount()` on the Statement instance. These methods are also important when you need to return back to the open executed statement’s results without executing it another time.

#### Example 1: (`execute()` for select query): Get count of all instructors.

void getInstructorsCount() {
    try {
        Statement st = con.createStatement();
        if (st.execute("select count(*) as c from instructor;")) {
            if (st.getResultSet().next())
                System.out.println(st.getResultSet().getInt(1));
        }

    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
}

In this example, we can simply use `Statement`, and as the query is `SELECT` we can simply execute statement with `executeQuery()`, but here we want to test the knowledge of the last topic, so we use the `execute()` method which will returns `true` in our query case.
We get the query result by the statement instance using the corresponding `st.getResultSet()` method, but firstly we need to move the cursor forward, we include this forwarding in an if condition to guarantee that the cursor doesn’t point to a null, after that we get the result “single count value” from the resultset by the suitable getter method on intended column, the first column in our case, whose type is `integer`.

#### Example 2: (`execute()` for update query): Given student id, update his name.

void updateStudentName(){
    try {
        PreparedStatement pst = con.prepareStatement(
                "update student set name = ? where id = ?;");
        pst.setString(1, "Alex");
        pst.setString(2, "00128");
        if(!pst.execute()){
            System.out.println("Rows "+pst.getUpdateCount());
        }
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
}

In this example, we need parameters placeholders in query , so we use `PreparedStatement`, and as the query is `UPDATE` we can simply execute statement with `executeUpdate()`, but here we want to test the knowledge of the last topic, so we use the `execute()` method which will returns false in our query case.
We get the query result by the `PreparedStatement` instance using the corresponding `pst.getUpdateCount()` method, which tell us about the number of rows that are affected.

# Examples

#### Example 1: Write a JAVA method that returns the maximum of students IDs.

int getMaxStdID() {
    //executeQuery using Statement
    String SQL = "SELECT max(id) FROM student;";
    int max = 0;
    try (Statement stmt = con.createStatement(); 
         ResultSet rs = stmt.executeQuery(SQL)) 
    {
        rs.next();
        max = rs.getInt(1);
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    return max;
}

In this example, we can simply use `Statement`, and as the query is `SELECT` we can simply execute statement with `executeQuery(SQL)`, which returns a ResultSet object, firstly we need to move the cursor forward with `rs.next()`, after that we get the result “single max value” from the resultset by the suitable getter method on intended column, the first column in our case, whose type is `integer`.

:::warning
Notice the try-with-resources Statement.
:::

:::info
The try-with-resources statement is a try statement that declares one or more resources inside the try parentheses. A resource is an object that must be closed after the program is finished with it. The try-with-resources statement ensures that each resource is closed at the end of the try block. No matter if the try block concluded without errors or ended dur to an exception, in both cases try-with-resources ensures it is closed. Any object that implements `java.lang.AutoCloseable`, which includes all objects which implement `java.io.Closeable`, can be used as a resource. JDBC implements `java.lang.AutoCloseable` for all its resources objects, so we will declare any JDBC resources in try-with-resources statement.
:::

#### Example 2: Write a JAVA method to print all instructors information.

void getAllInstructors() {
    String SQL = "SELECT * FROM instructor ;";
    try (Statement stmt = con.createStatement(); 
         ResultSet rs = stmt.executeQuery(SQL)) 
    {
        System.out.println("\n Fetching Query..............");

        while (rs.next()) {
            System.out.printf("%-20s", rs.getString(1));
            System.out.printf("%-20s", rs.getString(2));
            System.out.printf("%-20s", rs.getString(3));
            System.out.printf("%-20f", rs.getDouble(4));
            System.out.println();
        }
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
}

#### Example 3: (`ResultSetMetaData`)

try {
    PreparedStatement ps = con.prepareStatement("select * from student");
    ResultSet rs = ps.executeQuery();
    ResultSetMetaData rsmd = rs.getMetaData();
    System.out.println("Total columns: " + rsmd.getColumnCount());
    System.out.println("Column Name of 1st column: " 
                       + rsmd.getColumnName(1));
    System.out.println("Column Type Name of 1st column: " 
                       + rsmd.getColumnTypeName(1));
} catch (SQLException e) {
}

This example illustrates how we can get information about the table of the `ResultSet`.

Output:

Total columns: 5
Column Name of 1st column: id
Column Type Name of 1st column: varchar

#### Example 2 – another version

void getAllInstructors() {
    //executeQuery using Statement
    String SQL = "SELECT * FROM instructor ;";
    try (Statement stmt = con.createStatement(); 
         ResultSet rs = stmt.executeQuery(SQL)) 
    {
        ResultSetMetaData md = rs.getMetaData();

        System.out.println("\n Fetching Query..............");
        for (int i = 1; i <= md.getColumnCount(); i++) {
            System.out.printf("%-20s", md.getColumnLabel(i));
        }

        System.out.println();

        while (rs.next()) {
            for (int i = 1; i <= md.getColumnCount(); i++) {
                System.out.printf("%-20s", rs.getString(i));
            }
            System.out.println();
        }

    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
}

This example illustrates how we can extract data and deal with resultset even if we initially don’t have any information about its columns number and types, using the metadata of this ResultSet.

#### Example 4: Write a JAVA method that insert a student, method takes name and dept_name from user, initially set tot_cred to zero, give the next available id (maxID+1) for this student.

void insertStudent(String name, String dept) {
    String SQL = "INSERT INTO student(id,name,dept_name,tot_cred) " 
        + "VALUES(?,?,?,?)";
    int addID = getMaxStdID() + 1;

    try (PreparedStatement pstmt = con.prepareStatement(SQL)) {
        pstmt.setString(1, String.valueOf(addID));
        pstmt.setString(2, name);
        pstmt.setString(3, dept);
        pstmt.setInt(4, 0);

        int affectedRows = pstmt.executeUpdate();
        // check the affected rows 
        if (affectedRows > 0) {
            System.out.println("added successfully :)\n ID is " + addID);
        }
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
}

#### Example 5: Write a JAVA method that updates the instructor salary, method should take the id of the instructor and the new salary.

void updateSalary(String id, String newVal) {
    String SQL = "UPDATE instructor SET salary = ? WHERE id = ? ;";

    int affectedrows = 0;

    try (PreparedStatement pstmt = con.prepareStatement(SQL)) {

        pstmt.setDouble(1, Double.parseDouble(newVal));
        pstmt.setString(2, id);

        affectedrows = pstmt.executeUpdate();

    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    if (affectedrows > 0) {
        System.out.println("The instructor with id = " + id 
                           + " is updated successfully");
    }
}

#### Example 6: Write a JAVA method that deletes the student who has the given id, method takes the id of the student from user.

void deleteByID( String id) {
    String SQL = "DELETE FROM student WHERE id = ?";
    int affectedrows = 0;

    try (PreparedStatement pstmt = con.prepareStatement(SQL)) {
        pstmt.setString(1, id);
        affectedrows = pstmt.executeUpdate();
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    if (affectedrows > 0) {
        System.out.println("The student with id = " + id + 
                            " is deleted successfully");
    }
} 

# Additional information to know (optional)

## IntelliJ Database tools

IntelliJ provides a set of tools that do the same job as DBeaver.

For more information, please visit this pages [Connect to PostgreSQL](https://www.jetbrains.com/help/idea/postgresql.html), [Run queries](https://www.jetbrains.com/help/idea/run-a-query.html), etc.

## Read database information in a secure way

Writing the database connection information as strings in the code is an insecure method especially when the code is shared publicly on GitHub.
To avoid this, you can create a file that contains the database connection information and ignore pushing this file when uploading the code.

1. Create `src/dbconfig.properties` file.
2. Add the following lines to the file.

db.serverName=localhost
db.username=postgres
db.password=1234
db.databaseName=UNI1

3. In order to ignore the file when pushing the code. Add file name `src/dbconfig.properties` to `.gitignore` file.
4. To read from this file.

import java.util.ResourceBundle;

private void connect() {
    ResourceBundle reader;

    try {
        reader = ResourceBundle.getBundle("dbconfig");

        PGSimpleDataSource source = new PGSimpleDataSource();
        source.setServerName(reader.getString("db.serverName"));
        source.setDatabaseName(reader.getString("db.databaseName"));
        source.setUser(reader.getString("db.username"));
        source.setPassword(reader.getString("db.password"));

        connection = source.getConnection();
        System.out.println("Connected to database " 
                           + reader.getString("db.databaseName"));

    } catch (Exception exception) {
        exception.printStackTrace();
    }
}

###### tags: `Database Systems` `Pre-Lab` `IUG` `Computer Engineering`

<center>End Of Pre-Lab 7</center>