How to run the SQL command directly in the browser

xiaoxiao2021-03-06 58

How to run directly in the SQL command within the browser Author: Joel Murach; cactus studio Translation dispatch time: 2003.08.13 14:39:58 This article demonstrates how to use a Java Servlet, a JSP page and a static Java class to construct a SQL Gateway application. With this app, you can execute the SQL command directly, and the browser will submit the SQL command to the database system on the remote server, then return the result. If you are using the database provided by ISP (Internet Service Provider), you may be familiar with the concept of SQL gateway app. Some ISPs will provide an HTML page for an operational database, just like the gateway provided herein. If the ISP does not provide this interface, you can load this article to the server, which is convenient to access the database on the ISP server. SQL gateway applications can not only be used for development processes, but also directly provide end users who are more familiar with the system. Of course, allowing end users to run SQL commands directly on the database to bring some security hazards, should be careful. This article requires readers to have a certain basic knowledge of Java, Servlet, JSP, and databases. If you want to run this article, there is a servlet / JSP server and database server. In the following description, we want to use Tomcat 4.0 and mysql, but it should also run in other JSP / Servlet containers; if you want to use other databases other than mysql, just provide an appropriate driver, Then modify the database connection string. First, the user interface Figure 1 is the user interface of the SQL gateway of this article. In this interface, the SQL gateway has executed a SQL command and returns the result.

Figure 1: User interface of the SQL gateway

As can be seen from Figure 1, a message at the bottom of the page shows the number of rows affected by the recently executed SQL command. If the SQL command is a SELECT statement, when the SELECT statement is executed, the page will be displayed with the HTML table with the HTML table, as shown in Figure 2.

Figure 2: HTML form Shows the query result set

Of course, if the SQL command is executed, the SQL gateway will return an exception information. Second, the design JSP page is in the JSP page, we first put into a scriptlet, its function is to extract two attributes from the session object:

String SqlStateMent = (String) session.getattribute ("SqlStatement");

IF (SqlStatement == Null) SQLStatement = ""

String message = (string) session.getattribute ("message");

IF (message == null) message = "";

The first attribute SQLStatement represents the SQL command string, and the second attribute message is a string containing the result information. If the value of these two attributes is null, they have not been set, and we set the SQLStatement and Message variables into empty strings. The JSP page also has an HTML form, the HTML form contains a textual (TextArea) and a "Execute" button.

sql command:

</p> <p><% = sqlStatement%> </ textarea> <br></p> <p><br></p> <p><input type = "submit" value = "execution"></p> <p></ form></p> <p>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 of the text area, the size of the text area is a width of 60 characters, high 8 lines. When the JSP page is run, the content of this text area is empty. If the user clicks the "Execution" button under the text area, the JSP page submits the form content to the SqlgatewayServlet (later in detail later). The table at the bottom of the JSP page displays the contents of the Message string. As mentioned earlier, the content of Message is the result of running the SQL command.</p> <p><b> SQL command execution result: </ b> <br></p> <p><Table cellpadding = "5" border = "1"></p> <p><% = message%></p> <p></ table></p> <p>Third, write servlet SqlgatewayServlet to first import java.sql package to use the JDBC class. In addition, it also declares a Connection object so that all methods within the servlet can use the database connection.</p> <p>Package test;</p> <p>Import java.io. *;</p> <p>Import javax.servlet. *;</p> <p>Import javax.servlet.http. *;</p> <p>Import java.sql. *;</p> <p>Public class sqlgatewayServlet Extends httpservlet {</p> <p>PRIVATE CONNECTION Connection;</p> <p>When the servlet engine starts running this servlet, the servlet's init method opens a database connection:</p> <p>Public void init () throws servletexception {</p> <p>Try {</p> <p>Class.Forname ("org.gjt.mm.mysql.driver");</p> <p>String dburl = "JDBC: mysql: // localhost / murach";</p> <p>String Username = "root";</p> <p>String password = ""</p> <p>Connection = drivermanager.getConnection</p> <p>(DBURL, UserName, Password);</p> <p>}</p> <p>Catch (classnotfoundexception e) {</p> <p>System.out.println ("Can't find the database driver.");</p> <p>}</p> <p>Catch (SQLException E) {</p> <p>System.out.println ("You cannot open the database connection:"</p> <p> E.getMessage ());</p> <p>}</p> <p>}</p> <p>In this example, servlet uses a MYSQL database to open the Murach database connection, the database, and servlet run on the same server. In addition, servlet uses MySQL's default user name root, the password is empty. However, you can modify the code here, as long as there is an appropriate driver, you can let servlets connect to any database on any server (see www.mysql.com) about the MySQL database. Before the servlet engine turns off, call the Destroy method to turn off the database connection, release the connection resource: public void destroy () {</p> <p>Try {</p> <p>Connection.Close ();</p> <p>}</p> <p>Catch (SQLException E) {</p> <p>System.out.println ("You cannot close the database connection:" E.GetMessage ());</p> <p>}</p> <p>}</p> <p>The jsp page described earlier To call the DOPOST method of the servlet, the DOPOST method calls the doget method:</p> <p>Public void dopost (httpservletRequest Request,</p> <p>Httpservletresponse response</p> <p>THROWS IOException, servletexception {</p> <p>DOGET (Request, Response);</p> <p>}</p> <p>In the Doget method, the first statement first gets the SQL command entered in the JSP page, the second statement declares the Message variable:</p> <p>Public void doget (httpservletRequest Request,</p> <p>Httpservletresponse response</p> <p>THROWS IOException, servletexception {</p> <p>String sqlStatement = Request.getParameter ("SqlStatement");</p> <p>String message = "";</p> <p>Then, within the TRY block, the first statement creates a statement object using the Connection object, the next two statements use the TRIM method of the String object and the SUBSTRING method to return the first six characters of the user entered the SQL command.</p> <p>Try {</p> <p>Statement Statement = connection.createstatement ();</p> <p>SqlStateMent = SQLStatement.trim ();</p> <p>String SQLTYPE = SQLSTATEMENT.SUBSTRING (0, 6);</p> <p>If the first six characters of the SQL command are "select", use the statement's ExecuteQuery method to execute the SQL statement, get a resultSet object, passed this object to the gethtmlrows method of the SQLUTIL class (later detailed), the gethtmlrows method will record the record The concentrated record is formatted into an HTML form and returns.</p> <p>IF (SQLTYPE.EQUALSIGNORECASE ("SELECT")) {</p> <p>ResultSet ResultSet = Statement.executeQuery (SqlStatement);</p> <p>// Construct a String, which contains the result set data in the form of an HTML table.</p> <p>Message = SQLUTIL.GETHTMLROWS (RESULTSET);</p> <p>}</p> <p>If the first six characters of the SQL statement are not "SELECT", we call the executeUpdate method for the Statement object. 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, etc., otherwise, the SQL command may be a DML command, such as INSERT, UPDATE, or DELETE command. No matter which SQL command, we all set the Message variable into the corresponding message. Else</p> <p>{</p> <p>INT i = statement.executeUpdate (SqlStatement);</p> <p>IF (i == 0) // This is a DDL command</p> <p>Message =</p> <p><TR> <TD> " </p> <p>"The command executes success." </p> <p>"</ td> </ tr>";</p> <p>Else // This is an insert, update or delete command</p> <p>Message =</p> <p><TR> <TD> " </p> <p>"The SQL command is successful. <br>" </p> <p>"Changed" i "line." </p> <p>"</ td> </ tr>";</p> <p>}</p> <p>STATEMENT.CLOSE ();</p> <p>}</p> <p>If any of the statements in the TRY block throws a SQLException, the CATCH block sets the Message variable to contain information about the SQLException. For example, if the SQL command syntax error entered in the form, the Message variable value set below will help you troubleshoot errors.</p> <p>Catch (SQLException E) {</p> <p>Message = "<tr> <td> encountered an error when executing the SQL command: <br></p> <p> E.getMessage () "</ tr> </ td>";</p> <p>}</p> <p>After the Catch block, the next three statements get the session object, set the SQLStatement and Message variables to the properties of the session:</p> <p>HttpSession session = request.getations ();</p> <p>Session.SetaTRibute ("Message", Message;</p> <p>Session.setttribute ("SqlStatement", SQLStatement;</p> <p>Next, the last two statements create a RequestDispatcher and forward the request and response objects to the JSP page introduced in the following:</p> <p>RequestDispatcher dispatcher =</p> <p>GetServletContext (). getRequestDispatcher</p> <p>"/SQL/SQL_GATEWAY.JSP");</p> <p>Dispatcher.Forward (Request, Response);</p> <p>Fourth, write tool classes to see tool class SQLUTIL code:</p> <p>Package test;</p> <p>Import java.sql. *;</p> <p>Public class sqlutil {</p> <p>The SQLUTIL class contains a gethtmlrow static method. The front servlet is in the HTML form by calling the method. GetHtmlrow's input parameters are a RESULTSET object that returns a String object, and the content of this String object is all lists of records and HTML code for rows. In order to construct such a String object, gethtmlrows declares a StringBuffer object called HTMLROWS and add data to this StringBuffer object during the method execution. At the end of the gethtmlrows method, we use the toString method to convert StringBuffer content to string, and finally return this string to servlet: public static synchronized string gethtmlrows (Resultset Results)</p> <p>Throws sqlexception {</p> <p>StringBuffer HTMLROWS = New StringBuffer ();</p> <p>ResultSetmetaData metadata = results.getMetadata ();</p> <p>INT columncount = metadata.getcolumncount ();</p> <p>// Title as the name of the column column as the title of the HTML table</p> <p>HTMLROWS.APPEND ("<tr>");</p> <p>For (int i = 1; i <= columncount; i )</p> <p>HTMLROWS.APPEND ("<TD> <B>" metadata.getcolumnname (i) "</ td>");</p> <p>Htmlrows.Append ("</ TR>");</p> <p>// For each line of the result, each line ...</p> <p>WHILE (results.next ()) {</p> <p>HTMLROWS.APPEND ("<tr>");</p> <p>// convert each column in the line into a table unit</p> <p>For (int i = 1; i <= columncount; i )</p> <p>HTMLROWS.APPEND ("<TD>" Results.getstring (i) "</ td>");</p> <p>}</p> <p>Htmlrows.Append ("</ TR>");</p> <p>Return htmlrows.tostring ();</p> <p>}</p> <p>In order to obtain the column header of the recordset object, the gethtmlrows method uses the resultSet's getMetadata method to create a RESULTSETMETADATA object, and the ResultSetMetadata object contains the description of the record set. For example, the number of columns, the column can call the resultetMetadata's getColumnTmetata's getColumncount and getColumnname method. obtain. In order to extract the data of the record set, the gethtmlrows method utilizes a nested loop, which is a nested for loop in the While loop to extract each of the values of each column in each row. Within the loop, we use the GetString method of the record set to get the value of each field, regardless of the original type of the field value, the getString method converts it to String. Note that this method has a synchronized keyword, which is to avoid two or more Servlet threads simultaneously. Download this article: sqlgateway_code.zip.</p></div><div class="text-center mt-3 text-grey"> 转载请注明原文地址:https://www.9cbs.com/read-113918.html</div><div class="plugin d-flex justify-content-center mt-3"></div><hr><div class="row"><div class="col-lg-12 text-muted mt-2"><i class="icon-tags mr-2"></i><span class="badge border border-secondary mr-2"><h2 class="h6 mb-0 small"><a class="text-secondary" href="tag-2.html">9cbs</a></h2></span></div></div></div></div><div class="card card-postlist border-white shadow"><div class="card-body"><div class="card-title"><div class="d-flex justify-content-between"><div><b>New Post</b>(<span class="posts">0</span>) </div><div></div></div></div><ul class="postlist list-unstyled"> </ul></div></div><div class="d-none threadlist"><input type="checkbox" name="modtid" value="113918" checked /></div></div></div></div></div><footer class="text-muted small bg-dark py-4 mt-3" id="footer"><div class="container"><div class="row"><div class="col">CopyRight © 2020 All Rights Reserved </div><div class="col text-right">Processed: <b>0.035</b>, SQL: <b>9</b></div></div></div></footer><script src="./lang/en-us/lang.js?2.2.0"></script><script src="view/js/jquery.min.js?2.2.0"></script><script src="view/js/popper.min.js?2.2.0"></script><script src="view/js/bootstrap.min.js?2.2.0"></script><script src="view/js/xiuno.js?2.2.0"></script><script src="view/js/bootstrap-plugin.js?2.2.0"></script><script src="view/js/async.min.js?2.2.0"></script><script src="view/js/form.js?2.2.0"></script><script> var debug = DEBUG = 0; var url_rewrite_on = 1; var url_path = './'; var forumarr = {"1":"Tech"}; var fid = 1; var uid = 0; var gid = 0; xn.options.water_image_url = 'view/img/water-small.png'; </script><script src="view/js/wellcms.js?2.2.0"></script><a class="scroll-to-top rounded" href="javascript:void(0);"><i class="icon-angle-up"></i></a><a class="scroll-to-bottom rounded" href="javascript:void(0);" style="display: inline;"><i class="icon-angle-down"></i></a></body></html><script> var forum_url = 'list-1.html'; var safe_token = 'zVmwr6FUupbA_2Blm8SSSsWaVif7JeXPsZIzZ1xE5MFfIM3mlKqoRYvg0nGvyin9mlVS6Pu1GaagWgA2ZU'; var body = $('body'); body.on('submit', '#form', function() { var jthis = $(this); var jsubmit = jthis.find('#submit'); jthis.reset(); jsubmit.button('loading'); var postdata = jthis.serializeObject(); $.xpost(jthis.attr('action'), postdata, function(code, message) { if(code == 0) { location.reload(); } else { $.alert(message); jsubmit.button('reset'); } }); return false; }); function resize_image() { var jmessagelist = $('div.message'); var first_width = jmessagelist.width(); jmessagelist.each(function() { var jdiv = $(this); var maxwidth = jdiv.attr('isfirst') ? first_width : jdiv.width(); var jmessage_width = Math.min(jdiv.width(), maxwidth); jdiv.find('img, embed, iframe, video').each(function() { var jimg = $(this); var img_width = this.org_width; var img_height = this.org_height; if(!img_width) { var img_width = jimg.attr('width'); var img_height = jimg.attr('height'); this.org_width = img_width; this.org_height = img_height; } if(img_width > jmessage_width) { if(this.tagName == 'IMG') { jimg.width(jmessage_width); jimg.css('height', 'auto'); jimg.css('cursor', 'pointer'); jimg.on('click', function() { }); } else { jimg.width(jmessage_width); var height = (img_height / img_width) * jimg.width(); jimg.height(height); } } }); }); } function resize_table() { $('div.message').each(function() { var jdiv = $(this); jdiv.find('table').addClass('table').wrap('<div class="table-responsive"></div>'); }); } $(function() { resize_image(); resize_table(); $(window).on('resize', resize_image); }); var jmessage = $('#message'); jmessage.on('focus', function() {if(jmessage.t) { clearTimeout(jmessage.t); jmessage.t = null; } jmessage.css('height', '6rem'); }); jmessage.on('blur', function() {jmessage.t = setTimeout(function() { jmessage.css('height', '2.5rem');}, 1000); }); $('#nav li[data-active="fid-1"]').addClass('active'); </script>