In this tutorial you will learn how to use between and operator of mysql in servlet
MySql BETWEEN AND Operator
In this tutorial you will learn how to use between and operator of mysql in servlet
BETWEEN AND operator of mysql returns the value between the given range including the both range after checking that whether the value is existed between the range or not. If the range given by you is exceeded than the range then it will return all the value from the initial range given by you.
Example :
In this example first I created a table named ajax_countries for fetching the data. To use this mysql operator in servlet I created a class named MysqlBetweenAnd.java which extends the class HttpServlet to support the http functionality to servlet, into which I defined the doGet() method into which the object's of HttpServeltRequest and HttpServletResponse are created. Since we have to use the mysql query so we are required to connect with a database package (here I am using MySql) that's why in further steps I perform the database connectivity with servlet using the java.sql package. In example to use this operator I use the query "SELECT id, countryName FROM ajax_countries WHERE id BETWEEN 4 AND 10" which will return the country name from the database table of which 'id' will be exist between 4 and 10.
MysqlBetweenAnd.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 MysqlBetweenAnd extends HttpServlet
{
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException
{
response.setContentType("text/html");
PrintWriter out= response.getWriter();
String className= "com.mysql.jdbc.Driver";
String url= "jdbc:mysql://192.168.10.13/data";
String user="root";
String password="root";
Connection con;
PreparedStatement ps;
ResultSet rs;
try
{
Class.forName(className);
con= DriverManager.getConnection(url, user, password);
String sql= "SELECT id, countryName FROM ajax_countries WHERE id BETWEEN 4 AND 10";
ps= con.prepareStatement(sql);
rs= ps.executeQuery();
out.println("<html><table>");
out.println("<tr><th>ID</th><th>Country Name</th>");
while(rs.next())
{
String id= rs.getString("id");
String countryName= rs.getString("countryName");
out.println("<tr><td align=left>"+id+"</td>");
out.println("<td align=right>"+countryName+"</td>");
out.println("</tr>");
}
out.println("</table></html>");
rs.close();
ps.close();
con.close();
}
catch(ClassNotFoundException cx)
{
out.println(cx);
}
catch(SQLException sx)
{
out.println(sx);
}
}
}
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>mysqlComparisonFunctionAndOperator</display-name> <servlet> <servlet-name>MysqlBetweenAnd</servlet-name> <servlet-class>MysqlBetweenAnd</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlBetweenAnd</servlet-name> <url-pattern>/MysqlBetweenAnd</url-pattern> </servlet-mapping> </web-app>
Output :
When you will execute the above example you will get the output like as :
[ 0 ] Comments