import java.sql.*; import java.lang.*; import java.io.*; import javax.servlet.*; import javax.servlet.http.*; public class DatabaseServletBar 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 graphing 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/bar_graph/shbarchart/ // //----------------------------------------------------------------------------- // 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