Simple Database Test

/** Save this into a file called 'DatabaseCheck.java' **/ /** * DatabaseCheck.java * written by Marie Woods. (c) University of Liverpool, 2001 * * This program may be redistributed or modified but must always contain * this notice. It may not be sold individually or as part of any other * package. * * For information about LUSID (Liverpool University Student * Interactive Database) please see http://lusid.liv.ac.uk/ * */ import javax.servlet.*; import javax.servlet.http.*; import java.io.*; import java.sql.*; final public class DatabaseCheck extends HttpServlet { // **************************************************************** // The following FOUR constants need to be set for your environment // Simply fill in your details, compile the servlet: // // $ javac DatabaseCheck.java // // install in your servlet zone and invoke the servlet. If all works // then you are ready to install LUSID. // **************************************************************** final private static String DB_URL = "jdbc:oracle:thin:IPaddress:1521:lusl"; final private static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; final private static String DB_PASSWORD = "password"; final private static String DB_USERNAME = "username"; private Statement stmt; private Connection dbCon; private HttpServletRequest req; private HttpServletResponse res; private PrintWriter out; final public void doGet(HttpServletRequest req, HttpServletResponse res) throws IOException { try { res.setContentType("Text/html"); out = res.getWriter(); out.println(""); out.println("Simple Database Test"); out.println(""); out.println(""); out.println("

Simple Database Test!

"); out.println("

"); // Load DB Driver class out.println("

Loading DB Driver............."); Class.forName(DB_DRIVER); out.println("OK

"); out.println("

Opening Connection............."); dbCon = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD); out.println("OK

"); createTable(); doInsert(); printResultsTable(); dropTable(); getSomeDBMetaData(); } catch(SQLException sqle) { out.println("

Get Method - SQL Exception error:

" + sqle); } catch(IOException ioe) { out.println("

Get method - Input/Output Error:

" + ioe); } catch(ClassNotFoundException cnfe) { out.println("

Get method - Class not found error:

" + cnfe); } // tidy up finally { try { dbCon.close(); } catch (SQLException sqle) {} } } final private void createTable() throws SQLException, IOException { stmt = dbCon.createStatement(); try { out.println("

Creating Table............."); String createCommand = "CREATE TABLE TestTable(id integer, name varchar(64))"; stmt.execute(createCommand); out.println("OK

"); } catch (SQLException sqle) { out.println("

createTable - Error creating table:

" + sqle + sqle.getMessage()); } catch (Exception e) { out.println("

createTable - Error creating table:

" + e + e.getMessage()); } finally { try { stmt.close(); } catch (SQLException sqle) {} } } final public void doInsert() throws SQLException, ClassNotFoundException { try { out.println("

Inserting Data into Table............."); stmt = dbCon.createStatement(); String sql = "INSERT INTO TestTable values ('1"; sql = sql + "', 'Marie Woods')"; int insertResult = stmt.executeUpdate(sql); out.println("OK

"); } catch(SQLException sqle) { out.println("

doInsert - Error inserting into table:

" + sqle + sqle.getMessage()); } finally { try { stmt.close(); } catch (SQLException sqle) {} } } final private void printResultsTable() throws SQLException { try { stmt = dbCon.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM lusid.TestTable"); //Print start of table and column headers out.println(""); out.println(""); //Loop through results of query. while(rs.next()) { out.println(""); out.println(""); out.println(""); out.println(""); out.println("

"); } out.println("

ID	NAME
" + rs.getString("ID") + "	" + rs.getString("Name") + "

"); } catch(SQLException sqle) { out.println("

printResultsTable - Error selecting values from the database:

" + sqle +sqle.getMessage()); } finally { try { stmt.close(); } catch (SQLException sqle) {} } } final private void dropTable() throws SQLException { try { out.println("

Dropping Table............."); stmt = dbCon.createStatement(); String sql = "DROP TABLE testTable"; int insertResult = stmt.executeUpdate(sql); out.println("OK

"); } catch(SQLException sqle) { out.println("

dropTable - Error dropping table:

" + sqle + sqle.getMessage()); } finally { try { stmt.close(); } catch (SQLException sqle) {} } } final private void getSomeDBMetaData() throws SQLException { try { DatabaseMetaData dbMetaData = dbCon.getMetaData(); out.println("

Database Product Information......

"); out.println("Product Name: " + dbMetaData.getDatabaseProductName()); out.println("
Version : " + dbMetaData.getDatabaseProductVersion()); } catch (SQLException sqle) { out.println("

getSomeDBMetaData - Error getting metadata information from database!

" + sqle + sqle.getMessage()); } } }