java—如果产品已经输入数据库，如何只增加数据库中的数量？

nnt7mjpx 于 2021-07-11 发布在 Java

关注(0)|答案(2)|浏览(271)

我已经创建了一个食品订购系统，它有5个不同的jframe，它们有自己的类别，比如膳食、快餐、开胃菜、饮料。每个类别都有弹出窗口，每个食物是另一个jframe。在这里，当客户选择所需的饭菜时，它将被添加到数据库中。
我已经为数据库创建了一个查询，从弹出菜单中选择不同的膳食时，这些项目将被输入数据库。这是完美的工作。
但是，如果产品描述存在，我只想更新数量。例如，如果数据库中有奶酪汉堡，我只想增加数量。我该怎么做？

public void  InsertOrderDetails(){
         String Insert;
         String Update;
         int i = Integer.parseInt(DBqty);
         BigDecimal Totals=new BigDecimal(DBtotalPrice);
         BigDecimal TotalValue=new BigDecimal(Total);
            try
            {    
             //Opening database for connection
            conn = DriverManager.getConnection(connectionUrl, username, Pass);
            Statement st=conn.createStatement();
            String sql="SELECT * FROM SALESORDER WHERE ProductDescription ='" + ProductDescription +"'";
            ResultSet rs=st.executeQuery(sql);
            if(rs.next()){
            int quan =i+ qty;
            BigDecimal totaly = Totals.add(TotalValue);
             Update="UPDATE SALESORDER SET Quantity=? ,TotalPrice=? WHERE ProductDescription ='" + ProductDescription +"'";
             PreparedStatement pstmt = conn.prepareStatement(Update);
             pstmt.setInt(1, quan);
             pstmt.setBigDecimal(2, totaly);
             pstmt.executeUpdate();
             pstmt.close();
             JOptionPane.showMessageDialog(null,"Sucessfully Added to plate");
            }

           else{   

             Insert="INSERT INTO SalesOrder (CustID,ProductDescription,Quantity,TotalPrice) VALUES (?,?,?,?)";
             PreparedStatement pstmt = conn.prepareStatement(Insert);
             pstmt.setInt(1,CustID);
             pstmt.setString(2, ProductDescription);
             pstmt.setInt(3, qty);
             pstmt.setBigDecimal(4, TotalValue);
             pstmt.executeUpdate();
             pstmt.close();
             JOptionPane.showMessageDialog(null,"Sucessfully Added to plate");
            }
            }
            catch(SQLException e){
                   JOptionPane.showMessageDialog(null,"Something went wrong\n");
                 e.printStackTrace();

        }
            finally{
                try {

                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(POPUP_Message_FriedRice.class.getName()).log(Level.SEVERE, null, ex);
                }} 
       }

Java sql jdbc swing

来源：https://stackoverflow.com/questions/64894672/how-to-only-increase-quantity-in-database-if-the-product-is-already-entered-to-t

2条答案

按热度按时间

nwlqm0z11#

Statement.executeUpdate 返回一个 int 这是受影响的行数，使用此字段可以了解是否存在记录。
所以你的代码变成了：

public static final String UPDATE_QUERY = "UPDATE SALESORDER SET Quantity = Quantity + ?, TotalPrice = TotalPrice + ? WHERE ProductDescription = ? and CustId = ?";
public static final String INSERT_STATEMENT = "INSERT INTO SalesOrder (CustID,ProductDescription,Quantity,TotalPrice) VALUES (?,?,?,?)";

public void InsertOrderDetails() {
    try (Connection connection = DriverManager.getConnection(connectionUrl, username, password)) {
        //Opening database for connection
        int affectedRows;
        try (PreparedStatement updateStatement = connection.prepareStatement(UPDATE_QUERY)) {
            updateStatement.setInt(1, quantity);
            updateStatement.setBigDecimal(2, itemPrice);
            updateStatement.setString(3, productDescription);
            updateStatement.setInt(4, customerId);

            affectedRows = updateStatement.executeUpdate();
        }

        if (affectedRows > 0) {
            JOptionPane.showMessageDialog(null, "Sucessfully Added to plate");
        } else {
            try (PreparedStatement insertStatement = connection.prepareStatement(INSERT_STATEMENT)) {
                insertStatement.setInt(1, customerId);
                insertStatement.setString(2, productDescription);
                insertStatement.setInt(3, quantity);
                insertStatement.setBigDecimal(4, itemPrice);
                insertStatement.executeUpdate();
            }

            JOptionPane.showMessageDialog(null, "Sucessfully Added to plate");
        }
    } catch (SQLException e) {
        JOptionPane.showMessageDialog(null, "Something went wrong\n");
        e.printStackTrace();
    }
}

我在这里使用java7 try with resource来删除一些重复的代码。

赞(0）回复(0）举报 2021-07-11

bgibtngc2#

public void  InsertOrderDetails(){
     String Insert;
     String Update;
     BigDecimal TotalValue=new BigDecimal(Total);
        try
        {    
         //Opening database for connection
        conn = DriverManager.getConnection(connectionUrl, username, Pass);
        Statement st=conn.createStatement();
        String sql="SELECT * FROM SALESORDER WHERE ProductDescription ='" + ProductDescription +"'";
        ResultSet rs=st.executeQuery(sql);
        if(rs.next()){
         Update="update SALESORDER set Quantity = Quantity + ?, TotalPrice = TotalPrice + ? where ProductDescription = ?";
         PreparedStatement pstmt = conn.prepareStatement(Update);
         pstmt.setInt(1,qty);
         pstmt.setBigDecimal(2,TotalValue);
         pstmt.setString(3,ProductDescription);
         pstmt.executeUpdate();
         pstmt.close();
         JOptionPane.showMessageDialog(null,"Sucessfully Added to plate");
        }

       else{   

         Insert="INSERT INTO SalesOrder (CustID,ProductDescription,Quantity,TotalPrice) VALUES (?,?,?,?)";
         PreparedStatement pstmt = conn.prepareStatement(Insert);
         pstmt.setInt(1,CustID);
         pstmt.setString(2, ProductDescription);
         pstmt.setInt(3, qty);
         pstmt.setBigDecimal(4, TotalValue);
         pstmt.executeUpdate();
         pstmt.close();
         JOptionPane.showMessageDialog(null,"Sucessfully Added to plate");
        }
        }
        catch(SQLException e){
               JOptionPane.showMessageDialog(null,"Something went wrong\n");
             e.printStackTrace();

    }
        finally{
            try {

                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(POPUP_Message_FriedRice.class.getName()).log(Level.SEVERE, null, ex);
            }} 
   }

赞(0）回复(0）举报 2021-07-11

我来回答

java—如果产品已经输入数据库，如何只增加数据库中的数量？

2条答案

相关问题

热门标签

最新问答