Wednesday, December 26, 2012

Sql Query Tool using Servlet


Project View
 
public interface ResultSetMetaData extends Wrapper
An object that can be used to get information about the types and properties of the columns in a ResultSet object.

public ResultSetMetaData getMetaData() throws SQLException
Retrieves the number, types and properties of this ResultSet object's columns. 


QueryServlet.java
 package blog.webideaworld;  
 import javax.servlet.*;  
 import java.sql.*;  
 import java.io.IOException;  
 import java.io.PrintWriter;  
 import javax.servlet.ServletException;  
 import javax.servlet.http.HttpServlet;  
 import javax.servlet.http.HttpServletRequest;  
 import javax.servlet.http.HttpServletResponse;  
 public class QueryServlet extends HttpServlet  
 {  
   protected void doGet(HttpServletRequest request, HttpServletResponse response)  
   throws ServletException, IOException  
   {      
    myForm(request,response);  
   }  
   private void myForm(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException  
   {  
      response.setContentType("text/html");  
      PrintWriter out = response.getWriter();  
      try  
      {  
      out.println("<html><body bgcolor=brown><center><h1>SQL Tool</h1><form method=post>");  
      out.println("<table border=1 bgcolor=green> <tr> <td><textarea rows=15 cols=55 name=t1></textarea></td></tr>");  
      out.println("<tr><td><input type=submit value=execute name=t3></td></tr> </table>");  
      out.println("</form></center></body></html>");  
     }  
      catch(Exception e){}  
   }  
    protected void doPost(HttpServletRequest request, HttpServletResponse response)  
   throws ServletException, IOException  
   {  
     response.setContentType("text/html");  
     PrintWriter out = response.getWriter();  
     myForm(request, response);  
     String query=request.getParameter("t1");  
     ServletConfig config=getServletConfig();  
     ServletContext context=config.getServletContext();  
       String s1=context.getInitParameter("d1");  
       String s2=context.getInitParameter("d2");  
       String s3=context.getInitParameter("d3");  
       String s4=context.getInitParameter("d4");  
     try {  
        Class.forName(s1);  
        Connection con=DriverManager.getConnection(s2,s3,s4);  
        PreparedStatement ps=con.prepareStatement(query);  
        if(query.startsWith("select"))  
        {  
          ResultSet rs=ps.executeQuery(query);  
          ResultSetMetaData rsmd=rs.getMetaData();  
          int columncount=rsmd.getColumnCount();  
          out.println("<center><table bgcolor=yellow border=2>");  
          out.println("<tr>");  
          for(int k=1;k<=columncount;k++)  
          {  
            out.println("<th>");  
            out.println(rsmd.getColumnName(k));  
            out.println("</th>");  
          }  
          out.println("</tr>");  
          while(rs.next())  
          {  
            out.println("<tr>");  
            for(int j=1;j<=columncount;j++)  
            {  
              out.println("<td>");  
              out.println(rs.getString(j));  
              out.println("</td>");  
            }  
            out.println("</tr>");  
          }  
          out.println("</table></center>");  
        }  
      else if(query.startsWith("insert"))  
      {  
       int i=ps.executeUpdate();  
       if(i!=0)  
       out.println("<center>"+i+ "row(s) inserted Successfully"+"</center>");  
      }  
      else if(query.startsWith("delete"))  
        {  
         int i=ps.executeUpdate();  
       if(i!=0)  
       out.println("<center>"+i+ " row(s) deleted Successfully"+"</center>");  
       }  
        else if(query.startsWith("update"))  
        {  
       int i=ps.executeUpdate();  
       if(i!=0)  
        out.println("<center>"+i+ "row(s) updated Successfully"+"</center>");  
        }  
     }  
     catch(Exception e) { }  
   }  
 }  

web.xml
 <?xml version="1.0" encoding="UTF-8"?>  
 <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">  
   <context-param>  
     <param-name>d1</param-name>  
     <param-value>oracle.jdbc.driver.OracleDriver</param-value>  
   </context-param>  
   <context-param>  
     <param-name>d2</param-name>  
     <param-value>jdbc:oracle:thin:@localhost:1521:xe</param-value>  
   </context-param>  
   <context-param>  
     <param-name>d3</param-name>  
     <param-value>hr</param-value>  
   </context-param>  
   <context-param>  
     <param-name>d4</param-name>  
     <param-value>hr</param-value>  
   </context-param>  
   <servlet>  
     <servlet-name>QueryServlet</servlet-name>  
     <servlet-class>blog.webideaworld.QueryServlet</servlet-class>  
   </servlet>  
   <servlet-mapping>  
     <servlet-name>QueryServlet</servlet-name>  
     <url-pattern>/Queryrun</url-pattern>  
   </servlet-mapping>  
   <session-config>  
     <session-timeout>  
       30  
     </session-timeout>  
   </session-config>  
   <welcome-file-list>  
     <welcome-file>Queryrun</welcome-file>  
   </welcome-file-list>  
 </web-app>  


Download Code Link 1
Download Code Link 2


Output:

No comments:

Popular Posts