Using custom queries, but maintaining database independence

Question

With Spring JPA, is there an easy way to use custom queries, but still maintain database independence, for example, using the best query?

At the moment, I am doing this by checking the currently installed Dialectfrom Environmentand calling the correct method of mine Repository:

public Foo fetchFoo() {
    if (POSTGRES_DIALECT.equals(env.getRequiredProperty("hibernate.dialect"))) {
      return repo.postgresOptimizedGetFoo();
    }    
    return repo.getFoo();
}

It works, but I have the feeling that there is a better way or that I am missing something. Moreover, (Spring) JPA makes it easy to use your own queries, but this violates one of its biggest advantages: database independence.

Answer 1

@Transactional (readOnly = false), session.createQuery session.createSQLQuery, . sql . , .:)

@Override
@Transactional(readOnly = false)
public Long getSeqVal() {
Session session = entityManager.unwrap(Session.class);
String sql = "SELECT nextval('seqName')";
Query query = session.createSQLQuery(sql);
BigInteger big = (BigInteger) query.list().get(0);
return big.longValue();
}

Answer 2

: , :

, Spring -Data-JPA- entiy ( ). , "" . ( intrface , DB). Spring -JPA (, -)

Answer 3

.

, . orm.xml ( , Spring Boot schema.xml ).

public interface YourRepository extends JpaRepository<YourEntity, Long> {

    List<YourEntity> yourQueryMethod();
}

YourEntity.yourQueryMethod. orm.xml ( , - ).

LocalContainerEntityManagerFactory . , , , , database.type, -

<bean class="LocalContainerEntityManagerFactoryBean">        
    <property name="mappingResources" value="classpath:META-INF/orm-${database.type}.xml" />
    ... other config ...
</bean>

This way you can keep your code clean if / then / else and apply where necessary. Cleans your code beautifully imho.