How to create a sequence in JPA without creating it directly in the database

Question

I am doing a service that gets data from a source and fetches them into my Database by using JPA. The id is generated by using sequence. I have created the sequence on my DB by using this command:

CREATE SEQUENCE crm_test_sq_1 MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 100 NOCYCLE

However, I don't know how to create the sequence directly from my code.

Can anyone please help me to figure out this issue? Thank you so much!

My model class, modeltest.java:

    @Entity
    @Table(name = "MODEL_TEST")
    
    @JsonPropertyOrder({ "ID", "CODE", "NAME" })
    @XmlRootElement
    @NamedQueries({
        @NamedQuery(name = "modeltest.getall", query = "SELECT e FROM modelteste"),
        @NamedQuery(name = "modeltest.deleteAll", query = "DELETE FROM modeltest e")})
    public class modeltest implements Serializable {
        private static final long serialVersionUID = 1L;
    
        @Id
        @GeneratedValue(generator = "crm_test_sq", strategy = GenerationType.SEQUENCE)
        @SequenceGenerator(name = "crm_test_sq", sequenceName = "crm_test_sq",allocationSize=1)
        @Column(name="ID", unique=true, nullable=false, precision=10, scale=0)
        private Long id;
    
        @Column(name = "CODE")
        private String isocurrencycode;
    
        @Column(name = "NAME")
        private String currencyname;
    
        public modeltest() {
        }
//........... getter and setter method

My dao class, modeltestDao.java

public Boolean saveData() {
        
        
//............................................
            getEntityManager().createNamedQuery("modeltest.deleteAll").executeUpdate();
            
            // i want something like below, but obviously this line of code give me an error
            getEntityManager().createNativeQuery ("CREATE SEQUENCE crm_test_sq_1 MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 100 NOCYCLE").executeUpdate();
            
        /....................
        return true;
    }

EDIT: Btw, why i suppose to reset the sequence is each time i run the service (fetch data into database), the unique identifiers has increased by 1. For example, first time i run data, the id start with 1 and end with 10, second time i run data, the id start with 11 and end with 20. This problem only stop when i restart the server.

For example:
call saveData() first time
DATA IN TABLE:
ID 1 2 3 4 5
call saveData() second time
DATA IN TABLE:
ID 6 7 8 9 10

Other data do not change, but id. So i find a way to solve this is drop and recreate sequence each time i run saveData()

Side note: I recommend to maintain the db schema using a tool such as Liquibase. Liquibase supports sequences. Tools such as Liquibase allow you e.g. to rename columns without loosing the data in the column. JPA usually can only help you with the initial creation of the db tables. — Puce
– Puce, Commented Mar 23, 2016 at 10:44
Side note 2 (JsonPropertyOrder): Usually it's not recommended to export JPA entities to external clients, if this is what you want to achieve with the JsonPropertyOrder annotation. Reason: malicious clients might tamper with the db primary key. It's better to expose only business keys, not technical ids. — Puce
– Puce, Commented Mar 23, 2016 at 10:53
Thank you for answering my post, what i want to do is drop the sequence and recreate it each time i run the function saveData() (the reason why i do this is because i avoid the cache ID on database) . Therefore i need to stick the create and drop command of my sequence on my code. Hope you got what i mean — minh
– minh, Commented Mar 23, 2016 at 10:56
JPA should CREATE that sequence itself via the schema creation facilities that are part of JPA 2.1. Why do it yourself??! The implementation I use will create it automatically at runtime if it needs to create an instance of the Entity in the datastore and it finds the sequence is not present. — Neil Stockton
– Neil Stockton, Commented Mar 23, 2016 at 10:57

Vlad Mihalcea · Accepted Answer · 2017-05-03 14:06:22Z

5

There's a code smell in your proposal. Why do you need to avoid the database caching for the sequences in the first place? The whole point for a sequence is to generate unique identifiers, so you shouldn't recreate the sequence while the application is running.

If you create the sequence in that method, imagine what happens when that method is called concurrently by multiple threads.

If your use case is valid, and you really need to delete all those records and reset the sequence, and you are using Oracle 12c, you can do something like this:

entityManager.createNativeQuery(
    "ALTER SEQUENCE crm_test_sq_1 RESTART START WITH 1")
.executeUpdate();

edited May 3, 2017 at 14:06

answered Mar 23, 2016 at 13:26

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

minh Over a year ago

Hi, i have tried your code, buy it give me an error: " org.hibernate.util.JDBCExceptionReporter: ORA-02286: no options specified for ALTER SEQUENCE".

minh Over a year ago

Btw, why i suppose to reset the sequence is each time i run the service (fetch data into database), the unique identifiers has increased by 1. For example, first time i run data, the id start with 1 and end with 10, second time i run data, the id start with 11 and end with 20. This problem only stop when i restart the server.

minh Over a year ago

Thank you, i have found the solution, createNativeQuery() can be used for create sequence.

Collectives™ on Stack Overflow

How to create a sequence in JPA without creating it directly in the database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related