This Function Will Returns the number of months between periods.
Firstly, i made a table in database named ‘dbase’ with required fields and values in it. I import all the required java packages from the java library. I made a class named ‘MySqlPeriodDiffFunc’, which extends the ‘HttpServlet’. After then i use serviceMethod() which will bring the request from doGet()method for output. Then after i loaded all required drivers for the database accessing. I declare some mandatory variable for database connecting as i declare ‘Connection’ (this variable will use to create a link between database and the java code or actual code). The next will be ‘resultSet’ the variable will be responsible for the getting values from required columns and rows. The other one variable will be ‘preparedStatement’ this variable will use to execute the Selected Query like executeQuery() as (Select PERIOD_DIFF(201405,201402)).i use doGet()Method which will bring the output on web browser.
While show Output on a Web Browser i use ‘HTML’ Code and some of these tags to get output in the tabular form it makes the output a manner 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 MySqlPeriodDiffFunc 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 PERIOD_DIFF(201405,201402) AS pd");
rs = ps.executeQuery();
String title = "Using Period Diff 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>Result</th>\n"+ "</body> </html>");
while(rs.next())
{
String curr = rs.getString(1);
disp.println("<tr><td align=\"center\">" + curr +"</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>MySqlPeriodDiffFunc</servlet-name>
<servlet-class>MySqlPeriodDiffFunc</servlet-class>
</servlet>
<!-- servlet mapping -->
<servlet-mapping>
<servlet-name>MySqlPeriodDiffFunc</servlet-name>
<url-pattern>/MySqlPeriodDiffFunc</url-pattern>
</servlet-mapping>
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular