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
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 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 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 int cdId = cdIds[i];
75 ps.setInt(1, cdId);
76
77 ResultSet rs = ps.executeQuery();
79
80 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 }
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 ArrayList<String> fieldNames = new ArrayList<String>();
06 fieldNames.add("cd_formula");
07 fieldNames.add("cd_molweight");
08
09 ArrayList<Object[]> fieldValues = new ArrayList<Object[]>();
11
12 HitColoringAndAlignmentOptions displayOpts = null;
14
15 jcs.getHitsAsMolecules(cdIds, displayOpts, fieldNames, fieldValues);
17
18 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