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

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

Answer

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

Advertisement

Answer