searchformbean.java

java数据库编程源码

package JavaDatabaseBible.ch15;

import java.sql.*;
import javax.sql.*;

public class SearchFormBean extends java.lang.Object{
  private static String dbUserName = "sa";
  private static String dbPassword = "dba";

  protected int yearMin;
  protected int yearMax;
  protected int price;
  protected String id;
  protected String year;
  protected String make;
  protected String model;
  protected String body;
  protected String engine;
  protected String transmission;
  protected String zip;
  
  protected int index = 0;
  protected int pageSize = 5;
  protected int rowCount = 0;
	protected ResultSet rs = null;
	
  public SearchFormBean(){
  }
  public void setYearMin(int yearMin){
    this.yearMin = yearMin;
  }
  public void setYearMax(int yearMax){
    this.yearMax = yearMax;
  }
  public void setMake(String make){
    this.make = make;
  }
  public void setZip(String zip){
    this.zip = zip;
  }
  public void setModel(String model){
    this.model = model;
  }
  public void setBody(String body){
    this.body = body;
  }
  public void setEngine(String engine){
    this.engine = engine;
  }
  public void setTransmission(String transmission){
    this.transmission = transmission;
  }
  public void setPrice(int price){
    this.price = price;
  }
  public String getId(){
    return id;
  }
  public String getYear(){
    return year;
  }
  public String getMake(){
    return make;
  }
  public String getZip(){
    return zip;
  }
  public String getmodel(){
    return model;
  }
  public String getBody(){
    return body;
  }
  public String getEngine(){
    return engine;
  }
  public String getTransmission(){
    return transmission;
  }
  public int getPrice(){
    return price;
  }
  public int getIndex(){
    return index;
  }
  public int getRowCount(){
    return rowCount;
  }
  public String getPage(){
    return ""+(index/pageSize+1)+" of "+(rowCount/pageSize+1);
  }
  public boolean pageForward(){
    boolean validRow = false;
    if(index<0||index+pageSize>rowCount){
      index=0;
    }else{
      index += pageSize;
    }
    try {
      validRow = rs.absolute(index+1);
    }catch(SQLException e){
      System.err.println(e.getMessage());
    }
    return validRow;
  }
  public boolean pageBack(){
    boolean validRow = false;
    if(index<pageSize){
      index=rowCount/pageSize*pageSize;
    }else if(index>=pageSize){
      index -= pageSize;
    }
    try {
      validRow = rs.absolute(index);
    }catch(SQLException e){
      System.err.println(e.getMessage());
    }
    return validRow;
  }
  public boolean selectRow(int row){
    boolean validRow = false;
    try {
      validRow = rs.absolute(index+1);
      if(validRow){
        if(row > 0)validRow = rs.relative(row);
        if(rs.getRow()<0)validRow=false;
        
        if(validRow){
          id = rs.getString("memberID");
          year = rs.getString("year");
          make = rs.getString("make");
          zip = rs.getString("zip");
          model = rs.getString("model");
          body = rs.getString("body");
          engine = rs.getString("engine");
          transmission = rs.getString("transmission");
          price = rs.getInt("price");
        }
      }
    }catch(SQLException e){
      System.err.println(e.getMessage());
    }
    return validRow;
  }
  /*
  getMatches uses the stored procedure SEARCH:
  
  CREATE PROCEDURE SEARCH @BODY VARCHAR(50), 
  @ZIP VARCHAR(10), @MAKE VARCHAR(50), 
  @MODEL VARCHAR(50), @ENGINE VARCHAR(50), 
  @TRANSMISSION VARCHAR(50), @PRICE INT, @YEAR1 INT, 
  @YEAR2 INT AS SELECT TOP 50 *
             FROM VEHICLES
             WHERE BODY LIKE @BODY AND 
                  ZIP LIKE @ZIP AND MAKE LIKE @MAKE AND 
                  MODEL LIKE @MODEL AND 
                  ENGINE LIKE @ENGINE AND 
                  TRANSMISSION LIKE @TRANSMISSION AND 
                  PRICE <= @PRICE AND YEAR BETWEEN 
                  @YEAR1 AND @YEAR2;
  */
  public int getMatches(){
    try {
      Class.forName("com.inet.pool.PoolDriver");
      com.inet.tds.TdsDataSource tds = new com.inet.tds.TdsDataSource();
      //tds.setServerName( "JUPITER" );
      //tds.setDatabaseName( "MEMBERS" );
      tds.setServerName( "MARS" );
      tds.setDatabaseName( "CONTACTS" );
      tds.setUser( dbUserName );
      tds.setPassword( dbPassword );

      DataSource ds = tds;
      Connection con = ds.getConnection(dbUserName,dbPassword);
      
      CallableStatement cs = con.prepareCall("{call SEARCH(?,?,?,?,?,?,?,?)}");
      cs.setString(1,body);
      cs.setString(2,zip);
      cs.setString(3,make);
      cs.setString(4,model);
      cs.setString(7,engine);
      cs.setString(5,transmission);
      cs.setInt   (6,price);
      cs.setInt   (7,yearMin);
      cs.setInt   (8,yearMax);
      
      rs = cs.executeQuery();
      rs.last();
      rowCount = rs.getRow();
      con.close();
    }catch(ClassNotFoundException e1){
      System.err.println(e1.getMessage());
    }catch(SQLException e2){
      System.err.println(e2.getMessage());
    }
    return rowCount;
  }
}