In this tutorial you will learn how to use coalesce function of mysql in servlet
MySql COALESCE Function Example
In this tutorial you will learn how to use coalesce function of mysql in servlet
COALESCE function of mysql gives first non null value of the list.
Example :
In this example first I created a table named ajax_countries for fetching the data. To use this mysql operator in servlet I created a class named MysqlCoalesce.java which extends the class HttpServlet to support the http functionality to servlet, into which I defined the doGet() method into which the object's of HttpServeltRequest and HttpServletResponse are created. Since we have to use the mysql query so we are required to connect with a database package (here I am using MySql) that's why in further steps I perform the database connectivity with servlet using the java.sql package. In example to use this function I use the query "SELECT countryName,coalesce(currency,capital) FROM ajax_countries".
MysqlCoalesce.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 MysqlCoalesce 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";
Connection con;
PreparedStatement ps;
ResultSet rs;
try
{
Class.forName(className);
con= DriverManager.getConnection(url, user, password);
String sql= "SELECT countryName,coalesce(currency,capital) FROM ajax_countries";
ps= con.prepareStatement(sql);
rs= ps.executeQuery();
out.println("<html><table border=1>");
out.println("<tr><td align=left><b>Country Name</b></td><td align= middle><b>coalesce(currency,capital)</b></td></tr>");
while(rs.next())
{
String countryName= rs.getString("countryName");
String coal= rs.getString("coalesce(currency,capital)");
out.println("<tr><td align=left>"+countryName+"</td>");
out.println("<td align=left>"+coal+"</td>");
out.println("</tr>");
}
out.println("</table></html>");
rs.close();
ps.close();
con.close();
}
catch(ClassNotFoundException cx)
{
out.println(cx);
}
catch(SQLException sx)
{
out.println(sx);
}
}
}
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>mysqlComparisonFunctionAndOperator</display-name> <servlet> <servlet-name>MysqlCoalesce</servlet-name> <servlet-class>MysqlCoalesce</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlCoalesce</servlet-name> <url-pattern>/MysqlCoalesce</url-pattern> </servlet-mapping> </web-app>
Output :
Data in the database table is as following :
When you will execute the servlet example you will get the output like as :
[ 0 ] Comments