<%@ page import="java.sql.Connection, java.sql.Statement, java.sql.ResultSet, java.sql.ResultSetMetaData, java.sql.DriverManager, java.sql.SQLWarning, java.sql.SQLException" %> <% // first we handle form stuff // did the user submit a query? String queryString = request.getParameter ( "query" ); // did the user ask to clear the form String clearString = request.getParameter ( "clear" ); // if there is no query or clear is needed set the query to "" if ( queryString == null || clearString != null ) { queryString =""; } // end if query null or clear // you should load this info from a "safe" configuration file. // you could use XML for this if you wanted to be fancy about it // the URL is in part determined by the JDBC driver you intend to use String connectionURL = "jdbc:mysql://mysql.wpi.edu/web_ware_2004_summer"; // the JDBC driver to load String jdbcDriver = "com.mysql.jdbc.Driver"; String username = "kap"; String password = "iZMN4o"; // handles connection to database Connection connection = null; // issue SQL statements to the database and retrieve results Statement statement = null; // provides access to results ResultSet resultSet = null; // iterate through rows int r; // iterate through columns int c; // Now we must load the driver and make the connection // here we load the mysql JDBC driver // this class name is in the documentation packaged with the driver // this line of code loads the driver Class.forName ( jdbcDriver ); // make the connection to the database connection = DriverManager.getConnection ( connectionURL, username, password ); // create a statement object so we can execute SQL queries statement = connection.createStatement(); // if a query was entered try executing it if ( ! queryString.equals ( "" ) ) { // this could throw a SQL exception. we want to show the user any errors // so do it in a try try { // check if there are results to display if ( statement.execute ( queryString ) ) { // OK, there are results to show resultSet = statement.getResultSet(); // get the meta data for the result set ResultSetMetaData metaData = resultSet.getMetaData(); // make table for result and start header row %> <% for ( c = 1; c < metaData.getColumnCount() + 1; c++ ) { %> <% } // end for each column // end header row %> <% // now show the values for each row while ( resultSet.next() ) { %> <% // show value for each column in this row for ( c = 1; c < metaData.getColumnCount() + 1; c++ ) { %> <% } // end for each column %> <% } // end while more rows // end table %>
Query Result
<%= metaData.getColumnName ( c ) %>
<%= resultSet.getString ( c ) %>
<% } // end if there are results to display else { // check if there's an update count int count = statement.getUpdateCount(); if ( count > -1 ) { %>

Number of updates: <%= count %>

<% } // end if update count } // end else maybe there's an update count // display any warnings that might be present SQLWarning sqlWarning = statement.getWarnings(); while ( sqlWarning != null ) { %>

Warning Message:

<%= sqlWarning.getMessage() %>

X/Open SQL State: <%= sqlWarning.getSQLState() %>
Vendor Error Code: <%= sqlWarning.getErrorCode() %>

<% sqlWarning = sqlWarning.getNextWarning(); } // end while more warnings } // end try executing query catch ( SQLException ex ) { // exceptions could be chained together // show messages from all of them while ( ex != null ) { %>

Error Message:

<%= ex.getMessage() %>

X/Open SQL State: <%= ex.getSQLState() %>
Vendor Error Code: <%= ex.getErrorCode() %>

<% // exceptions could be chained together ex = ex.getNextException(); } // end while more exceptions } // end catch SQL exception } // end if there's a SQL query to execute // this is the query used for showing the whole students table String query = "select * from students order by ID asc"; // execute the query and get the result resultSet = statement.executeQuery ( query ); // start a table and make a header row %> <% // remember ID for deleting row buttons String rowID; // show values and delete button for each row while ( resultSet.next() ) { rowID = resultSet.getString ( 1 ); %> <% // I know there are 4 columns in the table students // show the value for each column for ( r = 2; r < 5; r++ ) { %> <% } // end for each column // time for the delete row button %> <% } // end while result has more stuff // remember to put away your toys when you're done resultSet.close(); statement.close(); connection.close(); %>
Table name: students
ID last_name first_name grade
<%= rowID %><%= resultSet.getString ( r ) %>

Please enter query



List of handy dandy SQL commands

Notes: