JPA: left join without @OneToMany annotations

Question

I have a OneToMany relationship in my DB but I don't want that Hibernate manages it directly.

This relationships are translations, but a DTO represents itself a translated registry:

@Entity
@Table(name = "my_table")
public class MyTable {

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

    @Transient
    private String lang;

    @Transient
    private String text;

    // getters and setters
    ...
}

@Entity
@Table(name = "my_table_translation")
public class MyTableTranslation {

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

    @Id
    @Column(name = "lang", nullable = false, unique = false, length = 2)
    private String lang;

    @Column(name = "text", nullable = false, unique = false, length = 200)
    private String text;

    // getters and setters
    ...
}

I want to have an specific findAll(String lang) method with a lang parameter, and use an Specification Criteria to build the query. Something like that:

public void findAll(String language) {
    List<MyTable> list = repository.findAll(new Specification<MyTable>() {
        @Override
        public Predicate toPredicate(Root<MyTable> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            // something there
            return ...;
        }
    });
}

The fact is that I don't know how to do that, because I can't use JOIN clause, as I have not an attribute in the model that represents the relationship.

I tried to use the SELECT...FROM...LEFT JOIN query with SQL notation,

SELECT t1, t2 FROM MyTable t1 LEFT JOIN MyTableTranslation t2 ON t1.id = t2.id

and it works, but not as desired. The resulting list of objects, is a list of 2 object per item: one is the MyTable object, and the other is the MyTableTranslation related object. I need to parse the list and programatically build the objects using PropertyUtils class from Apache Commons library.

It is not clean I think... Does anybody know how to make it easy, without using SQL notation?

Answer 1

Marc, you can do the following to make it work and you do not need complicated join clauses or predicate right now. A simple implementation in embedded H2 database and JUnit testing will be sufficient for proof of concept (POC) as below

NOTE:

1. I am using Spring + Plain JPA with Hibernate implementation for POC.
2. I am using the Spring recommended way of managing transaction.
3. com.mycompany.h2.jpa package contains the entity classes.
4. Take a look at the mytable.sql which has similar structure to your needs.

MyTable.java

@Entity
@Table(name = "my_table")
public class MyTable implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, unique = true)
    @JoinColumn(referencedColumnName="id", insertable=true, updatable=false)
    private Long id;

    @Column(name = "lang", unique=true)
    private String lang;

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

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name="id", insertable=true, updatable=true, referencedColumnName="id")
    private List<MyTableTranslation> translations = new ArrayList<MyTableTranslation>();
    ...
    // getters and setters, toString()
}   

MyTableTranslation.java

@Entity
@Table(name = "my_table_translation")
public class MyTableTranslation implements Serializable {

    private static final long serialVersionUID = 11L;

    @Id
    @Column(name = "id")
    private Long id;

    @Id
    @Column(name = "speaker")
    String speaker;
    ...
    // getters and setters, toString()
}

TestH2DatabaseConfiguration.java

@Configuration
@EnableTransactionManagement
public class TestH2DatabaseConfiguration {

    final static Logger log = LoggerFactory.getLogger(TestH2DatabaseConfiguration.class);

    @Bean
    @Qualifier("dataSource")
    public DataSource h2DataSource() {
        return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).addScript("classpath:mytable.sql").build();
    }

    @Bean
    public EntityManagerFactory entityManagerFactory() {

        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setGenerateDdl(true);

        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        factoryBean.setDataSource(h2DataSource());
        factoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        factoryBean.setPackagesToScan("com.mycompany.h2.jpa");
        factoryBean.setPersistenceUnitName("my_table");

        Properties prop = new Properties();
        prop.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
        prop.put("hibernate.show_sql", "true");
        prop.put("hibernate.hbm2ddl.auto", "none");
        factoryBean.setJpaProperties(prop);

        factoryBean.afterPropertiesSet();

        return factoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager txManager = new JpaTransactionManager();
        txManager.setEntityManagerFactory(entityManagerFactory());
        return txManager;
    }

    @Bean
    public MyTableDAO myTableDAO() {
        return new MyTableDAOJPAImpl();
    }

    @Bean
    public MyTableServiceImpl myTableService() {
        MyTableServiceImpl myTableService = new MyTableServiceImpl();
        myTableService.setMyTableDAO(myTableDAO());
        return myTableService;
    }
}

MyTableService.java

public interface MyTableService {   
    public MyTable saveMyTableTranslation(MyTable myTable);
    public List<MyTable> getAllMyTables();
    public MyTable getMyTable(Long entityId);
    public MyTable getMyTable(String lang);
}

MyTableServiceImpl.java

@Transactional
public class MyTableServiceImpl implements MyTableService {

    final static Logger log = LoggerFactory.getLogger(MyTableServiceImpl.class);

    private MyTableDAO myTableDAO;

    public void setMyTableDAO(MyTableDAO myTableDAO) {
        this.myTableDAO = myTableDAO;
    }   

    public MyTable saveMyTableTranslation(MyTable myTable) {
        return myTableDAO.saveMyTableTranslation(myTable);
    }

    public List<MyTable> getAllMyTables() {
        return myTableDAO.getAllMyTables();
    }

    public MyTable getMyTable(Long entityId) {
        return myTableDAO.getMyTable(entityId);
    }

    public MyTable getMyTable(String lang) {
        return myTableDAO.getMyTable(lang);
    }
}

MyTableDAO.java

public interface MyTableDAO {
    public MyTable saveMyTableTranslation(MyTable myTable);
    public List<MyTable> getAllMyTables();
    public MyTable getMyTable(Long entityId);
    public MyTable getMyTable(String lang);
}

MyTableDAOJPAImpl.java

public class MyTableDAOJPAImpl implements MyTableDAO {

    final static Logger log = LoggerFactory.getLogger(MyTableDAOJPAImpl.class);

    @PersistenceContext
    private EntityManager entityManager;

    public MyTable saveMyTableTranslation(MyTable myTable) {
        entityManager.persist(myTable);
        return myTable;
    }

    @SuppressWarnings("unchecked")
    public List<MyTable> getAllMyTables() {
        return (List<MyTable>) entityManager.createQuery("FROM MyTable").getResultList();       
    }

    public MyTable getMyTable(Long entityId) {
        return (MyTable) entityManager.createQuery("FROM MyTable m WHERE m.id = :id ").setParameter("id", entityId).getSingleResult();
    }

    public MyTable getMyTable(String lang) {
        return (MyTable) entityManager.createQuery("FROM MyTable m WHERE m.lang = :lang ").setParameter("lang", lang).getSingleResult();
    }
}

MyTableTest.java (a JUnit test class)

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { TestH2DatabaseConfiguration.class }, loader = AnnotationConfigContextLoader.class)
public class MyTableTest extends AbstractTransactionalJUnit4SpringContextTests {

    final static Logger log = LoggerFactory.getLogger(MyTableTest.class);

    @Autowired
    @Qualifier("myTableService")
    MyTableService myTableService;  


    @Test
    public void test() throws ParseException {

        MyTable parent = new MyTable();
        parent.setLang("Italian");
        parent.setText("Fast...");

        MyTableTranslation child = new MyTableTranslation();
        child.setSpeaker("Liotta");

        parent = myTableService.saveMyTableTranslation(parent);

        log.debug("parent ID : " + parent.getId());

        MyTable spanishTables= myTableService.getMyTable("Spanish");
        List<MyTableTranslation> spanishTranslations = spanishTables.getTranslations();
        log.debug("spanishTranslations SIZE : " + spanishTranslations.size());

        for (MyTableTranslation myTableTranslation : spanishTranslations) {
            log.debug("myTableTranslation -> : " + myTableTranslation);
        }       
    }
}

mytable.sql

CREATE TABLE IF NOT EXISTS my_table (
  id      IDENTITY PRIMARY KEY,
  lang    VARCHAR UNIQUE,
  text    VARCHAR
);

delete from my_table;
INSERT INTO my_table VALUES (1, 'Spanish', 'Beautiful...');
INSERT INTO my_table VALUES (2, 'English', 'Great...');
INSERT INTO my_table VALUES (3, 'French', 'Romantic...');


CREATE TABLE IF NOT EXISTS my_table_translation (
  id      INTEGER,
  speaker VARCHAR
);

delete from my_table_translation;
INSERT INTO my_table_translation VALUES (1, 'Eduardo');
INSERT INTO my_table_translation VALUES (1, 'Diego');
INSERT INTO my_table_translation VALUES (2, 'George');
INSERT INTO my_table_translation VALUES (3, 'Pierre');

Answer 2

I tried to use @OneToMany annotation, and I change my DTO:

@Entity
@Table(name = "my_table")
public class MyTable {

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

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "id", referencedColumnName = "id")
    private List<MyTableTranslation> translations = new ArrayList<MyTableTranslation>();

    // getters and setters
    ...
}

And changed the Criteria as:

public void findAll(String isolang) {
    List<MyTable> list = repository.findAll(new Specification<MyTable>() {
        @Override
        public Predicate toPredicate(Root<MyTable> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Join<Object, Object> langJoin = root.join("translations", JoinType.LEFT);
            return cb.equal(langJoin.get("lang"), isolang);
        }
    });
}

But the list has no items. If I change the FetchType to EAGER, the Predicate has no effect, I get all the languages. I don't know how to proceed now...