Storing Files in a Database

Storing Files in a Database

1. Why Store Files in DB? (beginner)

Resumes, PDFs, CSVs alongside rows = one backup, transactions, access control (vs scattered folders!). DB types: BLOB (binary!) / CLOB/TEXT (long text!).

2. Code — Save Text File (exam pattern!)

// table: docs(id INT, name VARCHAR(100), data BLOB)
String sql = "INSERT INTO docs(name, data) VALUES(?, ?)";
try (PreparedStatement ps = con.prepareStatement(sql);
     FileInputStream fis = new FileInputStream("a.txt")) {
    ps.setString(1, "a.txt");
    ps.setBinaryStream(2, fis, (int) new File("a.txt").length()); // stream!
    System.out.println("saved=" + ps.executeUpdate()); // 1!
}

Big files: setBinaryStream(2, fis) (no length — streams!). Text: setCharacterStream / setClob!

3. Rules (traps!)

• PreparedStatement (NOT Statement — binary safe + no injection!).
• Column BLOB (not VARCHAR — truncates!). Size limits: check max_allowed_packet (MySQL!).
• Stream file (don't readAllBytes huge — OOM!).

4. DB vs Folder? (viva!)

DB: backup/ACID/search together (small files!). Folder: faster streaming (videos!). Hybrid: DB path + folder bytes (common!).

5. Exam Points

• BLOB insert program — 5-7 marks, frequent.
• Stream + PreparedStatement need — 3 marks, frequent.