In this section we will discuss how a statement " Insert Into Select From " can be used in java sservlet.
INSERT INTO SELECT FROM statement Servlet
In this section we will discuss how a statement " Insert Into Select From " can be used in java sservlet.
First of all I want to briefly introduce you about the INSERT INTO SELECT FROM statement. So, first lets understand the condition where this statement can be used, if we have two tables (table1 and table2) and we are required to store data of some field or all field of table2 into the existing field of table1 then how is it possible ? In this situation we will be use this statement. Using this statement we can copy records of a field of table1 or all records of all fields of a table1 into the table 2. Now lets come into the point that we have to use this statement into servlet. How is it possible ? To solve this problem I first created two tables named 'employee' and 'department'. Then I have created a class in java named InsertIntoSelectServlet which extends the HttpServlet class. Inside the class I have overridden the doGet() method into which objects of HttpServletRequest and HttpServletResponse are created. Using the object of HttpServletResponse object called the getWriter() method of ServletResponse interface which is inherited by the HttpServletResponse interface that returns an object of PrintWriter class that helps in to show output on browser. To use a SQL statement we will have to first establish a connection between the database system and java code. So I used the java.sql package for doing work with database system from establishing a connection to creating and executing the query as well as extract the result. After loading the driver made a connection with 'mysql' and then passed the SQL query into the prepareStatement() method of Connection interface which returns a PreparedStatement object. This PreparedStatement object is executed by its method executeUpdate() method that returns an integer value of rows affected. Again I executed a SELECT query by using executeQuery() method of Connection interface for displaying a changed table which returns a ResultSet object, through which I extracted the result.
Example :
InsertIntoSelectServlet.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class InsertIntoSelectServlet 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";
Connection con;
PreparedStatement ps;
ResultSet rs;
String sql = "INSERT INTO employee (departmentId,department)" +
"SELECT departmentId, department FROM department";
String sql1 = "SELECT * FROM employee";
try
{
Class.forName(driver);
con = DriverManager.getConnection(url,uid,psw);
ps = con.prepareStatement(sql);
int i= ps.executeUpdate();
pw.println(i+"row(s) affected ");
ps=con.prepareStatement(sql1);
rs= ps.executeQuery();
pw.println("<table><tr>");
pw.println("<td><b>Id</b></td>");
pw.println("<td><b>Name</b></td>");
pw.println("<td><b>Department</b></td>");
pw.println("<td><b>Salary</b></td>");
pw.println("<td><b>DepartmentId</b></td>");
pw.println("</tr>");
while(rs.next())
{
Integer id = rs.getInt("Id");
String name = rs.getString("Name");
String dept = rs.getString("Department");
Integer s = rs.getInt("Salary");
Integer did = rs.getInt("DepartmentId");
pw.println("<tr>");
pw.println("<td>"+id+"</td>");
pw.println("<td>"+name+"</td>");
pw.println("<td>"+dept+"</td>");
pw.println("<td>"+s+"</td>");
pw.println("<td>"+did+"</td>");
pw.println("</tr>");
}
pw.println("</table>");
con.close();
ps.close();
}
catch(SQLException sx)
{
pw.println(sx);
}
catch(ClassNotFoundException cx)
{
pw.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>join</display-name> <servlet> <servlet-name>InsertIntoSelectServlet</servlet-name> <servlet-class>InsertIntoSelectServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>InsertIntoSelectServlet</servlet-name> <url-pattern>/InsertIntoSelectServlet</url-pattern> </servlet-mapping> </web-app>
Output :
Tables that I had created earlier are :
1. employee
2. department
When you will execute the above example you will get the following output
[ 0 ] Comments