<%--A Java ServerPage to create, insert, delete, update, query and drop
a table by Tom Brown for database classes --%>
<%@ page import="java.sql.*" %>
<%
Connection conn = null;
Statement stmt = null;
String user = "guest";
String passwd = "house";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection ("jdbc:odbc:orcl",user,passwd);
stmt = conn.createStatement();
String createString = "CREATE TABLE GUEST.STAFF " +
"(ID DECIMAL(3), " +
"NAME CHAR(10), " +
"OFFICE CHAR(7), " +
"FEES DECIMAL (6,2))";
stmt.executeUpdate(createString);
stmt.executeUpdate
("INSERT INTO GUEST.STAFF VALUES(33,'Huerter','BA 311',1234.56)");
stmt.executeUpdate
("INSERT INTO GUEST.STAFF VALUES(407,'McWhorter','BA 310',2345.67)");
stmt.executeUpdate
("INSERT INTO GUEST.STAFF VALUES(705,'McBride','BA 301',3753.19)");
stmt.executeUpdate ("DELETE GUEST.STAFF WHERE ID = 705");
stmt.executeUpdate ("UPDATE GUEST.STAFF SET FEES = 3579.13 WHERE ID = 33");
ResultSet rs = stmt.executeQuery
("SELECT * FROM GUEST.STAFF ORDER BY NAME");
//Print start of table and column headers
out.println("Id Name Office Fees" + "
");
//Loop through results of query.
while(rs.next()) {
out.println( rs.getString("ID") );
out.println( rs.getString("NAME") );
out.println( rs.getString("OFFICE") );
out.println( rs.getBigDecimal("FEES") );
out.print("
");
} // end while
stmt.executeUpdate("DROP TABLE GUEST.STAFF");
stmt.close();
conn.close();
} // end try
catch(SQLException e) {
out.println("SQLException: " + e.getMessage() + "
");
}
catch(ClassNotFoundException e) {
out.println("ClassNotFoundException: " + e.getMessage() + "
");
}
%>