The code for the servlet
The SQLGatewayServlet, which is stored in the murach.sql
package, starts by importing the java.sql package so it can use
the JDBC classes. In addition, it declares a Connection object
so the database connection can be used by all of the methods in
the servlet:
package murach.sql;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
public class SQLGatewayServlet extends HttpServlet{
private Connection connection;
When the servlet engine places this servlet into service, the
init method opens the connection to the database.
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(
"Error opening the db connection: "
+ e.getMessage());
}
}
Usually, this occurs when the first user uses the application.
That way, the database connection will be open and available for
all subsequent users. Then, a new thread is spawned for each
user that uses this servlet.
In this example, the servlet uses a driver for the MySQL
database to open a connection to a database named "murach"
that's running on the same server as the servlet. In addition,
this servlet uses MySQL's default username of "root" and a blank
password. However, you can modify this code to connect to just
about any type of database running on any type of server. Either
way, you'll need to make sure that an appropriate driver for the
database is installed on the server. For more information about
getting, installing, and configuring MySQL, you can go to
www.mysql.com. In addition, there's an introduction to MySQL in
chapter 10 of Murach's Java Servlets and JSP.
Before the servlet engine takes a servlet out of service, the
destroy method closes the database connection and frees up the
resources required by the connection.
public void destroy() {
try{
connection.close();
}
catch(SQLException e){
System.out.println(
"Error closing the db connection: "
+ e.getMessage());
}
}
When the JSP shown earlier calls the doPost method, this
method calls the doGet method.
public void doPost(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException{
doGet(request, response);
}
Within the doGet method, the first statement gets the SQL
statement that the user entered in the JSP, 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 a Statement object, and the next two
statements use the trim and substring methods of a String object
to return the first six letters of the SQL statement that the
user entered.
try{
Statement statement = connection.createStatement();
sqlStatement = sqlStatement.trim();
String sqlType = sqlStatement.substring(0, 6);
If the first six letters of the SQL statement are "select", the
executeQuery method of the Statement object returns a ResultSet
object. Then, this object is passed to the getHtmlRows method of
the SQLUtil class that's shown later in this article, and it
returns the result set formatted with the HTML tags for rows and
columns.
if (sqlType.equalsIgnoreCase("select")){
ResultSet resultSet =
statement.executeQuery(sqlStatement);
message =
SQLUtil.getHtmlRows(resultSet);
}
However, if the first six letters of the SQL statement aren't
"select", the executeUpdate method of the Statement object is
called, which returns the number of rows that were affected. If
the number of rows is 0, the SQL statement was a DDL statement
like a DROP TABLE or CREATE TABLE statement. Otherwise, the SQL
statement was an INSERT, UPDATE, or DELETE statement. Either
way, the code sets the message variable to an appropriate
message.
else{
int i = statement.executeUpdate(sqlStatement);
if (i == 0) // this is a DDL statement
message =
"<tr><td>" +
"The statement executed successfully." +
"</td></tr>";
else // this is a DML statement
message =
"<tr><td>" +
"The statement executed successfully.<br>" +
i + " row(s) affected." +
"</td></tr>";
}
statement.close();
}
If any of the statements within the try block throw an
SQLException, the catch block sets the message variable to
display information about the SQLException. If, for example, you
enter an SQL statement that contains incorrect syntax, this
message will help you troubleshoot your syntax problem.
catch(SQLException e){
message = "Error executing the SQL statement: <br>"
+ e.getMessage();
}
After the catch block, the next three statements get the session
object and set the sqlStatement and message variables as
attributes of that object.
HttpSession session = request.getSession();
session.setAttribute("message", message);
session.setAttribute("sqlStatement", sqlStatement);
Then, the last two statements return a RequestDispatcher object
that forwards the request and response objects to the JSP shown
earlier in this article.
RequestDispatcher dispatcher =
getServletContext().getRequestDispatcher(
"/sql/sql_gateway.jsp");
dispatcher.forward(request, response);
}
}
New on the Java Boutique:
New Review:
Time Management Made Easy with the Quartz Enterprise Job Scheduler
Why not just use the Java timer API? This open source scheduling
API boasts simplicity, ease-of-integration, a well-rounded feature
set, and it's free!
New Applet:
Reverse Complement
Reverse Complement is a simple applet that converts DNA or RNA
sequences into three useful formats.
Elsewhere on internet.com:
WebDeveloper Java
Lots of Java information on webdeveloper.com
WDVL Java
Thorough Java resource at the Web Developer's Virtual Library.
ScriptSearch Java
Hundreds of free Java code files to download.
jGuru: Your View of the Java Universe
Customizable portal with online training, FAQs, regular news updates, and tutorials.
