goodsdata.java~1~

商品销售管理系统工程代码

package data;

import java.sql.*;

public class GoodsData {
  Connection conn = null;
  //取得数据库连接的方法
  public void setupConn(){
    try{
      if(conn == null){
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url =
            "jdbc:sqlserver://localhost;databaseName=LoginC1";
        //使用DriverManager类的getConnection()方法建立连接
        conn = DriverManager.getConnection(url, "sa", "1234");
      }
    }catch(Exception e){
      e.printStackTrace();
    }
  }
  //创建商品类别的方法
  public int createGoodsCategory(int parentId, String categoryName,
                                 String categoryDescription) {
    int result = 0;
    //建立连接
    setupConn();
    try {
      //倒序排列，使序号最大的记录成为第1条记录
      String selectSql = "select * from goodsCategory order by categoryId desc";
      String insertSql = "INSERT INTO goodsCategory VALUES(?, ?, ?, ?)";
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(selectSql);
      //创建序号变量
      int categoryId = 1;
      if(rs.next()){
        //新序号是最大序号加1
        categoryId = rs.getInt(1) + 1;
      }
      PreparedStatement insertGoods = conn.prepareStatement(insertSql);
      //设置添加商品类别的4个参数
      insertGoods.setInt(1, categoryId);
      insertGoods.setInt(2, parentId);
      insertGoods.setString(3, categoryName);
      insertGoods.setString(4, categoryDescription);
      //成功添加记录，返回1
      result = insertGoods.executeUpdate();
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //更新商品类别的方法
  public int updateGoodsCategory(int categoryId, int parentId,
                                 String categoryName,
                                 String categoryDescription) {
    int result = 0;
    //建立连接
    setupConn();
    try {
      //第1个SCROLL_SENSITIVE参数表示反映其它用户的更新操作
      //第2个CONCUR_UPDATABLE参数表示数据集可更新
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      String sql = "select * from goodsCategory where categoryId = " +
          categoryId;
      //取得要修改的记录
      ResultSet rs = stmt.executeQuery(sql);
      if(rs.next()){
        rs.updateInt(2, parentId);
        rs.updateString(3, categoryName);
        rs.updateString(4, categoryDescription);
        //向数据库提交更新记录
        rs.updateRow();
        result = 1;
      }
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //删除商品类别的方法
  public int deleteGoodsCategory(int categoryId) {
    int result = 0;
    //建立连接
    setupConn();
    try {
      Statement stmt = conn.createStatement();
      String sql1 = "select * from goods where categoryId = " + categoryId;
      //根据商品类别取得商品数据
      ResultSet rs = stmt.executeQuery(sql1);
      //只有商品类别没有商品数量才可以删除商品类别
      if(!rs.next()){
        String sql2 = "delete from goodsCategory where categoryId = " + categoryId;
        //删除记录
        result = stmt.executeUpdate(sql2);
      }
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //取得所有商品类别的方法
  public String[][] getAllGoodsCategory() {
    String[][] detail = new String[0][4];
    //建立连接
    setupConn();
    try{
      //CONCUR_READ_ONLY表示数据集不可更新
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                             ResultSet.CONCUR_READ_ONLY);
      String sqlCount = "select count(*) from goodsCategory";
      String sql = "select * from goodsCategory";
      //取得商品类别数据表的记录总数
      ResultSet rs = stmt.executeQuery(sqlCount);
      int count = 0;
      if(rs.next()){
        count = rs.getInt(1);
      }
      //重新创建数组
      detail = new String[count][4];
      //取得商品类别数据表的全部记录
      rs = stmt.executeQuery(sql);
      int i = 0;
      //将记录加入数组
      while (rs.next()) {
        for(int j = 0; j < 4; j++){
          detail[i][j] = rs.getString(j + 1);
        }
        i++;
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return detail;
  }
  //创建商品的方法
  public int createGoods(String[] goods) {
    int result = 0;
    if(goods.length != 13){
      return result;
    }
    //建立连接
    setupConn();
    try {
      //尝试根据商品类别标识取得商品类别记录
      String selectSql = "select * from goodsCategory where categoryId = " + goods[1];
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(selectSql);
      //商品类别数据表存在商品类别记录才可以进行添加商品操作
      if(rs.next()){
        String insertSql = "INSERT INTO goods VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
            + "?, ?, ?)";
        //创建PreparedStatement接口
        PreparedStatement insertGoods = conn.prepareStatement(insertSql);
        //设置添加商品的13个参数, 数字类型可以应用设置字符串方法
        for(int i = 0; i < 13; i++){
          insertGoods.setString(i + 1, goods[i]);
        }
        //成功添加记录，返回1
        result = insertGoods.executeUpdate();
      }
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //更新商品的方法
  public int updateGoods(String[] goods) {
    int result = 0;
    if(goods.length != 13){
      return result;
    }
    //建立连接
    setupConn();
    try {
      //第1个SCROLL_SENSITIVE参数表示反映其它用户的更新操作
      //第2个CONCUR_UPDATABLE参数表示数据集可更新
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      String sql = "select * from goods where goodsBarCode = '" +
          goods[0] + "'";
      //取得要修改的记录
      ResultSet rs = stmt.executeQuery(sql);
      //商品条形码是主键，不进行修改
      if(rs.next()){
        for (int i = 1; i < 13; i++) {
          rs.updateString(i + 1, goods[i]);
        }
        //向数据库提交更新记录
        rs.updateRow();
        result = 1;
      }
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //删除商品的方法
  public int deleteGoods(String goodsBarCode) {
    int result = 0;
    //建立连接
    setupConn();
    try {
      Statement stmt = conn.createStatement();
      String sql = "delete from goods where goodsBarCode = '" + goodsBarCode + "'";
      //删除记录
      result = stmt.executeUpdate(sql);
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //根据类别取得商品的方法
  public String[][] getGoodsByGoodsCategory(int categoryId){
    String[][] detail = new String[0][13];
    //建立连接
    setupConn();
    try {
      Statement stmt = conn.createStatement();
      String sql1 = "select count(*) from goods where categoryId = " + categoryId;
      String sql2 = "select * from goods where categoryId = " + categoryId;
      //根据商品类别取得商品总数
      ResultSet rs = stmt.executeQuery(sql1);
      if(rs.next()){
        //重新创建数组
        detail = new String[rs.getInt(1)][13];
      }
      //根据商品类别取得商品
      rs = stmt.executeQuery(sql2);
      int i = 0;
      while (rs.next()) {
        for(int j = 0; j < 13; j++){
          //将商品记录写入数组
          detail[i][j] = rs.getString(j + 1);
        }
        i++;
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return detail;
  }
  //根据字段取得商品的方法
  public String[][] getGoodsByField(String fieldName, String value){
    String[][] detail = new String[0][13];
    //建立连接
    setupConn();
    try{
      //CONCUR_READ_ONLY表示数据集不可更新
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                             ResultSet.CONCUR_READ_ONLY);
      //%表示任意字符,%商品%表示查询包括"商品"字符串的所有记录
      String sqlCount = "select count(*) from goods where " + fieldName +
          " like '%" + value + "%'";
      String sql = "select * from goods where " + fieldName +
          " like '%" + value + "%'";
      //取得记录总数
      ResultSet rs = stmt.executeQuery(sqlCount);
      int count = 0;
      if(rs.next()){
        count = rs.getInt(1);
      }
      //重新创建数组
      detail = new String[count][13];
      //取得商品数据表的全部记录
      rs = stmt.executeQuery(sql);
      int i = 0;
      //将记录加入数组
      while (rs.next()) {
        for(int j = 0; j < 13; j++){
          detail[i][j] = rs.getString(j + 1);
        }
        i++;
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return detail;
  }
  //取得折扣商品的方法
  public String[][] getDiscountGoods() {
    String[][] detail = new String[0][13];
    //建立连接
    setupConn();
    try {
      Statement stmt = conn.createStatement();
      String sql1 = "select count(*) from goods where discount < 1";
      String sql2 = "select * from goods where discount < 1";
      //取得折扣商品记录总数
      ResultSet rs = stmt.executeQuery(sql1);
      if(rs.next()){
        //重新创建数组
        detail = new String[rs.getInt(1)][13];
      }
      //取得折扣商品记录
      rs = stmt.executeQuery(sql2);
      int i = 0;
      while (rs.next()) {
        for(int j = 0; j < 13; j++){
          //将商品记录写入数组
          detail[i][j] = rs.getString(j + 1);
        }
        i++;
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return detail;
  }
  //设置商品价格和折扣的方法
  public int setGoodsPriceDiscount(String goodsBarCode, double salePrice,
                                   double discount) {
    int result = 0;
    //建立连接
    setupConn();
    try {
      //创建更新商品价格和折扣的SQL语句
      String sql = "update goods set salePrice = ?, discount = ? " +
          "where goodsBarCode = '" + goodsBarCode + "'";
      PreparedStatement updatePS = conn.prepareStatement(sql);
      //设置价格和折扣
      updatePS.setDouble(1, salePrice);
      updatePS.setDouble(2, discount);
      result = updatePS.executeUpdate();
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //设置商品库存的方法
  public int setGoodsStock(String goodsBarCode, int upperLimit,
                                   int lowerLimit) {
    int result = 0;
    //建立连接
    setupConn();
    try {
      //创建更新商品库存的SQL语句
      String sql = "update goods set upperLimit = ?, lowerLimit = ? " +
          "where goodsBarCode = '" + goodsBarCode + "'";
      PreparedStatement updatePS = conn.prepareStatement(sql);
      //设置库存
      updatePS.setInt(1, upperLimit);
      updatePS.setInt(2, lowerLimit);
      result = updatePS.executeUpdate();
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //检查是否是整数的方法
  public int checkInt(String str){
    int result = 0;
    try{
      Integer.parseInt(str);
      result = 1;
    }catch(Exception ex){ }
    return result;
  }
  //检查数字是否大于0小于等于1的方法
  public int checkNumIn0To1(String str){
    int result = 0;
    try{
      double num = Double.parseDouble(str);
      if(num <= 1 & num > 0){
        result = 1;
      }
    }catch(Exception ex){ }
    return result;
  }
  //检查是否是小数的方法
  public int checkDouble(String str){
    int result = 0;
    try{
      Double.parseDouble(str);
      result = 1;
    }catch(Exception ex){ }
    return result;
  }
}