Mining Google Web Services: Building Applications with the Google API

Google is an amazingly fast search engine. However, it can't beat the speed of a local database connection. In addition, Google limits the number of calls you can make to Google Web Services during a single day. These two reasons, among others, make database storage of Google data viable . Storing data locally means you can get fast responses for common searches and you'll make fewer requests of Google Web Services.

You'll need to work with this example in two parts . The first part handles the request from the database if possible. The second part makes the request from Google if the database doesn't contain the requested information or the information in the database is too old. Listing 8.4 shows the essential code for the first part of this example ”the listing isn't complete. You'll find the complete code for this example in the \Chapter 08\DatabaseStore folder of the source code located on the Sybex Web site. The macros and data to re-create the SQL database appears in the \Chapter 08\SQL Data folder of the source code located on the Sybex Web site.

Listing 8.4: Handling the Initial Request

// This class handles button click events. private class ButtonHandler implements ActionListener { public void actionPerformed(ActionEvent AE) { ... Variable Declarations ... // End the program. if (AE.getSource() == btnQuit) System.exit(0); // Issue a request and receive a response. if (AE.getSource() == btnTest) { // Initialize the variables. Scanned = new java.util.Date(); Now = new java.util.Date(); SearchNum = "0"; // Create an instance of the JDBC-ODBC Driver. try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } catch (java.lang.ClassNotFoundException e) { ... Handle Error ... } try { // Make a connection to the database. Con = DriverManager.getConnection("jdbc:odbc:StoreGoogle"); // Define an executable statement. Stmnt = Con.createStatement(); // Get a result set. RS = Stmnt.executeQuery( "SELECT * FROM SearchQueries WHERE SearchQuery='" + txtSearch.getText() + "'"); // Verify the data exists. if (!RS.next()) { // Make the request. GenerateRequest(); // Exit the routine. return; } else { // Get the SearchNumber value. SearchNum = RS.getString("SearchNumber"); // Get the scanned date. Scanned = RS.getDate("DateScanned"); // Convert the scanned date to a string. ConvDate = new SimpleDateFormat("dd MMMM, yyyy"); txtScanned.setText(ConvDate.format(Scanned)); // Add 24 hours to the scan date. TimeComp = Scanned.getTime(); TimeComp = TimeComp + 86400000; Scanned.setTime(TimeComp); // Close the result set. RS.close(); // If the data is too old. if (Now.after(Scanned)) { // Remove the old records. Stmnt.executeUpdate( "DELETE FROM SearchQueries WHERE SearchQuery='" + txtSearch.getText() + "'"); Stmnt.executeUpdate( "DELETE FROM Results WHERE SearchNumber='" + SearchNum + "'"); // Make the request. GenerateRequest(); // Return without processing the database. return; } } } catch (java.sql.SQLException e) { ... Handle Error ... } try { // Define an executable statement. Stmnt = Con.createStatement(); // Create a new query for the results. RS = Stmnt.executeQuery( "SELECT * FROM Results WHERE SearchNumber='" + SearchNum + "'"); // Clear the response table. for (int RCount = 0; RCount < 10; RCount++) for (int CCount = 0; CCount < 4; CCount++) tblResp.setValueAt("", RCount, CCount); // Process the records. Count = 0; while (RS.next()) { // Print out the information. tblResp.setValueAt(RS.getString("Title"), Count, 0); ... Other Table Entries ... // Go to the next row. Count++; } // Close the result set. RS.close(); } catch (java.sql.SQLException e) { ... Handle Error ... } } } }

The first step is to establish a connection with the database. As previously mentioned, this example relies on the JDBC-ODBC Bridge Driver to perform the required linkage between Java and SQL Server. The example shows a typical setup for this kind of database connection. Once the driver is instantiated , the code can use it to make the actual connection using the getConnection() method. The code also creates a standard statement with the execute-Query() method ”one that it can use for queries made up of executable SQL statements. The first statement returns a recordset, RS , when the database contains the information needed. If the RS.next() method fails, the code knows the data doesn't exist in the database and calls the GenerateRequest() method to request the information from Google. Because the GenerateRequest() method also fills the screen with data, the routine exits.

The example relies on two databases, SearchQueries, which stores the main node data, and Results, which stores the individual links. These two tables are joined with the SearchNumber field. Once the code retrieves a record for SearchQueries, it retrieves the SearchNumber field using the getString() method. The SearchNum string holds the data for later use in retrieving the links.

The example must also determine whether the data in the database is too old. It retrieves the DateScanned field as a date using the getDate() method. The Scanned.getTime() method converts the date to a long and adds 86,400,000 milliseconds to it (24 hours). You can use any comparison you see fit, or even allow the user to make the decision based on the date displayed. The Now.after(Scanned) method determines whether the data is too old. If it is, the example deletes the old records from the database and makes the request using GenerateRequest() .

At this point, the example knows that the database contains the required information and that the information isn't too old. It uses a technique similar to the one explained for Listing 8.2 to clear the current response table data and to display the new data on screen. Notice that this data comes from the Results table and that the code retrieves it using the executeQuery() method.

This discussion assumes that the database contains the required data. When the database can't provide the required information, the code must request it from Google Web Services. Listing 8.5 shows the request code.

Listing 8.5: Creating the Request and Performing the Database Update

public void GenerateRequest() { ... Variable Declarations ... ... Initialize the variables... try { // Create the required SOAP objects. Service = new GoogleSearchServiceLocator(); Port = Service.getGoogleSearchPort( new URL("http://api.google.com/search/beta2")); Result = Port.doGoogleSearch( txtKey.getText(), txtSearch.getText(), 0, 10, false, "", false, "", "", ""); } ... Handle Errors (See Listing 8.2 for Details) ... // Set the scanned date. Scanned = new java.util.Date(); // Convert the scanned date to a string. ConvDate = new SimpleDateFormat("dd MMMM, yyyy"); txtScanned.setText(ConvDate.format(Scanned)); // Format the released date for use with SQL Server. FormDate = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); // Create the SearchQueries record. try { // Create a prepared statement with variables for // value entries. You must include one variable for // each database column. PState = Con.prepareStatement("INSERT INTO SearchQueries " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); // The database entries are 1-based, not 0-based. Use // the correct set method for each entry. PState.setString(1, Result.getSearchQuery()); ... Other Prepared Statement Entries ... PState.executeUpdate(); // Close this statement. PState.close(); } catch (java.sql.SQLException e) { System.out.println("SearchQueries Update Error"); System.out.println(e); } // Get the search number of the newly created record. try { // Define an executable statement. Stmnt = Con.createStatement(); // Get a result set. RS = Stmnt.executeQuery( "SELECT * FROM SearchQueries WHERE SearchQuery='" + txtSearch.getText() + "'"); // Get the record. RS.next(); // Get the SearchNumber value. SearchNum = RS.getLong("SearchNumber"); // Close the result set. RS.close(); } catch (java.sql.SQLException e) { ... Handle Error ... } // Clear the response table. ... See Listing 8.4 for Details ... // Retrieve the result elements. Elements = Result.getResultElements(); // Output the result. for (int Count = 0; Count < Elements.length; Count++) { // Print out the information. tblResp.setValueAt( StringToText(Elements[Count].getTitle()), Count, 0); ... Other Response Table Entries ... // Create the Results record. try { // Make a connection to the database. Con2 = DriverManager.getConnection("jdbc:odbc:StoreGoogle"); // Create a prepared statement. PState = Con2.prepareStatement("INSERT INTO Results " + "VALUES (?, ?, ?, ?, ?, ?)"); // Use the correct set method for each entry. PState.setLong(1, SearchNum); ... Other Prepared Statement Entries ... PState.executeUpdate(); // Close the statement and the connection after each update. PState.close(); Con2.close(); } catch (java.sql.SQLException e) { ... Handle Error ... } } }

The Google Web Services request begins by creating the service and establishing a connection using a port through the getGoogleSearchPort() method. The code uses the resulting Port object to request the data from Google Web Services. On return from the call, Result contains the Google response in a series of variables, including some arrays. The code dissects this data and places the important information in the database.

One of the first tasks is to establish a scanning date. The code places the date in Scanned and also displays the information on screen. Notice the use for the ConvDate.format() method in this case. The code uses a similar technique to create a date in a format suitable for storage in SQL Server using the FormDate object.

You can interact with SQL Server using a number of techniques. However, when inserting a date into the database, you'll find the prepared statement technique shown here works exceptionally well because it can handle all kinds of data. The code uses the prepareStatement() method to create an insertion statement. It then uses various set methods , such as setString() , to fill the statement entries with data. Finally, the code makes the entry permanent using the executeUpdate() method.

Remember that the two tables are linked using the SearchNumber field. At this point, the code retrieves the SearchNumber value by making a query. You can't obtain this information in any other way. For this reason, you must make sure the queries in the database are unique or that you find some unique combination of entries to use to store the data. Otherwise, you can corrupt the linkage between the two tables.

The code processes the individual links next. The technique used is similar to the one in Listing 8.2. However, you also need to create the Results table entries. After the code outputs the link values to the response table, it creates a new connection to the database, creates a prepared statement, fills the statement with data, and executes the statement. The final step closes both the prepared statement and the database connection. If you don't perform this step, the example will fail with errors stating the connection is busy.