Reading a `BLOB` via JDBC takes too long

Question

When I read an image from database, it appears in the JLabel immediately, but it takes too much time to complete streaming the BLOB from the DB.

public byte[] selectImage(int Id) throws SQLException {
    ResultSet res=null;
    int c=0;

    try {
        Class.forName(driver);
        con = DriverManager.getConnection(connectionURL);
    } catch (SQLException ex) {
        Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
    }

    System.out.println(con.getAutoCommit());

    statement = con.createStatement() ;
    res = statement.executeQuery("SELECT PHOTO FROM CUSTOMER where ID="+Id) ;

    while(res.next() && (res!=null)) {
        Blob bodyOut = res.getBlob("PHOTO");
        int length = (int) bodyOut.length();
        System.out.println("   Body Size = "+length);
        imageBytes = bodyOut.getBytes(1, length);
        bodyOut.free(); 
    }

    return imageBytes;
}

It's almost like you shouldn't store image data in a database - just a reference to the file on disk. Have a read of http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay for more information. — John Parker, Jan 19 '11 at 13:40

Hank Gay · Answer 1 · 2011-01-19T13:58:57.280

3

Consider storing the image outside of the database. Only store enough info in the DB to let you find the file (either on the filesystem, or from an HTTP server, or however you're storing it now that it's not in the DB). Binary data isn't really the use case that an RDBMS is optimized to handle.

Also, your code has some serious problems:

The biggest problem is probably the security flaw you get by failing to use bind variables, a.k.a. a PreparedStatement in Java. This is SQL injection 101.
you're using raw JDBC. Raw JDBC is tedious and easy to mess up. For instance, you aren't closing your ResultSet or your Connection, not to mention the statement variable, which should definitely be local. And when you do start closing them, you should do it in a finally block to make sure it always happens, even if there is an error.
If you happen to get more than one result from your query—I'm guessing you shouldn't, but just in case—you will only know if you happen to look at STDOUT, and you'll just get the last image. Your while loop is probably better expressed as an if to indicate that you only expect and/or care about the first result. If you care if there is more than one results, you should probably use an if instead of a while, then add a subsequent if (rs.next()) throw new MyAppropriatelyNamedException; so you know that there is something unexpected going on.
The null check for res is worthless. By the time you execute the check, the rs.next() statement will have already thrown a NullPointerException. You should probably just delete the check.
Why are you using the logging framework, only to turn around and use System.out.println to output some debugging info?

edited Jan 19 '11 at 13:58

answered Jan 19 '11 at 13:39

Hank Gay

70,339
36
160
222

Storing BLOBs in the database might sometimes even be faster depending on the images and the access pattern. Read this interesting article: http://research.microsoft.com/apps/pubs/default.aspx?id=64525 (SQL Server related but I wouldn't be surprised if this is valid for other DBMS just as well) – Jan 19 '11 at 13:55
@a_horse_with_no_name As always, every situation has its own unique nuances, but putting binary data in your DB should be the carefully justified exception to the general rule: don't put binary data in a RDBMS. – Hank Gay Jan 19 '11 at 14:02
personally I have good experience with putting BLOBs into the database. It makes life so much easier (no file-system cleanups, no permission problems, a single DB backup contains everything, storing them is transaction safe, no worries about distributing 1.000.000 files over a directory hierarchy). But I agree that with large images it puts an unnecessary burden on the DB. But then, modern DBMS can really handle this very well – Jan 19 '11 at 14:53
@a_horse_with_no_name As I said, every situation is different. In my case, it makes no sense to put files in a database, because they're going to be served by a webserver. If the webserver is already optimized for serving files, why waste resources putting stuff in a DB and looking it up? Also, you often-times wind up treating binary resources differently than regular data in your DB anyway: separate tablespaces, different backup strategies, etc. It's probably better to let the discussion at http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay cover the details. – Hank Gay Jan 19 '11 at 17:30
I completely agree - especially regarding *every situation is different* ;) – Jan 19 '11 at 17:48

Reading a `BLOB` via JDBC takes too long

1 Answers1