1 package search.db;
2 
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.util.ArrayList;
7 
8 import util.ConnectionUtil;
9 import util.SearchUtil;
10import util.TableOperations;
11import chemaxon.jchem.db.JChemSearch;
12import chemaxon.sss.SearchConstants;
13import chemaxon.sss.search.JChemSearchOptions;
14import chemaxon.util.ConnectionHandler;
15import chemaxon.util.HitColoringAndAlignmentOptions;
16
17/**
18 * Example codes for retrieving database fields of hit molecules.
19 * 
20 * @author JChem Base team, ChemAxon Ltd.
21 */
22public final class RetrievingDatabaseFieldsExample {
23
24    private static final String TABLE_NAME = "demo";
25
26    private ConnectionHandler connHandler;
27
28    public static void main(String[] args) {
29        try {
30            new RetrievingDatabaseFieldsExample().run();
31        } catch (Exception e) {
32            e.printStackTrace();
33        }
34    }
35
36    private void run() throws Exception {
37        connHandler = ConnectionUtil.connectToDB();
38        try {
39            TableOperations.setupMoleculeTable(connHandler, TABLE_NAME);
40            search();
41        } finally {
42            ConnectionUtil.closeConnection(connHandler);
43        }
44    }
45
46    private void search() throws Exception {
47
48        JChemSearchOptions searchOpts = new JChemSearchOptions(SearchConstants.SUBSTRUCTURE);
49        JChemSearch jcs = SearchUtil.createJChemSearch(connHandler, "c1cc(O)c(Br)cc1",
50                TABLE_NAME, searchOpts);
51
52        jcs.run();
53
54        // cd_id values of hits
55        int[] cdIds = jcs.getResults();
56
57        retrieveFieldsWithSQL(cdIds);
58        retrieveFieldsWithJChemSearch(jcs);
59    }
60
61    private void retrieveFieldsWithSQL(int[] cdIds) throws SQLException {
62
63        System.out.println("Retrieving field values " + "with SQL statement.");
64        System.out.println();
65
66        // Specify fields to retrieve, cd_id is (the first) parameter!
67        String retrieverSql = "SELECT cd_formula, cd_molweight from " + TABLE_NAME
68                + " WHERE cd_id = ?";
69        PreparedStatement ps = connHandler.getConnection().prepareStatement(retrieverSql);
70        try {
71            for (int i = 0; i < cdIds.length; i++) {
72
73                // Set (first) parameter value to cd_id
74                int cdId = cdIds[i];
75                ps.setInt(1, cdId);
76
77                // Retrieve fields
78                ResultSet rs = ps.executeQuery();
79
80                // Display result
81                try {
82                    if (rs.next()) {
83                        System.out.printf("ID: %d\nFormula: %s\nMass: %.3f\n\n", cdId,
84                                rs.getString(1), rs.getDouble(2));
85                    } else {
86                        // Do nothing, the record may have been deleted in the meantime
87                    }
88                } finally {
89                    rs.close();
90                }
91            }
92        } finally {
93            ps.close();
94        }
95    }
96
97    private void retrieveFieldsWithJChemSearch(JChemSearch jcs) throws Exception {
98
99        System.out.println("Retrieving field values using JChemSearch.");
00        System.out.println();
01
02        int[] cdIds = jcs.getResults();
03
04        // Specify database fields to retrieve
05        ArrayList<String> fieldNames = new ArrayList<String>();
06        fieldNames.add("cd_formula");
07        fieldNames.add("cd_molweight");
08
09        // ArrayList for returned database field values
10        ArrayList<Object[]> fieldValues = new ArrayList<Object[]>();
11
12        // One can also specify coloring and alignment options (not used now)
13        HitColoringAndAlignmentOptions displayOpts = null;
14
15        // Retrieve result molecules fieldValues will be also filled!
16        jcs.getHitsAsMolecules(cdIds, displayOpts, fieldNames, fieldValues);
17
18        // Print results
19        for (int i = 0; i < cdIds.length; i++) {
20            String formula = (String) fieldValues.get(i)[0];
21            Double mass = (Double) fieldValues.get(i)[1];
22            System.out.printf("ID: %d\nFormula: %s\nMass: %.3f\n\n", cdIds[i], formula, mass);
23        }
24
25    }
26
27}
28