Mysql AVG() function using java servlet

In this section we will discuss how an aggregate AVG() function statement is used in servlet.

Mysql AVG() function using java servlet

In this section we will discuss how an aggregate AVG() function statement is used in servlet.

The avg() function is used to find out the average value of a defined column. To use Mysql avg() function statement in java servlet we have to first established a connection between database and java. This is a later process in java. The first step from where we start the java is making a class. So I first made a class named AvgServlet in java servlet which extends the HttpServlet class. In the body of class I have overridden the doGet() method into which objects of HttpServletRequest and HttpServletResponse are created. Inside the doGet() method at first I set the response type i.e. into which format the server will show the output, then in next line I used the getWriter() method with the object of HttpServletResponse interface which inherits this method from ServletResponse interface and then established a connection with mysql using classes and interfaces of java.sql package. When the connection is established I passed the query "SELECT avg(Weight) FROM vegetable" into the parameter of prepareStatement() method of Connection interface which returns an object of PreparedStatement object. So the avg() sql query which is now hold by the PreparedStatement object will be executed using the executeQuery() method of PreparedStatement which returns a ResultSet interface object. Finally I have extracted the result from the 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 AvgServlet 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 avg(Weight) 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>Avg_Veg_Weight</b></td>");
pw.println("</tr><tr>");
while(rs.next())
{
Double avgweight = rs.getDouble(1);
pw.println("<td>"+avgweight+"</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>AvgServlet</servlet-name>
<servlet-class>AvgServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>AvgServlet</servlet-name>
<url-pattern>/AvgServlet</url-pattern>
</servlet-mapping>

</web-app>

Output :

Table on which we will implement the avg() function

When you will execute the above example you will get the following output

Download Source Code