MySQL Generate AlphaNumberic Id In JSP

This example explains you that how to generate alpha numeric id in MySQL and also how to fetch the record from database table in JSP and display the record in specific format into JSP page. This example explains you all the steps for creating the alpha numeric id using MySQL as database, tomcat 7 as a server for deploying the JSP, and the Eclipse IDE for writing and compiling the web application.

MySQL Generate AlphaNumberic Id In JSP

MySQL Generate AlphaNumberic Id In JSP

In this section we will discus about how to generate id in specific format in MySQL and JSP.

This example explains you that how to generate alpha numeric id in MySQL and also how to fetch the record from database table in JSP and display the record in specific format into JSP page. This example explains you all the steps for creating the alpha numeric id using MySQL as database, tomcat 7 as a server for deploying the JSP, and the Eclipse IDE for writing and compiling the web application.

This tutorial is about generating the id by incrementing the id value automatically. In the generated alpha numeric id only the integer value will be incremented automatically whereas, the alphabets will be remain constant as "alphabets-id". As well as we will also generate a bill number which will contain the some "alphabets/daymonth/id".

Example

Here I am giving an example for how to generate alpha numeric id in the database using MySQL as well as how we will do this in JSP. To accomplish this task we will first create a table in MySQL (query is given below) and will insert some data into the table. Then we will generate the alpha numeric employee number and bill number. Then we will create a JSP page for generating the employee number and the bill number as generated in the database.

SQL query for creating the database table

CREATE TABLE `employee` ( 
`tag_id` char(3) NOT NULL, 
`serial_id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT, 
`dd` int(2) unsigned zerofill DEFAULT NULL, 
`mm` int(2) unsigned zerofill DEFAULT NULL, 
PRIMARY KEY (`tag_id`,`serial_id`), 
UNIQUE KEY `serial_id` (`serial_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 

To insert data into the table query is as follows :

insert into `employee`(`tag_id`,`serial_id`,`dd`,`mm`) values ( 'SCS','0001 ','08','02')
insert into `employee`(`tag_id`,`serial_id`,`dd`,`mm`) values ( 'SCS','0002','09','02')

After inserting the value into the table the database table will be as follows :

To generate the alpha numeric employee id in MySQL we can use the concat() function of MySQL as follows :

SELECT CONCAT(tag_id,'-',serial_id) as employeeId FROM employee;

The employeeId will be as follows :

To generate the bill number in MySQL we can use the concat() function of MySQL as follows :

SELECT CONCAT(tag_id,'/',dd,mm,'/',serial_id) as billnumber FROM employee;

The billnumber will be as follows :

Now to do as above in the JSP we will create a JSP page where we will write the code for connecting the database and the query for fetching the resultset. Then after we will concatenate the variables into the format which we are required. To create a JSP page we will use Eclipse IDE.

jdbcGetSpecificFormatData.jsp

<%@ page language="java" contentType="text/html;
charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 
Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*" %>
<html>
<head>
<meta http-equiv="Content-Type" 
content="text/html; charset=ISO-8859-1">
<title>Get Employee Detail</title>
</head>
<body>
<%!String driver = "com.mysql.jdbc.Driver";%>
<%!String url = "jdbc:mysql://localhost/record";%>
<%!String uid = "root";%>
<%!String psw = "root";%>
<%!String sql = "SELECT * FROM employee";%>
<%
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;

try
{
Class.forName(driver);
con = DriverManager.getConnection(url,uid,psw);
ps = con.prepareStatement(sql);
//boolean bol = ps.execute();
rs = ps.executeQuery(); 
%>
<table border="1">
<tr>
<th>employee number</th>
<th>bill number</th>
</tr>
<tr>
<%
while(rs.next())
{
String t_id = rs.getString("tag_id");
String s_id = rs.getString("serial_id");
String d = rs.getString("dd");
String m = rs.getString("mm");
String employeeNumber = t_id+"-"+s_id;
String billNumber = t_id+"/"+d+m+"/"+s_id; 
%>

<td> <%= employeeNumber %> </td>
<td> <%= billNumber %> </td>
</tr> 
<%
}
%>
</table>
<br>
<% 
}
catch(SQLException sqle)
{
out.println(sqle);
}
finally{
try{
if(rs != null)
{
rs.close();
}
if(ps != null)
{
ps.close();
}
if(con != null)
{
con.close();
}
}
catch(Exception e)
{
out.println(e);
}
}
%>
</body>
</html>

Now we will deploy this JSP page using Tomcat 7 server.

Output

After successfully deployment of the above JSP page the output will be as follows :

Download Source Code (WAR file)