This Function is Been used to Sort the value Ascending or Descending order Within The Alias of a column reference.
First i made a table named ‘dept’ into a database named ‘dbase’ within the reference of mySql(php myAdmin). In the starting of Program, we need to import all the Required Packages from the java library. Then to declare a class named ‘MySqlOrderByColumnAlias’ which is been extends ‘HttpServlet’. After then we have to use serviceMethod() which is responsible for the Getting request for Output. Then load drivers and create an Object of resultSet, which will, useful to show the table result as an Output. Connection variable also to be declare for interact between database and actual code as like in between frontend and backend. Now on other hand preparedStatement will be use for executing query like executeQuery() as (SELECT name,job,salary, salary*12 annsal FROM dept ORDER BY annsal DESC). And to fetch the result on web browser doGet() method will be initialize.
For getting output in tabular form i use ‘HTML’ tags with that the output will looks in a manner way.
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 MySqlOrderByColumnAlias extends HttpServlet
{
public void service(HttpServletRequest rq, HttpServletResponse rp)throws IOException, ServletException
{
rp.setContentType("text/html");
PrintWriter disp = rp.getWriter();
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost/dbase";
String uid = "root";
String psw = "root";
Connection con=null;
PreparedStatement ps = null;
ResultSet rs;
try
{
Class.forName(driver);
con = DriverManager.getConnection(url,uid,psw);
ps=con.prepareStatement("SELECT name,job,salary, salary*12 annsal FROM dept ORDER BY annsal DESC");
rs = ps.executeQuery();
String title = "Employee's Info And Salary With Using Of Order By Query ";
String docType ="<!doctype html public \"-//w3c//dtd html 4.0 " + "transitional//en\">\n";
disp.println(docType + "<html>\n" + "<head><title>" + title + "</title></head>\n" +
"<body bgcolor=\"#f4efef\">\n" + "<h1 align=\"center\">" + title + "</h1>\n" + "<ul>\n" +
"<table width=\"50%\" border=\"1\" align=\"center\">\n" + "<th>Name</th><th>job</th><th>Salary</th><th>Annual Salary</th>\n"+ "</body> </html>");
while(rs.next())
{
String nm = rs.getString("name");
String jb=rs.getString("jb");
int sal=rs.getInt("salary");
Double c=rs.getDouble("annsal");
disp.println("<tr><td align=\"center\">" + nm + "<td align=\"center\">" + jb +"<td align=\"center\">" + sal + "<td align=\"center\">" + c +"</td></tr>" );
}
}
catch(Exception e)
{
e.printStackTrace();
}
disp.close();
}
public void doPost(HttpServletRequest rq,HttpServletResponse rp)throws IOException,ServletException
{
doGet(rq,rp);
}
}
WEB.xml
<servlet>
<servlet-name>MySqlOrderByColumnAlias</servlet-name>
<servlet-class>MySqlOrderByColumnAlias</servlet-class>
</servlet>
<!-- servlet mapping -->
<servlet-mapping>
<servlet-name>MySqlOrderByColumnAlias</servlet-name>
<url-pattern>/MySqlOrderByColumnAlias</url-pattern>
</servlet-mapping>
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular