MySQL ALTER DROP INDEX USING JAVA SERVLET

In this section we will discuss about how an index that is existed in the table can be removed.

MySQL ALTER DROP INDEX USING JAVA SERVLET

In this section we will discuss about how an index that is existed in the table can be removed.

INDEX in a database is a term that is used for the speed up for fetching data from the database server. In Mysql when a process of finding the data from database is start it first searches for the table index whether it is existed or not. If the table index is existed it uses the index to select the exact physical corresponding rows without searching in the whole table. Once an index is created it can be altered/removed also by its name. So, I create a table 'employee' in mysql and also created an index on the field DepartmentId (to see how an index can be created click here). Now I have to remove this index from the table 'employee' and since I have to use this query in java program so I will have to make a servlet class and to use sql query will have to establish a connection between java program and database system. Therefore, I made a class named MysqlDropIndexServlet in java which extends the HttpServlet class. In the body of class I overridden the method doGet() and created an objects of HttpServletRequest and HttpServletResponse into its parameter. Inside the doGet() method first I set the mime type that in 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 we have to make a connection between java code and database system. So, since I am using Mysql in the example given below therefore 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 am passing the query "ALTER TABLE employee DROP INDEX index_1" for removing an index 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 :

MysqlDropIndexServlet.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
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 MysqlDropIndexServlet 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 DROP INDEX index_1";
try
{
Class.forName(driver);
Connection con = DriverManager.getConnection(url,uid,psw);
PreparedStatement ps = con.prepareStatement(sql);
int i = ps.executeUpdate();
pw.println(i +" row(s) affected <br>");
pw.println("Index has been removed successfully");
}
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>AlterDropIndexServlet </servlet-name>
<servlet-class>AlterDropIndexServlet </servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>AlterDropIndexServlet </servlet-name>
<url-pattern>/AlterDropIndexServlet </url-pattern>
</servlet-mapping>

</web-app>

Output :

When you will see the table description after creating an index it will look as

1. employee description

2. When you will execute the above example you will get the output as :

3. When you will see again the description of table employee after executing this above example it will look as :

Download Source Code