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
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 if (UpdateHandler.isStructureTable(connHandler, TABLE_NAME)) {
60 UpdateHandler.dropStructureTable(connHandler, TABLE_NAME);
61 }
62
63 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 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 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