In this tutorial you will learn how to use export_set() String function of mysql with java servlet
Mysql EXPORT_SET() Function Example Using Java Servlet
In this tutorial you will learn how to use export_set() String function of mysql with java servlet
EXPORT_SET() method of mysql returns the value as per the bit value. There are five arguments in the parameter of this function. First argument represents the bit value, this argument is given as an integer value and is changed internally into bits(0,1) value by this function, other the next two arguments are given as string these are the arguments which are returned as output and the output is depends upon the first argument of this function i.e. the bit value if the bit value is 1 returns the first string and if the bit value is 0 it returns the second string which are placed as the second and third argument respectively in the parameter of this function, the fourth argument is also a string which acts as a separator which separates the returned output values, and the last fifth argument is an integer value which is responsible for compairing the output at the specified number of bits (times) if this argument is not specified by you then it will be specified default value by 64 bit.
Syntax :
export_set(integer,'string1','string2','separator string','integer');
Here at the first argument integer can be either 1 or 0 or a number (except the value 1 or 0 other number will be internally changed into bit form by this function, and the last argument of this function represents the no. of bits to compare).
Now, since I have to use this function in java servlet program, therefore I have created a java servlet class named MysqlExportSetServlet 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 Next step I am taking inputs using the getParameter() method of ServletRequest with the object of HttpServletRequest. In continue we have to establish 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 EXPORT_SET("+i+","+"'"+str+"'"+","+"'"+str1+"'"+","+"'"+str2+"'"+","+j+")"; (Here i, str, str1, str2, j are the variables that stores the value given by the user.) 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 :
ExportSet.html
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>EXPORT_SET() Function</title> </head> <body> <form method="get" action="MysqlExportSetServlet"> <table> <tr> <td>Enter a number </td> <td><input type="text" name="txtName"/></td> </tr> <tr> <td>Enter string</td> <td><input type="text" name="txtName1"/></td> </tr> <tr> <td>Enter string</td> <td><input type="text" name="txtName2"/></td> </tr> <tr> <td>Enter string for separator</td> <td><input type="text" name="txtName3"/></td> </tr> <tr> <td>Enter a number</td> <td><input type="text" name="txtName4"/></td> </tr> <tr> <td align="center"><input type="submit" value="CLICK"/></td> </tr> </table> </form> </body> </html>
MysqlExportSetServlet.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Connection;
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 MysqlExportSetServlet extends HttpServlet
{
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException
{
response.setContentType("text/html");
PrintWriter out = response.getWriter();
int i=(Integer.parseInt(request.getParameter("txtName")));
String str=request.getParameter("txtName1");
String str1=request.getParameter("txtName2");
String str2=request.getParameter("txtName3");
int j=(Integer.parseInt(request.getParameter("txtName4")));
String className="com.mysql.jdbc.Driver";
String url="jdbc:mysql://192.168.10.13";
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 EXPORT_SET("+i+","+"'"+str+"'"+","+"'"+str1+"'"+","+"'"+str2+"'"+","+j+")";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next())
{
sql=rs.getString(1);
out.println(sql);
}
}
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>mysqlStringFunction</display-name> <servlet> <servlet-name>MysqlExportSetServlet</servlet-name> <servlet-class>MysqlExportSetServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlExportSetServlet</servlet-name> <url-pattern>/MysqlExportSetServlet</url-pattern> </servlet-mapping> </web-app>
Output
When you will execute the above example you will get the output as :
1.
2. When you will enter the value.
3. Output when you will click on CLICK button.
[ 0 ] Comments