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();
	}
    }

}

Do you have a question? Would you like to learn more? Please browse among the related topics on our support forum or search the website. If you want to suggest modifications or improvements to our documentation email our support directly!