package de.uhilger.baselink;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Ulrich Hilger
 */
public class Selector extends DBActor {
  
  private static final Logger logger = Logger.getLogger(Selector.class.getName());
  
  public Selector(PersistenceManager pm) {
    this.pm = pm;
  }

  /* --------------- selects ---------------- */

  /**
   * Select a list of objects through a given SQL statement
   * @param sql  sql query string that designates the requested objects
   * @param record object to use to map db records to objects
   * @return a list of objects that match the given query
   */
  public List<Object> select(String sql, Record record) {
    return select(sql, record, Record.WITH_BLOBS);
  }

  /**
   * Select a list of objects through a given SQL statement
   * @param sql  sql query string that designates the requested objects
   * @param record  object to use to map db records to objects
   * @param includeBlobs  true when BLOB contents are to be retrieved, false if not
   * @return  a list of objects that match the given query
   */
  public List<Object> select(String sql, Record record, boolean includeBlobs) {
    Connection c = null;
    ArrayList<Object> list = new ArrayList<>();
    try {
      c = pm.getConnection();
      list = (ArrayList<Object>) select(c, sql, record, includeBlobs);
      c.close();
      c = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeConnectionFinally(c);
    }
    return list;
  }

  /**
   * Select a list of objects through a given SQL statement
   * @param sql  sql query string that designates the requested objects
   * @param record  object to use to map db records to objects
   * @param includeBlobs  true when BLOB contents are to be retrieved, false if not
   * @param params  list of parameters in the order they appear in the SQL string
   * @return  a list of objects that match the given query
   */
  public List<Object> select(String sql, Record record, boolean includeBlobs, Object... params) {
    Connection c = null;
    ArrayList<Object> list = new ArrayList<>();
    try {
      c = pm.getConnection();
      list = (ArrayList<Object>) select(c, sql, record, includeBlobs, params);
      c.close();
      c = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeConnectionFinally(c);
    }
    return list;
  }


  /**
   * Select a list of objects that match a given SQL statement
   * @param sql  sql query string that designates the requested objects
   * @return  a list of map objects, one for each record. An element in the
   * list can be accessed with list.get(recordno).get("fieldname")
   */
  public List<Map<String, Object>> select(String sql) {
    //Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(sql);
    Connection c = null;
    List<Map<String, Object>> list = null;
    try {
      c = pm.getConnection();
      list = select(c, sql);
      c.close();
      c = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeConnectionFinally(c);
    }
    return list;
  }

  /**
   * Select a list of objects that match a given SQL statement
   * @param sql  sql query string that designates the requested objects
   * @param includeBlobs true when content of blob coloumns should be returned, false if not
   * @return  a list of list objects, one for each record. An element in the
   * list can be accessed with list.get(recordno).get(fieldno), each element is of type String
   */
  public List<List<String>> select(String sql, boolean includeBlobs) {
    Connection c = null;
    List<List<String>> list = null;
    try {
      c = pm.getConnection();
      list = select(c, sql, includeBlobs);
      c.close();
      c = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeConnectionFinally(c);
    }
    return list;
  }

  /**
   * Select a list of objects that match a given SQL statement
   * @param sql  sql query string that designates the requested objects with ? at the position of params
   * @param includeBlobs true when content of blob coloumns should be returned, false if not
   * @param params list of parameters in the order they appear in the SQL string
   * @return  a list of list objects, one for each record. An element in the
   * list can be accessed with list.get(recordno).get(fieldno), each element is of type String
   */
  public List<List<String>> select(String sql, boolean includeBlobs, Object... params) {
    Connection c = null;
    List<List<String>> list = null;
    try {
      c = pm.getConnection();
      list = select(c, sql, includeBlobs, params);
      c.close();
      c = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeConnectionFinally(c);
    }
    return list;
  }
  
  /* ---------- mit Connection ---------- */
  

  /**
   * Select a list of objects that match a given SQL statement
   * @param c  the database connection to use for this query, expected to be established and open already
   * @param sql  sql query string that designates the requested objects
   * @return  a list of map objects, one for each record. An element in the
   * list can be accessed with list.get(recordno).get("fieldname")
   */
  public List<Map<String, Object>> select(Connection c, String sql) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List<Map<String, Object>> list = null;
    try {
      ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      rs = ps.executeQuery();
      list = pm.toList(rs);
      rs.close();
      rs = null;
      ps.close();
      ps = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeResultSetFinally(rs);
      pm.closeStatementFinally(ps);
    }
    return list;
  }

  /**
   * Select a list of objects that match a given SQL statement
   * @param c  the database connection to use for this query, expected to be established and open already
   * @param sql  sql query string that designates the requested objects
   * @param includeBlobs true when content of blob coloumns should be returned, false if not
   * @return  a list of list objects, one for each record. An element in the
   * list can be accessed with list.get(recordno).get(fieldno), each element is of type String
   */
  public List<List<String>> select(Connection c, String sql, boolean includeBlobs) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List<List<String>> list = null;
    try {
      ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      rs = ps.executeQuery();
      list = pm.toList(rs, includeBlobs);
      rs.close();
      rs = null;
      ps.close();
      ps = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeResultSetFinally(rs);
      pm.closeStatementFinally(ps);
    }
    return list;
  }

  /**
   * Select a list of objects that match a given SQL statement
   * @param c  the database connection to use for this query, expected to be established and open already
   * @param sql  sql query string that designates the requested objects with ? at the position of params
   * @param includeBlobs true when content of blob coloumns should be returned, false if not
   * @param params list of parameters in the order they appear in the SQL string
   * @return  a list of list objects, one for each record. An element in the
   * list can be accessed with list.get(recordno).get(fieldno), each element is of type String
   */
  public List<List<String>> select(Connection c, String sql, boolean includeBlobs, Object... params) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List<List<String>> list = null;
    try {
      ps = pm.buildQuery(c, sql, params);
      rs = ps.executeQuery();
      list = pm.toList(rs, includeBlobs);
      rs.close();
      rs = null;
      ps.close();
      ps = null;
    } catch (Exception ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeResultSetFinally(rs);
      pm.closeStatementFinally(ps);
    }
    return list;
  }
  
  /**
   * Select a list of objects that match a given SQL statement
   * @param c  the database connection to use for this query, expected to be established and open already
   * @param sql  sql query string that designates the requested objects
   * @param record  object to use to map db records to objects
   * @param includeBlobs  true when BLOB contents are to be retrieved, false if not
   * @return  a list of objects that match the given query
   */
  public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<Object> list = new ArrayList<>();
    try {
      ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      rs = ps.executeQuery();
      if (rs.first()) {
        while (!rs.isAfterLast()) {
          list.add(record.toObject(rs, includeBlobs));
          rs.next();
        }
      }
      rs.close();
      rs = null;
      ps.close();
      ps = null;
    } catch (Exception ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeResultSetFinally(rs);
      pm.closeStatementFinally(ps);
    }
    return list;
  }

  /**
   * Select a list of objects that match a given SQL statement
   * @param c  the database connection to use for this query, expected to be established and open already
   * @param sql  sql query string that designates the requested objects
   * @param record  object to use to map db records to objects
   * @param includeBlobs  true when BLOB contents are to be retrieved, false if not
   * @param params  list of parameters in the order they appear in the SQL string
   * @return  a list of objects that match the given query
   */
  public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs, Object... params) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<Object> list = new ArrayList<>();
    try {
      //ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      ps = pm.buildQuery(c, sql, params);
      rs = ps.executeQuery();
      if (rs.first()) {
        while (!rs.isAfterLast()) {
          list.add(record.toObject(rs, includeBlobs));
          rs.next();
        }
      }
      rs.close();
      rs = null;
      ps.close();
      ps = null;
    } catch (Exception ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeResultSetFinally(rs);
      pm.closeStatementFinally(ps);
    }
    return list;
  }
  
}