JDBC, Oracle SQL, adding and displaying picture on label

Question

I have picture 'osta.jpg' on my desktop and I want to add this to my database Oracle SQL and then load this picture from database in Java application (JDBC) and show on label by a JLabel lblFilm = new JLabel(new ImageIcon( "picture_from_SQL") (Swing library)

I try adding image to database:

ALTER TABLE film ADD bfile_loc bfile, bfile_type varchar2(4);
UPDATE film SET bfile_type = 'JPEG';
UPDATE film SET bfile_loc = bfilename('GIF_FILES','C:\Users\Maciej\Desktop\osta.jpg') WHERE kategoria IN 'thriller';

However, I don't know how load it in the Java application.

Answer 1

First things first, in order to create a valid BFILE you need to specify an Oracle directory and then the location of the file within that directory. I don't know what your GIF_FILES directory is, but if it were created using

CREATE DIRECTORY GIF_FILES AS 'C:\Users\Maciej\Desktop';

then you would use the following to set the BFILE:

UPDATE film SET bfile_loc = bfilename('GIF_FILES', 'osta.jpg') WHERE kategoria IN 'thriller';

Secondly, in order to read the BFILE out of Oracle using JDBC, you need to use some Oracle-specific classes. Firstly, you need to cast the ResultSet to oracle.jdbc.OracleResultSet, so you can use the getBFILE() method to get the BFILE locator from the ResultSet. Then open the BFILE, get an InputStream from the BFILE's binaryStreamValue() method and read the data out of that.

Here's some example code (error handling could be improved):

import java.io.*;
import java.sql.*;
import oracle.sql.BFILE;
import oracle.jdbc.OracleResultSet;

// ...

    // 'connection' here is your Oracle database connection.
    Statement stmt = connection.createStatement();
    OracleResultSet rSet = (OracleResultSet)stmt.executeQuery(
                                                 "SELECT bfile_loc FROM film");
    if (rSet.next()) {
        BFILE bfile = rSet.getBFILE(1);
        System.out.println("Length: " + bfile.length());
        bfile.open();
        InputStream is = bfile.binaryStreamValue();
        // Read data from input stream...
        is.close();
        bfile.close();
    }

In the code above, I also fetch the length of the file, which isn't essential but it will fail if the file cannot be found.

Thirdly, you need to convert the InputStream to an ImageIcon. The ImageIcon class has a constructor that takes a byte array, and you can use answers to this question to convert the InputStream into a byte array.