sqlgatewayservlet.java

J2EE独立开发制作

package test;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class SQLGatewayServlet extends HttpServlet{

    private Connection connection;

    public void init() throws ServletException{
        try{
            Class.forName("org.gjt.mm.mysql.Driver");
            String dbURL = "jdbc:mysql://localhost/murach?useUnicode=true&characterEncoding=GB2312";
            String username = "root";
            String password = "sun";
            connection = DriverManager.getConnection(dbURL, username, password);
        }
        catch(ClassNotFoundException e){
            System.out.println("找不到数据库驱动程序.");
        }
        catch(SQLException e){
            System.out.println("不能打开数据库连接: "
                               + e.getMessage());
        }
    }

    public void destroy() {
        try{
            connection.close();
        }
        catch(SQLException e){
            System.out.println("不能关闭数据库连接: " + e.getMessage());
        }
    }

    public void doGet(HttpServletRequest request,
                      HttpServletResponse response)
                      throws IOException, ServletException{

        String sqlStatement = request.getParameter("sqlStatement");
        String message = "";

        try{
            Statement statement = connection.createStatement();
            sqlStatement = sqlStatement.trim();
            String sqlType = sqlStatement.substring(0, 6);
            if  (sqlType.equalsIgnoreCase("select")){
                ResultSet resultSet = statement.executeQuery(sqlStatement);
                // 构造一个String，其中包含HTML表格形式的结果集数据
                message = SQLUtil.getHtmlRows(resultSet);
            }
            else
            {
                int i = statement.executeUpdate(sqlStatement);
                if (i == 0) // 这是一个DDL命令
                  message =
                    "<tr><td>" +
                      "命令执行成功." +
                    "</td></tr>";
                else // 这是一个INSERT、UPDATE或DELETE命令
                    message =
                      "<tr><td>" +
                        "SQL命令执行成功。<br>" +
                        "已更改" + i + " 行。" +
                       "</td></tr>";
            }
            statement.close();
        }
        catch(SQLException e){
            message = "<tr><td>执行SQL命令时遇到错误：<br>"
                    + e.getMessage() + "</tr></td>";
        }

        HttpSession session = request.getSession();
        session.setAttribute("message", message);
        session.setAttribute("sqlStatement", sqlStatement);

        RequestDispatcher dispatcher =
            getServletContext().getRequestDispatcher(
                "/sql_gateway.jsp");
        dispatcher.forward(request, response);

    }

    public void doPost(HttpServletRequest request,
                      HttpServletResponse response)
                      throws IOException, ServletException{
        doGet(request, response);
    }
}