1 package search.db;
2 
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.sql.Statement;
7 import java.util.Random;
8 
9 import util.ConnectionUtil;
10import util.SearchUtil;
11import util.TableOperations;
12import chemaxon.jchem.db.JChemSearch;
13import chemaxon.sss.SearchConstants;
14import chemaxon.sss.search.JChemSearchOptions;
15import chemaxon.util.ConnectionHandler;
16
17/**
18 * Example codes for filtering search results based on other (possibly not chemical) database
19 * tables.
20 * 
21 * @author JChem Base team, ChemAxon Ltd.
22 */
23public final class SearchWithFilterQueryExample {
24
25    private static final String TABLE_NAME = "demo";
26
27    private static final String STOCK_TABLE_NAME = "stock";
28    private static final int MAX_QUANTITY = 10;
29
30    private ConnectionHandler connHandler;
31
32    public static void main(String[] args) {
33        try {
34            new SearchWithFilterQueryExample().run();
35        } catch (Exception e) {
36            e.printStackTrace();
37        }
38    }
39
40    private void run() throws Exception {
41        connHandler = ConnectionUtil.connectToDB();
42        try {
43            TableOperations.setupMoleculeTable(connHandler, TABLE_NAME);
44            createPopulateStockTable(connHandler);
45            search();
46        } finally {
47            ConnectionUtil.closeConnection(connHandler);
48        }
49    }
50
51    private void search() throws Exception {
52
53        JChemSearchOptions searchOpts = new JChemSearchOptions(SearchConstants.SUBSTRUCTURE);
54        JChemSearch jcs = SearchUtil.createJChemSearch(connHandler, "Brc1ccccc1", TABLE_NAME,
55                searchOpts);
56
57        jcs.run();
58        SearchUtil.printSearchResults(jcs.getResults());
59
60        // Include into the substructure search only the substances of which we have
61        // less than 3 (grams) in stock
62        jcs.getSearchOptions().setFilterQuery("SELECT cd_id FROM " + STOCK_TABLE_NAME
63                + " WHERE quantity < 3");
64
65        jcs.run();
66        SearchUtil.printSearchResults(jcs.getResults());
67    }
68
69    /**
70     * Creates a table which holds the amount in stock for each structure in the structure
71     * table. The stock table is created such that it can be joined with the structure table
72     * through the cd_id column.
73     */
74    private void createPopulateStockTable(ConnectionHandler connHandler) throws SQLException {
75
76        System.out.println("Setting up stock table... ");
77
78        Statement stmt = connHandler.getConnection().createStatement();
79        try {
80            String sql = "DROP TABLE " + STOCK_TABLE_NAME;
81            stmt.execute(sql);
82        } catch (SQLException sqlException) {
83            // The stock table doesn't exist yet
84        } finally {
85            stmt.close();
86        }
87        stmt = connHandler.getConnection().createStatement();
88        try {
89            String sql = "CREATE TABLE " + STOCK_TABLE_NAME
90                    + " (cd_id NUMERIC(10,0), quantity NUMERIC(10,2))";
91            stmt.execute(sql);
92        } finally {
93            stmt.close();
94        }
95
96        stmt = connHandler.getConnection().createStatement();
97        try {
98            String sql = "SELECT cd_id FROM " + TABLE_NAME;
99            ResultSet rs = stmt.executeQuery(sql);
00
01            Random r = new Random(System.currentTimeMillis());
02            String stockPopulatorSql = "INSERT INTO " + STOCK_TABLE_NAME
03                    + " (cd_id, quantity) VALUES(?, ?)";
04            PreparedStatement ps =
05                    connHandler.getConnection().prepareStatement(stockPopulatorSql);
06            try {
07                while (rs.next()) {
08                    int cdId = rs.getInt(1);
09                    float qOnStock = r.nextInt(10 * MAX_QUANTITY) / 10F;
10                    ps.setInt(1, cdId);
11                    ps.setFloat(2, qOnStock);
12                    ps.execute();
13                }
14            } finally {
15                rs.close();
16                ps.close();
17            }
18        } finally {
19            stmt.close();
20        }
21    }
22
23}
24