create id

**daisy** · 03-15-2011, 04:15 AM

Hi, i'm a newbie in java. I want to create an id for example:-

when i insert a new data, the first data will create an id such as F001,then when a second data insert it will create an id such as F002,third data will be F003 and so on..

thanks in advance

**dswastik** · 03-15-2011, 07:29 AM

You might create a function like this

Java Code:

  private String getNewRowId(Connection con){
	int maxVal=0;
	String sql="SELECT MAX(ID) FROM TABLENAME";
	Statement st=con.createStatement();
	String lastId="";
	ResultSet rs=st.executeQuery(sql);
	if(rs.hasNext()){
	   lastId=rs.getString(1);
	   maxVal=Integer.parseInt(lastId.substring(0,4));
	}
	//close ResultSet and Statement
	maxVal++;
	if(maxVal>=1 && maxVal<=9){
		return "F00"+maxVal;
	}
	else if(maxVal>=10 && maxVal<=99){
		return "F0"+maxVal;
	}
	else{
		return "F"+maxVal;
	}
}

There might be better approaches too.

**sanjeevtarar** · 03-15-2011, 07:40 AM

Hello,

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

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

**dswastik** · 03-15-2011, 07:48 AM

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

**masijade** · 03-15-2011, 07:54 AM

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.

**sanjeevtarar** · 03-15-2011, 08:21 AM

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

**Tolls** · 03-15-2011, 08:37 AM

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.

**daisy** · 03-15-2011, 05:21 PM

Actually i have created a table as below:-

CREATE TABLE `input` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`transaction_date` VARCHAR(15) NOT NULL,
`operator1` VARCHAR(15) NOT NULL,
`validstart_date` VARCHAR(15) NOT NULL,
`operator2` VARCHAR(15) NOT NULL,
`validend_date` VARCHAR(15) NOT NULL,
`filename` VARCHAR(255) NOT NULL,
`filetype` VARCHAR(255) NOT NULL,
`filepath` VARCHAR(255) NOT NULL
);

The data will be insert into the table based on form below (where i saved the file as insert_data.jsp)

<%@ page import="java.util.Date,java.text.DateFormat,java.t ext.SimpleDateFormat"%>
<%
DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
Date date = new Date();

%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Page Insert</title>
</head>

<body>
<form action="" method="post" enctype="multipart/form-data" >
<table width="361" border="2" align="center">
<tr>
<th scope="row">ID</th>

<td><input type="text" name="textfield" size="15">
</tr>

<tr>
<th scope="row">TRANSACTION</th>

<td><input type="text" name="transaction" readonly="true" value="<%=dateFormat.format(date)%>">
</tr>
<tr>
<th scope="row">VALID START </th>
<td><select name="start" size="1" >
<option selected>equal</option>
<option>before</option>
<option>after</option>
<option>meet</option>
<option>met_by</option>
</select>
<input type="text" name="textfield" size="15">
</tr>
<tr>
<th scope="row">VALID END </th>
<td><select name="end" size="1" id="end">
<option selected>equal</option>
<option>before</option>
<option>after</option>
<option>meet</option>
<option>met_by</option>
</select>
<input type="text" name="textfieldd" size="15">
</tr>
<tr>
<th scope="row">UPLOAD FILE </th>

<td><input type="file" name="file"> </tr>
</table>

<p align="center">
<input type="submit" name="Submit" value="Submit">
<input type="reset" name="Reset" value="Reset">
</p>

</form>
</body>
</html>

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.

**dswastik** · 03-15-2011, 05:54 PM

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.

**daisy** · 03-16-2011, 12:08 AM

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?

private String getNewRowId(Connection con){
int maxVal=0;
String sql="SELECT MAX(ID) FROM TABLENAME";
Statement st=con.createStatement();
String lastId="";
ResultSet rs=st.executeQuery(sql);
if(rs.hasNext()){
lastId=rs.getString(1);
maxVal=Integer.parseInt(lastId.substring(0,4));
}
//close ResultSet and Statement
maxVal++;
if(maxVal>=1 && maxVal<=9){
return "F00"+maxVal;
}
else if(maxVal>=10 && maxVal<=99){
return "F0"+maxVal;
}
else{
return "F"+maxVal;
}
}

**dswastik** · 03-16-2011, 06:51 AM

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.

**Tolls** · 03-16-2011, 08:28 AM

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...

**masijade** · 03-16-2011, 08:51 AM

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.

**Tolls** · 03-16-2011, 08:58 AM

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

**masijade** · 03-16-2011, 09:22 AM

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. ;-)

**Tolls** · 03-16-2011, 09:34 AM

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...

Thread: create id

LinkBack

Thread Tools

Search Thread

Display

create id

Similar Threads

jsp to create xml

how to create log of sample2010-05-06.log

How to create the Jvm using JDK?

How to create a GeneralPath

Create XML From XSD

Bookmarks

Bookmarks

Posting Permissions

PDF to TIFF Conversion & Control...

License4J 4.0

Pastèque Server 0.3

Nuiton-I18n 2.5.1

Xowa 0.5.2

DEMUX Framework 0.1

GSLB.me Dynamic DNS Client 0.1

ZedLog 0.2 Beta

Dragon parser generator 1.3.9

Data Crow 3.10

Switzerlandâ€ Fabiol

Deutschland Air Max Shining a...

Air Max Schuhe Collection brings...

Cant dispatch event or listener?

Java library or framework to...

[NEED HELP]coding a simple...

Deleting columns in excel using...

My First Post in Java - Forums

PDF to TIFF Conversion & Control...

Hi