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

Viewed 9231

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

1 Answers
Related