shopmanagesystembean.java

java 数据库编程源代码

package shopmanagesystem;

/**
 * Title:			商店购物管理系统的完整实现
 * Description:			教学示范
 * Copyright:			Copyright (c) 2003
 * Company:			北京师范大学计算机系
 * @author			孙一林
 * @version			1.0
 */

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

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

  private String name;                                 //定义登录系统的用户名
  private String password;                             //定义登录系统的用户密码

  public shopManageSystemBean() {
  }

  public void setUserInfo(String name, String password)  //当用户登录成功后记录用户的用户名和密码
  {
    this.name = name;
    this.password = password;
  }

  public String getUserName()                          //获取用户登录的用户名
  {
    return this.name;
  }

  public String getUserPassword()                      //获取用户登录的用户密码
  {
    return this.password;
  }

  public boolean isNameExisted(String name)            //判断用户登录使用的用户名是否存在
  {
    boolean existed = false;
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "select * from manager_table where name='" + name + "'";  //创建获取用户名的SQL语句
      Statement statement = connection.createStatement();  //创建Statement接口实例
      ResultSet rs = statement.executeQuery(sql);          //将数据存入结果集中
      if(rs.next())                                        //如果结果集不为空，则用户登录使用的用户名存在
      {
        existed = true;
      }
    }
    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 existed;
  }

  public boolean isPassRight(String name, String password)     //判断用户登录使用的用户密码是否正确
  {
    boolean isRight = false;
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "select * from manager_table where name='" + name + "' and password='" + password + "'";  //创建获取相对于用户名的用户密码的SQL语句
      Statement statement = connection.createStatement();  //创建Statement接口实例
      ResultSet rs = statement.executeQuery(sql);          //将数据存入结果集中
      if(rs.next())                                        //如果结果集不为空，则用户登录使用的用户密码正确
      {
        isRight = true;
      }
    }
    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 isRight;
  }

  public ResultSet getProductSold()                       //获取售出物品记录的结果集
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "select sell_table.product_id,sell_table.product_price,sell_table.sell_number,sell_table.sell_profit,product_table.product_name from sell_table,product_table where sell_table.product_id=product_table.product_id";  //创建获取售出商品记录的SQL语句
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  //创建Statement接口实例
      rs = statement.executeQuery(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();
    }
    return rs;
  }

  public void delSoldProduct(int id)                   //删除指定的售出商品记录
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "delete from sell_table where product_id=" + id;    //创建删除指定ID号的售出商品记录的SQL语句
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);  //创建Statement接口实例
      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();
    }
  }

  public ResultSet getDealInfo()                       //获取顾客交易记录的结果集
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "select cart_table.product_id,cart_table.buy_number,cart_table.customer_name,cart_table.customer_address";
      sql+=",product_table.product_name from cart_table,product_table where cart_table.product_id=product_table.product_id";    //创建获取顾客交易记录的SQL语句
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  //创建Statement接口实例
      rs = statement.executeQuery(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();
    }
    return rs;
  }

  public ResultSet getSingleDeal(String customerName)    //获取指定顾客的交易记录的结果集
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "select cart_table.product_id,cart_table.buy_number,cart_table.customer_name,cart_table.customer_address";
      sql+=",product_table.product_name from cart_table,product_table where cart_table.product_id=product_table.product_id and cart_table.customer_name='" + customerName + "'";    //创建获取指定顾客交易记录的SQL语句
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  //创建Statement接口实例
      rs = statement.executeQuery(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();