Retrieving Files from a Database
1. Reverse Flow (beginner)
Retrieve = SELECT BLOB → getBinaryStream → write to file (streams both sides!). Mirror of storing!
2. Code — Download by ID (exam program!)
String sql = "SELECT name, data FROM docs WHERE id = ?";
try (PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, 1);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
String name = rs.getString("name");
try (InputStream in = rs.getBinaryStream("data");
FileOutputStream out = new FileOutputStream("out_" + name)) {
byte[] buf = new byte[4096]; // 4KB chunks!
int n;
while ((n = in.read(buf)) != -1) out.write(buf, 0, n);
System.out.println("saved out_" + name);
}
} else System.out.println("no id 1!");
}
}
3. Rules (traps!)
- Buffer loop (4KB!) — NOT one giant
readAllBytes(OOM on big!). rs.next()FIRST (cursor starts BEFORE row!). No row → friendly message!- Close streams (try-with-resources auto!).
4. Text Alt (CLOB!)
getCharacterStream / getString for TEXT/CLOB (not binary!).
5. Exam Points
- Download program with buffer loop — 5-7 marks, frequent.
next()need + chunk reason — 3 marks, frequent.