In this section we will discuss how SUM() function is used in java servlet.
Mysql SUM() function using Servlet
In this section we will discuss how SUM() function is used in java servlet.
An aggregate function sum() adds the numbers of a defined column. To use mysql sum() function statement in java servlet we have to first established a connection between the java and database system. Here I will used mysql so I have to need first to load the mysql driver. Before starting the java code I created a table named 'vegetable'. In this table I will calculate the total weight of vegetables and the total price. Now lets come in java. At first I created a class named SumServlet that extends the HttpServlet class. Inside the class I have overridden the doGet() method into which objects of HttpServletRequest and HttpServletResponse are created. Inside the doGet() method first I set the response type into which the server will show the output, then I used the getWriter() method with the object of HttpServletResponse which inherits this method from the ServletResponse interface. Now I have to use SQL query in java so at first I established a connection of java with mysql. Then passed the query "select sum(Weight) total_veg_weight, sum(Weight*Price) total_veg_price from vegetable" into the parameter of prepareStatement() method of Connection interface which returns an object of PreparedStatement interface. Now I used the the executeQuery() method of PreparedStatement which returns an object of ResultSet which stores the result of executed query. Finally I have extracted the result from ResultSet object.
Example :
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 SumServlet 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 sum(Weight) total_veg_weight, sum(Weight*Price) total_veg_price from vegetable";
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>Total_Veg_Weight</b></td>");
pw.println("<td><b>Total_Veg_Price</b></td>");
pw.println("</tr><tr>");
while(rs.next())
{
Double totalweight = rs.getDouble(1);
Double totalprice = rs.getDouble(2);
pw.println("<td>"+totalweight+"</td>");
pw.println("<td>"+totalprice+"</td>");
pw.println("</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>SumServlet</servlet-name> <servlet-class>SumServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>SumServlet</servlet-name> <url-pattern>/SumServlet</url-pattern> </servlet-mapping> </web-app>
Output :
Table onto which I performed the sum() function.
When you will execute the above example you will get the following output :
[ 0 ] Comments