In this section we will discuss how can we search the element in a database table using LIKE operator in mysql using java servlet.
MySql SELECT LIKE Query using Java servlet
In this section we will discuss how can we search the element in a database table using LIKE operator in mysql using java servlet.
LIKE operator in mysql is used with WHERE clause. This operator is used to compare the string and searches the words matchs with the specified character, when the like operator is used with ' % ' sign then it acts as a meta character search. In this example we will use with 'select where' clause. Now, since I have to use the like operator query in java servlet program, therefore I created a java servlet class named MysqlLikeOperatorServlet 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 two query 1. "SELECT * FROM employee" (This query displays all the elements of table 'employee' 2. "SELECT * FROM employee WHERE Department LIKE '%ment' ", (This query will search those every words in a column ' department ' of table employee having the ' ment ' string at the last position of each word) into the parameter of prepareStatement() method of Connection interface which gives an object of PreparedStatement interface. In the next line I used the executeQuery() method of PreparedStatement interface which returns a ResultSet object that holds the result of query. At last I have extracted the result from the ResultSet object.
Example :
MysqlLikeOperatorServlet.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class MysqlLikeOperatorServlet extends HttpServlet
{
Connection con;
PreparedStatement ps1, ps2;
ResultSet rs1, rs2;
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws IOException, ServletException
{
PrintWriter pw = res.getWriter();
res.setContentType("text/html");
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://192.168.10.13/data";
String uid = "root";
String psw = "root";
String sql1 = "SELECT * FROM employee";
String sql2 = "SELECT * FROM employee WHERE Department LIKE '%ment'";
try
{
Class.forName(driver);
con = DriverManager.getConnection(url,uid,psw);
ps1 = con.prepareStatement(sql1);
rs1 = ps1.executeQuery();
ps2 = con.prepareStatement(sql2);
rs2 = ps2.executeQuery();
pw.println("Employee Table<br>");
pw.println("<b>employee</b>");
pw.println("<html><table><tr>");
pw.println("<td><b>Id</b></td>");
pw.println("<td><b>Name</b></td>");
pw.println("<td><b>Department</b></td>");
pw.println("<td><b>Salary</b></td>");
pw.println("<td><b>DepartmentId</b></td>");
pw.println("</tr>");
while(rs1.next())
{
Integer id = rs1.getInt(1);
String name = rs1.getString(2);
String dept = rs1.getString(3);
Integer salary = rs1.getInt(4);
Integer deptId = rs1.getInt(5);
pw.println("<tr>");
pw.println("<td>"+id+"</td>");
pw.println("<td>"+name+"</td>");
pw.println("<td>"+dept+"</td>");
pw.println("<td>"+salary+"</td>");
pw.println("<td>"+deptId+"</td>");
pw.println("</tr>");
}
pw.println("</table><br>");
pw.println("Employee Table after using the <b>like</b> operator<br>");
pw.println("<b>employee</b>");
pw.println("<table><tr>");
pw.println("<td><b>Id</b></td>");
pw.println("<td><b>Name</b></td>");
pw.println("<td><b>Department</b></td>");
pw.println("<td><b>Salary</b></td>");
pw.println("<td><b>DepartmentId</b></td>");
pw.println("</tr>");
while(rs2.next())
{
Integer id = rs2.getInt(1);
String name = rs2.getString(2);
String dept = rs2.getString(3);
Integer salary = rs2.getInt(4);
Integer deptId = rs2.getInt(5);
pw.println("<tr>");
pw.println("<td>"+id+"</td>");
pw.println("<td>"+name+"</td>");
pw.println("<td>"+dept+"</td>");
pw.println("<td>"+salary+"</td>");
pw.println("<td>"+deptId+"</td>");
pw.println("</tr>");
}
pw.println("</table><br>");
}
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>MysqlLikeOperatorServlet</servlet-name> <servlet-class>MysqlLikeOperatorServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlLikeOperatorServlet</servlet-name> <url-pattern>/MysqlLikeOperatorServlet</url-pattern> </servlet-mapping> </web-app>
Output :
When you will execute the above example you will get the output as
[ 0 ] Comments