by Dinesh Thakur Category: Servlet with MySql Database

This function Will Return the ADD (sum) value of the required field from a table like (salary, age etc...).

We need to make a table named 'worker' into a database named 'dbase’. The required fields must be filling with values. After that, Java packages to be call from java library. Here we need to define a class named 'MySqlSUMFunction' extends the 'HttpServlet'. This program is been responsible for desired output as required statement from user. Here we use the service method() (this method is responsible for calling the doGet() method to getting the request). the variable which are mandatory to use link or present Backend and Frontend will have to be declared like Connection,ResultSet,preparedStatement all these variables does their required functions or job. Like the connection variable will use to make Connection between database and java code. The drivers are also to be load then created an object of Connection interface. After that preparedStatement will use to represent the Query for output like 'SELECT SUM(salary) AS total FROM worker'. The resultset will be define as executeQuery() to execute the query for desired result. And in the last we used doGet() method for fetching result on a web browser.

Now on the browser to get an output in designer look we just use the 'HTML' code tags, which will present the Output in Tabular form for an efficient look.

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 MySqlSUMFunction 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 SUM(salary) AS total FROM worker");
                  rs = ps.executeQuery();
              String title = "Using SUM(Total) Function";
                  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>Total</th>\n"+ "</body> </html>");
                 while(rs.next())
                     {
                           String tl = rs.getString("total");
                       disp.println("<tr><td align=\"center\">" + tl +"</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>MySqlSUMFunction</servlet-name>
   <servlet-class>MySqlSUMFunction</servlet-class>
</servlet>
<!-- servlet mapping -->
<servlet-mapping>
   <servlet-name>MySqlSUMFunction</servlet-name>
   <url-pattern>/MySqlSUMFunction</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