In this tutorial you will learn about how can you find the required top elements of a table.
Java Servlet MySql SELECT LIMIT
In this tutorial you will learn about how can you find the required top elements of a table.
LIMIT in MySql specifies that the what number of top records/elements of a table you want to see/show. It is used with the SELECT query. For example I have a table named 'employee' contains some fields named (Id, Name, Department, Salary, DepartmentId) and have some records. I want to see the the top 2 records of the table 'employee' through the java servlet program. Let's see the solution of this problem. Since we have to use this query in java program therefore, we have to make a java program and will have to establish a connection to use SQL query in java program. So, I created a class named MysqlLimitServlet 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 since we have to use a SQL query so at first we will have to make a connection between java code and database system. I am using here Mysql so in the example given below 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 passed the two queries 1. "SELECT * FROM employee" to show all the records of 'employee', 2. "SELECT * FROM employee LIMIT 2" to show the top two records of '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 an object of ResultSet interface from which I extract the records.
Example :
MysqlLimitServlet.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 MysqlLimitServlet 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 sql1 = "SELECT * FROM employee";
String sql2 = "SELECT * FROM employee LIMIT 2";
Connection con;
PreparedStatement ps1, ps2;
ResultSet rs1, rs2;
try
{
Class.forName(driver);
con = DriverManager.getConnection(url,uid,psw);
// Displaying all the elements of table employee
ps1 = con.prepareStatement(sql1);
rs1 = ps1.executeQuery();
pw.println("<b>#</b> Elements of table employee are as follows :");
pw.println("<br><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 sal = rs1.getInt(4);
Integer deptId = rs1.getInt(5);
pw.println("<tr>");
pw.println("<td>"+id+"</td>");
pw.println("<td>"+name+"</td>");
pw.println("<td>"+sal+"</td>");
pw.println("<td>"+dept+"</td>");
pw.println("<td>"+deptId+"</td>");
}
pw.println("</table>");
// Displaying top 2 elements of table employee
ps2 = con.prepareStatement(sql2);
rs2 = ps2.executeQuery();
pw.println("<br>");
pw.println("<b>#</b> Top 2 elements of table employee are as follows :");
pw.println("<br><table>");
pw.println("<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 sal = rs2.getInt(4);
Integer deptId = rs2.getInt(5);
pw.println("<tr>");
pw.println("<td>"+id+"</td>");
pw.println("<td>"+name+"</td>");
pw.println("<td>"+sal+"</td>");
pw.println("<td>"+dept+"</td>");
pw.println("<td>"+deptId+"</td>");
}
pw.println("</table>");
}
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>MysqlLimitServlet</servlet-name> <servlet-class>MysqlLimitServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlLimitServlet</servlet-name> <url-pattern>/MysqlLimitServlet</url-pattern> </servlet-mapping> </web-app>
Output :
When you will execute the above example you will get the output as :
[ 0 ] Comments