title: Joining tables in different databases / sessionfactory in hibernate
body: <p>My application works with 2 databases: 1 SQL Server and 1 PostgreSQL.
  The SQL Server is used for the website and had some performance issues in the past, therefore, I separated some analysis data into a different database, where I am adding data constantly and calculate some things all day long.</p>
  
  <p>In most of the cases I use the databases separately, but in one case I would like to join two tables from different databases.</p>
  
  <p>Is there any way I can do this with Hibernate?</p>
  
  <p>To exemplify, I have this two simplified entities: Order and OrderSource.
  The Order Table is in the SQL Server and OrderSource is in the PostgreSQL.
  They both work fine by themselves, but when I added the OrderSource to the entity Order and Order to the entity OrderSource, it obviously went wrong, because they are trying to access a database that is configured in another SessionFactory.</p>
  
  <p>Order Entity:</p>
  
  <pre><code>@Entity
  @Table(name=DomainConstants.TB_ORDER, schema=DomainConstants.DB_SCHEMA) 
  public class Order {
  
    @Id
    @Column(name="cod")
    @GeneratedValue
    private Long id;
  
    @OneToOne(mappedBy="order", fetch=FetchType.LAZY)
    private OrderSource orderSource;
  
    public Long getId() {
      return id;
    }
  
    public void setId(Long id) {
      this.id = id;
    }
  
    public OrderSource getOrderSource() {
      return orderSource;
    }
  
    public void setOrderSource(OrderSource orderSource) {
      this.orderSource = orderSource;
    }
  }
  </code></pre>
  
  <p>OrderSource Entity:</p>
  
  <pre><code>@Entity
  @Table(name=DomainConstants.TB_ORDER_SOURCE, schema=DomainConstants.DB_SCHEMA_GOOGLE)
  public class OrderSource {
  
    @Id
    @Column(name="id")
    private long id;
  
    @Column(name="source")
    private String source;
  
    @Column(name="medium")
    private String medium;
  
    @OneToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="id")
    private Order order;
  
    public long getId() {
      return id;
    }
  
    public void setId(long id) {
      this.id = id;
    }
  
    public String getSource() {
      return source;
    }
  
    public void setSource(String source) {
      this.source = source;
    }
  
    public String getMedium() {
      return medium;
    }
  
    public void setMedium(String medium) {
      this.medium = medium;
    }
  
    public Order getOrder() {
      return order;
    }
  
    public void setOrder(Order order) {
      this.order = order;
    }
  
  }
  </code></pre>
  
  <p>One solution I could come up with was to work on my OrderService with my OrderDao and my OrderSourceDao.
  This way I removed the mapping from each of the entities and created one Service to get all Orders and iterate over all of them getting the OrderSource.
  This is, however, not as fast as I would like and I am not able to group data or order them.</p>
  
  <pre><code>@Component   
  public class OrderServiceImpl implements OrderService {
  
    private OrderDao orderDao;
    private OrderSourceDao orderSourceDao;
  
    @Autowired
    public OrderServiceImpl(OrderDao orderDao, OrderSourceDao orderSourceDao) {
      this.orderDao = orderDao;
      this.orderSourceDao = orderSourceDao;
    }
  
    public List&lt;Order&gt; getOrdersWithOrderSource() {
      List&lt;Order&gt; orders = this.orderDao.index(); // get all Orders
  
      for(Order order: orders)
          order.setOrderSource(this.orderSourceDao.findById(order.id)); // Find OrderSource by Order id
    }
  
  }
  </code></pre>
  
  <p>Has anyone come across something like this? Does anyone have a solution for this join on different databases?</p>
  
  <p>Thanks so much everyone!</p>