home *** CD-ROM | disk | FTP | other *** search
Java Source | 2004-07-05 | 5.5 KB | 170 lines |
- import java.sql.*;
- import java.lang.*;
- import java.io.*;
- import javax.servlet.*;
- import javax.servlet.http.*;
-
- public class DatabaseServletPie extends HttpServlet {
- //
- // This simple servlet is designed to demonstrate how a servlet can be used
- // to retrieve data from a database and return the data in the correct format
- // to either the pie chart applet or servlet.
- //
- // As you will see the main routine ( doGet() ) uses the method
- // GraphData() to construct the return data.
- //
- // For further information visit,
- // http://www.jpowered.com/pie_chart/index.htm
- //
- //-----------------------------------------------------------------------------
-
- // Initialise and set variables
- String url="jdbc:MySQL:///TESTDB"; // URL specifying the JDBC connection to a MySQL database TESTDB.
- Connection con = null; // Database connection object
- Statement stmt = null; // Statement String
- String query; // Query String
-
- //-----------------------------------------------------------------------------
- public void doGet(HttpServletRequest req, HttpServletResponse res)
- throws ServletException, IOException {
-
- // Set the output characterics for the return data
- res.setContentType("text/html");
- ServletOutputStream out = res.getOutputStream();
-
- // Establish the database connection
- try {
- // Connect to TESTDB
- Class.forName("org.gjt.mm.mysql.Driver");
- con = DriverManager.getConnection (url,"[DB Username]","[DB Password]");
- stmt = con.createStatement();
-
- // Build the query statement and retrieve the database records
- query = "SELECT * FROM ProductSales WHERE Year='2003'";
- ResultSet srs = stmt.executeQuery(query);
-
- // Process the database records and return the Data
- out.println(GraphData(srs));
-
- } // End try
-
-
- // Error handling
- catch(ClassNotFoundException e) {out.println("Could not load database driver: " + e.getMessage());}
- catch(SQLException e) {out.println("SQLException caught: " + e.getMessage());}
-
- // All finished so close the database connection
- finally {
- try {if (con != null) con.close();}
- catch (SQLException e) {}
- }
-
-
-
-
- } // End doGet
- //-----------------------------------------------------------------------------
- public void doPost(HttpServletRequest request,HttpServletResponse response)
- throws ServletException, IOException {doGet(request, response);}
- //-----------------------------------------------------------------------------
-
- public static String GraphData(ResultSet srs) {
-
- String rsltStr = "\n";
- String productname = null;
- String jansales;
- String febsales;
- String marsales;
- String aprsales;
- String maysales;
- String junsales;
- String julsales;
- String augsales;
- String sepsales;
- String octsales;
- String novsales;
- String decsales;
-
- // Read through the records and construct the return string
- // ProductX will be set series1
- // ProductY will be set to series2
- // ProductZ will be set to series3
-
- try {
-
- while (srs.next()) {
-
- jansales = "0.0";
- febsales = "0.0";
- marsales = "0.0";
- aprsales = "0.0";
- maysales = "0.0";
- junsales = "0.0";
- julsales = "0.0";
- augsales = "0.0";
- sepsales = "0.0";
- octsales = "0.0";
- novsales = "0.0";
- decsales = "0.0";
-
- productname = srs.getString("ProductName");
-
- jansales = srs.getString("m1sales");
- febsales = srs.getString("m2sales");
- marsales = srs.getString("m3sales");
- aprsales = srs.getString("m4sales");
- maysales = srs.getString("m5sales");
- junsales = srs.getString("m6sales");
- julsales = srs.getString("m7sales");
- augsales = srs.getString("m8sales");
- sepsales = srs.getString("m9sales");
- octsales = srs.getString("m10sales");
- novsales = srs.getString("m11sales");
- decsales = srs.getString("m12sales");
-
- if (productname.equals("ProductX")) {
- rsltStr = rsltStr +
- "data1series1: "+ jansales +"\n"+
- "data2series1: "+ febsales +"\n"+
- "data3series1: "+ marsales +"\n"+
- "data4series1: "+ aprsales +"\n"+
- "data5series1: "+ maysales +"\n"+
- "data6series1: "+ junsales +"\n";
- }
- if (productname.equals("ProductY")) {
- rsltStr = rsltStr +
- "data1series2: "+ jansales +"\n"+
- "data2series2: "+ febsales +"\n"+
- "data3series2: "+ marsales +"\n"+
- "data4series2: "+ aprsales +"\n"+
- "data5series2: "+ maysales +"\n"+
- "data6series2: "+ junsales +"\n";
- }
- if (productname.equals("ProductZ")) {
- rsltStr = rsltStr +
- "data1series3: "+ jansales +"\n"+
- "data2series3: "+ febsales +"\n"+
- "data3series3: "+ marsales +"\n"+
- "data4series3: "+ aprsales +"\n"+
- "data5series3: "+ maysales +"\n"+
- "data6series3: "+ junsales +"\n";
- }
-
-
- } // end while
-
-
- } // End try
-
-
- // Error handling
- catch(SQLException e) {rsltStr = rsltStr + "\nSQLException caught: " + e.getMessage();}
-
-
- return(rsltStr);
- }
-
-
- //-----------------------------------------------------------------------------------
-
- } // End class