Using BYTEA data with Java v42.7.3.1

Suggest edits

The BYTEA data type stores a binary string in a sequence of bytes. Digital images and sound files are often stored as binary data. EDB Postgres Advanced Server can store and retrieve binary data by way of the BYTEA data type.

The following Java sample stores BYTEA data in an EDB Postgres Advanced Server database and then shows how to retrieve that data.

First, the following creates a table (emp_detail) that stores BYTEA data. emp_detail contains two columns:

The first column stores an employee’s ID number (type INT) and serves as the primary key for the table.
The second column stores a photograph of the employee in BYTEA format.

CREATE TABLE emp_detail
(
  empno INT4 PRIMARY KEY,
  pic   BYTEA
);

The following creates a procedure (ADD_PIC) that inserts a row into the emp_detail table:

CREATE OR REPLACE PROCEDURE ADD_PIC(p_empno IN int4, p_photo IN bytea) AS
BEGIN
  INSERT INTO emp_detail VALUES(p_empno, p_photo);
END;

Then, the following creates a function (GET_PIC) that returns the photograph for a given employee:

CREATE OR REPLACE FUNCTION GET_PIC(p_empno IN int4) RETURN BYTEA IS
DECLARE
  photo BYTEA;
BEGIN
  SELECT pic INTO photo from EMP_DETAIL WHERE empno = p_empno;
  RETURN photo;
END;

Inserting BYTEA data into an EDB Postgres Advanced Server

The following shows a Java method that invokes the ADD_PIC procedure to copy a photograph from the client file system to the emp_detail table on the server:

public void InsertPic(Connection con)
{
  try
  {
    Console c = System.console();
    int empno = Integer.parseInt(c.readLine("Employee No :"));
    String fileName = c.readLine("Image filename :");
    File f = new File(fileName);

    if(!f.exists())
    {
      System.out.println("Image file not found. Terminating...");
      return;
    }

    CallableStatement stmt = con.prepareCall("{call ADD_PIC(?, ?)}");
    stmt.setInt(1, empno);
    stmt.setBinaryStream(2, new FileInputStream(f), (int)f.length());
    stmt.execute();
    System.out.println("Added image for Employee "+empno);
  }
  catch(Exception err)
  {
    System.out.println("An error has occurred.");
    System.out.println("See full details below.");
    err.printStackTrace();
  }
}

InsertPic() prompts the user for an employee number and the name of an image file:

int empno = Integer.parseInt(c.readLine("Employee No :"));
String fileName = c.readLine("Image filename :");

If the requested file doesn't exist, InsertPic() displays an error message and terminates:

File f = new File(fileName);

if(!f.exists())
{
  System.out.println("Image file not found. Terminating...");
  return;
}

Next, InsertPic() prepares a CallableStatement object (stmt) that calls the ADD_PIC procedure. The first placeholder (?) represents the first parameter expected by ADD_PIC (p_empno). The second placeholder represents the second parameter (p_photo). To provide actual values for those placeholders, InsertPic() calls two setter methods. Since the first parameter is of type INTEGER, InsertPic() calls the setInt() method to provide a value for p_empno. The second parameter is of type BYTEA, so InsertPic() uses a binary setter method. In this case, the method is setBinaryStream():

CallableStatement stmt = con.prepareCall("{call ADD_PIC(?, ?)}");
stmt.setInt(1, empno);
stmt.setBinaryStream(2 ,new FileInputStream(f), f.length());

Once the placeholders are bound to actual values, InsertPic() executes the CallableStatement:

stmt.execute();

If all goes well, InsertPic() displays a message verifying that the image was added to the table. If an error occurs, the catch block displays a message to the user:

System.out.println("Added image for Employee \""+empno);
catch(Exception err)
{
  System.out.println("An error has occurred.");
  System.out.println("See full details below.");
  err.printStackTrace();
}

Retrieving BYTEA data from an EDB Postgres Advanced Server database

Now that you know how to insert BYTEA data from a Java application, the following shows how to retrieve BYTEA data from the server:

public static void GetPic(Connection con)
{
  try
  {
    Console c = System.console();
    int empno = Integer.parseInt(c.readLine("Employee No :"));
    CallableStatement stmt = con.prepareCall("{?=call GET_PIC(?)}");
    stmt.setInt(2, empno);
    stmt.registerOutParameter(1, Types.BINARY);
    stmt.execute();
    byte[] b = stmt.getBytes(1);

    String fileName = c.readLine("Destination filename :");
    FileOutputStream fos = new FileOutputStream(new File(fileName));
    fos.write(b);
    fos.close();
    System.out.println("File saved at \""+fileName+"\"");
  }
  catch(Exception err)
  {
    System.out.println("An error has occurred.");
    System.out.println("See full details below.");
    err.printStackTrace();
  }
}

GetPic() starts by prompting the user for an employee ID number:

int empno = Integer.parseInt(c.readLine("Employee No :"));

Next, GetPic() prepares a CallableStatement with one IN parameter and one OUT parameter. The first parameter is the OUT parameter that will contain the photograph retrieved from the database. Since the photograph is BYTEA data, GetPic() registers the parameter as a Type.BINARY. The second parameter is the IN parameter that holds the employee number (an INT), so GetPic() uses the setInt() method to provide a value for the second parameter.

CallableStatement stmt = con.prepareCall("{?=call GET_PIC(?)}");
stmt.setInt(2, empno);
stmt.registerOutParameter(1, Types.BINARY);

Next, GetPic() uses the getBytes getter method to retrieve the BYTEA data from the CallableStatement:

stmt.execute();
byte[] b = stmt.getBytes(1);

The program prompts the user for the name of the file to store the photograph:

String fileName = c.readLine("Destination filename :");

The FileOutputStream object writes the binary data that contains the photograph to the destination file:

FileOutputStream fos = new FileOutputStream(new File(fileName));
fos.write(b);
fos.close();

Finally, GetPic() displays a message confirming that the file was saved at the new location:

System.out.println("File saved at \""+fileName+"\"");

← Prev

Using REF CURSORS with Java

↑ Up

Advanced JDBC Connector functionality

Using object types and collections with Java

Using BYTEA data with Java v42.7.3.1

Inserting BYTEA data into an EDB Postgres Advanced Server

Retrieving BYTEA data from an EDB Postgres Advanced Server database

← Prev

↑ Up

Next →