home *** CD-ROM | disk | FTP | other *** search
/ Chip 2005 March / CMCD0305.ISO / Software / Shareware / Grafica / 3dpie / Documentation / ServerTemplateScripts / DatabaseServletPie.java next >
Text File  |  2004-07-05  |  6KB  |  170 lines
  1. import java.sql.*;
  2. import java.lang.*;
  3. import java.io.*;
  4. import javax.servlet.*;
  5. import javax.servlet.http.*;
  6.  
  7. public class DatabaseServletPie extends HttpServlet {
  8. //
  9. // This simple servlet is designed to demonstrate how a servlet can be used
  10. // to retrieve data from a database and return the data in the correct format
  11. // to either the pie chart applet or servlet.
  12. //
  13. // As you will see the main routine ( doGet() ) uses the method
  14. // GraphData() to construct the return data.
  15. //
  16. // For further information visit,
  17. // http://www.jpowered.com/pie_chart/index.htm
  18. //
  19. //-----------------------------------------------------------------------------
  20.  
  21.     // Initialise and set variables
  22.     String url="jdbc:MySQL:///TESTDB";  // URL specifying the JDBC connection to a MySQL database TESTDB.
  23.     Connection con = null;              // Database connection object
  24.     Statement stmt = null;              // Statement String
  25.     String query;                       // Query String
  26.  
  27. //-----------------------------------------------------------------------------
  28.     public void doGet(HttpServletRequest req, HttpServletResponse res)
  29.            throws ServletException, IOException {
  30.  
  31.         // Set the output characterics for the return data
  32.         res.setContentType("text/html");
  33.         ServletOutputStream out = res.getOutputStream();
  34.  
  35.         // Establish the database connection
  36.         try {
  37.             // Connect to TESTDB
  38.             Class.forName("org.gjt.mm.mysql.Driver");
  39.             con = DriverManager.getConnection (url,"[DB Username]","[DB Password]");
  40.                stmt = con.createStatement();
  41.  
  42.             // Build the query statement and retrieve the database records
  43.             query = "SELECT * FROM ProductSales WHERE Year='2003'";
  44.             ResultSet srs = stmt.executeQuery(query);
  45.  
  46.             // Process the database records and return the Data
  47.             out.println(GraphData(srs));
  48.  
  49.         } // End try
  50.  
  51.  
  52.         // Error handling
  53.         catch(ClassNotFoundException e) {out.println("Could not load database driver: " + e.getMessage());}
  54.         catch(SQLException e) {out.println("SQLException caught: " + e.getMessage());}
  55.  
  56.         // All finished so close the database connection
  57.         finally {
  58.                  try {if (con != null) con.close();}
  59.                  catch (SQLException e) {}
  60.         }
  61.  
  62.  
  63.  
  64.  
  65.     } // End doGet
  66. //-----------------------------------------------------------------------------
  67.     public void doPost(HttpServletRequest request,HttpServletResponse response)
  68.                 throws ServletException, IOException {doGet(request, response);}
  69. //-----------------------------------------------------------------------------
  70.  
  71.     public static String GraphData(ResultSet srs) {
  72.  
  73.         String rsltStr = "\n";
  74.         String productname = null;
  75.         String jansales;
  76.         String febsales;
  77.         String marsales;
  78.         String aprsales;
  79.         String maysales;
  80.         String junsales;
  81.         String julsales;
  82.         String augsales;
  83.         String sepsales;
  84.         String octsales;
  85.         String novsales;
  86.         String decsales;
  87.  
  88.         // Read through the records and construct the return string
  89.         //   ProductX will be set series1
  90.         //   ProductY will be set to series2
  91.         //   ProductZ will be set to series3
  92.  
  93.         try {
  94.  
  95.            while (srs.next()) {
  96.  
  97.             jansales = "0.0";
  98.             febsales = "0.0";
  99.             marsales = "0.0";
  100.             aprsales = "0.0";
  101.             maysales = "0.0";
  102.             junsales = "0.0";
  103.             julsales = "0.0";
  104.             augsales = "0.0";
  105.             sepsales = "0.0";
  106.             octsales = "0.0";
  107.             novsales = "0.0";
  108.             decsales = "0.0";
  109.  
  110.             productname = srs.getString("ProductName");
  111.  
  112.             jansales = srs.getString("m1sales");
  113.             febsales = srs.getString("m2sales");
  114.             marsales = srs.getString("m3sales");
  115.             aprsales = srs.getString("m4sales");
  116.             maysales = srs.getString("m5sales");
  117.             junsales = srs.getString("m6sales");
  118.             julsales = srs.getString("m7sales");
  119.             augsales = srs.getString("m8sales");
  120.             sepsales = srs.getString("m9sales");
  121.             octsales = srs.getString("m10sales");
  122.             novsales = srs.getString("m11sales");
  123.             decsales = srs.getString("m12sales");
  124.  
  125.             if (productname.equals("ProductX")) {
  126.                 rsltStr = rsltStr +
  127.                           "data1series1:        "+ jansales +"\n"+
  128.                           "data2series1:        "+ febsales +"\n"+
  129.                           "data3series1:        "+ marsales +"\n"+
  130.                           "data4series1:        "+ aprsales +"\n"+
  131.                           "data5series1:        "+ maysales +"\n"+
  132.                           "data6series1:        "+ junsales +"\n";
  133.             }
  134.             if (productname.equals("ProductY")) {
  135.                 rsltStr = rsltStr +
  136.                           "data1series2:        "+ jansales +"\n"+
  137.                           "data2series2:        "+ febsales +"\n"+
  138.                           "data3series2:        "+ marsales +"\n"+
  139.                           "data4series2:        "+ aprsales +"\n"+
  140.                           "data5series2:        "+ maysales +"\n"+
  141.                           "data6series2:        "+ junsales +"\n";
  142.             }
  143.             if (productname.equals("ProductZ")) {
  144.                 rsltStr = rsltStr +
  145.                           "data1series3:        "+ jansales +"\n"+
  146.                           "data2series3:        "+ febsales +"\n"+
  147.                           "data3series3:        "+ marsales +"\n"+
  148.                           "data4series3:        "+ aprsales +"\n"+
  149.                           "data5series3:        "+ maysales +"\n"+
  150.                           "data6series3:        "+ junsales +"\n";
  151.             }
  152.  
  153.  
  154.           } // end while
  155.  
  156.  
  157.         } // End try
  158.  
  159.  
  160.         // Error handling
  161.         catch(SQLException e) {rsltStr = rsltStr + "\nSQLException caught: " + e.getMessage();}
  162.  
  163.  
  164.          return(rsltStr);
  165.     }
  166.  
  167.  
  168. //-----------------------------------------------------------------------------------
  169.  
  170. } // End class