Home Tutorials Java Servlet Find Number of Rows using Servlet

Find Number of Rows using Servlet

Posted in : Java Posted on : July 8, 2011 at 6:47 PM Comments : [ 0 ]

In this section we will discuss how can you find the number of rows in a table using servlet.

Find Number of Rows using Servlet

In this section we will discuss how can you find the number of rows in a table using servlet.

To solve this problem first we would have to make a table in database. So I created a table 'data' in MS Access. Further I created a java file GetRowNumber.java which extends the HttpServlet. In doGet() method use the getWriter() method of ServletRespsonse interface which is further extended by the HttpSerevletResponse that helps in to write on browser. Further in the try-catch block first I load the driver and make a connection to the database and then write the query "insert" for inserting data into the table and "select count(*)" query for counting the number of rows in a table. In this example I am inserting and counting the number of rows simultaneously. In final step I close the Connection, PreparedStatement, and ResultSet. I also created a html page from which getParameter() method of HttpServletRequest gets the parameter request and stored it into the table in their respective columns.

Example :

rowNo.html

<html>
<head>
<title>DataRecord</title>
</head>
<body>
<form method="get" action="/columnRow/test">
<table style="width: 100%">
<tr>
<td>Name</td>
<td>
<input name="name" type="text" />
</td>
</tr>
<tr>
<td>Age</td>
<td>
<input name="age" type="text" />
</td>
</tr>
<tr>
<td>Password</td>
<td>
<input name="pass" type="password" />
</td>
</tr>
<tr>
<td></td>
<td>
<input name="Submit" type="submit" value="submit" />
</td>
</tr>
</table>
</form>
</body>
</html>

GetRowNumber.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
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 GetRowNumber extends HttpServlet {
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws IOException, ServletException {
res.setContentType("text/html");
PrintWriter pw = res.getWriter();
String nm = req.getParameter("name");
Integer age = Integer.parseInt(req.getParameter("age"));
String psw = req.getParameter("pass");
Connection con;
PreparedStatement ps;
ResultSet rs;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:test");
ps = con.prepareStatement("insert into data values (?,?,?)");
ps.setString(1, nm);
ps.setInt(2, age);
ps.setString(3, psw);
int i = ps.executeUpdate();
if (i != 0)
pw.println("Your data has been added successfully.<br>");
ps = con.prepareStatement("select count (*) from data");
rs = ps.executeQuery();
while (rs.next()) 
{
pw.println("And now the total number of rows (record) =<b> " + rs.getInt(1)+"</b>");
}
} 
catch (SQLException e) {
pw.println(e);
}
catch(ClassNotFoundException ex)
{
pw.println(ex);
}
}
}

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>columnRow</display-name>
<servlet>
<servlet-name>TestDatabase</servlet-name>
<servlet-class>GetRowNumber</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>TestDatabase</servlet-name>
<url-pattern>/test</url-pattern>
</servlet-mapping>
</web-app>

Output :

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

As I adding the records into the table so, after adding the record into the table the following message is displayed.