In this section we will discuss how an order by clause is used with select statement in java serlvlet.
Mysql ORDER BY java servlet example
In this section we will discuss how an order by clause is used with select statement in java serlvlet.
An ORDER BY clause is used with the select statement that specifies the order of data display. In other word this clause arranges the table data in which order they will be displayed i.e. in ascending order or descending order. Now discusses about its function with java. So at first I created a table in mysql named 'employee' and inserted some records in its field Id, Name, Department, Salary, and DepartmentId. Then created a class in java named MysqlOrderByServlet which extends the HttpServlet class. In the body of class I overridden the doGet() method and created 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" 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 :
MysqlOrderByServlet.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 MysqlOrderByServlet 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";
// Or we can use SELECT name, salary FROM employee ORDER BY salary ASC;
//query
Connection con;
PreparedStatement ps;
ResultSet rs;
try
{
Class.forName(driver);
con = DriverManager.getConnection(url,uid,psw);
ps = con.prepareStatement(sql);
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>MysqlOrderByServlet</servlet-name> <servlet-class>MysqlOrderByServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlOrderByServlet</servlet-name> <url-pattern>/MysqlOrderByServlet</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