Configure Spring Boot with two DataSources

3

I am using Spring Boot 2.X with Hibernate 5.X to connect two MySQL databases (Bar and Foo). I want to be able to list the different entities, with all their attributes (ID, Name and relationships @OneToMany/@ManyToOne ), of each of the two databases from an @RESTController.

I can make the list for the database that I have marked as @Primary (in this case Foo), however, when I try to do the same list for the other database (Bar) I always get an exception when returning the @OneToMany attribute (declared as lazy). If I write the second database (Bar) as @Primary instead of Foo, I get the opposite effect. Is there a way to solve this by keeping the data like Lazy?

This is the exception I'm getting:

...w.s.m.s.DefaultHandlerExceptionResolver :
Failed to write HTTP message: org.springframework.http.converter.HttpMessageNotWritableException: 
    Could not write JSON document: failed to lazily initialize a collection of role: 
        com.foobar.bar.domain.Bar.manyBars, could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]-com.foobar.bar.domain.Bar["manyBars"]); 
    nested exception is com.fasterxml.jackson.databind.JsonMappingException:
        failed to lazily initialize a collection of role: 
        com.foobar.bar.domain.Bar.manyBars, could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]->com.foobar.bar.domain.Bar["manyBars"])

The configuration of the Foo database:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory",
        transactionManagerRef = "transactionManager",
        basePackages = {"com.foobar.foo.repo"})
public class FooDbConfig {

    @Primary
    @Bean(name = "dataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "entityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("dataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.foobar.foo.domain")
                .persistenceUnit("foo")
                .build();
    }

    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

The configuration of the Bar database:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "barEntityManagerFactory",
        transactionManagerRef = "barTransactionManager", basePackages = {"com.foobar.bar.repo"})
public class BarDbConfig {

    @Bean(name = "barDataSource")
    @ConfigurationProperties(prefix = "bar.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "barEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean barEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("barDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.foobar.bar.domain")
                .persistenceUnit("bar")
                .build();
    }

    @Bean(name = "barTransactionManager")
    public PlatformTransactionManager barTransactionManager(
            @Qualifier("barEntityManagerFactory") EntityManagerFactory barEntityManagerFactory) {
        return new JpaTransactionManager(barEntityManagerFactory);
    }
}

This is the REST driver:

@RestController
public class FooBarController {

    private final FooRepository fooRepo;
    private final BarRepository barRepo;

    @Autowired
    FooBarController(FooRepository fooRepo, BarRepository barRepo) {
        this.fooRepo = fooRepo;
        this.barRepo = barRepo;
    }

    @RequestMapping("/foo")
    public List<Foo> listFoo() {
        return fooRepo.findAll();
    }

    @RequestMapping("/bar")
    public List<Bar> listBar() {
        return barRepo.findAll();
    }

    @RequestMapping("/foobar/{id}")
    public String fooBar(@PathVariable("id") Integer id) {
        Foo foo = fooRepo.findById(id);
        Bar bar = barRepo.findById(id);

        return foo.getName() + " " + bar.getName() + "!";
    }

}

Repositories:

@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {
  Foo findById(Integer id);
}

@Repository
public interface BarRepository extends JpaRepository<Bar, Long> {
  Bar findById(Integer id);
}

Entities for database @Primaria . The entities of the secondary database are equivalent (changing the name of the classes):

@Entity
@Table(name = "foo")
public class Foo {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Integer id;

    @Column(name = "name")
    private String name;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "foo")
    @JsonIgnoreProperties({"foo"})
    private Set<ManyFoo> manyFoos = new HashSet<>(0);

    // Constructors, Getters, Setters
}

@Entity
@Table(name = "many_foo")
public class ManyFoo {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Integer id;

    @Column(name = "name")
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JsonIgnoreProperties({"manyFoos"})
    private Foo foo;

    // Constructors, Getters, Setters
}  

Finally, the main method:

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

Thank you!

    
asked by Gael 07.03.2018 в 17:31
source

1 answer

1

I really can not tell you why the load lazy works with the TransactionManager marked as @Primary , but the problem you have is that you are returning from your REST controller a list of JPA / Hibernate entities List<Bar> . The problem with doing this is that you run the risk that when you try to work with those entities, as is the case, they have been marked as detached since they are outside of a transaction. Since you do not use annotation @Transactional , the transaction dies with the call to the findAll() method of the repository and when SpringMVC needs to transform the entities to JSON to send them to the client, the entity tries to load the relationships marked as lazy and there occurs the error.

To solve this, what I recommend is that (ALWAYS) use a services layer where you mark with @Transactional the methods that need to perform several actions on entities. You must take into account that having more than one TransactionManager you have to specify which one to use.

This is an example of a service that allows you to obtain entity listings.

The first thing I will need is a POJO class for each entity.

public class BarDto {
    private Integer id;
    private String name;
    private List<ManyBarDto> manyBars = new ArrayList();

    // Constructores, getters y setters
}

public class ManyBarDto {
    private Integer id;
    private String name;

    // Constructores, getters y setters
}

Now my service.

public interface BarService {
    List<BarDto> getAllBars();
}

@Service
@Transactional("barTransactionManager")
public class BarServiceImpl implements BarService {


    private final BarRepository barRepo;

    @Autowired
    public BarServiceImpl(BarRepository barRepo) {
        this.barRepo = barRepo;
    }

    public List<BarDto> getAllBars() {
        List<Bar> barList = barRepo.findAll();
        List<BarDto> barDtoList = new ArrayList<>();

        for (Bar bar : barList) {
            BarDto barDto= new BarDto();

            barDto.setId(bar.getId());
            barDto.setName(bar.getName());

            Set<ManyBar> manyBarList = bar.getManyBars();
            for (ManyBar manyBar : manyBarList) {
                ManyBarDto manyBarDto = new ManyBarDto();

                manyBarDto.setId(manyBar.getId());
                manyBarDto.setName(manyBar.getName);

                barDto.getManyBars().add(manyBarDto);
            }

            barDtoList.add(barDto);
        }

        return barDtoList;
    }
}

Then in the REST driver we would use the service instead of the repository.

@RestController
public class FooBarController {

    private final BarService barService;

    @Autowired
    FooBarController(BarService barService) {
        this.barService = barService;
    }

    @GetMapping("/bar")
    public List<BarDto> listBar() {
        return barService.getAllBars();
    }

    // Resto de métodos
}

This is the recommended way to work, to have the code well organized, so that each class has a clear responsibility and to avoid mistakes like you have. It is a VERY bad practice for entities to be manipulated in several layers of the application, and that is what you do when you return them from your REST controllers.

I hope that with this you can solve your problem, and as I said before, I have no idea why it works with @Primary .

    
answered by 08.03.2018 / 21:22
source