shoppingsystembean.java

java 数据库编程实例 经过反复调试 保证可以运行 是学习java网络编程的绝佳资料

package shoppingsystem;

/**
 * Title:			使用JSP与JavaBean创建购物车
 * Description:			教学示范
 * Copyright:			Copyright (c) 2003
 * Company:			北京师范大学计算机系
 * @author			孙一林
 * @version			1.0
 */

import java.io.*;
import java.sql.*;
import java.util.*;

public class shoppingSystemBean {
  private Connection connection = null;                //定义与数据库进行连接的Connection对象
  private Statement statement = null;                  //定义查询数据库的Statement对象
  private ResultSet rs = null;                         //定义数据库查询的结果集

  public shoppingSystemBean() {
  }

  public ResultSet getProduct()                       //获取库存商品的信息
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      statement = connection.createStatement();        //创建Statement接口实例
      String sql = "select * from product_table";      //创建取出库存商品表中所有数据的SQL语句
      rs = statement.executeQuery(sql);                //获取结果集信息
      return rs;
    }
    catch(SQLException ex){                            //捕捉异常
      System.out.println("\nERROR:----- SQLException -----\n");
      while (ex != null) {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
        ex = ex.getNextException();
      }
    }
    catch(Exception ex ) {
      ex.printStackTrace();
    }
    return null;
  }

  public float getPrice(int p_id)                  //根据商品ID获取相应商品的价格
  {
    float price = 0;
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);    //创建Statement接口实例
      String sql = "select product_price from product_table where product_id = " + p_id;      //创建取出指定ID号的商品价格的SQL语句
      rs = statement.executeQuery(sql);      //获取结果集信息
      rs.first();
      price = rs.getFloat("product_price");            //获取指定ID号商品的价格
      return price;
    }
    catch(SQLException ex){                            //捕捉异常
      System.out.println("\nERROR:----- SQLException -----\n");
      while (ex != null) {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
        ex = ex.getNextException();
      }
    }
    catch(Exception ex ) {
      ex.printStackTrace();
    }
    return price;
  }

  public String trimID(String str)           //从字符串中获取商品ID
  {
    String tmp = "";
    int idx = str.indexOf(':');
    tmp = str.substring(idx+1);
    return tmp;
  }

  public String trimName(String str)         //从字符串中获取商品名称
  {
    String tmp = "";
    int idx = str.indexOf('(');
    tmp = str.substring(0,idx);
    return tmp;
  }

  public void sealDeal(int id, String name, String addr)    //将交易结果存入数据库
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);    //创建Statement接口实例
      String sql = "select * from product_table where product_id = " + id;      //创建取出指定ID号的商品价格的SQL语句
      rs = statement.executeQuery(sql);      //获取结果集信息
      rs.first();
      int p_quantity = rs.getInt("product_quantity");  //将库存表中指定ID号的商品的数量减一
      p_quantity--;
      statement.close();
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);     //创建Statement接口实例
      sql = "update product_table set product_quantity = " + p_quantity + " where product_id = " + id;      //创建更新指定ID号的商品数量的SQL语句
      statement.executeUpdate(sql);      //更新指定ID号的库存商品数量
      statement.close();
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);     //创建Statement接口实例
      sql = "insert into cart_table (product_id, customer_name, customer_address) values (" + id + ", '" + name + "', '" + addr + "')";    //创建向顾客交易表中插入新数据的SQL语句
      statement.executeUpdate(sql);      //向顾客交易表中插入新的交易记录
    }
    catch(SQLException ex){                            //捕捉异常
      System.out.println("\nERROR:----- SQLException -----\n");
      while (ex != null) {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
        ex = ex.getNextException();
      }
    }
    catch(Exception ex ) {
      ex.printStackTrace();
    }
  }
}