---
title: Database Systems Pre-Lab 8 Part II
---
<h1 style='border: none'><center>Database Systems Pre-Lab 8 Part II</center></h1>
<h2 style='border: none'><center>JDBC Part II [Storing Binary Data]</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>

---

# Storing Binary Data

## Introduction

PostgreSQL provides two distinct ways to store binary data. Binary data can be stored in a table using the data type bytea or by using the Large Object feature.

But in order to determine which method is appropriate for you, you need to understand the limitations of each method.

## The bytea data type
* Is not well suited for storing very large amounts of binary data.
* A column of type bytea can hold up to `1 GB` of binary data, it would require a huge amount of memory to process such a large value.
* To use the bytea data type you should simply use the `getBytes()`, `setBytes()`, `getBinaryStream()`, or `setBinaryStream()` methods.

## The Large Object

* It is better suited for storing very large values of binary data.
* Deleting a row that contains a Large Object reference does not delete the Large Object. Deleting the Large Object is a separate operation that needs to be performed.
* Large Objects also have some security issues since anyone connected to the database can view and/or modify any Large Object, even if they don't have permissions to view/update the row containing the Large Object reference.
* To use the Large Object functionality you can use either the `LargeObject` class provided by the PostgreSQL JDBC driver, or by using the `getBLOB()` and `setBLOB()` methods.
* You must access Large Objects within an SQL transaction block. You can start a transaction block by calling `setAutoCommit(false)`.

**Note:**
In a future release of the JDBC driver, the `getBLOB()` and `setBLOB()` methods may no longer interact with Large Objects and will instead work on the data type bytea. So it is recommended that you use the LargeObject API if you intend to use Large Objects.

# Store and retrieve Image

For example, suppose you have a table containing the file names of images and you also want to store the image.

## First Let’s use bytea data type

CREATE TABLE images (imgname text, img bytea);

## To insert an image, you would use

File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, file.length());
ps.executeUpdate();
ps.close();
fis.close();

Here, `setBinaryStream()` transfers a set number of bytes from a stream into the column of type bytea. This also could have been done using the `setBytes()` method if the content of the image was already in a `byte[]`.

## Retrieving an image

PreparedStatement ps = con.prepareStatement(
                            "SELECT img FROM images WHERE imgname = ?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
if (rs != null) {
    while (rs.next()) {
        byte[] imgBytes = rs.getBytes(1);
        // use the data in some way here
    }
    rs.close();
}
ps.close();

(We use `PreparedStatement` here, but the `Statement` class can equally be used.)

Here the binary data was retrieved as an `byte[]`. You could have used an `InputStream` object instead.

# Second Let’s use Large Object feature

You could be storing a very large file and want to use the `LargeObject API` to store the file.

CREATE TABLE images (imgname text, imgoid oid);

## To insert an image, you would use

// All LargeObject API calls must be within a transaction block
conn.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();

// Create a new large object
int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);

// Open the large object for writing
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

// Now open the file
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);

// Copy the data from the file to the large object
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0) {
    obj.write(buf, 0, s);
    tl += s;
}

// Close the large object
obj.close();

// Now insert the row into imageslo
PreparedStatement ps = conn.prepareStatement("INSERT INTO imageslo VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setInt(2, oid);
ps.executeUpdate();
ps.close();
fis.close();

## Retrieving the image from the Large Object

// All LargeObject API calls must be within a transaction block
conn.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();

PreparedStatement ps = con.prepareStatement(
                            "SELECT imgoid FROM imageslo WHERE imgname = ?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
if (rs != null) {
    while (rs.next()) {
        // Open the large object for reading
        int oid = rs.getInt(1);
        LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

        // Read the data
        byte buf[] = new byte[obj.size()];
        obj.read(buf, 0, obj.size());
        // Do something with the data read here

        // Close the object
        obj.close();
    }
    rs.close();
}
ps.close();

# How to backup and restore your database (Additional)

## Backup your database

We will ues `pg_dump` command to extract a PostgreSQL database into a script file.

`pg_dump` is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql. Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications, even on other SQL database products.

To dump a database called `mydb` into an `SQL-script` file:

pg_dump mydb > db.sql

For more information [PostgreSQL: Documentation: 15: pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html).

The following java code will backup the `UNI1` database including all schemas.

import java.util.ResourceBundle;
//This method will return 0 if the backup process completed successfully..
public int backup(String path) throws InterruptedException, IOException {
    ResourceBundle reader = ResourceBundle.getBundle("dbconfig");
    String[] envp = {
            "PGHOST=" + reader.getString("db.serverName"),         //localhost
            "PGDATABASE=" + reader.getString("db.databaseName"),   //UNI1
            "PGUSER=" + reader.getString("db.username"),           //postgres
            "PGPASSWORD=" + reader.getString("db.password"),       //1234
            "PGPORT=5432",
            "path=C:\\Program Files\\PostgreSQL\\15\\bin"         //PostgreSQL path
    };
    String[] cmdArray = {
            "cmd",
            "/c",
            String.format("pg_dump -f \"%s\"", path)
    };
    Runtime runtime = Runtime.getRuntime();
    Process process = runtime.exec(cmdArray, envp);
    process.waitFor();
    return process.exitValue();
}

## Restore your database

To reload such a script into a (freshly created) database named `newdb`:

psql -d newdb -f db.sql

The following java code will restore the `UNI1` database including all schemas.

//This method will return 0 if the restore process completed successfully..
public int restore(String path) throws IOException, InterruptedException {
    ResourceBundle reader = ResourceBundle.getBundle("dbconfig");
    String[] envp = {
            "PGHOST=" + reader.getString("db.serverName"),        //localhost
            "PGDATABASE=" + reader.getString("db.databaseName"),  //UNI1
            "PGUSER=" + reader.getString("db.username"),          //postgres
            "PGPASSWORD=" + reader.getString("db.password"),      //1234
            "PGPORT=5432",
            "path=C:\\Program Files\\PostgreSQL\\15\\bin"        //PostgreSQL path
    };
    String[] cmdArray = {
            "cmd",
            "/c",
            String.format("psql -f \"%s\"", path)
    };
    Runtime runtime = Runtime.getRuntime();
    Process process = runtime.exec(cmdArray, envp);
    process.waitFor();
    return process.exitValue();
}

# Example Project

Try to build the following interfaces using Scene Builder, write all required database query handlers in a model class, then control the UI constructs behaviors and the flow of data in view controllers.
Our example project performs two tasks:
(1) Update student picture, (2) show student picture.

Use the university database, but **make sure to add a new column** for the student table which we will use to store student pictures.
a. Build the project using bytea data type to store student pictures.
b. Build the project using Large Object to store student pictures.

## Notes:

1. You can find the example repository here: `https://github.com/UsamaZayan/Database_Storing_Binary_Data.git`.
2. Don't forget to add column std_pic to the student table.

    alter table student add column std_pic bytea;

## Test the Example

### Main Menu
Here is the main menu which allows you to select the job you want to do:

<center>

</center>

### Insert Student Picture

If you click on Change Student Picture button, this interface should appear to you:
It allows you to select a picture from your local machine, and assign it to a specific student, by taking the student ID and inserting it in the student table.

<center>

</center>

After Submit.

<center>

</center>

### Show Student Picture

If you click on Change Student Picture button, this interface should appear to you:
It allows you to display a student picture by entering his ID.

<center>

</center>

After Show Photo.

<center>

</center>

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

<center>End Of Pre-Lab 8 Part II</center>