如何将函数逻辑存储在数据库中

Question

如何将函数逻辑存储在数据库中

3

我正在制作一款财务管理应用程序。我有一个包含用户所有资金所在地（包括银行）的数据库。以下是表格的结构...

CREATE TABLE IF NOT EXISTS reserves (
                            id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                            name VARCHAR(31) NOT NULL,
                            balance DECIMAL(10, 2) NOT NULL
                        )
CREATE TABLE IF NOT EXISTS banks (
                            reserve_id SMALLINT UNSIGNED UNIQUE NOT NULL,
                            apy DECIMAL(4, 2) NOT NULL,
                            accrued_interest DECIMAL(10, 4) NOT NULL,
                            last_transaction DATE,
                            FOREIGN KEY(reserve_id) REFERENCES reserves(id)
                        )

在这个模型中，我可以拥有一个固定的APY，它将在插入时设置。但在现实世界中，银行根据余额有可变的利率。而且对于银行表中的每个银行来说，具体情况都不同。

在Java类中，我可以非常容易地捕获这一点，将APY定义为Function<BigDecimal，Big Decimal> APY，在其中我可以存储特定的APY逻辑，并使用APY.apply(balance)在任何时候检索利率。

但我不知道如何在MySQL数据库中存储这个逻辑。

我知道我可以创建一个单独的表，比如bank_balance_interest，在其中我可以存储利率到最低余额到特定银行的id，然后引用它。

但这感觉并不正确。首先，它非常繁琐和乏味。此外，如果余额与利息没有明确的边界而是连续函数，则仍然没有解决方案。

是否有更优雅的方法？

以下是我的一些代码：

public class Reserve {
    short id;
    final String name;
    BigDecimal balance;

    ReservesData reservesData;
    public Reserve(short id, String name, BigDecimal balance) {
        this.id = id;
        this.name = name;
        this.balance = balance;

        reservesData = ReservesData.instance;
    }

    public Reserve(String name) {
        this((short) -1, name, new BigDecimal("0.0"));
    }

    @Override
    public String toString() {
        return name;
    }

    public short getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public BigDecimal getBalance() {
        return balance;
    }

    public boolean transact(BigDecimal amount) {
        if(balance.add(amount).compareTo(new BigDecimal("0.0")) < 0)
            return false;
        balance = balance.add(amount);
        return true;
    }

    public boolean save() {
        if(id == -1)
            return (id = reservesData.addReserve(this)) != -1;
        return reservesData.updateReserve(this);
    }
}

public class Bank extends Reserve{

    private final Function<BigDecimal, BigDecimal> APY;
    private BigDecimal accruedInterest;
    private Date lastTransactionDate;

    private final BanksData banksData;

    public Bank(short id, String name, BigDecimal balance, Function<BigDecimal, BigDecimal> APY) {
        super(id, name, balance);

        this.APY = APY;
        accruedInterest = new BigDecimal("0.0");

        banksData = BanksData.instance;
    }

    public Bank(String name, Function<BigDecimal, BigDecimal> APY) {
        this((short) -1, name, new BigDecimal("0.0"), APY);
    }

    @Override
    public BigDecimal getBalance() {
        return balance.add(accruedInterest);
    }

    public Function<BigDecimal, BigDecimal> getAPY() {
        return APY;
    }

    public BigDecimal getAccruedInterest() {
        return accruedInterest;
    }

    public void setAccruedInterest(BigDecimal accruedInterest) {
        this.accruedInterest = accruedInterest;
    }

public class ReservesDAO implements ReservesData {

    public ReservesDAO() {
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            stmt.executeUpdate("""
                            CREATE TABLE IF NOT EXISTS reserves (
                                id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                                name VARCHAR(31) NOT NULL,
                                balance DECIMAL(10, 2) NOT NULL
                            )"""
            );
        } catch (SQLException sqlException) {
            System.out.println("Failed to create reserves table on the database!");
            sqlException.printStackTrace();
        }
    }

    @Override
    public short addReserve(Reserve reserve) {
        try (
                PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                        INSERT INTO reserves (name, balance) VALUES (?, ?)""", Statement.RETURN_GENERATED_KEYS
                )
        ) {
            pstmt.setString(1, reserve.getName());
            pstmt.setBigDecimal(2, reserve.getBalance());

            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next())
                return rs.getShort(1);
            else
                throw new RuntimeException("Auto-Generated ID was not returned from reserves!");
        } catch (SQLException sqlException) {
            System.out.println("Failed to insert " + reserve.getName() + " info in the database!");
            sqlException.printStackTrace();
            return -1;
        }
    }

    public Reserve getReserve(short id) {
        try(
                PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                        SELECT * FROM reserves WHERE id = ?""")
        ) {
            pstmt.setShort(1, id);
            ResultSet rs = pstmt.executeQuery();

            if(rs.next())
                return new Reserve(rs.getShort(1), rs.getString(2), rs.getBigDecimal(3));
            else throw new RuntimeException("No reserve found on the database with the id " + id);

        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch reserve from the database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    public List<Reserve> getAllReserves() {
        List<Reserve> reserves = new ArrayList<>();
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("SELECT * FROM reserves");
            while(rs.next())
                reserves.add(new Reserve(rs.getShort(1), rs.getString(2), rs.getBigDecimal(3)));
        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch reserves from the database!");
            sqlException.printStackTrace();
        }

        return reserves;
    }

    @Override
    public BigDecimal getTotalReserveBalance() {
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("""
                    SELECT SUM(balance) FROM reserves""");
            if(rs.next())
                return rs.getBigDecimal(1);
            return new BigDecimal("0.0");
        } catch (SQLException sqlException) {
            System.out.println("Could not get total reserve balance from database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Reserve> getAllWallets() {
        List<Reserve> reserves = new ArrayList<>();
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("""
                    SELECT reserves.* FROM reserves
                    LEFT JOIN banks ON reserves.id = banks.id
                    WHERE banks.id IS NULL
                    """);
            while(rs.next())
                reserves.add(new Reserve(rs.getShort(1), rs.getString(2), rs.getBigDecimal(3)));
        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch reserves from the database!");
            sqlException.printStackTrace();
        }

        return reserves;
    }

    @Override
    public BigDecimal getTotalWalletBalance() {
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("""
                    SELECT SUM(balance) FROM reserves
                    LEFT JOIN banks ON reserves.id = banks.id
                    WHERE banks.id IS NULL
                    """);
            if(rs.next())
                return rs.getBigDecimal(1) == null ? new BigDecimal("0.0") : rs.getBigDecimal(1);
            return new BigDecimal("0.0");
        } catch (SQLException sqlException) {
            System.out.println("Could not get total wallet balance from database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    @Override
    public boolean updateReserve(Reserve reserve) {
        try(PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                UPDATE reserves SET name = ?, balance = ? WHERE id = ?""")
        ) {
            pstmt.setString(1, reserve.getName());
            pstmt.setBigDecimal(2, reserve.getBalance());
            pstmt.setShort(3, reserve.getId());
            pstmt.executeUpdate();
            return true;
        } catch(SQLException sqlException) {
            System.out.println("Failed to update reserves with new data!");
            sqlException.printStackTrace();
            return false;
        }
    }
}

public class BanksDAO extends ReservesDAO implements BanksData {
    public BanksDAO() {
        try(
            Statement stmt = MyConnection.getMySQLconnection().createStatement()
        ) {
            stmt.executeUpdate("""
                            CREATE TABLE IF NOT EXISTS banks (
                                id SMALLINT UNSIGNED UNIQUE NOT NULL,
                                apy DECIMAL(4, 2) NOT NULL, // I have no way to store a logic here, so currently it only stores fixed value.
                                accrued_interest DECIMAL(10, 4) NOT NULL,
                                last_transaction_date DATE,
                                FOREIGN KEY(id) REFERENCES reserves(id)
                            )"""
            );
        } catch (SQLException sqlException) {
            System.out.println("Failed to create banks table on the database!");
            sqlException.printStackTrace();
        }
    }

    @Override
    public short addBank(Bank bank) {
        try (
                PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                        INSERT INTO banks(id, apy, accrued_interest, last_transaction_date) VALUES (?, ?, ?, ?)"""
                )
        ) {
            short id = addReserve(bank);
            pstmt.setShort(1, id);
            pstmt.setBigDecimal(2, bank.getAPY());
            pstmt.setBigDecimal(3, bank.getAccruedInterest());
            pstmt.setDate(4, bank.getLastTransactionDate());

            pstmt.executeUpdate();
            return id;
        } catch (SQLException sqlException) {
            System.out.println("Failed to insert " + bank.getName() + " info in the database!");
            sqlException.printStackTrace();
            return -1;
        }
    }

    @Override
    public Bank getBank(short reserve_id) {
        try(
            PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                        SELECT * FROM reserves NATURAL JOIN banks WHERE id = ?""")
        ) {
            pstmt.setShort(1, reserve_id);
            ResultSet rs = pstmt.executeQuery();
            if(!rs.next())
                return null;
            Bank requestedBank = new Bank(rs.getShort(1), rs.getString(2),
                    rs.getBigDecimal(3), rs.getBigDecimal(4));
            requestedBank.setAccruedInterest(rs.getBigDecimal(5));
            requestedBank.setLastTransactionDate(rs.getDate(6));
            return requestedBank;
        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch bank data from the database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Bank> getAllBanks() {
        List<Bank> allBanks = new ArrayList<>();
        try(
            Statement stmt = MyConnection.getMySQLconnection().createStatement()
        ) {
            ResultSet rs = stmt.executeQuery("SELECT * FROM reserves NATURAL JOIN banks");
            while(rs.next()) {
                Bank bank = new Bank(rs.getShort(1), rs.getString(2),
                        rs.getBigDecimal(3), rs.getBigDecimal(4));
                bank.setAccruedInterest(rs.getBigDecimal(5));
                bank.setLastTransactionDate(rs.getDate(6));
                allBanks.add(bank);
            }

            return allBanks;

        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch bank data from the database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    @Override
    public BigDecimal getTotalBankBalance() {
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("""
                    SELECT SUM(balance) FROM reserves NATURAL JOIN banks""");
            if(rs.next())
                return rs.getBigDecimal(1) == null ? new BigDecimal("0.0") : rs.getBigDecimal(1);
            return new BigDecimal("0.0");
        } catch (SQLException sqlException) {
            System.out.println("Could not get total bank balance from database!");
            sqlException.printStackTrace();
            return null;
        }
    }
}

现在我可以这样初始化银行：

Bank bank1 = new Bank("TestBank1", balance -> balance.compareTo(new BigDecimal("10000")) == -1 ? new BigDecimal("4") : new BigDecimal("5"));

虽然我可以创建另一个银行，如下：

Bank bank2 = new Bank("TestBank2", balance -> balance.compareTo(new BigDecimal("8000")) == -1 ? new BigDecimal("3.5") : new BigDecimal("5.3"));

现在这两个银行都是在内存中创建的，并且只要应用程序在运行，它们就可以完美地工作。但是当我需要将其持久化以供长期使用时，我无法直接将类型为Function<BigDecimal, BigDecimal>的变量存储到MySQL数据库中。

许多人建议使用存储过程，如果只有一个逻辑，例如对于banks表中的每个银行都是balance -> balance.compareTo(new BigDecimal("10000")) == -1 ? new BigDecimal("4") : new BigDecimal("5")，那么这将起作用，但是这些信息会随着时间而改变。

这意味着，如果我的banks表中有50个条目，则我需要为我的banks表中的每个条目创建50个不同的存储过程，以保持更新APY字段随余额更改而变化的方式。可能有更好的方法吗？

- Chandrachur Mukherjee

你应该创建一个存储过程来模拟相同的逻辑，然后在银行表执行插入查询时调用该存储过程。 - Amine

@Anime 但是银行表中每个条目的逻辑细节都不同。这意味着对于每个条目，我都需要一个新的存储过程？我如何将特定的存储过程链接到特定的行？ - Chandrachur Mukherjee

生成银行余额利息的字段是否也映射到/存在于数据库中？如果是，我认为您可以在数据库中实现相同逻辑的存储过程。 - Amine

@Anime 兴趣所依赖的唯一字段是储备表中存在的当前余额。储备表被映射为外键。 - Chandrachur Mukherjee

我还是不明白为什么你不能使用储备表中的余额字段创建一个存储过程，以执行与Java类相同的行为。尽管如此，我已经点赞了这个问题，希望有人能更好地理解问题并为您找到解决方案。 - Amine

显示剩余2条评论

2个回答

0

建议/意见

数据库用于持久存储数字和文本。

应用程序用于计算和决策。

也就是说，您所描述的复杂业务逻辑可能更适合使用Java（或其他应用程序语言）进行编码。与此同时，利率的断点等应存储在数据库中。

换句话说：“业务逻辑”应该放在应用程序中，而不是数据库中。（当然，在它们之间有灰色的界限。例如，SQL非常擅长对表中的所有数据进行汇总；因此，我会在SQL中执行此操作，而不是在Java中执行。）

小数

银行有挑剔的规则，可能与Java或MySQL或任何其他计算机语言提供的DECIMAL或DOUBLE不同。请注意可能需要的规则。特别是，在您的应用程序中，DECIMAL（10,4）不太可能足够。另一方面，如果向客户呈现了apy DECIMAL（4,2），则可能足够。但是，请注意在生成该数字时的舍入规则。您可能需要手动输入该数字。

请注意DECIMAL和DOUBLE之间取整特性的差异。

答案

如果选择在数据库中实现算法，则请参见CREATE STORED PROCEDURE和CREATE FUNCTION。

Stored procedures可用于封装一组SQL语句。它可以接收和发送字符串和数字，但不能接收数组。它能够读取表格（因此属于某种程度上的“数组”）。

函数可以在任何表达式可以出现的地方调用。它可以接收一些数字/字符串并产生一个数字或字符串。

数组

我不清楚需要什么，但我设想一个有几行或几十行的表格，每行都说“对于值高达$xxx，使用y.yy％的利率”。

存储过程具有“游标”和“循环”，但它们很笨拙；应用程序语言可能有更好的代码。

- Rick James

1

回复：“业务逻辑应该放在应用程序中，而不是数据库中。”这是不正确的，关系型数据库本质上已经包含了“业务规则”，这是由于规范化过程的结果。业务规则一直存在于数据库中、客户端应用程序甚至用户界面（网页）中，自这些东西存在以来就一直如此。对于特定情况下哪些业务规则属于哪里没有单一或简单的规则可以确定。 - RBarryYoung

@RBarryYoung - 好的，你说得对。也许我们可以达成一致？——它是在数据库和应用程序之间找到正确平衡的艺术，既不能把太多业务逻辑放在数据库中，也不能放得太少。 - Rick James

当然，我没有任何问题。 - RBarryYoung

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MarcinJ · Accepted Answer

我认为你被误解了。你可能并不是在问如何将逻辑移到数据库中，存储过程可能是一个答案，而是在问如何将你正在实现的代码逻辑存储到数据库中，以便以后恢复该状态。这就是我的回答的前提。

与这些计算相关的设计并不好。你应该考虑将这些APY计算方法表达为实现某种 IAPYCalculationMethod 接口的类，其中包含一个实际计算它的方法。像你所拥有的匿名lambda表达式对于这个目的来说只会是不好的。

所以假设你确实有一个带有CalculateAPY方法的IAPYCalculatioMethod接口，然后有一个类BalanceBasedCalculationMethod看起来像这样：

// The code is not complete. It is just to give you an idea
class BalanceBasedCalculationMethod implements IAPYCalculationMethod {
  public BalanceBasedCalculationMethod(BigDecimal balanceThreshold, BigDecimal whenLower, BigDecimal whenGreater) { ... }

  public BigDecimal CalculateAPY(Bank bank) {
     if (bank.getBalance() < this.balanceThreshold)
         return this.whenLower;
     else
         return this.whenGreater;
  }
}

当您创建新的银行账户时：

bank1 = new Bank("TestBank1", new BalanceBasedCalculationMethod(10000, 4, 5));
bank2 = new Bank("TestBank2", new BalanceBasedCalculationMethod(8000, 3.5, 5.3));

这已经更好了。此外，这还使您能够以某种方式序列化所有这些内容。您可以拥有一个包含所有计算方法、银行与所使用方法之间关系的表格，以及参数的JSON（因为不同的方法可能有不同的参数；您也可以将它们存储在单独的表格中，而不是JSON中）。

CREATE TABLE IF NOT EXISTS apy_calculation_method
(
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
   name VARCHAR(100) NOT NULL
);


CREATE TABLE IF NOT EXISTS bank_calculation_method
(
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bank_id SMALLINT UNSIGNED NOT NULL REFERENCES reserves(id),
  method_id SMALLINT UNSIGNED NOT NULL REFERENCES apy_calculation_method(id),
  arguments JSON
);

为了表示我们的基于余额的方法以及使用它的银行，我们会有以下内容：

INSERT INTO apy_calculation_method (name) VALUES ('Balance Based');

然后是每个银行的方法：

INSERT INTO bank_calculation_method (bank_id, method_id, arguments)
VALUES (1, 1, '{"balance": 10000, "whenLower": 4, "whenGreater": 5}')
     , (2, 1, '{"balance":  8000, "whenLower": 3.5, "whenGreater": 5.3}');

还有银行：

INSERT INTO banks (id, bank_method_id, accrued_interest)
VALUES (1, 1, 0) -- first bank using the first method (balance based with 10000 balance)
     , (2, 2, 0) -- second bank using the second method (balance based with 8000 balance)

这种方法可以更加灵活或严谨（规范化/非规范化），例如，您可以将计算方法仅存储为 JSON 格式在银行表中（采用 {"method": "balance", "balance": 10000, .... } 这样的结构）。您的计算方法工厂的设计以及它如何将数据序列化/反序列化到数据库中都取决于它，但我相信您可以解决这个问题。

这样做的好处是，您可以将计算方法序列化到数据库中，而不是使用某种无法引用的随机 Lambda 函数。因此，您只需将“所使用逻辑的类型”和参数编写到数据库中，而非将实际逻辑编写到数据库中。

另外一个好处是，它创造了一种可测试的设计，您可以编写一个 BalanceBasedCalculationMethod 测试，以确保其真正实现了预定功能。相比之下，您无法自动地测试这些 Lambda 函数，因为无法确定它们可能会做什么或不会做什么，对吧？