Home > Others

How to run SQL commands directly in a browser

Source: Internet
Author: User
Tags empty execution getmessage mysql variables variable tostring trim
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
Browser


This article demonstrates how to construct a SQL gateway application with a Java Servlet, a JSP page, and a static Java class. With this application, you can execute SQL commands directly in the browser, and the browser will submit the SQL commands to the database system on the remote server and return the results.

If you are using a database provided by your ISP (Internet Service Provider), you may already be familiar with the concept of SQL Gateway application. Some ISPs provide an HTML page to manipulate the database, just like the gateway application provided in this article. If the ISP does not provide such an interface, you can upload the program to the server, and later access to the database on the ISP server is convenient.

SQL Gateway application can not only be used in the development process, but also can be directly supplied to the end-users of the system more familiar with the user. Of course, allowing end users to run SQL commands directly on the database poses some security risks and should be considered carefully.

This article requires the reader to have some basic knowledge of Java, Servlet, JSP and database, and if you want to run the program in this article, you should have a servlet/jsp server and a database server. In the instructions below, we're going to use Tomcat 4.0 and MySQL, but it should also work in other jsp/servlet containers, and if you want to switch to a database other than MySQL, just provide an appropriate driver and then modify the database connection string.

　　first, the user interface

Figure 1 is the user interface for this SQL gateway. In this interface, the SQL Gateway has executed an SQL command and returned the result.



Figure 1:sql Gateway user interface

As you can see from Figure 1, a piece of information at the bottom of the page shows the number of rows affected by the most recently executed SQL command. If the SQL command is a SELECT statement, when the SELECT statement is executed successfully, the query results are displayed in an HTML table at the bottom of the page, as shown in Figure Ii.



Figure 2:html Table shows the query result set

Of course, if the SQL command fails, the SQL Gateway returns exception information.

　　 second, design jsp page

In the JSP page, we first put in a scriptlet, which is the function of extracting two attributes from the Session object:

FFFFFF cellpadding=2 width=540 align=center bordercolorlight=black border=1> 
 
   
    
  <!doctype HTML PUBLIC "-//W3C//DTD HTML 4.0 transitional//en" ><%    string sqlstatement= (String) Session.getattribute ("SQLStatement");   if (SQLStatement = = null)    SQLStatement = "";   String message =  (string) session.getattribute (' message ');   if (message = = NULL) message  = "";%>


The first property sqlstatement represents the SQL command string, and the second property message is the string that contains the result information. If the values of these two properties are null, they are not yet set, we set the SQLStatement and message variables to an empty string.

The JSP page also has an HTML form with an HTML form containing a text area (TEXTAREA) and an "execute" button. 

 
    
     
  <form action= ". /servlet/test. Sqlgatewayservlet "method=" post "><b>sql command: </b><br><textarea name=" SqlStatement "cols=60 Rows=8><%=sqlstatement%></textarea><br><br><input type= "Submit" value= "executive" >< /form>


The text area in the form is used to enter the SQL command. We use the value of the SQLStatement variable as the default content for the text area, which is 60 characters wide and 8 lines high. When the JSP page runs for the first time, the contents of this text area are empty. If the user clicks on the "Execute" button below the text area, the JSP page submits the form content to Sqlgatewayservlet (described later in detail).

The table at the bottom of the JSP page displays the contents of the message string. As mentioned earlier, the content of the message is the result of running the SQL command. 

 
     
      
  <b>sql command Execution results: </b><br><table cellpadding= "5" border= "1" ><%=message%></table>


　　 Iii. writing a servlet

Sqlgatewayservlet first import the java.sql package to use the JDBC class. In addition, it declares a connection object so that all methods within the servlet can use a database connection. 

 
      
       
  Package Test;import java.io.*;import javax.servlet.*;import javax.servlet.http.*;import java.sql.*;p ublic class Sqlgatewayservlet extends httpservlet{    private Connection Connection;


When the servlet engine starts to run this Servlet,servlet init method, it opens a database connection: 

 
       
        
  public void Init () throws servletexception{    try{        class.forname ("Org.gjt.mm.mysql.Driver");        String Dburl = "Jdbc:mysql://localhost/murach";        String username = "root";        String password = "";        Connection = Drivermanager.getconnection (dburl, username, password);    catch (ClassNotFoundException e) {        System.out.println ("database driver not found.");    }    catch (SQLException e) {        System.out.println ("Cannot Open database connection:"                           + e.getmessage ());    }


In this example, the servlet uses a MySQL database driver to open the Murach database connection, and the database and servlet run on the same server. In addition, the servlet uses the MySQL default user name root and the password is blank. However, you can modify the code here, as long as you have the appropriate driver, you can have the servlet connect to any database on any server (see www.mysql.com For more information about the MySQL database).

Before the servlet engine closes the servlet, call the Destroy method to close the database connection and release the connection resources: 

 
        
         
  public void Destroy () {    try{        connection.close ();    }    catch (SQLException e) {        System.out.println ("Cannot Close database connection:" + e.getmessage ());    }


The JSP page described earlier wants to invoke the Dopost method of the servlet, Dopost method calls the Doget method: 

 
         
          
  public void DoPost (HttpServletRequest request,                   httpservletresponse response)                   throws IOException, servletexception{     doget (request, response);}


In the Doget method, the first statement gets the SQL command that the user entered in the JSP page, and the second statement declares the message variable: 

 
          
           
  public void doget (HttpServletRequest request,                   httpservletresponse response)                   throws IOException, servletexception{     String sqlstatement = Request.getparameter ("SQLStatement");     String message = "";


Then, within the try block, the first statement uses the connection object to create the statement object, and the next two statements use the Trim method of the string object and the substring method to return the first six characters of the SQL command entered by the user. 

 
           
            
  try{     Statement Statement = Connection.createstatement ();     SQLStatement = Sqlstatement.trim ();     String sqltype = sqlstatement.substring (0, 6);


If the first six characters of the SQL command are "select", the SQL statement is executed using the statement ExecuteQuery method, and a ResultSet object is obtained. By passing this object to the Gethtmlrows method of the Sqlutil class (detailed later), the Gethtmlrows method formats the record in the recordset into an HTML table and returns. 

 
            
             
  If  (Sqltype.equalsignorecase ("select")) {     ResultSet ResultSet = Statement.executequery (sqlstatement);     Constructs a string that contains the result set data message     = Sqlutil.gethtmlrows (resultSet) in the form of an HTML table;


If the first six characters of the SQL statement are not "select", we call the statement object's Executeupdate method, and the Executeupdate method returns the number of rows affected by the current operation-if this number is 0, the SQL command is a DDL command. such as drop table or create table, or otherwise, the SQL command might be a DML command, such as an INSERT, update, or delete command. Regardless of the SQL command, we set the message variable to the appropriate messages. 

 
             
              
  else     {         int i = statement.executeupdate (sqlstatement);         if (i = = 0)//This is a DDL command message           =             "<tr><td> +               " command executed successfully. "+             " </td></tr> ";         else//This is an INSERT, update, or delete command message             =               "<tr><td> +"                 SQL command executed successfully. <br> "+                 " has changed "+ i +" line. "+                " </td></tr> ";     }     Statement.close (); }


If any of the statements inside a try block throw a sqlexception,catch block, set the message variable to contain information about the SqlException. For example, if you enter a SQL command syntax error in a form, the message variable value set below will help you troubleshoot the error. 

 
              
               
  catch (SQLException e) {    message = ' <tr><td> encountered error executing SQL command:<br> '            + e.getmessage () + </tr ></td> ";}


After the catch block, the next three statements get the session object, setting the SQLStatement and message variables to the properties of the session: 

 
               
                
  HttpSession session = Request.getsession (); Session.setattribute ("message", message); Session.setattribute ("SQLStatement", SQLStatement);


Next, the last two statements create a requestdispatcher and forward the request and response objects to the JSP page described in the previous article: 

 
                
                 
  RequestDispatcher dispatcher =     getservletcontext (). Getrequestdispatcher (         "/sql/sql_gateway.jsp"); Dispatcher.forward (request, response);


　　 Four, the writing tool class

Let's look at the code for the tool class Sqlutil: 

 
                 
                  
  Package Test;import java.sql.*;p ublic class sqlutil{


The Sqlutil class contains a gethtmlrows static method that is the first servlet to format the result set into an HTML table by calling this method. The input parameter of the gethtmlrows is a ResultSet object whose return value is a string object whose contents are the HTML code for all the list questions and rows of the recordset. To construct such a string object, Gethtmlrows declares a StringBuffer object named Htmlrows, and then appends the data to the StringBuffer object during the execution of the method. At the end of the Gethtmlrows method, we use the ToString method to convert the contents of the StringBuffer to a string and finally return the string to the servlet: 

 
                  
                   
  public static synchronized String gethtmlrows (ResultSet results) throws sqlexception{StringBuffer    = new StringBuffer ();    ResultSetMetaData metaData = Results.getmetadata ();    int columnCount = Metadata.getcolumncount ();    Htmlrows.append the name of the column in the recordset as the title of the HTML table column    ("<tr>");    for (int i = 1; I <= columnCount i++)        htmlrows.append ("<td><b>" + metadata.getcolumnname (i) + "</td > ");    Htmlrows.append ("</tr>");    For each row in the result set ...    while (Results.next ()) {        htmlrows.append ("<tr>");        Converts each column in the row into a table cell for        (int i = 1; I <= columnCount; i++)            htmlrows.append ("<td>" + Results.getstrin G (i) + "</td>");    }    Htmlrows.append ("</tr>");    return htmlrows.tostring ();}


To get the column headers of the Recordset object, the Gethtmlrows method uses the ResultSet GetMetaData method to create a ResultSetMetaData object that contains descriptive information about the recordset. For example, the number of columns, and the names of columns, can be obtained by invoking the ResultSetMetaData getColumnCount and getColumnName methods respectively.

To extract the data from a recordset, the Gethtmlrows method extracts the values of each column in each row using a nested loop, which is a nested for loop inside the while loop. Within a loop, we use the GetString method of the recordset to get the values of each field, regardless of the original type of the field value, the GetString method converts it to a string.

Note that this method has a synchronized keyword in its declaration in order to prevent two or more servlet threads from executing the method at the same time.

Download the code for this article: Sqlgateway_code.zip.




This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

Related Keywords:
how to run php script in browser how to run php in browser how to run asp file in browser how to run php code in browser how to run unix commands in windows 8 how to run exe file in linux commands how to run view in sql

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

What's Trending
Top 10 Tags
datastax versions naming convention zookeeper client class definition md5 microsoft sql server 2005 data structures exception handling error handling
Top 10 Keywords
microsoft download center down wordpress address url site address url wordpress address url windows installer 4 0 download 302 not found web address url definition site address url wordpress db2 integer mac os installation step by step pdf abbreviation for return

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More