wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes Sequence  Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Sequence  Problem" Watch "Sequence  Problem" New topic

Sequence Problem

Rama Krishna Yeluvolu

Joined: Dec 31, 2007
Posts: 3
Hi ,

I know how to use the oracle sequence with hibernate but my problem is i have to append zero's to the number returned by sequence and i have to save the record with that newly generated number.

For Example :
seq_number:12345 . i have to append the zero's like below


i have to use 01234500 to save the record

Appreciate any help in this Regard .

Rama Krishna
Mark Spritzler

Joined: Feb 05, 2001
Posts: 17249

Well, when you call save() it returns an object with the new id in it. So before you commit() or flush() you can call getId(), append the zeros, and save it in some other field. I do not recommend using the zero filled number as your PK for your table.

Now, if you are using a legacy database where that is the PK already and it uses the sequence, then I recommend either an insert trigger, or don't use "sequence" or "native" as the generated type, but instead use "assigned" and in your code run a SQLQuery to get the next number "select myseq.nextval from dual", then fill with zeros and call setId(numberWithZeros)


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Rama Krishna Yeluvolu

Joined: Dec 31, 2007
Posts: 3
Hi Mark ,

Thanks for you reply

As you suggested ,i created a row level trigger on table before inserting record into that table.it works for me .i have a question here ,for example if table having more than 20,000 records if we create a row level trigger on it will it lead to any performance issues .In fact for inserting a record into table its not needed to check each row of that table.

But table level trigger does not work with this case as we can't use :NEW and LD references in table level trigger.

And tried the another way you suggested i.e Using SQLQuery to get the next number "select supplier_seq.nextVal from dual d", then fill with zeros and call setId(numberWithZeros) .i am getting following Exception

Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: dual is not mapped. [select supplier_seq.nextVal from dual d]
at org.hibernate.hql.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:157)
at org.hibernate.hql.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:87)
at org.hibernate.hql.ast.tree.FromClause.addFromElement(FromClause.java:70)
at org.hibernate.hql.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:265)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3049)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2938)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1583)
at com.rk.EmpDAO.getSeq(EmpDAO.java:42)
at com.rk.JoinDemo.main(JoinDemo.java:27)
Caused by: dual is not mapped.
... 21 more

Exception says that There is no mapping file for dual table .

Following is code i used

Iterator it=getSession().createQuery("select supplier_seq.nextVal from dual d").list().iterator();

in above code getSession() returns session

is there any wrong in my code ?

do we need to create a mapping file for dual table ?If so is that same way like normal table .

Please give me any suggestions on this regard

Thanks in advance

Rama Krishna
Mark Spritzler

Joined: Feb 05, 2001
Posts: 17249

You need to create a SQLQuery object, not a Query object.

so instead of calling createQuery() on the session object, call createSQLQuery() on the session object.

If you are inserting 20,000 rows at a time, yes the insert trigger will affect performance, but so will createSQLQuery and connecting to the database each time to get the id. So If you have a batch of inserts and the triggers fired on the database side, I would think would be less of a performance impact as compared to the 20,000 select calls to the database from the Hibernate side call. But it really does depend on many factors, that you can only be sure by testing in your test environment

Rama Krishna Yeluvolu

Joined: Dec 31, 2007
Posts: 3
Hi Mark

it worked for me .Thanks once again

Y Rama Krishna
I agree. Here's the link: http://aspose.com/file-tools
subject: Sequence Problem
Similar Threads
lock/read/write/unlock sequence
Leading zero's being trimmed off
prepareStatement problem with the driver classes12.zip
sequence number generation
How to get the next number of a table before insertion a record