GeeCON Prague 2014*
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


JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Sequence  Problem" Watch "Sequence  Problem" New topic
Author

Sequence Problem

Rama Krishna Yeluvolu
Greenhorn

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

0+12345+00

i have to use 01234500 to save the record

Appreciate any help in this Regard .

Thanks
Rama Krishna
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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)

Mark


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

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();
pf_seq=(String)it.next();

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

Regards
Rama Krishna
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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

Mark
Rama Krishna Yeluvolu
Greenhorn

Joined: Dec 31, 2007
Posts: 3
Hi Mark

it worked for me .Thanks once again

Regards
Y Rama Krishna
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Sequence Problem