Check that specific sequence exists or not in hibernate

Question

I want to run a query like this -

"SELECT nextval('sequenceName')"

but before this I want to check that sequenceName exists or not in Hibernate.

Answer 1

The answer here gives me an idea Check if sequence exists in Postgres (plpgsql), I don't know if this is a perfect solution or not but you can use :

SELECT COUNT(*) 
FROM information_schema.sequences 
WHERE sequence_schema='sch_name' AND sequence_name='sequence_name'

This will give you 0 (if not exist) or 1 (if exist).

So to solve your problem, you have to execute your query in two shots, one to check if your sequence exist, and the second is to select your sequence, so your code should look like this :

String sch_name = "sch_test";
String sequence_name = "sequence_name";

//Check if your sequence exist or not
Query q = createNativeQuery("SELECT COUNT(*) FROM        
                             information_schema.sequences 
                             WHERE sequence_schema='" + sch_name + "' 
                             AND sequence_name='" + sequence_name + "'");

int i = (Integer) q.getSingleResult();
int sequence = 0;

//if the sequence exit it will return 1
if(i == 1){
  //increment your sequence and return the result
  q = createNativeQuery("SELECT nextval('" + sequence_name + "')");
  sequence = (Integer) q.getSingleResult();
}else{
   //sequence not exist
}