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
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 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
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 } 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