Using existing Database Sequence and creating custom generator

Question

I have created a sequence in my Database as follows:

CREATE SEQUENCE "SCOTT"."ATA_SEQ_USERID" 
MINVALUE 1 MAXVALUE 9999999999999999999999999999 
INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER NOCYCLE ;

Now, I want to append the first two letters of the name of the user to the number created by this sequence and generate a user id everytime a new user registers, using Hibernate. How can I do that?

Lalit Kumar B · Accepted Answer · 2015-05-29 06:45:03Z

1

You just need to use:

SUBSTR function
concatenation operator || .

For example,

SQL> CREATE SEQUENCE s;

Sequence created.

SQL>
SQL> SELECT substr(ename, 1, 2)||s.nextval custom_seq FROM emp;

CUSTOM_SEQ
------------------------------------------
SM1
AL2
WA3
JO4
MA5
BL6
CL7
SC8
KI9
TU10
AD11
JA12
FO13
MI14

14 rows selected.

SQL>

answered May 29, 2015 at 6:45

Lalit Kumar B

49.5k13 gold badges105 silver badges132 bronze badges

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

6 Comments

Abhineet Kumar Over a year ago

I need to use this sequence in my web project and then generate the id, using HQL.

Lalit Kumar B Over a year ago

@AbhineetKumar Ah! ok. I added the hibernate tag back to your question. I am sorry, I could only help with the SQL query part. Can you use the query output in the application?

Abhineet Kumar Over a year ago

Well, if I can know the equivalent HQL statement for the query that you have given, I think it can work.

Lalit Kumar B Over a year ago

@AbhineetKumar That's cool! Please mark it answered if it helped you. Would help others.

Abhineet Kumar Over a year ago

No, it didn't. I couldn't get the result from the database into an iterator or a list after executing the above query. It says that Iterator is not supported in native SQL Queries.

|

Abhineet Kumar · Accepted Answer · 2015-06-05 04:58:58Z

0

Finally found the way to do it.

String sql = "select MY_SEQ_ID.nextval from dual";
        SQLQuery query = session.getCurrentSession().createSQLQuery(sql);
        List idList=query.list();
        BigDecimal number=(BigDecimal) idList.get(0);
        System.out.println(number);

where MY_SEQ_ID is my sequence id.

answered Jun 5, 2015 at 4:58

Abhineet Kumar

3063 silver badges22 bronze badges

Collectives™ on Stack Overflow

Using existing Database Sequence and creating custom generator

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related