spring-data-jpa Spring Data JPA命名本地查询传递动态表名称

u4vypkhs  于 2022-11-10  发布在  Spring
关注(0)|答案(1)|浏览(470)

我使用完全相同的列但不同的表/视图名称重复了选择查询。是否有任何选项可以减少orm.xml文件中的行数?
示例:

<named-native-query name="PeriodEntity.periodQuery.NA"
                    result-class="com.data.store.foundation.model.PeriodEntity">
    <query>
        SELECT
            LEVELNAME,
            TIME_SVC_LVL_OPR_DESC,
            TIME_SVC_LVL_NBR,
            CALENDARID,
            CHANNEL_KEY,
            ISCURRENT,
            PREVIOUS_CALENDARID,
            START_DT,
            ENDDATE,
            CXT,
            CAL_YEAR,
            ID,
            RANGE_DATE,
            LBL,
            SHORT_LABEL,
            FISCAL_YEAR
        FROM
            dbo.v_NRT_Time_Hier_RTL_FRCH
        WHERE
            Channel_key = :channelId
    </query>
</named-native-query>

<named-native-query name="PeriodEntity.periodQuery.EU"
                    result-class="com.data.store.foundation.model.PeriodEntity">
    <query>
        SELECT
            LEVELNAME,
            TIME_SVC_LVL_OPR_DESC,
            TIME_SVC_LVL_NBR,
            CALENDARID,
            CHANNEL_KEY,
            ISCURRENT,
            PREVIOUS_CALENDARID,
            START_DT,
            ENDDATE,
            CXT,
            CAL_YEAR,
            ID,
            RANGE_DATE,
            LBL,
            SHORT_LABEL,
            FISCAL_YEAR
        FROM
            dbo.v_Day_Time_Hier_RTL
        WHERE
            Channel_key = :channelId
    </query>
</named-native-query>

注意事项:
1.我们只从表/视图中选择必需的列。
1.我们可以为所有列声明一个变量并在选择查询中使用变量名吗?
1.我们可以动态地提供表/视图名称吗?这样一个〈named-native-query name=“PeriodEntity.periodQuery.Generic”就可以同时满足这两种情况了吗?

3xiyfsfu

3xiyfsfu1#

JPA Named Queries are static, you cannot use a dynamic table name.
A couple of alternatives below. If you can alter the Java model, the first is preferable because it's based on a change that adapts the entities to your database, instead of adding complexity with dynamic queries.

Option #1: Define abstract PeriodEntity and children

From your queries, it seems both tables v_NRT_Time_Hier_RTL_FRCH and v_Day_Time_Hier_RTL represent identical entities. You can define an abstract PeriodEntity class annotated with @MappedSuperclass and defining the shared attributes. Then define entities for each children that shares this structure.

@MappedSuperclass
public abstract class PeriodEntity {
    @Column(name = "LEVELNAME")
    String levelName;

    @Column(name = "Channel_key")
    String channelKey;

    // etc.
}

@Entity
@Table(name = "v_NRT_Time_Hier_RTL_FRCH")
public class NrtTimePeriodEntity extends PeriodEntity {
}

@Entity
@Table(name = "v_Day_Time_Hier_RTL")
public class DayTimePeriodEntity extends PeriodEntity {
}

Your named native queries can then be replaced by non-native named queries, like: SELECT T FROM DayTimePeriodEntity T WHERE channelKey = :channelId .
From JPA 1.4.3, you can use a generic expression to refer to the entity name, and it will be replaced when the query is run: SELECT T FROM #{#entityName} T ... . That would let you go even further and define a single query for both entities.

Option #2: "Dynamic" Native query

Another approach without changing your model is to define the Named Query in the code, moving it from the orm.xml to a custom method in a repository class. You can have any SQL and apply string processing to change the name of the table.
In your repository you would have something like:

static final String SQL = "SELECT * FROM <table> WHERE Channel_key = :channelId";

@PersistenceContext
EntityManager entityManager;

public List<PeriodEntity> findDayTimeByChannelId(String channelId) {
    String sql = SQL.replaceAll("<table>", v_Day_Time_Hier_RTL);
    Query q = entityManager.createNativeQuery(sql, PeriodEntity.class);
    q.setParameter("channelId", channelId);
    return q.getResultList();
}

相关问题