// GetImage.java
// https://www.w3schools.blog/retrieve-image-from-database-in-java
// Andrew Davison, ad@coe.psu.ac.th, Nov. 2022

/* Retrieve an image from the "pics" table in pictures.db
   by supplying the id to match the record (id, BLOB).

   The BLOB is converted to a byte array, and then written
   out to the file <id>+"Copy.png"

   The SQLite driver does not support Blob.getBlob(),
   so I extract the blob as a binary stream and convert it
   using DBUtils.streamToBytes()

    Usage:
      > compile GetImage.java
      > run GetImage id
    e.g.
      > run GetImage cat
           -- retrieved image is stored in catCopy.png
*/

import java.io.*;
import java.sql.*;

public class GetImage
{

  public static void main(String args[])
  {
    if (args.length != 1) {
      System.out.println("Usage: java GetImage <image id>");
      System.exit(1);
    }
    String id = args[0];

    try{
      System.out.println("Connecting to pictures.db");
      Connection conn = DriverManager.getConnection(
                                  "jdbc:sqlite:pictures.db");
      PreparedStatement ps =
          conn.prepareStatement("select * FROM pics WHERE name = ?");
      ps.setString(1, id);

      ResultSet rs = ps.executeQuery();
      if (rs == null) {
        System.out.println("No suitable record found");
        return;
      }
      if (!rs.next()) {   // assume the answer is in the first row
        System.out.println("No " + id + " id found");
        return;
      }

      // BLOB --> byte array --> image file
      String outFnm = id+"Copy.png";
/*
      Blob blob = rs.getBlob(2);   // not supported by SQLite driver
      byte[] bytes = blob.getBytes(0, (int)blob.length());
*/
      InputStream in = rs.getBinaryStream(2);  // 2nd column
      byte[] bytes = DBUtils.getBytes(rs, 2);
      FileOutputStream fos = new FileOutputStream(outFnm);
      fos.write(bytes);
      fos.close();

      System.out.println(id + " image saved to " + outFnm);
      conn.close();
    }
    catch(Exception e)
    {  System.out.println(e); }
  } /* main */




}  // end of GetImage class