In this tutorial you will learn how to use substring_index() String function of mysql with java servlet.
Mysql SUBSTRING_INDEX() Function Example Using Java Servlet
In this tutorial you will learn how to use substring_index() String function of mysql with java servlet.
SUBSTRING_INDEX() function of Mysql gives back a part of an actual string before(left) or after(right) the specified number of occurrences delimiter this part is called a substring. The returned value depends upon the specified number of delimiter, if the delimiter is defined as a positive number then it returns the left string before the specified number of delimiter, if delimiter number is a negative value then it returns the right string after the specified number of delimiter, if the number of delimiter is specified greater than the occurrences of delimiter in a given string then the total string is returned as it is and if the number of delimiter is given as 0 then nothing will be returned.
Syntax :
substring_index('str', 'delim', num);
Here "str", is an actual string, "delim" is a specified delimiter and "num" is a specified number of occurrences of delimiter.
Now, since I have to use this function in java servlet program, therefore I have created a java servlet class named MysqlSubStrServlet 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 passed the query in my java code "SELECT SUBSTRING_INDEX(" + "'" + str + "'"+","+"'" + delim + "'"+"," +num+")"; (Here 'str', 'delim', 'num' are variables that stores the value given by the user in their respective textfields.) 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 :
substring_index.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>SUBSTRING_INDEX FUNCTION</title> </head> <body> <form action="MysqlSubstringIndexServlet" method="get"> <table> <tr> <td>Enter String </td> <td><input type="text" name="text1"/></td> </tr> <tr> <td>Enter the delimiter </td> <td><input type="text" name="text2"/></td> </tr> <tr> <td>Enter the occurrences of delimiter </td> <td><input type="text" name="text3"/></td> </tr> <tr> <td></td> <td><input type="submit" value="submit"/></td> </tr> </table> </form> </body> </html>
MysqlSubstringIndexServlet.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 MysqlSubstringIndexServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
String str = request.getParameter("text1");
String delim = request.getParameter("text2");
int num = Integer.parseInt(request.getParameter("text3"));
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 SUBSTRING_INDEX(" + "'" + str + "'"+","+"'" + delim + "'"+"," +num+")";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
sql= rs.getString(1);
out.println("Substring is ="+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>MysqlSubstringIndexServlet</servlet-name> <servlet-class>MysqlSubstringIndexServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlSubstringIndexServlet</servlet-name> <url-pattern>/MysqlSubstringIndexServlet</url-pattern> </servlet-mapping> </web-app>
Output
1. When you will execute the above example you will get the output as :
2.
3.
[ 0 ] Comments