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 — aurelius
– aurelius, Commented Nov 9, 2018 at 8:19
@aurelius can you read my question more carefully? Specifically, point 3 and see the link that I gave — ip696
– ip696, Commented Nov 9, 2018 at 8: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
– StephaneM, Commented Nov 9, 2018 at 8:41
But I use hibernate5. Thank you for sharing your information about using this approach. I also wanted to learn other approaches. — ip696
– ip696, Commented Nov 9, 2018 at 8:45

ip696 · Accepted Answer · 2018-11-09 10:47:58Z

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

Ivan · Accepted Answer · 2021-09-17 00:56:00Z

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

Collectives™ on Stack Overflow

JPA/Hibernate5 get sequence nextval by sequence name

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related