This is an SQL database browser done as a Servlet. Have fun.
(*Chris*)
/**
* WebSQL
*
* @author Chris Pratt
* @version 1.0
*
* Properties [Optional]
* dbdriver = The Database Driver Class
* dburl = The Database Instance URL
*
* 3/8/2000
*/
import java.io.BufferedReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.StringTokenizer;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpSessionBindingEvent;
import javax.servlet.http.HttpSessionBindingListener;
public class WebSQL extends HttpServlet {
private static final int version = 1;
private static final int release = 2;
private static final int build = 8;
private static String defDriver = "oracle.jdbc.driver.OracleDriver";
private static String defURL =
"jdbc:oracle:thin:@dbserver.inclusion.net:1521:ORCL";
private static final boolean DEBUG = false;
/**
* Write a message out if we're debugging
*
* @param The message to be written
*/
public void log (String msg) {
if(DEBUG) {
System.err.println(msg);
}
} //log
/**
* Con - Bound to a Session to automatically close the database connection
*/
private class Con implements HttpSessionBindingListener {
private Connection con;
/**
* Constructor
*
* @param con The Database Connection
*/
public Con (Connection con) {
this.con = con;
log("WebSQL - Using Connection - " + con);
} //Con
/**
* A value has been bound to the session
*
* @param event The binding event
*/
public void valueBound (HttpSessionBindingEvent event) {
} //valueBound
/**
* Close the Database Connection
*
* @param event The un-binding event
*/
public void valueUnbound (HttpSessionBindingEvent event) {
try {
log("WebSQL - Unbinding Connection " + con);
con.close();
con = null;
} catch(SQLException x) {
x.printStackTrace();
}
} //valueUnbound
/**
* Get the enclosed Connection
*
* @return The Connection
*/
public Connection getConnection () {
return con;
} //getConnection
} //*Con
public void init (ServletConfig cfg) throws ServletException {
super.init(cfg);
String temp = cfg.getInitParameter("dbdriver");
if(temp != null) {
defDriver = temp;
log("WebSQL.init: defDriver=" + defDriver);
}
if((temp = cfg.getInitParameter("dburl")) != null) {
defURL = temp;
log("WebSQL.init: defURL=" + defURL);
}
} //init
/**
* Replace the HTML characters in the string with their HTML equivalents
*
* @param str the String to strip
* @return the stripped String
*/
private static String stripHTML (String str) {
if(str != null) {
String s;
boolean eol = false;
StringBuffer buf = new StringBuffer();
StringTokenizer tok = new StringTokenizer(str,"<&>\n\r",true);
while(tok.hasMoreTokens()) {
s = tok.nextToken();
switch(s.charAt(0)) {
case '<':
buf.append("<");
eol = false;
break;
case '&':
buf.append("&");
eol = false;
break;
case '>':
buf.append(">");
eol = false;
break;
case '\n':
case '\r':
if(!eol) {
buf.append("<br>");
}
eol = true;
break;
default:
buf.append(s);
eol = false;
break;
}
}
return buf.toString();
}
return null;
} //stripHTML
/**
* Prepare a Stack Trace for HTML display
*
* @param t The Exception
* @return The HTML
*/
private static String prepareStackTrace (Throwable t) {
String line;
StringWriter str = new StringWriter();
PrintWriter out = new PrintWriter(str);
t.printStackTrace(out);
BufferedReader in = new BufferedReader(new
StringReader(str.toString()));
str = new StringWriter();
out = new PrintWriter(str);
try {
out.println("<tt><pre>");
while((line = in.readLine()) != null) {
out.println(stripHTML(line));
}
out.println("</pre></tt>");
} catch(IOException e) {
}
return str.toString();
} //prepareStackTrace
/**
* Print the Result Set to the Writer
*
* @param out The PrintWriter
* @param rs The Result Set
*/
public void printResultSet (PrintWriter out,ResultSet rs) throws
SQLException {
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
out.println(" <table border=\"1\" bgcolor=\"#c0c0c0\">");
out.println(" <tr bgcolor=\"pink\">");
for(int i = 0;i < cols;i++) {
out.println(" <th>" + stripHTML(meta.getColumnLabel(i +
1)) + "</th>");
}
out.println(" </tr>");
while(rs.next()) {
out.println(" <tr>");
for(int i = 0;i < cols;i++) {
out.println(" <td valign=\"baseline\"><tt>" +
stripHTML(rs.getString(i + 1)) + "</tt></td>");
}
out.println(" </tr>");
}
out.println(" </table>");
} //printResultSet
/**
* Print the Result Set to the Writer
*
* @param out The PrintWriter
* @param rs The Result Set
* @param cols The Column Names to be Printed
*/
public void printPartialResultSet (PrintWriter out,ResultSet rs,String[]
cols) throws SQLException {
out.println(" <table border=\"1\" bgcolor=\"#c0c0c0\">");
out.println(" <tr bgcolor=\"pink\">");
for(int i = 0;i < cols.length;i++) {
out.println(" <th>" + stripHTML(cols[i]) + "</th>");
}
out.println(" </tr>");
while(rs.next()) {
out.println(" <tr>");
for(int i = 0;i < cols.length;i++) {
out.println(" <td valign=\"baseline\"><tt>" +
stripHTML(rs.getString(cols[i])) + "</tt></td>");
}
out.println(" </tr>");
}
out.println(" </table>");
} //printPartialResultSet
/**
* Process the GET Request
*
* @param req The Servlet Request
* @param res The Servlet Response
* @exception java.io.IOException
* @exception javax.servlet.ServletException
*/
public void doGet (HttpServletRequest req,HttpServletResponse res) throws
ServletException, IOException {
try {
Connection con = null;
String drv;
String url;
String usr;
String pwd;
String sql;
HttpSession session = req.getSession(true);
if(session.getValue("Initialized") == null) {
if((drv = req.getParameter("driver")) == null) {
drv = defDriver;
}
if((url = req.getParameter("url")) == null) {
url = defURL;
}
usr = req.getParameter("user");
pwd = req.getParameter("password");
sql = req.getParameter("sql");
} else {
con = ((Con)session.getValue("connection")).getConnection();
drv = (String)session.getValue("driver");
url = (String)session.getValue("url");
usr = (String)session.getValue("user");
pwd = (String)session.getValue("password");
sql = (String)session.getValue("sql");
}
if(DEBUG) {
System.err.println("WebSQL.doGet: drv=" + drv);
System.err.println("WebSQL.doGet: url=" + url);
System.err.println("WebSQL.doGet: usr=" + usr);
System.err.println("WebSQL.doGet: pwd=" + pwd);
System.err.println("WebSQL.doGet: sql=" + sql);
}
res.setContentType("text/html");
PrintWriter out = res.getWriter();
out.println("<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>");
out.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0
Strict//EN\" \"DTD/xhtml1-strict.dtd\">");
out.println("<html>");
out.println(" <head>");
out.println(" <title>WebSQL</title>");
out.println(" </head>");
out.println(" <body>");
out.println(" <table border=\"0\" height=\"100%\"
width=\"100%\">");
out.println(" <tr valign=\"top\" height=\"100%\">");
out.println(" <td>");
out.println(" <h1>WebSQL</h1>");
out.println(" <form method=\"post\" action=\"" +
req.getRequestURI() + "\">");
out.println(" <table border=\"0\">");
out.println(" <tr>");
out.println(" <td>Driver Name:</td>");
out.println(" <td><input type=\"text\" name=\"driver\"
value=\"" + drv + "\" size=\"100\" /></td>");
out.println(" </tr>");
out.println(" <tr>");
out.println(" <td>Database URL:</td>");
out.println(" <td><input type=\"text\" name=\"url\"
value=\"" + url + "\" size=\"100\" /></td>");
out.println(" </tr>");
out.println(" <tr>");
out.println(" <td>Database User:</td>");
if(usr != null) {
out.println(" <td><input type=\"text\" name=\"user\"
value=\"" + usr + "\" size=\"100\" /></td>");
} else {
out.println(" <td><input type=\"text\" name=\"user\"
value=\"\" size=\"100\" /></td>");
}
out.println(" </tr>");
out.println(" <tr>");
out.println(" <td>User Password:</td>");
if(pwd != null) {
out.println(" <td><input type=\"password\"
name=\"password\" value=\"" + pwd + "\" size=\"100\" /></td>");
} else {
out.println(" <td><input type=\"password\"
name=\"password\" value=\"\" size=\"100\" /></td>");
}
out.println(" </tr>");
out.println(" <tr>");
out.println(" <td>SQL Statement:</td>");
if(sql != null) {
out.println(" <td><input type=\"text\" name=\"sql\"
value=\"" + sql + "\" size=\"100\" /></td>");
} else {
out.println(" <td><input type=\"text\" name=\"sql\"
value=\"\" size=\"100\" /></td>");
}
out.println(" </tr>");
out.println(" <tr>");
out.println(" <td colspan=\"2\" align=\"center\"><input
type=\"submit\" value=\"Engage\" /></td>");
out.println(" </tr>");
out.println(" </table>");
out.println(" </form>");
if(sql != null) {
if((con == null) && (url != null)) {
if(drv != null) {
session.putValue("driver",drv);
try {
log("WebSQL.doGet: Registering driver " + drv);
Class.forName(drv);
} catch(ClassNotFoundException x) {
out.print(prepareStackTrace(x));
}
}
try {
session.putValue("url",url);
if((usr == null) && (pwd == null)) {
con = DriverManager.getConnection(url);
} else {
session.putValue("user",usr);
session.putValue("password",pwd);
con = DriverManager.getConnection(url,usr,pwd);
}
if(con != null) {
session.putValue("connection",new Con(con));
session.putValue("Initialized",Boolean.TRUE);
}
} catch(SQLException x) {
out.print(prepareStackTrace(x));
}
}
if(con != null) {
out.println(" <hr />");
out.println(" <h1>Executing: " + sql + "</h1>");
try {
if(sql.regionMatches(true,0,"DESCRIBE ",0,9)) {
if(sql.regionMatches(true,sql.length() - 5," FULL",0,5)) {
printResultSet(out,con.getMetaData().getColumns(con.getCatalog(),"%",sql.sub
string(9,sql.length() - 5).trim(),"%"));
} else {
printPartialResultSet(out,con.getMetaData().getColumns(con.getCatalog(),"%",
sql.substring(9).trim(),"%"),new String[]
{"COLUMN_NAME","TYPE_NAME","COLUMN_SIZE","IS_NULLABLE"});
}
} else if(sql.equalsIgnoreCase("CATALOG")) {
printResultSet(out,con.getMetaData().getCatalogs());
} else if(sql.equalsIgnoreCase("TABLES")) {
printResultSet(out,con.getMetaData().getTables(con.getCatalog(),"%","%",null
));
} else if(sql.equalsIgnoreCase("HELP")) {
out.println(" <ul>");
out.println(" <li>CATALOG\t- Display the Catalog of
Tables</li>");
out.println(" <li>TABLES\t- Display the list of
Tables visible to the current User</li>");
out.println(" <li>DESCRIBE <table> [FULL]\t-
Display data about the specified Table</li>");
out.println(" <li>HELP\t- Display this help
information</li>");
out.println(" <li>Any Standard SQL
Statement</li>");
out.println(" </ul>");
} else {
Statement stm = con.createStatement();
try {
if(stm.execute(sql)) {
int count = -1;
int i;
ResultSet rs;
do {
if(count != -1) {
out.println(" <p><tt>Result Count: " + count
+ "</tt></p>");
count = -1;
} else if((rs = stm.getResultSet()) != null) {
printResultSet(out,rs);
} else {
out.println(" <p><tt>Null Result
Set</tt></p>");
}
} while(stm.getMoreResults() || ((count =
stm.getUpdateCount()) != -1));
} else {
out.println(" <p><tt>Results: " +
stm.getUpdateCount() + "</tt></p>");
}
} finally {
stm.close();
}
}
} catch(SQLException x) {
out.print(prepareStackTrace(x));
}
} else {
out.println(" <p>WebSQL - Connection Closed!</p>");
}
}
out.println(" </td>");
out.println(" </tr>");
out.println(" <tr valign=\"bottom\">");
out.println(" <td align=\"right\"><small>" + getServletInfo() +
"</small></td>");
out.println(" </tr>");
out.println(" </table>");
out.println(" </body>");
out.println("</html>");
} catch(Throwable t) {
t.printStackTrace();
throw new ServletException(t);
}
} //doget
public void doPost (HttpServletRequest req,HttpServletResponse res) throws
ServletException, IOException {
HttpSession session = req.getSession(true);
Con c = (Con)session.getValue("connection");
Connection con = (c != null) ? c.getConnection() : null;
String pURL = req.getParameter("url");
String pUsr = req.getParameter("user");
String pPwd = req.getParameter("password");
String sURL = (String)session.getValue("url");
String sUsr = (String)session.getValue("user");
String sPwd = (String)session.getValue("password");
if(DEBUG) {
System.err.println("WebSQL.doPost: pURL=" + pURL);
System.err.println("WebSQL.doPost: pUsr=" + pUsr);
System.err.println("WebSQL.doPost: pPwd=" + pPwd);
System.err.println("WebSQL.doPost: sURL=" + sURL);
System.err.println("WebSQL.doPost: sUsr=" + sUsr);
System.err.println("WebSQL.doPost: sPwd=" + sPwd);
}
if((con == null) || ((sURL != null) && !sURL.equals(pURL)) || ((sUsr !=
null) && !sUsr.equals(pUsr)) || ((sPwd != null) && !sPwd.equals(pPwd))) {
String driver = req.getParameter("driver");
if(driver != null) {
try {
log("WebSQL.doPost: Registering Driver " + driver);
Class.forName(driver);
session.putValue("driver",driver);
} catch(ClassNotFoundException x) {
x.printStackTrace();
}
}
try {
session.putValue("url",pURL);
if((pUsr == null) && (pPwd == null)) {
con = DriverManager.getConnection(pURL);
} else {
session.putValue("user",pUsr);
session.putValue("password",pPwd);
con = DriverManager.getConnection(pURL,pUsr,pPwd);
}
if(con != null) {
session.putValue("connection",new Con(con));
session.putValue("Initialized",Boolean.TRUE);
} else {
session.removeValue("connection");
}
} catch(SQLException x) {
x.printStackTrace();
}
}
String sql = req.getParameter("sql");
if(sql != null) {
int end = sql.length();
if(sql.endsWith(";")) {
--end;
}
session.putValue("sql",sql.substring(0,end));
} else {
session.removeValue("sql");
}
res.sendRedirect(req.getRequestURI());
} //doPost
public String getServletInfo () {
return "SQL Console for the Web - " + version + '.' + release + ':' +
build;
} //getServletInfo
} //*WebSQL
----- Original Message -----
From: "Nguyen, Thang P" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, January 29, 2002 6:17 PM
Subject: Re: [JSP-INTEREST] Java 2 question
> Hi,
>
> If you have any info (book/source code) on how to build a SQL database
> browser (insert, delete, update, browse, etc) please let me know. what I
am
> looking for is almost anything that can be hefpful to develop a GUI
browser
> for an sql database using java ( I need more than just to write sql query
> statements). Java source code is preferable, but C/C++ is good as well.
> Please help.
> Thanks,
> TN
>
>
===========================================================================
> To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
> For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST
DIGEST".
> Some relevant FAQs on JSP/Servlets can be found at:
>
> http://archives.java.sun.com/jsp-interest.html
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.jsp
> http://www.jguru.com/faq/index.jsp
> http://www.jspinsider.com
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://archives.java.sun.com/jsp-interest.html
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.jsp
http://www.jguru.com/faq/index.jsp
http://www.jspinsider.com
