selectbean.java~2~

一个简单的表单的实现

package com.hygj.sunqi.j2ee.bean;

import java.sql.*;
import java.util.*;
public class SelectBean {

  private String employeeID;
  private ArrayList data = new ArrayList();
  private ArrayList cell = null;
  private String classForName = "sun.jdbc.odbc.JdbcOdbcDriver";
  private String url = "jdbc:odbc:Northwind";
  private Connection conn = null;
  private PreparedStatement pstmt = null;
  private ResultSet rs = null;
  private String sql = "SELECT dbo.Orders.OrderID, dbo.Products.ProductName, dbo.[Order Details].UnitPrice,  "
                       +"dbo.[Order Details].Quantity "
                       +"FROM dbo.Products INNER JOIN "
                       +"dbo.[Order Details] ON  "
                       +"dbo.Products.ProductID = dbo.[Order Details].ProductID INNER JOIN "
                       +"dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID "
                       +"WHERE dbo.Orders.EmployeeID=?";

  public SelectBean() {
  }
  public String getEmployeeID() {
    return employeeID;
  }
  public void setEmployeeID(String employeeID) {
    this.employeeID = employeeID;
  }
  public ArrayList getData(){
    this.exec();
    return data;
  }
  private void exec(){
    try{
      Class.forName(classForName);
      conn = DriverManager.getConnection(url);
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1,employeeID);
      rs = pstmt.executeQuery();
      while(rs.next()){
        cell = new ArrayList();
        cell.add(new Integer(rs.getInt("OrderID")));
        cell.add(rs.getString("ProductName"));
        cell.add(new Float(rs.getFloat("UnitPrice")));
        cell.add(new Float(rs.getFloat("Quantity")));
        float total = (rs.getFloat("UnitPrice"))*(rs.getFloat("Quantity"));
        cell.add(new Float(total));
        data.add(cell);
      }
      rs.close();
      pstmt.close();
      conn.close();
    }catch(Exception e){
      System.out.println(e.getMessage());
    }
  }
}