// DBUtils.java
// Andrew Davison, Nov 2022, ad@coe.psu.ac.th

/* Useful JDBC functions

*/


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

import org.apache.commons.csv.*;


public class DBUtils
{

  public static int getNumRows(Statement stmt,
                            String tableNm) throws SQLException
  // how many rows are in the table?
  {
    ResultSet rs = stmt.executeQuery("select count(*) from " + tableNm);
    if (!rs.next())
      return 0;
     else
      return rs.getInt(1);
  }  // end of getNumRows()



  public static ArrayList<String> getTableNames(Connection conn)
                                   throws SQLException
  // return all the table names in the database as a list
  {
    ArrayList<String> tableNms = new ArrayList<>();

    DatabaseMetaData md = conn.getMetaData();
    ResultSet tables = 
       md.getTables(null, null, null, new String[]{"TABLE"});

    while(tables.next()) 
      tableNms.add( tables.getString("TABLE_NAME"));
    return tableNms;
  }  // end of getTableNames()


  public static boolean hasTable(Connection conn, String tableNm)
                                   throws SQLException
  // is table in the database?
  {
    ArrayList<String> tableNms = getTableNames(conn);
    return tableNms.contains(tableNm);
  }  // end of hasTable



  public static void displayColumnsInfo(Statement stmt, String tableNm)
                                      throws SQLException
  // print the column names and types for the table
  {
    ResultSet rs = stmt.executeQuery("select * from " + tableNm);

    ResultSetMetaData rsmd = rs.getMetaData();
    int numCols = rsmd.getColumnCount();
    if (numCols == 0)
      System.out.println(" No columns found");
    else {
      // list column header names and types
      for (int i = 1; i <= numCols; i++) 
        System.out.print("  " + rsmd.getColumnName(i) + 
               " (" + rsmd.getColumnTypeName(i) + 
                 ")  |");
      System.out.println();

    }
  }  // end of displayColumnsInfo()



  public static void writeToCSV(ResultSet rs, String fnm)
                        throws SQLException
  /* Save the ResultSet as a CSV table in fnm.
     Uses commons-csv-1.9.0.jar, which is in libs/
  */
  {
    try {
      BufferedWriter bw = new BufferedWriter( 
                            new FileWriter(new File(fnm)));
      CSVPrinter cp = new CSVPrinter(bw, CSVFormat.DEFAULT);
      cp.printRecords(rs, true);   // include headers
      cp.close();
      System.out.println("Written CSV records to " + fnm);
    }
    catch(IOException e)
    {  System.out.println(e);  }

  }  // end of writeToCSV()



  public static byte[] getBytes(ResultSet rs, int columnNo)
  /* 
     Treat the Blob stored in the specified column of the ResultSet
     as an InputStream. Read the bytes from the stream, and
     return them as an array.

     This approach is required since the SQLite driver does not
     support ResultSet.getBlob().
  */
  {
    try {
      InputStream in = rs.getBinaryStream(columnNo);
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      byte[] buffer = new byte[1024];
      int bytesRead = in.read(buffer);
      while (bytesRead > -1) {
        baos.write(buffer, 0, bytesRead);
        bytesRead = in.read(buffer);
      }
      return baos.toByteArray();
    }
    catch(SQLException e)
    {  System.out.println(e.getMessage()); }
    catch(IOException e)
    {  System.out.println("Could not read bytes from stream"); }

    return null;
  }  // end of streamToBytes()


}  // end of DBUtils class