package search;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import chemaxon.jchem.db.JChemSearch;
import chemaxon.sss.SearchConstants;
import chemaxon.sss.search.JChemSearchOptions;
import chemaxon.util.ConnectionHandler;

/**
 * Example codes for retrieving database fields of hit molecules.
 * 
 * @author Peter Kovacs
 * @author Tamas Csizmazia
 * @version 5.2.5, 08/26/2009
 * 
 */
public class RetrievingResults {

    public static void main(String[] args) {
	try {
	    SearchExampleBase.tableSetup();

	    JChemSearchOptions jcSearchOptions = new JChemSearchOptions();
	    jcSearchOptions.setSearchType(SearchConstants.SUBSTRUCTURE);

	    String strTable = Util.structureTableName;
	    ConnectionHandler connectionHandler = Connection
	    .getConnectionHandler(1);
	    JChemSearch jChemSearch = InitializingSearch.createJChemSearch(
		    connectionHandler, "Brc1ccccc1", 
		    strTable, jcSearchOptions);

	    jChemSearch.run();

	    // cd_id values of hits
	    int[] cdIds = jChemSearch.getResults();

	    /************************************
	     * 1st method - using SQL statement *
	     ************************************/
	    
	    // Specifying fields to retrieve
	    // cd_id is (the first) parameter!
	    String retrieverSql = "SELECT cd_formula, cd_molweight from "
		+ strTable + " where cd_id = ?";
	    PreparedStatement ps = connectionHandler.getConnection()
	    .prepareStatement(retrieverSql);
	    try {
		for (int i = 0; i < cdIds.length; i++) {
		    int cdId = cdIds[i];
		    // setting (first) parameter value to cd_id
		    ps.setInt(1, cdId);
		    // retrieving fields
		    ResultSet rs = ps.executeQuery();
		    // displaying result
		    if (rs.next()) {
			System.out.println("ID: " + cdId + "\tFormula: "
				+ rs.getString(1) + "\tMass: "
				+ rs.getDouble(2));
		    } else {
			; // Do nothing, the record
			// may have been deleted in the meantime.
		    }
		}
	    } finally {
		ps.close();
	    }

	    /******************************************
	     * 2nd method - using getHitsAsMolecules *
	     ******************************************/

	    // Will contain returned molecules
	    Molecule[] results = null;

	    // Specifying database fields to retrieve
	    ArrayList<String> fieldNames = new ArrayList<String>();
	    fieldNames.add("cd_formula");
	    fieldNames.add("cd_molweight");

	    // ArrayList for returned database field values
	    ArrayList<Object> fieldValues = new ArrayList<Object>();

	    // One can also specify coloring and alignment options
	    // (not used now)
	    HitColoringAndAlignmentOptions options = null;

	    // Retrieving result molecules
	    // fieldValues will be also filled!
	    results = jChemSearch. getHitsAsMolecules(
	                cdIds, options, fieldNames, fieldValues);
	    
	    // displaying result
	    for (int i = 0; i < cdIds.length; i++) {
		System.out.println("ID: " + cdIds[i]);
		for (int j = 0; j < fieldValues.get(i).length; j++) {
		    System.out.println(fieldNames.get(j) + ": " 
			    + fieldValues.get(i)[j]);
		}
		System.out.println();
	    } 

	} catch (Throwable throwable) {
	    throwable.printStackTrace();
	}
    }

}