示例#1
0
  public static int getLastInsertId(String table) {
    int id = -1;

    try {
      PreparedStatement st =
          SQL.getConnection()
              .prepareStatement(
                  "SELECT DISTINCT LAST_INSERT_ID() AS last_insert_id FROM " + table + ";");
      ResultSet result = st.executeQuery();
      result.next();
      id = result.getInt("last_insert_id");
      result.close();
      st.close();
    } catch (Exception e) {
      throw new RuntimeException(e.getMessage(), e);
    }

    return id;
  }
  @Override
  public void execute(DelegateExecution exec) throws Exception {
    long docID = (long) exec.getVariable("doc_id");

    Connection con = SQL.getConnection();
    PreparedStatement ps1 = con.prepareStatement("SELECT * FROM `o_lines` WHERE doc_id = ?");
    ps1.setLong(1, docID);
    ResultSet rs1 = ps1.executeQuery();

    PreparedStatement ps2 =
        con.prepareStatement("UPDATE `product_sale` SET reserved = reserved + ? WHERE p_id = ?");

    while (rs1.next()) {
      ps2.setInt(1, rs1.getInt("quantity"));
      ps2.setLong(2, rs1.getLong("p_id"));
      ps2.execute();
    }

    ps2.close();
    rs1.close();
    ps1.close();

    PreparedStatement ps =
        con.prepareStatement("UPDATE `order` SET waitingForProducts = 1 WHERE doc_id = ?");
    ps.setLong(1, docID);
    ps.execute();

    ps.close();

    ps =
        con.prepareStatement(
            "SELECT * FROM `product_sale` WHERE stock + ordered < minimumStock + reserved");
    ResultSet rs = ps.executeQuery();

    exec.setVariable("belowMinimum", rs.next());

    rs.close();
    ps.close();
  }