java - 使用 Java 更改 mysql 列

Question

我有一个作业，必须使用 Java 更新 mysql 中的列。我已经建立了连接等...

我有一个包含 4 列的表格:ID、产品名称、价格和数量

我希望能够通过单击 Java 应用程序上的按钮来仅更改一种产品的数量。 示例:

ID|ProductName|Price|Quantity
_____________________________
1 |  Water    | 20  |30

2 |  Soda     | 25  |30

3 |  Juice    | 25  |30

单击按钮时，我只想将苏打水的数量更改为 29，如果我再次按下按钮，它将减少到 28，而水和果汁将保持 30。我如何在 Java 中使用JButton 并选择该单一产品(而不是所有数量)..？

编辑:两次点击后，它应该如下所示:

ID|ProductName|Price|Quantity
_____________________________
1 |  Water    | 20  |30

2 |  Soda     | 25  |28

3 |  Juice    | 25  |30

** 请求的代码:

   public void updateMethod(){
    Connection conn = null;
    try {
        String userName = "root";
        String passWord = "";
        String dataBase = "database";
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/"+dataBase, userName, passWord);
        if (!conn.isClosed())
        {
            Statement s = conn.createStatement ( );
            s.executeQuery ("SELECT * FROM `database`.Drinks");
            String query = "UPDATE Drinks SET quantity = quantity - 1 WHERE productName = 'Soda' and quantity > 0";
            conn.setAutoCommit(false);
            PreparedStatement pstmt = conn.prepareStatement(query);
   //This one did not work.. pstmt.setString(1, "Soda");
            int result = pstmt.executeUpdate();
            conn.commit();
            ResultSet rs = s.getResultSet ( );
            while (rs.next()) // loop through rows of result set
            {
                int DrinkID = rs.getInt(1);
                String productName = rs.getString(2);
                int price = rs.getInt(3);
                int quantity = rs.getInt(4);

                System.out.println ("ID: " + DrinkID + "\t" + "Product: " + productName + " " + "Price: " + price + " " + "Quantity: " + quantity);
            }
            rs.close(); // close result set
            s.close(); // close statement
        }
    }
    catch (Exception e)
    {
        // If we don't have a error, close the connection!
        System.err.println("Exception: " + e.getMessage());

    }
    finally
    {
        try
        {
            if (conn != null)
            {
                conn.close();
            }
        }
        catch (SQLException e)
        {

        }
    }

Answer 1

您将根据产品名称执行 SQL:

UPDATE product_table
SET Quantity = Quantity - 1
WHERE ProductName = 'Soda'
and Quantity > 0

这会将数量值减 1。

这是有关如何在 java 中执行 sql 查询的指南 - http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

编辑:添加代码以执行上面的更新查询:

String query = "UPDATE product_table SET Quantity = Quantity - 1 WHERE ProductName = ? and Quantity > 0";

conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "Soda");

int result = pstmt.executeUpdate();
conn.commit();

编辑:添加完整的示例代码

public Connection getConnection() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
    String userName = "root";
    String passWord = "";
    String dataBase = "database";
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    return DriverManager.getConnection("jdbc:mysql://localhost:3306/" + dataBase, userName, passWord);
}

public void print() {

    Connection conn = null;

    try {
        conn = getConnection();

        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM `database`.Drinks");

        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            int DrinkID = rs.getInt(1);
            String productName = rs.getString(2);
            int price = rs.getInt(3);
            int quantity = rs.getInt(4);

            System.out.println("ID: " + DrinkID + "\t" + "Product: " + productName + " " + "Price: " + price + " " + "Quantity: " + quantity);

        }
        rs.close();
        pstmt.close();

    } catch (Exception e) {
        // If we don't have a error, close the connection!
        System.err.println("Exception: " + e.getMessage());

    } finally {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {

        }
    }
}

public void update() {
    Connection conn = null;

    try {
        conn = getConnection();

        String query = "UPDATE Drinks SET quantity = quantity - 1 WHERE productName = ? and quantity > 0";

        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "Soda");

        pstmt.executeUpdate();

    } catch (Exception e) {
        // If we don't have a error, close the connection!
        System.err.println("Exception: " + e.getMessage());

    } finally {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {

        }
    }

}