import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.*;
import java.sql.*;
import java.util.Properties;
import java.util.*;
/**
* ImageDBServlet.java
*
* This servlet demonstrates how to use JDBC driver to access the
* image database from the servlet, thus shows a 3-tier JDBC example.
*
* @author xjqiu@npac.syr.edu
*/
public class ImageDBServlet extends HttpServlet {
String imgname;
String color;
String type;
String url;
String noneLabel=" ";
/**
* doGet() method
* At Server side, collection query information from client side interface,
* access Image Database, send back HTML file containing query results
*/
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException
{
Connection con;
PrintWriter out;
res.setContentType("text/html");
out = new PrintWriter(res.getOutputStream());
// Fetch the parameters from client side
// while checking each field of the query table.
// Perform Database Access according to the value combinations provieded from User.
if (req.getParameter("imgname") != null)
imgname=req.getParameter("imgname");
else
imgname=noneLabel;
if (req.getParameter("color") != null)
color=req.getParameter("color");
else
color=noneLabel;
if (req.getParameter("type") != null)
type=req.getParameter("type");
else
type=noneLabel;
if (req.getParameter("url") != null)
url=req.getParameter("url");
else
url=noneLabel;
printPageHeader(out);
String cntrl = req.getParameter("control");
if(cntrl != null) {
if (cntrl.equals("Search")) {//Search button pressed
doSearch(req, out);
}else if (cntrl.equals("Insert")) {//Insert button pressed
doInsert(out);
}
}
printPageFooter(out);
}
private void printPageHeader(PrintWriter out) {
out.println("");
out.println("");
out.println("3-Tier Model - Java Servlet, XML and JDBC");
out.println("");
out.println("");
out.println("Image Database");
out.println("
");
}
private boolean printPageFooter(PrintWriter out) {
try {
System.out.println("In pringPageFooter\n");
out.println("
");
out.println("
");
out.println("![](\"http://class-server.npac.syr.edu:3768/cps616spring99-docs/cy99xfq/accessaries/bluebackarrow.gif\")
");
out.println("");
out.println("");
out.flush();
return true;
}
catch (Exception ex) {
System.err.println("printPageFooter->error display..." );
ex.printStackTrace();
return false;
}
}
private void displayHashtable(Hashtable ht, PrintWriter out) {
out.println("| Name : | "+ ht.get("imgname").toString()+" | ");
out.println("![](\""+ht.get("url").toString()+"\") ");
out.println(" |
|---|
| Color : | "+ht.get("color").toString()+" | ");
out.println("
|---|
| Type : | "+ht.get("type").toString()+" | ");
out.println("
|---|
| ");
out.println(" |
|---|
");
}
private String getSQLstring(HttpServletRequest req) {
String tmpstr = "select * from images_table where ";
StringBuffer strbuf = new StringBuffer();
if(!(imgname.trim().equals(""))) {
strbuf.append("upper(imgname)=upper('"+(req.getParameter("imgname")).trim()+"') and ");
}else {
strbuf.append("imgname like '%' and ");
}
if(!(color.trim().equals(""))) {
strbuf.append("upper(color)=upper('"+(req.getParameter("color")).trim()+"') and ");
}else {
strbuf.append("color like '%' and ");
}
if(!(type.trim().equals(""))) {
strbuf.append("upper(type)=upper('"+(req.getParameter("type")).trim()+"') and ");
}else {
strbuf.append("type like '%' and ");
}
if(!(url.trim().equals(""))) {
strbuf.append("upper(url)=upper('"+(req.getParameter("url")).trim()+"');");
}else {
strbuf.append("url like '%'");
}
tmpstr = tmpstr + strbuf.toString();
if(tmpstr == null || tmpstr.equals("") ) return noneLabel;
else return tmpstr;
}
private void doInsert(PrintWriter out) { //Insert new record to database file
String sql = "insert into images_table values(" +
"images_table_seq.nextval,'"+imgname+
"','"+color+"','"+type+"','"+
url+"')";
open();
executeSQL(sql);
close();
out.println("New record has been added to Database!");
}
private void doSearch(HttpServletRequest req, PrintWriter out) {
String sql;
sql = getSQLstring(req);
open();
Vector vec = getRecords(sql);
Hashtable ht ;
if(vec.size() <= 0) {
out.println(" No Records Matched the Query !");
}else {
out.println("");
out.println("");
out.println("| |
");
for(Enumeration e = vec.elements(); e.hasMoreElements(); ){
ht = (Hashtable) e.nextElement();
displayHashtable(ht,out);
}
out.println("
");
out.println("");
}
close();
}
private String get(String key, Hashtable ht){ //Check keyword field, subsitute null field with " "
if(ht.get(key) != null)
return ht.get(key).toString();
else
return " ";
}
public Connection conn;
private void open() { //Open the connection to DB
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection
("jdbc:oracle:thin:@carver.npac.syr.edu:1521:europe", "xjqiu", "judy");
System.out.println("Connection Open");
}
catch (Exception e) { System.err.println("Error connection: " + e);}
}
private int getMaxId(String sql) { //Get the Maxium id from records returned
int i = 0;
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if(rs == null) return -1;
rs.next();
i = rs.getInt("max(id)");
rs.close();
stmt.close();
}catch(Exception e) {
e.printStackTrace(System.out);
return -1;
}
return i;
}
private int executeSQL(String sql) { // Execute Query operation by input sql string
int i = -1;
try {
Statement stmt = conn.createStatement();
stmt.execute(sql);
i = 1;
stmt.close();
}catch(Exception e) {
System.out.println("Exception in updating record");
e.printStackTrace(System.out);
}
return i;
}
private Vector getRecords(String sql) {//Return all records that matches the given name/phone field
Vector vec = new Vector();
Hashtable ht = new Hashtable();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
ht.put("id",new Integer(rs.getInt("id")));
ht.put("imgname",rs.getString("imgname"));
ht.put("color",rs.getString("color"));
ht.put("type",rs.getString("type"));
ht.put("url",rs.getString("url"));
vec.addElement(ht.clone());
System.out.println("Hashtable inside getRec = "+ht);
ht.clear();
}
rs.close();
stmt.close();
}catch(Exception e) {
System.out.println(e.getMessage());
System.out.println("Exception inside getRecords...");
}
return vec;
}
private void close() { //Close connection to DB
try {
conn.close();
System.out.println("Connection Closed");
}
catch (Exception e) {System.err.println("Error in closing dbase");}
}
}//end of class