SpringDataJPA将存储过程结果从多个数据源Map到非实体pojo

cetgtptt  于 2021-07-06  发布在  Java
关注(0)|答案(2)|浏览(369)

我曾试图研究一种更好的方法,在两个不同的数据源上调用两个存储过程,但都没有成功。这是我的场景,我连接到两个数据源,每个数据源有一个存储过程。每当我将一个配置注解为primary时,就会得到结果,而另一个没有Map为primary的配置会抛出一个错误
给定的SqlResultsMapping名称[unknown SqlResultsMapping[testing]]未知
当我将调用存储过程的类的另一个配置设置为primary时,它会显示结果,但是当它们都没有设置为primary时,它们会抛出一个错误
给定的SqlResultsMapping名称[unknown SqlResultsMapping[testing]]未知
被困了好几天。我会感激你的帮助。提前谢谢你
我调用第一个数据源的第一个配置

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "guru.springframework.multipledatasources.model.Card",
        entityManagerFactoryRef = "cardEntityManagerFactory",
        transactionManagerRef= "cardTransactionManager")
public class Cardonfiguration {

        @Bean
        @Primary
        @ConfigurationProperties("datasource.card")
        public DataSourceProperties cardDataSourceProperties() {
            return new DataSourceProperties();
        }

        @Bean
        @Primary
        @ConfigurationProperties("datasource.card.configuration")
        public DataSource cardDataSource() {
            return cardDataSourceProperties().initializeDataSourceBuilder()
                    .type(BasicDataSource.class).build();
        }

        @Primary
        @Bean(name = "cardEntityManagerFactory")
        public LocalContainerEntityManagerFactoryBean cardEntityManagerFactory(
                EntityManagerFactoryBuilder builder) {
            return builder
                    .dataSource(cardDataSource())
                    .packages(Card.class)
                    .build();
        }

        @Primary
        @Bean
        public PlatformTransactionManager cardTransactionManager(
                final @Qualifier("cardEntityManagerFactory") LocalContainerEntityManagerFactoryBean cardEntityManagerFactory) {
            return new JpaTransactionManager(Objects.requireNonNull(cardEntityManagerFactory.getObject()));
    }
}

第二个数据源的第二个配置

@Configuration
@EnableJpaRepositories(basePackages = "guru.springframework.multipledatasources.model.member",
        entityManagerFactoryRef = "memberEntityManagerFactory",
        transactionManagerRef= "memberTransactionManager")
public class MemberConfiguration {
    @Bean
    @ConfigurationProperties("datasource.member")
    public DataSourceProperties zamphiaDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("datasource.member.configuration")
    public DataSource memberDataSource() {
        return memberDataSourceProperties().initializeDataSourceBuilder()
                .type(BasicDataSource.class).build();
    }

    @Bean(name = "memberEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean memberEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(memberDataSource())
                .packages(member.class)
                .build();
    }

    @Bean
    public PlatformTransactionManager memberTransactionManager(
            final @Qualifier("memberEntityManagerFactory") LocalContainerEntityManagerFactoryBean memberEntityManagerFactory) {
        return new JpaTransactionManager(memberEntityManagerFactory.getObject());
    }

}

第一个数据源的我的控制器

@RequestMapping("/api/v1")
@RestController
public class CardReports {

    @PersistenceContext
    private EntityManager entityManager;

    @SuppressWarnings("unchecked")
    @RequestMapping("/reports/CARD/dailyreport")
    public List<Testing> getDailyAchievements(){
        List<Testing> list;
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("dailyreports", "Testing");
        try {
            // Execute query
            query.execute();
            list = query.getResultList();
        } finally {
            try {
                query.unwrap(ProcedureOutputs.class).release();
            } catch (Exception e) {
            }
        }
        return list;
    }
}

第一个数据源的我的非实体

@MappedSuperclass
@SqlResultSetMapping(name = "Testing", classes = @ConstructorResult(targetClass = Testing.class, columns = {
        @ColumnResult(name = "TTEAM_ID", type = Integer.class),
        @ColumnResult(name = "VDATE", type = String.class),
        @ColumnResult(name="Days_in_Field", type = String.class),
        @ColumnResult(name= "Expected_cummilative_Number_of_HH", type = String.class),
        @ColumnResult(name="Cummlative_HH_Reached", type = String.class),
        @ColumnResult(name="HH_Reached_for_day", type = String.class),
        @ColumnResult(name ="HHs_Finalised_for_day", type = String.class),
        @ColumnResult(name="Number_of_HH_in_process_for_day", type = String.class),
        @ColumnResult(name="Number_of_HH_Refused_for_day", type = String.class),
        @ColumnResult(name="Number_of_Revisits_Scheduled_for_day", type = String.class),
        @ColumnResult(name="HH_loss_3rd_visit_attempt_refusals_for_day", type = String.class)
}))

public class Testing {
    private Integer TTEAM_ID;
    private String  VDATE;
    private String Days_in_Field;
    private String Expected_cummilative_Number_of_HH;
    private String Cummlative_HH_Reached;
    private String HH_Reached_for_day;
    private String HHs_Finalised_for_day;
    private String Number_of_HH_in_process_for_day;
    private String Number_of_HH_Refused_for_day;
    private String Number_of_Revisits_Scheduled_for_day;
    private String HH_loss_3rd_visit_attempt_refusals_for_day;

    public Testing(Integer TTEAM_ID, String VDATE, String Days_in_Field, String Expected_cummilative_Number_of_HH, String Cummlative_HH_Reached, String HH_Reached_for_day, String HHs_Finalised_for_day, String Number_of_HH_in_process_for_day, String Number_of_HH_Refused_for_day, String Number_of_Revisits_Scheduled_for_day, String HH_loss_3rd_visit_attempt_refusals_for_day) {
        this.TTEAM_ID = TTEAM_ID;
        this.VDATE = VDATE;
        this.Days_in_Field = Days_in_Field;
        this.Expected_cummilative_Number_of_HH = Expected_cummilative_Number_of_HH;
        this.Cummlative_HH_Reached = Cummlative_HH_Reached;
        this.HH_Reached_for_day = HH_Reached_for_day;
        this.HHs_Finalised_for_day = HHs_Finalised_for_day;
        this.Number_of_HH_in_process_for_day= Number_of_HH_in_process_for_day;
        this.Number_of_HH_Refused_for_day = Number_of_HH_Refused_for_day;
        this.Number_of_Revisits_Scheduled_for_day = Number_of_Revisits_Scheduled_for_day;
        this.HH_loss_3rd_visit_attempt_refusals_for_day = HH_loss_3rd_visit_attempt_refusals_for_day;
    }
//getters and setters

我的第二个数据源控制器

@RequestMapping("/api/v1")
    @RestController
    public class MemberDaily {

        @PersistenceContext
        private EntityManager entityManager;

        @SuppressWarnings("unchecked")
        @RequestMapping("/reports/member/dailyachievementreport")
        public List<DailyReport> getDailyAchievements(){
            List list;
            StoredProcedureQuery query = entityManager.createStoredProcedureQuery("dailyreports", "Testing");
            try {
                // Execute query
                query.execute();
                list = query.getResultList();
            } finally {
                try {
                    query.unwrap(ProcedureOutputs.class).release();
                } catch (Exception e) {
                }
            }
            return list;
        }
    }

如何在第二个数据源的pojo上Map结果

@MappedSuperclass
    @SqlResultSetMapping(name = "Testing", classes = @ConstructorResult(targetClass = Testing.class, columns = {
            @ColumnResult(name = "TTEAM_ID", type = Integer.class),
            @ColumnResult(name = "VDATE", type = String.class),
            @ColumnResult(name="Days_in_Field", type = String.class),
            @ColumnResult(name= "Expected_cummilative_Number_of_HH", type = String.class),
            @ColumnResult(name="Cummlative_HH_Reached", type = String.class),
            @ColumnResult(name="HH_Reached_for_day", type = String.class),
            @ColumnResult(name ="HHs_Finalised_for_day", type = String.class),
            @ColumnResult(name="Number_of_HH_in_process_for_day", type = String.class),
            @ColumnResult(name="Number_of_HH_Refused_for_day", type = String.class),
            @ColumnResult(name="Number_of_Revisits_Scheduled_for_day", type = String.class),
            @ColumnResult(name="HH_loss_3rd_visit_attempt_refusals_for_day", type = String.class)
    }))

    public class Testing {
        private Integer TTEAM_ID;
        private String  VDATE;
        private String Days_in_Field;
        private String Expected_cummilative_Number_of_HH;
        private String Cummlative_HH_Reached;
        private String HH_Reached_for_day;
        private String HHs_Finalised_for_day;
        private String Number_of_HH_in_process_for_day;
        private String Number_of_HH_Refused_for_day;
        private String Number_of_Revisits_Scheduled_for_day;
        private String HH_loss_3rd_visit_attempt_refusals_for_day;

        public Testing(Integer TTEAM_ID, String VDATE, String Days_in_Field, String Expected_cummilative_Number_of_HH, String Cummlative_HH_Reached, String HH_Reached_for_day, String HHs_Finalised_for_day, String Number_of_HH_in_process_for_day, String Number_of_HH_Refused_for_day, String Number_of_Revisits_Scheduled_for_day, String HH_loss_3rd_visit_attempt_refusals_for_day) {
            this.TTEAM_ID = TTEAM_ID;
            this.VDATE = VDATE;
            this.Days_in_Field = Days_in_Field;
            this.Expected_cummilative_Number_of_HH = Expected_cummilative_Number_of_HH;
            this.Cummlative_HH_Reached = Cummlative_HH_Reached;
            this.HH_Reached_for_day = HH_Reached_for_day;
            this.HHs_Finalised_for_day = HHs_Finalised_for_day;
            this.Number_of_HH_in_process_for_day= Number_of_HH_in_process_for_day;
            this.Number_of_HH_Refused_for_day = Number_of_HH_Refused_for_day;
            this.Number_of_Revisits_Scheduled_for_day = Number_of_Revisits_Scheduled_for_day;
            this.HH_loss_3rd_visit_attempt_refusals_for_day = HH_loss_3rd_visit_attempt_refusals_for_day;
        }
    //getters and setters
bgibtngc

bgibtngc1#

我不认为 SqlResultSetMapping 工作于 @MappedSuperclass . 试着把它放在一个实体上。

eufgjt7s

eufgjt7s2#

你的 cardEntityManagerFactory 将扫描 Card.class 对于带注解的类 @ManagedSuperClass 以及 memberEntityManagerFactory 将扫描 member.class 看起来你用的是同一个pojo SQLResultSetMapping “测试”两个控制器。但目前还不清楚这个pojo在哪个包中。因此,请确保两个实体管理器都扫描了pojo的包。
此外,您只需自动连接实体管理器,而无需在两个控制器中指定名称。根据我的理解,如果不指定 Qualifier .
我想,你可以试试 @PersistenceContext(name = "cardEntityManagerFactory") 以及 @PersistenceContext(name = "memberEntityManagerFactory")CardReports 以及 MemberDaily 控制器。
更新
我查看了你的github代码,发现你的 BAISVReports 控制器仅获取主实体管理器。所以只需使用 @Qualifier 如下图所示,现在辅助实体管理器正在自动连接。现在两个控制器都能找到 SQLResultSetMapping ```
@RequestMapping("/api/v1")
@RestController
public class BAISVReports {

@Autowired
@Qualifier("baisvEntityManagerFactory")
private EntityManager entityManager;

相关问题