How to run SQL commands directly in a browser

This article demonstrates how to use a Java Servlet, a JSP page, and a static Java class to construct an SQL gateway application. With this application, you can directly execute SQL commands in the browser. the browser will submit the SQL commands to the database system on the remote server and then return the results.
If you are using the database provided by the Internet Service Provider, you may be familiar with the concept of the SQL gateway application. Some ISPs provide an HTML page for database operations, just like the gateway application provided in this Article. If the ISP does not provide such an interface, you can upload the program in this article to the server. It is very convenient to access the database on the ISP Server in the future.
The SQL gateway application can be used not only in the development process, but also directly for end users who are familiar with the system. Of course, allowing end users to directly run SQL commands on the database poses some security risks and should be carefully considered.
This article requires you to have basic knowledge of Java, Servlet, JSP, and database. To run the program in this article, you must have a Servlet/JSP server and database server. In the following description, we want to use Tomcat 4.0 and MySQL, but it should be able to run in other JSP/Servlet containers. If you want to use a database other than MySQL, you only need to provide an appropriate driver and modify the database connection string.
I. User Interface
Figure 1 shows the SQL gateway User Interface in this article. On this page, the SQL gateway has executed an SQL command and returned the result.
Figure 1: SQL gateway user interface
As shown in figure 1, a piece of information 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 successfully, the query results are displayed in the HTML table at the bottom of the page, as shown in figure 2.
Figure 2: the query result set is displayed in the HTML table.
Of course, if the SQL command fails to be executed, the SQL gateway returns an exception message.
Ii. design the JSP page
On the JSP page, we first put a Scriptlet. Its function is to extract two attributes from the session object:
<! 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 attribute sqlStatement represents the SQL command string, and the second attribute message is a string containing the result information. If the values of these two attributes are null, they are not set yet. We set the sqlStatement and message variables to empty strings.