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(" " + el + " | ");
}
out.println("
");
while (rs.next()) {
out.println("");
for (int c=1; c<=noCols; c++) {
String el = rs.getString(c);
out.println(" " + el + " | ");
}
out.println("
");
}
out.println("
");
}
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.
e.g.:
SELECT * FROM COFFEES
SELECT * FROM COFFEES WHERE PRICE > 9
SELECT PRICE, COF_NAME FROM COFFEES
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.