In this section we will discuss how can we find the records in a database table using BETWEEN operator in mysql using java servlet.
MySql SELECT BETWEEN Query using Java servlet
In this section we will discuss how can we find the records in a database table using BETWEEN operator in mysql using java servlet.
BETWEEN operator select the range of records between two values. This operator is used with ' where ' clause. This operator acts as a displaying of elements i.e. when you will use this operator with the ' select from where ' query it will display all the records including the both start and end value, which will specified by you, if available. Now, since I have to use the this operator query in java servlet program, therefore I have created a java servlet class named MysqlBetweenOperatorServlet 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 BETWEEN 1 AND 5 ", (This query will display all the records between 1 and 5 including both the Ids 1 & 5 of table 'employee' ) 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 :
MysqlBetweenOperatorServlet.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 MysqlBetweenOperatorServlet 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 BETWEEN 1 AND 5";
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>BETWEEN</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>MysqlBetweenOperatorServlet</servlet-name> <servlet-class>MysqlBetweenOperatorServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlBetweenOperatorServlet</servlet-name> <url-pattern>/MysqlBetweenOperatorServlet</url-pattern> </servlet-mapping> </web-app>
Output :
When you will execute the above example you will get the output as :
[ 0 ] Comments