Getting Next value from sequence with spring hibernate

Question

I am using spring jpa repository with hibernate to save entites to my oracle database. How I can get the next value of my oracle database sequence using Spring-Hibernate?

This is my Event class :

@Entity
public class Event {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

  private Long seriesId;

  private String description;

  public Event() {
  }

  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public Long getSeriesId() {
    return seriesId;
  }

  public void setSeriesId(Long seriesId) {
    this.seriesId = seriesId;
  }

  public String getDescription() {
    return description;
  }

  public void setDescription(String description) {
    this.description = description;
  }
}

I need to get the next value of the sequence once for the all event series in the event resolver.

public class EventResolver {

    @Autowired
    private EventRepository eventRepository;

    public void createSeriesOfEvents(List<EventAPI> eventsToCreate){

        Long seriesId = null; // TODO: Get the series id from database sequence

        for (EventAPI currEvent : eventsToCreate){
            Event newEvent = new Event();
            newEvent.setDescription(currEvent.description);
            newEvent.setSeriesId(seriesId);
            eventRepository.save(newEvent);
        }

    }
}

Thanks for any kind of help..

Solution 1

Finally I Solved my problem in the Spring way, All you need is to add a native query in the JpaRepository like this:

public interface EventRepository extends JpaRepository<Event, Long> {

 @Query(value = "SELECT seq_name.nextval FROM dual", nativeQuery = 
        true)
 Long getNextSeriesId();

Solution 2

With Spring 5, you can use one of their built-in classes for this task like OracleSequenceMaxValueIncrementer

See all the available options in this package: https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/support/incrementer/package-summary.html

Solution 3

Annotate your id property like so:

@Id
@GeneratedValue(generator = "idSequence")
@SequenceGenerator(schema = "MYORASCHEMA", name = "idSequence", sequenceName = "MY_ORACLE_SEQ_NAME", allocationSize = 1)
@Column(name="ID")
private Long id;

Solution 4

You can use this approach in JPA:

Query q = em.createNativeQuery("select seq_name.nextval from dual");
return (Long)q.getSingleResult();

View more solutions

Comments

[Ron Badur]

from where I can get the em object?

[StanislavL]

em means EntityManager and you can autowire it

[Ron Badur]

I dont want that every object will get his own id , I want to give the same series id to all the series (4-5 objects of Event type)

[slambeth]

Do you have a parent table for the series?

[Ron Badur]

no I just want the option of creating a series of events - It means few events with the same series id but different id

[ktk]

just have to make sure the query compliance for dependant database(s).

[tplive]

Won't that break the thing - meaning that we use an interface so Spring can make the implementation and we DON'T have to worry about which db is underlying..? In this case, to switch the db I'd have to rewrite the interface...

[Ashwani Sharma]

I am using Oracle 12c but this query is not returning nothing and causing NullPointerException

[KidWithAComputer]

How to do it without using native query ? @Ron Badur, I am stuck at this beacuse I dont want to use native query.

[gagarwa]

This was a headache for me, so for others: YOU NEED THE ALLOCATION SIZE. Reason, see ntsim.uk/posts/…. Summary: Hibernate uses hi-lo strategy to get id, so if the db sequence doesn't allocate in blocks of 50, it will interfere with previously provided ids and you will get constraint exception.

[gagarwa]

@Ron You can use the same generator for your issue. Just use the same generator name, and declare it at class level. You should be able to use it in other classes too (though I haven't confirmed this).

[aswzen]

Can you explain what is Event object in your code?

[Moulaye Abderrahmane]

@aswzen Event is the object on which you are making the operation. the object you are going to query or save using this repository