mysql - JPARepository 和 Mysql 尝试使用 @Query 从多列中获取表中每一行的数据以获取条件

问题描述

在查询时我是新手，我有一些这样的问题。

我有两张这样的桌子

transactions
+-----+------------+----------+------------+
| id  |   trans    |trans_code| trans_time |
+-----+------------+----------+------------+
|  1  |   trans1   |   x01    |   22:00    |
|  2  |   trans2   |   x01    |   17:00    |
|  3  |   trans3   |   x02    |   23:00    |
|  4  |   trans4   |   x03    |   20:00    |
+-----+------------+----------+------------+

restricted_code
+-----+--------+----------------+--------------+
| id  |  code  |time_limit_start|time_limit_end|
+-----+--------+----------------+--------------+
|  1  |  x01   |      21:00     |     24:00    |
|  2  |  x03   |      16:00     |     23:00    |
+-----+--------+----------------+--------------+

expected result
+-----+------------+----------+------------+
| id  |   trans    |trans_code| trans_time |
+-----+------------+----------+------------+
|  1  |   trans1   |   x01    |   22:00    |
|  4  |   trans4   |   x03    |   20:00    |
+-----+------------+----------+------------+

我想从restricted_code表的任何行中获取trans_code存在的每个事务，并从其各自的行获取time_limit_start和time_limit_end之间的trans_time

我尝试在 JPARepository 中使用 @Query 来使用此代码进行查询

@Query(value = "select t from transactions t, restricted_code rc where t.trans_code = rc.code and t.trans_time >= rc.time_limit_start and t.trans_time <= rc.time_limit_end")

是否有可能得到预期的结果？或者我需要获取所有数据并手动循环以检查哪些交易符合条件？

edit 1 这是我的代码

@Repository
public interface TransactionRequestRepository extends JpaRepository<TransactionRequest, Long> {
    @Query(value = "select tr.transactionAmount as transactionAmount, ta.status as transactionStatus, ta.authResponseCode as authResponseCode, tr.posEntryMode as posEntryMode, tr.serviceCode as serviceCode, ta.cardHolderName as cardHolderName, tr.transactionType as transactionType, ta.latitude as latitude, ta.longitude as longitude, b.bankName as bankName, mc.companyName as companyName, ta.txid as transactionId, ta.pan as panNumber, ta.createdDate as transactionCreatedDate from transaction_request tr, transaction_authorized  ta, bank b, merchant mc, mid m, master_mcc masmcc, unexpected_transaction_mcc utm, category_master_mcc cmmcc" +
        " where ta.transactionDate = :transactionDate " +
        " and ta.transactionTime >= :startTime " +
        " and ta.transactionTime<= :endTime " +
        " and ta.transactionAuthorizedId = tr.transactionauthorizedFk.transactionAuthorizedId " +
        " and ta.mid = m.mid" +
        " and m.bankFk = b " +
        " and m.merchantFk = mc" +
        " and mc.categoryMasterMcc = cmmcc" +
        " and cmmcc.categoryMasterMccId = masmcc.categoryMasterMccFk.categoryMasterMccId " +
        " and ((tr.serviceCode is null " +
        " and (tr.posEntryMode = '021' " +
        " or tr.posEntryMode = '022')) " +
        " or (ta.authResponseCode = '04' " +
        " or ta.authResponseCode = '07' " +
        " or ta.authResponseCode = '12' " +
        " or ta.authResponseCode = '34' " +
        " or ta.authResponseCode = '41' " +
        " or ta.authResponseCode = '43') " +
        " or ( inner join utm.mccId on masmcc.mcc and ta.transactionTime <= utm.timeLimitEnd and ta.transactionTime >= utm.timeLimitStart )) ")
    List<SuspiciousTransactionProjection> findTransactionRequestByPosEntryModeAndServiceCode(@Param("transactionDate") String transactionDate,
                                                                                             @Param("startTime") String startTime,
                                                                                             @Param("endTime") String endTime);

public interface SuspiciousTransactionProjection {
    Long getSuspiciousTransactionId();
    String getTransactionId();
    String getBankName();
    String getCompanyName();
    String getTransactionAmount();
    String getTransactionStatus();
    String getAuthResponseCode();
    String getPosEntryMode();
    String getServiceCode();
    String getCardHolderName();
    String getTransactionType();
    String getLatitude();
    String getLongitude();
    String getPanNumber();
    Date getTransactionCreatedDate();
}

我不知道如何使它工作或更清洁我很确定这是一个混乱的查询

在我的最后一个查询行之前它工作正常

我也应该发布另一个实体吗？因为其中一些有很多专栏，我不知道这两个是否足够

标签： mysqlspring-data-jpa

mysql - JPARepository 和 Mysql 尝试使用 @Query 从多列中获取表中每一行的数据以获取条件

问题描述

解决方案

推荐阅读