In this section we will discuss how can we add an auto_increment into a column.
Mysql Add AutoIncrement using java servlet
In this section we will discuss how can we add an auto_increment into a column.
An auto_increment is a feature in mysql database system that it generates a random unique number of a field and added incremented value automatically to the table. To understand how it is used with the java servlet we will create a table in mysql database system then will make a java servlet program. So at first I created a table in mysql named 'employee' with the fields " Id, Name, Department, Salary, DepartmentId" where I made the Id field = primary key, not null and keep the auto_increment box unchecked. Now I made a servlet class named AddAutoIncrementServlet which extends a HttpServlet class. In the body of class I overridden the doGet() method and created an objects of HttpServletRequest and HttpServletResponse interfaces. Inside the doGet() method first I set mime type that into which format the browser will show the output. In the next line I used the getWriter() method of ServletResponse interface with the object of HttpServletResponse. HttpServletResponse interface extends this method from the ServletResponse. In continue since we have to use a SQL query so at first we will have to make a connection between java code and database system. I am using here Mysql so in the example given below I am loading the driver of mysql and make a connection by using the getConnection() method of DriverManager class. It returns a Connection. Further I passed the query into the parameter of prepareStatement() method of Connection interface which gives an object of PreparedStatement interface. In the next line I used the executeUpdate() method of PreparedStatement interface which returns an integer value of row affected.
Example :
AddAutoIncrementServlet.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class AddAutoIncrementServlet extends HttpServlet
{
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws IOException, ServletException
{
res.setContentType("text/html");
PrintWriter pw = res.getWriter();
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://192.168.10.13/data";
String uid = "root";
String psw = "root";
String sql = "ALTER TABLE employee CHANGE Id EmpId int auto_increment;";
try{
Class.forName(driver);
Connection con = DriverManager.getConnection(url,uid,psw);
PreparedStatement ps= con.prepareStatement(sql);
int i = ps.executeUpdate();
pw.println(i+" rows are affected");
}
catch(SQLException sx)
{
pw.println(sx);
}
catch(ClassNotFoundException cx)
{
pw.println(cx);
}
}
}
web.xml
<?xml version="1.0" encoding="UTF-8"?> <web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"> <display-name>join</display-name> <servlet> <servlet-name>AddAutoIncrementServlet</servlet-name> <servlet-class>AddAutoIncrementServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>AddAutoIncrementServlet</servlet-name> <url-pattern>/AddAutoIncrementServlet</url-pattern> </servlet-mapping> </web-app>
Output :
Initially the table which I had created
Description of table employee when I had created it
In the above example you will see that the column Extra of field Id is blank.
When you will execute the above example you will get the output as
When this example is executed successfully then if you will see the description the description will look like as
In the above image you will see that the Id is changed to EmpId and the column Extra of field EmpId is filled by auto_increment.
[ 0 ] Comments