<%@ page language="java" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
Dynamic Query Runner
<%
final String fName = "dynQuery.jsp"; //This string gets used in all URLs.
//This way if you need to change the
//name of this JSP file, change this
//value as well.
/** JSP files does not now allow embedded methods. However, since Java allows
* inner classes within methods, we can overcome this limitation.
* A Worker object does not have any state. It is simply used
* to perform certain repetitive tasks.
*
*/
class Worker{
//=================================================================================
/**
* Displays the connection form with default parameters.
*/
void displayConnectionForm(JspWriter out) throws IOException{
displayConnectionForm(out, "", "", "", "");
}
//======================================================================================
/**
* Displays the connection form with specified parameters.
*/
void displayConnectionForm(JspWriter out, String driverClass, String conUrl, String uid, String pwd)throws IOException{
if(driverClass == null || driverClass.trim().length() == 0)
driverClass = "sun.jdbc.odbc.JdbcOdbcDriver";
if(conUrl == null || conUrl.trim().length() == 0)
conUrl = "jdbc:odbc:";
%>
<%
}
//=================================================================================
/**
* Displays a form where the user can type in a SQL query
*/
void displayQueryForm(JspWriter out, String sqlString)throws IOException{
if(sqlString == null)
sqlString = "";
%>
Type the SQL Script and hit the submit button
<%
}
//=================================================================================
/**
* Puts the contents of a ResultSet object into an HTML table
*/
void resultSetToTable(JspWriter out, ResultSet rs) throws SQLException, IOException{
out.println("");
//Now display the contents of the result set in a table
ResultSetMetaData metaData = rs.getMetaData();
out.println("");
//Print the result fields in the table header
for(int i = 0; i < metaData.getColumnCount(); i++){
out.println("| " + metaData.getColumnName(i + 1) + " | ");
}
out.println("
");
boolean isEven = true;
int totalRecords = 0;
while(rs.next()){
totalRecords++;
isEven = !isEven; //toggles the color of the output
//If there is no style sheet, the browser will just ignore
//the class tag and therefore, won't generate any errors.
if(isEven)
out.println("");
else
out.println("
");
for(int i = 0; i < metaData.getColumnCount(); i++){
String value = rs.getString(i + 1);
if(value == null)
value = "NULL";
out.println("| " + value + " | ");
}
out.println("
");
}
out.println("
");
out.println("Total records: " + totalRecords + "
");
}
//=================================================================================
/**
* Displays an error message
*/
void displayError(JspWriter out, String errorMsg) throws IOException{
out.println("Error:
" + errorMsg + "
");
}
//=================================================================================
/**
* Displays the top menu bar
*/
void displayMenu(JspWriter out) throws IOException{
%>
Log out |
New Query |
Display Catalog
<%
}
}
class ConnectionHolder implements HttpSessionBindingListener{
private Connection dbConn = null;
ConnectionHolder(Connection dbConn){
this.dbConn = dbConn;
}
public void valueBound(HttpSessionBindingEvent event){
}
public void valueUnbound(HttpSessionBindingEvent event){
try{
if(dbConn != null){
dbConn.close(); //Close connection when session becomes
//invalid
}
}catch(SQLException se){
se.printStackTrace();
}
}
Connection getDbConn(){
return dbConn;
}
}
/*
List of operations being used so far
NULL - Display the login screen
1. - Establish database connection and show the query form
2. - Run Query
3. - Log out
4. - New Query
5. - Display Catalog menu
6. - Show list of tables
7. - Show list of views
8. - Show list of procedures.
9. - Show details for a table or view
*/
//The very first thing I check here is that if this method is secure.
if(!request.isSecure()){
out.println("WARNING:You are not using a secure method
");
}
//First find out if the Connection object exists in the session. If not, ask for it.
ConnectionHolder dbConnHolder = (ConnectionHolder)session.getAttribute("dbConnHolder");
//First, get the operation
String operation = request.getParameter("operation");
Worker worker = new Worker(); //Holds the methods to do individual operations
if(operation == null)
operation = "4"; //Will display a new query window
//this is the default
if(dbConnHolder == null && !operation.equals("1")){
//worker.displayMenu(out);
//worker.displayError(out, "No operation specified. We suggest that you log out and then log back in");
out.println("Dynamic query runner
");
worker.displayConnectionForm(out);
return;
}
if(operation.equals("1")){ //Display the query screen
//First try to establish connection.
//If connection is established, set the connection object in the session and display
//the query form.
String driverClass = request.getParameter("txtDriverClass");
String connUrl = request.getParameter("txtConnUrl");
String uid = request.getParameter("txtUID");
String pwd = request.getParameter("txtPWD");
try{
Class.forName(driverClass);
dbConnHolder = new ConnectionHolder(DriverManager.getConnection(connUrl,uid, pwd));
session.setAttribute("dbConnHolder", dbConnHolder);
}catch(SQLException se){
worker.displayError(out, "SQL Exception - " + se.getMessage());
worker.displayConnectionForm(out, driverClass, connUrl, uid, "");
return;
}catch(Exception s){
worker.displayError(out, "Exception - " + s.getMessage());
worker.displayConnectionForm(out, driverClass, connUrl, uid, "");
s.printStackTrace();
return ;
}
worker.displayMenu(out);
//Now display the query form.
worker.displayQueryForm(out, (String)session.getAttribute("queryString"));
}else if(operation.equals("2")){
//Now process the SQL query.
worker.displayMenu(out);
String sqlString = request.getParameter("txtQuery");
if(sqlString == null || sqlString.trim().length() == 0){
worker.displayError(out, "Please provide a SQL script");
worker.displayQueryForm(out, (String)session.getAttribute("queryString"));
return;
}
//Set the query string in the session
session.setAttribute("queryString", sqlString);
Statement stmt = null;
try{
stmt = dbConnHolder.getDbConn().createStatement();
ResultSet rs = null;
int resultNumber = 1;
if(stmt.execute(sqlString)){
while(true){
rs = stmt.getResultSet();
//out.println("
");
out.println("
");
out.println("Display result number: " + resultNumber + "");
resultNumber++;
worker.resultSetToTable(out, rs);
out.println("
");
if(!stmt.getMoreResults()){
int updateCount = stmt.getUpdateCount();
if(updateCount >= 0)
out.println("Success -
The query ran successfully and updated " + updateCount + " record(s)");
break;
}
}
if(rs != null)
rs.close();
}else{
int updateCount = stmt.getUpdateCount();
out.println("Success -
The query ran successfully and updated " + updateCount + " record(s)");
worker.displayQueryForm(out, (String)session.getAttribute("queryString"));
}
}catch(SQLException se){
worker.displayError(out, "SQL Exception - " + se.getMessage());
worker.displayQueryForm(out, (String)session.getAttribute("queryString"));
}finally{
try{
if(stmt != null) stmt.close();
}catch(SQLException sse){
worker.displayError(out, "Internal error - " + sse.getMessage());
}
}
}else if(operation.equals("3")){
//Invalide the session here.
//try{
//dbConn.close();
//}catch(SQLException se){
//No need for any processing here
//}
session.invalidate();
out.println("You have been successfully logged out
");
worker.displayConnectionForm(out);
}else if(operation.equals("4")){
worker.displayMenu(out);
//Display the query form
worker.displayQueryForm(out, (String)session.getAttribute("queryString"));
}else if(operation.equals("5")){
worker.displayMenu(out);
%>
Database catalog
Detailed Catalog
Type a name of a table or view to see its fields. A % sign signifies a wild card - if you put a % sign in both the fields below, you will see the columns for ALL the tables in the database.
<%
}else if(operation.equals("6")){
worker.displayMenu(out);
out.println("Displaying a list of all tables");
String types[] = {"TABLE"};
ResultSet rs = dbConnHolder.getDbConn().getMetaData().getTables(null, "%", "%", types);
worker.resultSetToTable(out, rs);
}else if(operation.equals("7")){
worker.displayMenu(out);
out.println("Displaying a list of all tables");
String types[] = {"VIEW"};
ResultSet rs = dbConnHolder.getDbConn().getMetaData().getTables(null, "%", "%", types);
worker.resultSetToTable(out, rs);
}else if(operation.equals("8")){
worker.displayMenu(out);
out.println("Displaying a list of all tables");
ResultSet rs = dbConnHolder.getDbConn().getMetaData().getProcedures(null, "%", "%");
worker.resultSetToTable(out, rs);
}else if(operation.equals("9")){
worker.displayMenu(out);
String objectName = request.getParameter("txtObjectName");
String schema = request.getParameter("txtSchema");
if(objectName == null){
out.println("No object specified");
return;
}
out.println("Displaying details for " + objectName + "");
ResultSet rs = dbConnHolder.getDbConn().getMetaData().getColumns(null, schema, objectName, "%");
worker.resultSetToTable(out, rs);
}
%>