by Dinesh Thakur Category: Servlet with MySql Database

This function Will sort the value of selected Columns (Multiple Columns) Descending or Ascending.

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 'MySqlOrderByMultiCol', 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 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 last_name, dept_id, salary FROM dept ORDER BY dept_id, salary DESC). And to fetch the result on web browser doGet() method will be initialize.

While getting Output on the Web Browser we use 'HTML' Code or some of these tags to present Output in tabular form.

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 MySqlOrderByMultiCol 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 last_name, dept_id, salary FROM dept ORDER BY dept_id, salary DESC");
                   rs = ps.executeQuery();
                               String title = "Order By Multiple Columns";
                   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>Last Name</th><th>Dept Id</th><th>Salary</th>\n"+ "</body> </html>");
                  while(rs.next())
                     {
                          String nm = rs.getString("last_name");
                                      int d_id=rs.getInt("dept_id");
                                      int sal=rs.getInt("salary");
                                      disp.println("<tr><td align=\"center\">" + nm +  "<td align=\"center\">" + d_id +"<td align=\"center\">" + sal +  "</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>MySqlOrderByMultiCol</servlet-name>
   <servlet-class>MySqlOrderByMultiCol</servlet-class>
</servlet>
<!-- servlet mapping -->
<servlet-mapping>
   <servlet-name>MySqlOrderByMultiCol</servlet-name>
   <url-pattern>/MySqlOrderByMultiCol</url-pattern>
</servlet-mapping>




About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.



Related Articles