In this tutorial you will learn how to use insert() String function of mysql with java servlet
Mysql INSERT() Function Example Using Java Servlet
In this tutorial you will learn how to use insert() String function of mysql with java servlet
INSERT() method of mysql gives the different outputs for different inputs. In this function a string/substring is inserted at the specified index position into the specified original string and also deletes the specified number of characters from the specified insertion index position from the original string. In some cases such as if the insertion index position given by you is out of range then it will be not put any effect on the original string and it will simply returns the original string as it is. In other case into which if you have given a value for removing number of characters from the original string is greater than the number of characters present into the original string then it removes the characters from the specified insertion index position to the end and if any string/substring is specified for inserting, this function inserts that specified string/substring at the insertion index position and returns that concatenated string.
Syntax :
insert('string', num1, num2, 'string');
Here the first argument "string" is an Original String at which you want to do operation, the second argument num1 is a numerical value that shows the index position of Original String, in the third argument num2 is also a numerical value that specifies the number of characters you want to remove from the Original String, and the last argument string is a string/substring that you want to insert within the Original String.
If you want to use this function with FROM and WHERE clause, you can write the query as :
SELECT INSERT(attr_name, num1, num2, 'string/substring') FROM table_name WHERE attr_name='value';
Now, since I have to use this function in java servlet program, therefore I have created a java servlet class named MysqlInsertServlet 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 "SELECT INSERT("+"'"+oriString+"'"+","+ind_pos+","+rmv_char+","+"'"+subString+"'"+")"; (Here 'oriString', 'ind_pos', 'rmv_char', 'subString' are the 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 :
Insert.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>Insert function</title> </head> <body> <form action="MysqlInsertServlet" method="get"> <table> <tr> <td>Enter String onto which you want to do operation</td> <td><input type="text" name="text1"/></td> </tr> <tr> <td>Enter numeric value as index position</td> <td><input type="text" name="text2"/></td> </tr> <tr> <td>Enter numeric value as number of characters want to be removed</td> <td><input type="text" name="text3"/></td> </tr> <tr> <td>Enter String that you want to add/insert within original string</td> <td><input type="text" name="text4"/></td> </tr> <tr> <td></td> <td><input type="submit" value="submit"/></td> </tr> </table> </form> </body> </html>
MysqlInsertServlet.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 MysqlInsertServlet extends HttpServlet
{
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException
{
response.setContentType("text/html");
PrintWriter out= response.getWriter();
String oriString= request.getParameter("text1");
int ind_pos= Integer.parseInt(request.getParameter("text2"));
int rmv_char= Integer.parseInt(request.getParameter("text3"));
String subString= request.getParameter("text4");
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 INSERT("+"'"+oriString+"'"+","+ind_pos+","+rmv_char+","+"'"+subString+"'"+")";
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>MysqlInsertServlet</servlet-name> <servlet-class>MysqlInsertServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>MysqlInsertServlet</servlet-name> <url-pattern>/MysqlInsertServlet</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. When you will click on submit button you will get the output as :
4. When you will give the value like this :
5. You will get the output as :
[ 0 ] Comments