home *** CD-ROM | disk | FTP | other *** search
/ PC Online 1999 November / PCONLINE_11_99.ISO / filesbbs / OS2 / APCHSSL2.ZIP / OS2HTTPD / servlets / DataAccessServlet.class (.txt) next >
Encoding:
Java Class File  |  1999-08-10  |  10.5 KB  |  310 lines
  1. import java.io.IOException;
  2. import java.sql.Connection;
  3. import java.sql.DatabaseMetaData;
  4. import java.sql.DriverManager;
  5. import java.sql.ResultSet;
  6. import java.sql.ResultSetMetaData;
  7. import java.sql.SQLException;
  8. import java.sql.SQLWarning;
  9. import java.sql.Statement;
  10. import java.util.Date;
  11. import java.util.Hashtable;
  12. import java.util.Properties;
  13. import javax.servlet.ServletConfig;
  14. import javax.servlet.ServletException;
  15. import javax.servlet.ServletOutputStream;
  16. import javax.servlet.ServletRequest;
  17. import javax.servlet.ServletResponse;
  18. import javax.servlet.http.HttpServlet;
  19.  
  20. public class DataAccessServlet extends HttpServlet {
  21.    private String m_strUID = "";
  22.    private String m_strPWD = "";
  23.    private String m_strURL = "";
  24.    private String m_strDriver = "";
  25.    private String m_strSQLString = "";
  26.    private String m_strActionType = "";
  27.    private String m_strDatabase = "";
  28.    private String m_strTableName = "";
  29.    private Properties m_properties;
  30.    private Connection m_connection;
  31.    private ResultSet m_resultSet;
  32.    private Statement m_statement;
  33.    private ServletOutputStream m_outstream;
  34.  
  35.    private static boolean checkForWarning(SQLWarning var0) throws SQLException {
  36.       boolean var1 = false;
  37.       if (var0 != null) {
  38.          System.err.println("\n *** SQL Warning ***\n");
  39.  
  40.          for(var1 = true; var0 != null; var0 = var0.getNextWarning()) {
  41.             System.err.println("SQLState: " + ((SQLException)var0).getSQLState());
  42.             System.err.println("Message:  " + ((Throwable)var0).getMessage());
  43.             System.err.println("Vendor:   " + ((SQLException)var0).getErrorCode());
  44.             System.err.println("");
  45.          }
  46.       }
  47.  
  48.       return var1;
  49.    }
  50.  
  51.    private void connectToDatabase(String var1, String var2, Properties var3, ServletResponse var4) {
  52.       try {
  53.          this.trace("Trying the JDBC driver.");
  54.          Class.forName(var2);
  55.          this.trace("Getting a database connection.");
  56.          this.m_connection = DriverManager.getConnection(var1, ((Hashtable)var3).get("UID").toString(), ((Hashtable)var3).get("PWD").toString());
  57.          this.trace("Checking for connection warnings.");
  58.          checkForWarning(this.m_connection.getWarnings());
  59.          this.trace("Connected to database.");
  60.       } catch (SQLException var6) {
  61.          this.trace("An SQL Exception was thrown:\n" + var6);
  62.          this.processSQLException(var6, var4);
  63.       } catch (Exception var7) {
  64.          ((Throwable)var7).printStackTrace();
  65.       }
  66.    }
  67.  
  68.    public void destroy() {
  69.       this.trace("Destroying the servlet.");
  70.  
  71.       try {
  72.          this.m_connection.commit();
  73.          this.m_resultSet.close();
  74.          this.m_resultSet = null;
  75.          this.m_statement.close();
  76.          this.m_statement = null;
  77.          this.m_connection.close();
  78.          this.m_connection = null;
  79.       } catch (SQLException var2) {
  80.          SQLException var1 = var2;
  81.          System.err.println("\n*** SQLException caught ***\n");
  82.  
  83.          while(var1 != null) {
  84.             System.err.println("SQLState: " + var1.getSQLState());
  85.             System.err.println("Message:  " + ((Throwable)var1).getMessage());
  86.             System.err.println("Vendor:   " + var1.getErrorCode());
  87.             var1 = var1.getNextException();
  88.             System.err.println("");
  89.          }
  90.  
  91.       }
  92.    }
  93.  
  94.    private void executeTheQuery(String var1, ServletResponse var2) {
  95.       try {
  96.          this.trace("Executing a query.");
  97.          this.m_statement = this.m_connection.createStatement();
  98.          this.m_resultSet = this.m_statement.executeQuery(var1);
  99.       } catch (SQLException var4) {
  100.          this.trace("An SQL Exception was thrown:\n" + var4);
  101.          this.processSQLException(var4, var2);
  102.       }
  103.    }
  104.  
  105.    private void executeTheUpdate(String var1, ServletResponse var2) {
  106.       try {
  107.          this.trace("Executing an update.");
  108.          this.m_statement = this.m_connection.createStatement();
  109.          this.m_statement.executeUpdate(var1);
  110.       } catch (SQLException var4) {
  111.          this.trace("An SQL Exception was thrown:\n" + var4);
  112.          this.processSQLException(var4, var2);
  113.       }
  114.    }
  115.  
  116.    public String getServletInfo() {
  117.       return "a data access servlet by Sheldon Bradley Wosnick which demonstrates server-side JDBC programming with servlets";
  118.    }
  119.  
  120.    public void init(ServletConfig var1) {
  121.       this.trace("Calling overridden init() method now.");
  122.  
  123.       try {
  124.          super.init(var1);
  125.       } catch (ServletException var3) {
  126.          System.err.println("ServletException: " + ((Throwable)var3).getMessage());
  127.       }
  128.    }
  129.  
  130.    private void outResultSet(ServletResponse var1, String var2) throws SQLException {
  131.       try {
  132.          this.trace("Getting an output stream for the HTML response page.");
  133.          this.m_outstream = var1.getOutputStream();
  134.          var1.setContentType("text/html");
  135.          this.m_outstream.println("<html>");
  136.          this.m_outstream.println("<head><title>A Data Access Servlet using Pure JDBC</title></head>");
  137.  
  138.          try {
  139.             this.trace("Preparing some connection information.");
  140.             ResultSetMetaData var3 = this.m_resultSet.getMetaData();
  141.             DatabaseMetaData var4 = this.m_connection.getMetaData();
  142.             int var5 = var3.getColumnCount();
  143.             this.m_outstream.println("JDBC DATABASE CONNECTION DETAILS:");
  144.             this.m_outstream.println("<BR>");
  145.             this.m_outstream.println("<BR>Current date and time: " + (new Date()).toString());
  146.             this.m_outstream.println("<BR>Connection URL: " + var4.getURL());
  147.             this.m_outstream.println("<BR>Driver: " + var4.getDriverName());
  148.             this.m_outstream.println("<BR>Version: " + var4.getDriverVersion());
  149.             this.m_outstream.println("<BR>User Name: " + var4.getUserName());
  150.             this.m_outstream.println("<BR>Driver Major Version: " + var4.getDriverMajorVersion());
  151.             this.m_outstream.println("<BR>Driver Minor Version: " + var4.getDriverMinorVersion());
  152.             this.m_outstream.println("<BR>Database Product Name: " + var4.getDatabaseProductName());
  153.             this.m_outstream.println("<BR>Database Product Version: " + var4.getDatabaseProductVersion());
  154.             this.m_outstream.println("<BR>SQL query or update command processed by servlet: " + this.m_strSQLString);
  155.             this.m_outstream.println("<BR><BR>");
  156.             this.trace("Preparing an HTML table.");
  157.             this.m_outstream.println("<TABLE>");
  158.             this.m_outstream.println("<TABLE BORDER=1>");
  159.             String var6 = "TABLE: " + this.m_strTableName + " ***** ACTION TAKEN: " + var2;
  160.             this.m_outstream.println("<CAPTION>" + var6 + "</CAPTION>");
  161.             this.trace("Creating the column names");
  162.  
  163.             for(int var7 = 1; var7 <= var5; ++var7) {
  164.                this.m_outstream.println("<TH>" + var3.getColumnLabel(var7));
  165.             }
  166.  
  167.             boolean var8 = this.m_resultSet.next();
  168.             this.trace("Filling in the table elements.");
  169.  
  170.             while(var8) {
  171.                this.m_outstream.println("<TR>");
  172.  
  173.                for(int var11 = 1; var11 <= var5; ++var11) {
  174.                   if (!this.m_strTableName.toUpperCase().equals("TESTTT") || var11 != 1 && var11 != 3) {
  175.                      this.m_outstream.println("<TD>" + this.m_resultSet.getString(var11));
  176.                   } else {
  177.                      this.m_outstream.println("<TD>" + new String(this.m_resultSet.getBytes(var11), "ibm819"));
  178.                   }
  179.                }
  180.  
  181.                var8 = this.m_resultSet.next();
  182.             }
  183.  
  184.             this.m_outstream.println("</FONT>");
  185.             this.m_outstream.println("</TABLE>");
  186.             this.trace("Finished preparing the HTML table.");
  187.          } catch (SQLException var9) {
  188.             this.trace("An SQL Exception was thrown:\n" + var9);
  189.             this.processSQLException(var9, var1);
  190.          }
  191.  
  192.          this.m_outstream.println("</body></html>");
  193.          this.trace("Finished with the output stream.");
  194.          this.m_outstream.flush();
  195.          this.m_outstream.close();
  196.          this.trace("Flushed and closed the output stream.");
  197.       } catch (IOException var10) {
  198.          System.err.println("\n*** IOException caught ***\n");
  199.          System.err.println("IOException: " + ((Throwable)var10).toString());
  200.       }
  201.    }
  202.  
  203.    private void processSQLException(SQLException var1, ServletResponse var2) {
  204.       try {
  205.          this.trace("Processing an SQL Exception");
  206.          this.m_outstream = var2.getOutputStream();
  207.          var2.setContentType("text/html");
  208.          this.m_outstream.println("<html>");
  209.          this.m_outstream.println("<head><title>Servlet threw an SQLException</title></head>");
  210.          this.m_outstream.println("<body BGCOLOR=#000000 TEXT=#C0C0C0>");
  211.          this.m_outstream.println("<P>*** An SQLException was caught ***</P>");
  212.  
  213.          while(var1 != null) {
  214.             this.m_outstream.println("<P>SQLState: " + var1.getSQLState() + "</P>");
  215.             this.m_outstream.println("<P>Message:  " + ((Throwable)var1).getMessage() + "</P>");
  216.             this.m_outstream.println("<P>Vendor:   " + var1.getErrorCode() + "</P>");
  217.             var1 = var1.getNextException();
  218.             this.m_outstream.flush();
  219.             this.m_outstream.close();
  220.          }
  221.  
  222.       } catch (IOException var4) {
  223.          System.err.println("\n*** IOException caught ***\n");
  224.          System.err.println("IOException: " + ((Throwable)var4).toString());
  225.       }
  226.    }
  227.  
  228.    public void service(ServletRequest var1, ServletResponse var2) throws ServletException, IOException {
  229.       this.trace("Calling the service() method now.");
  230.       this.m_properties = new Properties();
  231.       this.trace("Getting request parameters.");
  232.       this.m_strUID = var1.getParameter("UID");
  233.       this.m_strPWD = var1.getParameter("password");
  234.       this.m_strDriver = var1.getParameter("driver");
  235.       this.m_strTableName = var1.getParameter("table");
  236.       this.m_strSQLString = var1.getParameter("SQL");
  237.       this.m_strDatabase = var1.getParameter("database");
  238.       if (!this.m_strDriver.substring(0, 16).equals("org.gjt.mm.mysql") && !this.m_strDriver.substring(0, 22).equals("com.imaginary.sql.msql") && !this.m_strDriver.substring(0, 20).equals("COM.ibm.db2.jdbc.net")) {
  239.          this.m_strURL = "jdbc:" + var1.getParameter("URL") + ":" + this.m_strDatabase;
  240.       } else {
  241.          this.m_strURL = "jdbc:" + var1.getParameter("URL") + "/" + this.m_strDatabase;
  242.       }
  243.  
  244.       this.m_strActionType = var1.getParameter("action");
  245.       this.m_properties.put("UID", this.m_strUID);
  246.       this.m_properties.put("PWD", this.m_strPWD);
  247.       this.trace("User ID: " + this.m_strUID);
  248.       this.trace("Driver: " + this.m_strDriver);
  249.       this.trace("URL: " + this.m_strURL);
  250.       this.trace("Table Name = " + this.m_strTableName);
  251.       this.trace("SQL String = " + this.m_strSQLString);
  252.       this.trace("Action Type = " + this.m_strActionType);
  253.       if (this.m_connection == null) {
  254.          this.connectToDatabase(this.m_strURL, this.m_strDriver, this.m_properties, var2);
  255.       }
  256.  
  257.       if (this.m_strActionType.equals("query")) {
  258.          this.executeTheQuery(this.m_strSQLString, var2);
  259.  
  260.          try {
  261.             this.outResultSet(var2, "User-defined Query");
  262.          } catch (SQLException var7) {
  263.             this.trace("An SQL Exception was thrown.");
  264.             this.processSQLException(var7, var2);
  265.          }
  266.       } else if (this.m_strActionType.equals("update")) {
  267.          try {
  268.             this.trace("Querying the database for table " + this.m_strTableName + " before any updates.");
  269.             this.m_statement = this.m_connection.createStatement();
  270.             this.m_resultSet = this.m_statement.executeQuery("select * from " + this.m_strTableName);
  271.             this.outResultSet(var2, "Query Before the Update");
  272.          } catch (SQLException var6) {
  273.             this.trace("An SQL Exception was thrown:\n" + var6);
  274.             this.processSQLException(var6, var2);
  275.          }
  276.  
  277.          this.executeTheUpdate(this.m_strSQLString, var2);
  278.  
  279.          try {
  280.             this.trace("Requerying the database for table " + this.m_strTableName + " after any updates.");
  281.             this.m_statement = this.m_connection.createStatement();
  282.             this.m_resultSet = this.m_statement.executeQuery("select * from " + this.m_strTableName);
  283.             this.outResultSet(var2, "Query after the Update");
  284.          } catch (SQLException var5) {
  285.             this.trace("An SQL Exception was thrown:\n" + var5);
  286.             this.processSQLException(var5, var2);
  287.          }
  288.       } else {
  289.          System.err.println("ERROR >> For processing purposes, you must explicitly pass 'update' or 'query'");
  290.       }
  291.  
  292.       try {
  293.          this.m_connection.commit();
  294.          this.m_resultSet.close();
  295.          this.m_resultSet = null;
  296.          this.m_statement.close();
  297.          this.m_statement = null;
  298.          this.m_connection.close();
  299.          this.m_connection = null;
  300.       } catch (SQLException var4) {
  301.          this.trace("An SQL Exception was thrown:\n" + var4);
  302.          this.processSQLException(var4, var2);
  303.       }
  304.    }
  305.  
  306.    public void trace(String var1) {
  307.       System.out.println("DEBUG TRACE:  " + var1);
  308.    }
  309. }
  310.