JPA/Hibernate5 get sequence nextval by sequence name

Question

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&#xd;

Possible duplicate of [get next sequence value from database using hibernate](https://stackoverflow.com/questions/6386888/get-next-sequence-value-from-database-using-hibernate) — aurelius, Nov 09 '18 at 08:19
@aurelius can you read my question more carefully? Specifically, point 3 and see the link that I gave — ip696, Nov 09 '18 at 08:21
Point 3 also shows a method for hibernate 4. And using the dialect IS a good approach. (at least this is how we do and this works fine). — StephaneM, Nov 09 '18 at 08:41
But I use hibernate5. Thank you for sharing your information about using this approach. I also wanted to learn other approaches. — ip696, Nov 09 '18 at 08:45

ip696 · Answer 1 · 2018-11-09T10:47:58.467

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();
    }
}

score 0 · Answer 2 · answered Sep 17 '21 at 00:56

Try this:

// Get metadata from connection
DatabaseMetaData metaData = connection.getMetaData();

// Create adapter between MetaData and DialectResolutionInfo
DialectResolutionInfo info = new DatabaseMetaDataDialectResolutionInfoAdapter(metaData);

// Resolve dialect
DialectResolver dialectResolver = new StandardDialectResolver();
Dialect dialect = dialectResolver.resolveDialect(info);

// Use it
System.out.println(dialect.getSelectSequenceNextValString(sequenceName));

JPA/Hibernate5 get sequence nextval by sequence name

2 Answers2