Wsrtjtyk

How can I get sequence nextval in JPA or Hibernate 5 by sequence name?

I have sequence the following TEST_SEQ in Oracle DB and ANOTHER_NAME_SEQ in Postgresql DB.

I need a method with following signature

public Long getSequenceByName(String sequenceName){}

And when I call this method it must return nextval from DB which is now used.

I have a couple of ideas, but they are not suitable.

1) Store native query for each DB in properties and write method like this:

@Value("${query}")//"SELECT {name}.NEXTVAL FROM DUAL"

private StringQuery;



public Long getSequenceByName(String sequenceName){

    uery q = em.createNativeQuery(StringQuery.replace("{name}", sequenceName));

    return (java.math.BigDecimal) q.getSingleResult();

  }

But I need to store the query string with placeholders and replace placeholder to sequence name, store query for each DB.

2) Create entity with only one field @Id. Insert entity and getId(sequence value).

But if in different DB is different sequence names - ???

3) Use this. But It for hibernate 3 and I don't know if this is a good approach.

EDIT:

I try this solution:

@Component

public class SequenseRepository {



    @PersistenceContext

    private EntityManager em;



    @Transactional

    public Long getID(final String sequenceName) {

        final List<Long> ids = new ArrayList<>(1);



        Session session = em.unwrap(Session.class);

        session.doWork(connection -> {

            DialectResolver dialectResolver = new StandardDialectResolver();

            Dialect dialect =  dialectResolver.resolveDialect((DialectResolutionInfo) connection.getMetaData());

            PreparedStatement preparedStatement = null;

            ResultSet resultSet = null;

            try {

                preparedStatement = connection.prepareStatement( dialect.getSequenceNextValString(sequenceName));

                resultSet = preparedStatement.executeQuery();

                resultSet.next();

                ids.add(resultSet.getLong(1));

            }catch (SQLException e) {

                throw e;

            } finally {

                if(preparedStatement != null) {

                    preparedStatement.close();

                }

                if(resultSet != null) {

                    resultSet.close();

                }

            }

        });



        return ids.get(0);

    }

}

And I get exeption:

java.lang.ClassCastException: oracle.jdbc.driver.OracleDatabaseMetaData cannot be cast to org.hibernate.engine.jdbc.dialect.spi.DialectResolutionInfo

I found solution thanks to this article enter link description here

    public interface SequenceRepository {

    int getNext(String sequenceName);

   }

and implementation for each DB:

@Profile("oracle")

@Component("oracleSequenceRepository")

public class OracleSequenceRepository implements SequenceRepository{



    private final DataSource dataSource;



    @Autowired

    public OracleSequenceRepository(@Qualifier("dataSource") DataSource dataSource) {

        this.dataSource = dataSource;

    }



    @Transactional(readOnly = true)

    @Override

    public int getNext(String sequenceName) {

        AbstractSequenceMaxValueIncrementer incr = new OracleSequenceMaxValueIncrementer(this.dataSource, sequenceName);

        return incr.nextIntValue();

    }

}

and

@Profile("postgre")

@Component("postgresSequenceRepository")

public class PostgreSequenceRepository implements SequenceRepository{



    private final DataSource dataSource;



    @Autowired

    public PostgreSequenceRepository(@Qualifier("dataSource") DataSource dataSource) {

        this.dataSource = dataSource;

    }



    @Transactional(readOnly = true)

    @Override

    public int getNext(String sequenceName) {

        AbstractSequenceMaxValueIncrementer incr = new PostgresSequenceMaxValueIncrementer(this.dataSource, sequenceName);

        return incr.nextIntValue();

    }

}