Mysql CONVERT_TZ Example using Java Servlet

In this section you will learn how mysql convert_tz() function works in using java servlet.

Mysql CONVERT_TZ Example using Java Servlet

In this section you will learn how mysql convert_tz() function works in using java servlet.

CONVERT_TZ() function of MySql converts the datetime value from one time zone to another. It returns 'null' if the arguments of convert_tz() is entered invalid. Now, since I have to use this function in java servlet program, therefore I have created a java servlet class named MysqlConvertTzServlet 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 CONVERT_TZ(now(),'+00:00','+08:00') AS convert_time_zone" 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 :

MysqlConvertTzServlet.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 MysqlConvertTzServlet 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 CONVERT_TZ(now(),'+00:00','+08:00') AS convert_time_zone";
		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>convert_time_zone</b></td>");
			pw.println("</tr>");
			while(rs.next())
			{
				String ctz = rs.getString(1);
				pw.println("<tr>");
				pw.println("<td>"+ctz+"</td>");
				pw.println("</tr>");
			}
			pw.println("</table>");
		}
		catch(SQLException sx)
		{
			pw.println(sx);
		}
		catch(ClassNotFoundException cx)
		{
			pw.println();
		}
	}
}

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>MysqlConvertTzServlet</servlet-name>
<servlet-class>MysqlConvertTzServlet</servlet-class>
</servlet>

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

</web-app>

Output :

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

Download Source Code