Build a Servlet-Based Application that Executes SQL Statements Against a Database - The Code for the Servlet
(Page 5 of 7 )
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);
}
}
Next: The Code for the Utility Class >>
More Java Articles
More By Joel Murach
/ 16 
