Java中合并不同结构交易实体并排序用户交易记录的方案

先修正实体代码笔误 #

你提供的代码里重复定义了Deposit类，应该是Withdraw，修正后如下：

@Entity
public class Withdraw extends Transaction {
    @Column(nullable = false, updatable = false)
    @Positive
    private BigDecimal amount;
}

方案一：调整JPA继承策略（推荐） #

当前Transaction用了@MappedSuperclass，导致三个子类各自生成独立表，查询合并麻烦。改成单表继承，所有交易数据存在同一张表，直接查询父类即可实现合并排序。

1. 修改父类Transaction的注解：

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "trans_type", discriminatorType = DiscriminatorType.STRING)
public class Transaction {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false,updatable = false)
    private Long id;
    @Column(nullable = false,updatable = false)
    private Long sourceAccount;
    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(nullable = false,name = "transaction_detail_id")
    private TransactionDetail transactionDetail;
    @CreationTimestamp
    @Column(nullable = false,updatable = false)
    private Date transactionDate;
    @CreationTimestamp
    @Column(nullable = false,updatable = false)
    private Time time;
}

2. 给每个子类添加区分标识：

@Entity
@DiscriminatorValue("DEPOSIT")
public class Deposit extends Transaction {
    @Column(nullable = false, updatable = false)
    @Positive
    private BigDecimal amount;
}

@Entity
@DiscriminatorValue("WITHDRAW")
public class Withdraw extends Transaction {
    @Column(nullable = false, updatable = false)
    @Positive
    private BigDecimal amount;
}

@Entity
@DiscriminatorValue("TRANSFER")
public class Transfer extends Transaction{
    @Column(nullable = false, updatable = false,name = "desc_acct")
    private Long recipientAccount;
    @Column(nullable = false, updatable = false)
    private BigDecimal amount;
}

3. 编写Repository查询方法
   直接基于父类Transaction查询，自带排序逻辑：

public interface TransactionRepository extends JpaRepository<Transaction, Long> {
    // 按交易日期+时间倒序查询指定用户所有交易
    List<Transaction> findAllBySourceAccountOrderByTransactionDateDescTimeDesc(Long sourceAccount);
}

返回的结果已经是合并并排序后的所有交易记录，前端直接使用即可。

方案二：JPQL UNION查询（不修改原有表结构） #

如果不想调整表结构，用UNION合并三个子类的查询结果，同时在查询语句中完成排序。

1. 定义Repository查询：

public interface TransactionRepository extends JpaRepository<Transaction, Long> {
    @Query("SELECT t.id, t.sourceAccount, t.transactionDetail, t.transactionDate, t.time, d.amount, null as recipientAccount " +
           "FROM Deposit d " +
           "WHERE d.sourceAccount = :accountId " +
           "UNION " +
           "SELECT t.id, t.sourceAccount, t.transactionDetail, t.transactionDate, t.time, w.amount, null as recipientAccount " +
           "FROM Withdraw w " +
           "WHERE w.sourceAccount = :accountId " +
           "UNION " +
           "SELECT t.id, t.sourceAccount, t.transactionDetail, t.transactionDate, t.time, tr.amount, tr.recipientAccount " +
           "FROM Transfer tr " +
           "WHERE tr.sourceAccount = :accountId " +
           "ORDER BY transactionDate DESC, time DESC")
    List<Object[]> findAllTransactionsByAccountId(@Param("accountId") Long accountId);
}

2. 转换为统一DTO
   因为UNION返回的是数组，需要转换成前端友好的DTO：

public class TransactionDTO {
    private Long id;
    private Long sourceAccount;
    private TransactionDetail transactionDetail;
    private Date transactionDate;
    private Time time;
    private BigDecimal amount;
    private Long recipientAccount;

    // 构造函数、getter/setter省略
}

3. Service层处理转换：

@Service
public class TransactionService {
    @Autowired
    private TransactionRepository transactionRepository;

    public List<TransactionDTO> getUserTransactions(Long accountId) {
        List<Object[]> results = transactionRepository.findAllTransactionsByAccountId(accountId);
        return results.stream().map(obj -> {
            TransactionDTO dto = new TransactionDTO();
            dto.setId((Long) obj[0]);
            dto.setSourceAccount((Long) obj[1]);
            dto.setTransactionDetail((TransactionDetail) obj[2]);
            dto.setTransactionDate((Date) obj[3]);
            dto.setTime((Time) obj[4]);
            dto.setAmount((BigDecimal) obj[5]);
            dto.setRecipientAccount((Long) obj[6]);
            return dto;
        }).collect(Collectors.toList());
    }
}

注意事项 #

• 原代码中Transaction和TransactionDetail都有transactionDate和time字段，存在冗余，建议只保留父类Transaction中的字段，避免数据不一致。
• 单表继承方案性能更优，查询逻辑简单；UNION方案适合无法修改现有表结构的场景。

内容的提问来源于stack exchange，提问作者ferrocene