MySql GET_FORMAT() FUNCTION Example Using Java Servlet

In this section you will learn how to use mysql get_format() function in java servlet.

MySql GET_FORMAT() FUNCTION Example Using Java Servlet

In this section you will learn how to use mysql get_format() function in java servlet.

In GET_FORMAT() function of MySql formats a date/time/datetime according to the specified argument. You can combine this function with date_format() function.

Syntax :

get_format(date/time/datetime, 'eur/usa/jis/iso/internal')

Here the argument date/time/datetime is a date/datetime format passed by you.

And eur/usa/jis/iso/internal is a required date/datetime format into which the function converts.

Now, since I have to use this function in java servlet program, therefore I have created a java servlet class named MysqlGetFormatServlet which extends the HttpServlet class. In the body of class I overridden the method doGet() and created an objects of HttpServletRequest and HttpServletResponse into its parameter. Inside the doGet() method first I set the mime type that in which format the browser will show the output. In the next line I used the getWriter() method of ServletResponse interface with the object of HttpServletResponse. HttpServletResponse interface extends this method from the ServletResponse. In continue we have to make a connection between java code and database system. So, since I am using Mysql in the example given below therefore, I am loading the driver of mysql and make a connection by using the getConnection() method of DriverManager class. It returns a Connection. Further I am passing the query "SELECT GET_FORMAT(DATE, 'EUR')AS EUR_FORMAT" (This query will return the output as %d.%m.%Y ) and "SELECT DATE_FORMAT('2011-08-10', GET_FORMAT(DATE, 'EUR')) AS DATE_IN_EUR_FORMAT" (This query will return the output as 10.08.2011 ) with two different parameters into the parameter of prepareStatement() method of Connection interface which gives an object of PreparedStatement interface. In the next line I used the executeQuery() method of PreparedStatement interface which returns a ResultSet object that holds the result of query. At last I have extracted the result from the ResultSet object.

Example :

MysqlGetFormatServlet.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class MysqlGetFormatServlet extends HttpServlet
{
  public void doGet(HttpServletRequest req, HttpServletResponse res)
    throws IOException, ServletException
    {
	 res.setContentType("text/html");
	 PrintWriter pw = res.getWriter();
	 String className = "com.mysql.jdbc.Driver";
	 String url = "jdbc:mysql://192.168.10.13/data";
	 String user = "root";
	 String password = "root";
	 String sql = "SELECT GET_FORMAT(DATE, 'EUR')AS EUR_FORMAT";
	 String sql1 = "SELECT DATE_FORMAT('2011-08-10', GET_FORMAT(DATE, 'EUR')) AS DATE_IN_EUR_FORMAT";
	 Connection con;
	 PreparedStatement ps, ps1;
	 ResultSet rs, rs1;
	 
	 try
	 {
	 Class.forName(className);
	 con = DriverManager.getConnection(url, user, password);
	 ps = con.prepareStatement(sql);
	 ps1 = con.prepareStatement(sql1);
	 rs = ps.executeQuery();
	 rs1 = ps1.executeQuery();
	 //DATE FORMAT OF EUR
	 pw.println("<table>");
	 pw.println("<tr>");
	 pw.println("<td><b>EUR_FORMAT</b></td>");
	 pw.println("</tr>");
	 
	 while(rs.next())
	 {
	  String getFormat = rs.getString(1);
	  pw.println("<tr>");
	  pw.println("<td>"+getFormat+"</td>");
	  pw.println("</tr>");
	 }
	 pw.println("</table>");
	// DATE IN EUR FORMAT 
	 pw.println("<table>");
	 pw.println("<tr>");
	 pw.println("<td><b>DATE_IN_EUR_FORMAT</b></td>");
	 pw.println("</tr>");
	 
	 while(rs1.next())
	 {
	  String formatDate = rs1.getString(1);
	  pw.println("<tr>");
	  pw.println("<td>"+formatDate+"</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>mysqlDate</display-name>

<servlet>
<servlet-name>MysqlGetFormatServlet</servlet-name>
<servlet-class>MysqlGetFormatServlet</servlet-class>
</servlet>

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

</web-app>

Output :

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

Download Source Code