shopmanagesystembean.java

java 数据库编程源代码

      }
    }
    catch(Exception ex ) {
      ex.printStackTrace();
    }
    return rs;
  }

  public void delSingleCustomerDeal(String customerName)    //删除指定顾客的交易记录
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "delete from cart_table where customer_name='" + customerName + "'";    //创建删除指定顾客的交易记录的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 getProductList()               //获取库存物品记录的结果集
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "select * from product_table";      //创建获取库存商品记录的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 insertNewProduct(int id, String name, int price, int quantity)   //向库存商品中插入新商品记录
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "insert into product_table values("+id+",'"+name+"',"+price+","+quantity+")";      //创建向库存商品中插入新商品记录的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 boolean isProductExistedInOtherTable(int id)  //判断售出商品表和顾客交易表中是否存在涉及到库存商品表中的商品信息
  {
    boolean isExisted = false;
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "select * from sell_table where product_id=" + id;      //创建获取指定ID号的售出商品记录的SQL语句
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  //创建Statement接口实例
      rs = statement.executeQuery(sql);          //将数据存入结果集中
      if(rs.next())                    //如果结果集不为空，则售出商品表中存在涉及到库存商品表中的商品信息
      {
        isExisted = true;
        return isExisted;
      }
      sql = "select * from cart_table where product_id=" + id;      //创建获取指定ID号的交易商品记录的SQL语句
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  //创建Statement接口实例
      rs = statement.executeQuery(sql);          //将数据存入结果集中
      if(rs.next())                    //如果结果集不为空，则顾客交易表中存在涉及到库存商品表中的商品信息
      {
        isExisted = true;
        return isExisted;
      }
    }
    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 isExisted;
  }

  public void delProduct(int id)                   //删除指定的库存商品记录
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "delete from product_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 getProductInfo(int id)         //获取指定的库存商品记录的结果集
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "select * from product_table where product_id=" + id;    //创建获取指定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 updateProduct(int id, String name, float price, int quantity)       //更新指定的库存商品记录信息
  {
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:shop";                   //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      String sql = "update product_table set product_name='" + name + "', product_price=" + price + ",product_quantity=" + quantity +" 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();
    }
  }
}