In this section we will discuss how can we sort the column in descending order using servlet java.
Mysql Sorting in Reverse order using java servlet
In this section we will discuss how can we sort the column in descending order using servlet java.
Sorting in reverse order means arranging the elements from their higher to lower order. To solve the such problem I will used an ORDER BY clause. An order by clause is used with the select statement. This clause orders the elements in the order specified by you such as in ascending order or descending order. In the example given below I have used the descending order. To apply an order by clause we will required a list. So I made a table named 'employee' and keep some records into the fields Id, Name, Department, and Salary. Since I have to use this query in java therefore, at first I created a class named MysqlReverseSortingServlet which extends the HttpServlet class. Inside the class body I overridden the doGet() method and created an objects of HttpServletRequest and HttpServletResponse interfaces into its parameter. Inside the doGet() method first I set the mime type. In the next line I used the getWriter() method of ServletResponse interface with the HttpServletResponse reference which gives an object of PrintWriter class that helps in to show output on browser. Further in the program since, I have to use a SQL query so at first I have to make a connection with database system. I am using the 'mysql' so I load the mysql driver to make connection and then created an object of Connection interface. With the object of Connection interface I passed the query "SELECT name, salary FROM employee ORDER BY salary DESC " into the parameter of its prepareStatement() method which returns an object of PreparedStatement object. With the object of PreparedStatement interface I called its method executeQuery() which executed the query stored in its object and gives an object of ResultSet interface that holds the result of executed query. Then I extracted the result from the ResultSet object.
Example :
MysqlReverseSortingServlet.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class MysqlReverseSortingServlet 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 sql = "SELECT name, salary FROM employee ORDER BY salary DESC";
try{
Class.forName(driver);
Connection con = DriverManager.getConnection(url,uid,psw);
PreparedStatement ps= con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
pw.println("<table><tr>");
pw.println("<td><b>Name</b></td>");
pw.println("<td><b>Salary</b></td>");
while(rs.next())
{
String name = rs.getString(1);
Integer salary = rs.getInt(2);
pw.println("<tr><td>"+name+"</td>");
pw.println("<td>"+salary+"</td></tr>");
}
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> MysqlReverseSortingServlet </servlet-name> <servlet-class> MysqlReverseSortingServlet </servlet-class> </servlet> <servlet-mapping> <servlet-name> MysqlReverseSortingServlet </servlet-name> <url-pattern> /MysqlReverseSortingServlet </url-pattern> </servlet-mapping> </web-app>
Output :
Table that I had created earlier
When you will execute the above example you will get the output as
[ 0 ] Comments