In this section we will discuss how can we specify the multiple values in a database table using IN operator in mysql using java servlet.
MySql SELECT IN Query using Java servlet
In this section we will discuss how can we specify the multiple values in a database table using IN operator in mysql using java servlet.
IN operator specifies the multiple values with ' where ' clause. This operator acts as a searching of elements i.e. when you will use this operator with the ' select from where ' query it will display all the records of those values which were used with IN operator. Now, since I have to use the IN operator query in java servlet program, therefore I have created a java servlet class named MysqlInOperatorServlet 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 will display all the elements of table 'employee' 2. "SELECT * FROM employee WHERE Id IN (1,6) ", (This query will search in column ' Id ' of table 'employee' whether the Ids '1' & '6' are available or not if it founds the specified values then it will display all the records related to the founded Ids) 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 :
MysqlInOperatorServlet.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 MysqlInOperatorServlet 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 Id IN(1,6)";
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>IN</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>MysqlInOperatorServlet</servlet-name> <servlet-class>MysqlInOperatorServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlInOperatorServlet</servlet-name> <url-pattern>/MysqlInOperatorServlet</url-pattern> </servlet-mapping> </web-app>
Output :
When you will execute the above example you will get the following output :
[ 0 ] Comments