In this section we will discuss how can we create an index in mysql using java servlet.
MySQL CREATE INDEX USING JAVA SERVLET
In this section we will discuss how can we create an index in mysql using java servlet.
In the database INDEX is created to increase the speed of finding of data from the database server. In Mysql when we are trying to find the data from the database table it searches for the table index first 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. Now come to the point that we have to use this query in java servlet program so we will have to make a table in database and will have to establish a connection between java servlet program and the database system. So I created a table 'employee' in the database 'data' in mysql. Now its turn to create a java servlet program so, I created a class named MysqlCreateIndexServlet 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 "CREATE INDEX index_1 ON employee(DepartmentId)" for creating 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 :
MysqlCreateIndexServlet.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 MysqlCreateIndexServlet 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 = "CREATE INDEX index_1 ON employee(DepartmentId)";
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");
}
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> MysqlCreateIndexServlet </servlet-name> <servlet-class>MysqlCreateIndexServlet </servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlCreateIndexServlet </servlet-name> <url-pattern>/MysqlCreateIndexServlet </url-pattern> </servlet-mapping> </web-app>
Output :
Table that I had created earlier is :
1. Description of table 'employee'
2. When you will execute the above example you will get the output as :
3. After executing the java program when you will look at the description of the table employee it will look as :
[ 0 ] Comments