1 package search.db;
2 
3 import java.sql.SQLException;
4 import java.util.HashMap;
5 import java.util.Map;
6 
7 import resource.ResourceLocator;
8 import util.ConnectionUtil;
9 import util.MolImportUtil;
10import util.SearchUtil;
11import chemaxon.jchem.db.JChemSearch;
12import chemaxon.jchem.db.StructureTableOptions;
13import chemaxon.jchem.db.TableTypeConstants;
14import chemaxon.jchem.db.UpdateHandler;
15import chemaxon.sss.SearchConstants;
16import chemaxon.sss.search.JChemSearchOptions;
17import chemaxon.util.ConnectionHandler;
18
19/**
20 * Example code showing the usage of calculated columns during search.
21 * 
22 * @author JChem Base team, ChemAxon Ltd.
23 */
24public final class CalculatedColumnsSearchExample {
25
26    private static final String TABLE_NAME = "demo";
27
28    private ConnectionHandler connHandler;
29
30    public static void main(String[] args) {
31        try {
32            new CalculatedColumnsSearchExample().run();
33        } catch (Exception e) {
34            e.printStackTrace();
35        }
36    }
37
38    private void run() throws Exception {
39        connHandler = ConnectionUtil.connectToDB();
40        try {
41            System.out.print("Setting up molecule table... ");
42
43            createTable();
44            MolImportUtil.databaseImport(ResourceLocator.getDefaultInputPath(), connHandler,
45                    TABLE_NAME);
46
47            System.out.println("Done.");
48            System.out.println();
49
50            search();
51        } finally {
52            ConnectionUtil.closeConnection(connHandler);
53        }
54    }
55
56    private void createTable() throws SQLException {
57
58        // Drop the table if it already exists
59        if (UpdateHandler.isStructureTable(connHandler, TABLE_NAME)) {
60            UpdateHandler.dropStructureTable(connHandler, TABLE_NAME);
61        }
62
63        // Create the table
64        StructureTableOptions tableOptions = getStructureTableOptions(TABLE_NAME);
65        UpdateHandler.createStructureTable(connHandler, tableOptions);
66    }
67
68    private StructureTableOptions getStructureTableOptions(String tableName) {
69
70        final StructureTableOptions tableOptions =
71                new StructureTableOptions(tableName, TableTypeConstants.TABLE_TYPE_MOLECULES);
72        tableOptions.setExtraColumnDefinitions(", logp  numeric(30,15), "
73                + "rtbl_bnd_cnt numeric(1,0), " + "pka_ac_2  numeric(30,15)");
74
75        Map<String, String> chemTermMap = new HashMap<String, String>();
76        chemTermMap.put("logP", "logP()");
77        chemTermMap.put("rtbl_bnd_cnt", "rotatableBondCount()>4");
78        chemTermMap.put("pka_ac_2", "pKa(\"acidic\", \"2\")");
79        tableOptions.setChemTermColsConfig(chemTermMap);
80
81        return tableOptions;
82    }
83
84    private void search() throws Exception {
85        String query = "CCC1C=CC=CC=1";
86
87        // Init searcher
88        JChemSearchOptions searchOpts =
89                new JChemSearchOptions(SearchConstants.DEFAULT_SEARCHTYPE);
90        JChemSearch jcs =
91                SearchUtil.createJChemSearch(connHandler, query, TABLE_NAME, searchOpts);
92
93        searchOpts = jcs.getSearchOptions();
94        String[] columns = { "logp", "rtbl_bnd_cnt", "pka_ac_2" };
95        double[] thresholds = { 3.85, 3, 18 };
96
97        for (int i = 0; i < columns.length; i++) {
98            // e.g. SELECT cd_id FROM search_example WHERE logp>0,3
99            searchOpts.setFilterQuery("SELECT cd_id FROM " + TABLE_NAME + " WHERE "
00                    + columns[i] + ">" + thresholds[i]);
01            jcs.run();
02            int[] cdIDs = jcs.getResults();
03            System.out.println("Results using " + columns[i]);
04            SearchUtil.printSearchResults(cdIDs);
05        }
06    }
07
08}
09