Coffee Break JSP Example

<%@ page errorPage="error.jsp" import="java.sql.*" %> <%! // --------------- inits for the servlet -------------- // The database connection Connection con; // The statement Statement stmt; // The queryString and updateString String queryString = null; String updateString = null; String goString = null; // some HTML String newline = "
"; // ---- configure this for your site String username = "nobody"; String password = null; // The URL that will connect to TECFA's MySQL server // Syntax: jdbc:TYPE:machine:port/DB_NAME // String url = "jdbc:mysql://localhost:3306/COFFEEBREAK"; String url = "jdbc:mysql://tecfa2.unige.ch:3306/COFFEEBREAK"; %> <% // --------------- code for the service method -------------- // Let's see if we got a request queryString = request.getParameter ("QUERYSTRING"); updateString = request.getParameter ("UPDATESTRING"); goString = request.getParameter("GO"); if (goString != null) { try { Class.forName("org.gjt.mm.mysql.Driver"); // Establish Connection to the database at URL with usename and password con = DriverManager.getConnection(url, username, password); out.println ("Ok, connection to the DB is working."); } catch (Exception e) // (ClassNotFoundException and SQLException) { throw(new UnavailableException(this, "Sorry! The Database didn't load!")); } if ((queryString != "") && (queryString != null)) { try { out.println ("

You asked:

"); out.println ( "Query: " + queryString + "
" ); out.println("

Query Result

"); out.println(""); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(queryString); stmt.close(); ResultSetMetaData rsMeta = rs.getMetaData(); // Get the N of Cols in the ResultSet int noCols = rsMeta.getColumnCount(); out.println(""); for (int c=1; c<=noCols; c++) { String el = rsMeta.getColumnLabel(c); out.println(""); } out.println(""); while (rs.next()) { out.println(""); for (int c=1; c<=noCols; c++) { String el = rs.getString(c); out.println(""); } out.println(""); } out.println("
" + el + "
" + el + "
"); } catch (SQLException ex ) { out.println ( "

" );
	    while (ex != null) {
		out.println("Message:   " + ex.getMessage ());
		out.println("SQLState:  " + ex.getSQLState ());
		out.println("ErrorCode: " + ex.getErrorCode ());
		ex = ex.getNextException();
		out.println("");
	    }
	    out.println ( "

" ); } } else if ((updateString != "") && (updateString != null)) { try { stmt = con.createStatement(); int updateRowCount = stmt.executeUpdate(updateString); stmt.close(); if (updateRowCount > 0) out.println("

Update successful:" + updateRowCount + " Rows updated.

"); else out.println ("

Update feedback: " + updateRowCount + " Rows updated." + newline + "Either NO update was needed, or you misspelled a value" + newline + "You sent: \"" + updateString + "\"" ); } catch(SQLException ex) { out.println("SQLException: " + ex); } } con.close(); } %>


You can now try to retrieve something.
Query:

e.g.:
SELECT * FROM COFFEES
SELECT * FROM COFFEES WHERE PRICE > 9
SELECT PRICE, COF_NAME FROM COFFEES

Update

e.g.:
 UPDATE COFFEES SET SALES = 75 WHERE COF_NAME LIKE 'Colombian'
 INSERT INTO COFFEES VALUES ('Home_Grown', 101, 17.99, 10, 0)

Encore une fois ? | Source: CoffeeBreakLine2.jsp.text
Last modified: Wed Feb 16 18:33:56 MET 2000
D.K.S.