First create sequence with name TABLENAME_SEQ on table and then use below query

select 'F'||lpad(TABLENAME_SEQ.NEXTVAL,3,'0') from DUAL;

03-15-2011, 08:48 AM

dswastik

Nice solution, but seems to be oracle specific, correct me if I am wrong.

03-15-2011, 08:54 AM

masijade

Yes, for MySQL you can use an auto-increment, for other DBs are other ways, but they all have this ability in one form or another.

03-15-2011, 09:21 AM

sanjeevtarar

Yes, this is Oracle specific syntax but for other DBs auto increment is possible

03-15-2011, 09:37 AM

Tolls

Except these auto-increment fields will not be in that format.
Since that's a display thing I would argue you simply stick a format over the top of it when you display, and strip it out when searching in the db.

As i mentioned before,i want the id will be autogenerate(example first data insert,id will autogenerate as F001 and so on) everytime the user insert data to database.

03-15-2011, 06:54 PM

dswastik

As per your table definition, the field id is of type int so in this you can't expect to have a character type data for e.g. F001. But as it is an auto increment field, it should keep increasing the id but with numerical values for e.g. 1,2 etc.

03-16-2011, 01:08 AM

daisy

first of all,i want to say thank you and really appreciate when someone reply my post:)
if i expected to have character type for my id,what must i do?if i not wrong i need to change`id` varchar NOT NULL, PRIMARY KEY,

based on my file' insert_data.jsp' before,where must i put the code given?

Try to use the solution suggested by Sanjeev, that looks much better. As far as putting a method is concerned, u should put it under declares section. But to add more putting java code inside jsp is not a proper approach. You should put your java code in java classes, jsp should only be used for representing your output.

03-16-2011, 09:28 AM

Tolls

Don't use that "SELECT MAX(ID)...".
There will come a time when two people will hit your system at the same time and will both get the same ID and, unless you cover your self against duplicate primary key errors, you'll have problems.

What is wrong with simply using a formatter? This really is a display thing...

03-16-2011, 09:51 AM

masijade

Simply use the insert and let the db generate a key in the autoincrement. If you want to "see" a specific format then simply apply the format after the fact (when you display the data). If you need to know what the key is after inserting then see the API docs for Connection and PreparedStatement (which you are hopefully using). There are ways to "get" those autogenerated keys directly from the PreparedStatement that caused their creation if you create/prepare the statement properly.

03-16-2011, 09:58 AM

Tolls

This is what I've been saying...but no one seems to be listening.
It's a display issue, not a db issue.

03-16-2011, 10:22 AM

masijade

Quote:

Originally Posted by Tolls

This is what I've been saying...but no one seems to be listening.
It's a display issue, not a db issue.

I wasn't, at first, aware that it was really a formatting issue. For some reason I was just thinking it was sequence/increment issue. ;-)

03-16-2011, 10:34 AM

Tolls

It can be hard to tell sometimes, it has to be said.
Just seen too many of these sorts of things on db forums where someone insists on having their id field being of a certain format, except that that format is entirely for display purposes and it's really only the number that needs to be stored. Often it's like talking to a brick wall, though...