SearchProducts.java

package com.oreilly.frdg;

import java.sql.*;
import java.io.Serializable;
import sun.jdbc.rowset.*;

public class SearchProducts implements Serializable{
  public SearchProducts() {}
  
  private String myDriverString = "sun.jdbc.odbc.JdbcOdbcDriver";
  private String myConnectionString = "jdbc:odbc:northwind";
  private String myUsername = "myUsername";
  private String myPassword = "myPassword";
  private Connection conn = null;
  
  public ResultSet getSearchResult(String search) throws Exception {
    String errors = "";
    CachedRowSet rowset = new CachedRowSet();
  
    try {
      Class.forName(myDriverString);
      conn = DriverManager.getConnection(
              myConnectionString, myUsername, myPassword);
    } catch (ClassNotFoundException e) {
      errors = "Incorrect JDBC Driver\n";
    }
  if(errors == "") {
    try{
      Statement s = conn.createStatement();
      String sql = "SELECT ProductName,UnitPrice,QuantityPerUnit FROM Products";
      if(search != "") {
        sql += " WHERE ProductName LIKE '%" + search + "%'";
      }
      ResultSet rs = s.executeQuery(sql);
      rowset.populate(rs);
      rs.close();
      s.close();      
      } catch(SQLException e) { //catch any SQL errors
        errors += e.toString() ;
      } finally {
        if(conn != null) {
          conn.close();
        }
      }
    }
    if (errors!="") {
      throw new Exception (errors) ;
    };
    return rowset;
  }
}