Mysql STR_TO_DATE() Function Example using java servlet

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

Mysql STR_TO_DATE() Function Example using java servlet

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

STR_TO_DATE() function of mysql takes a string and a specific format for that string as argument and returns a date/datetime value.

Syntax :

str_to_date(string, format);

Here the argument 'string' could be a date/datetime and ' format ' is a format of date/datetime.

e.g.

str_to_date( ' 18,08,2011 ', ' %d, %m, %Y ' );

Now, since I have to use this function in java servlet program, therefore I have created a java servlet class named MysqlStringToDateServlet 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 STR_TO_DATE( ' Wednesday 17th August 2011 12:00:00', '%W %D %M %Y %H:%i:%s ' ) AS DATE_AND_TIME;" (This query will return the output as 2011-08-17 12:00:00.0 ) 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

MysqlStringToDateServlet.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 MysqlStringToDateServlet extends HttpServlet
{
 public void doGet(HttpServletRequest request, HttpServletResponse response)
  throws IOException, ServletException
  {
   response.setContentType("text/html");
   PrintWriter out = response.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 STR_TO_DATE('Wednesday 17th August 2011 12:00:00', '%W %D %M %Y %H:%i:%s') AS DATE_AND_TIME;";
   
   Connection con;
   PreparedStatement ps;
   ResultSet rs;
   
   try
   {
	 Class.forName(className);
	 con = DriverManager.getConnection(url, user, password);
	 ps = con.prepareStatement(sql);
	 rs = ps.executeQuery();
	 
   out.println("<table>");
   out.println("<tr>");
   out.println("<td><b>DATE_AND_TIME</b></td>");
   out.println("</tr>");
   while(rs.next())
   {
	String strToDate = rs.getString(1);
	out.println("<tr>");
	out.println("<td>"+strToDate+"</td>");
	out.println("</tr>");
   }
   out.println("</table>");
  }
  catch(SQLException sx)
  {
   out.println(sx);
  }
  catch(ClassNotFoundException cx)
  {
	out.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>MysqlStringToDateServlet</servlet-name>
<servlet-class>MysqlStringToDateServlet</servlet-class>
</servlet>

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

</web-app>

Output :

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

Download Source Code