/**This servlet get request from client and extract the process name, job, id, input file, output file, time sending, time done, type of job, and annonation from http request. All these parameters are used to access database by JDBC. This program can do the jobs of lookup, insertion, and deletion a process. Exception can be returned to client.This progarm is difference between the DBservlet.java is the result from the database are sorted according to the sending time.and suport the wilcard. API: void service( HttpServletRequest, HttpServletResponse ) throw ServletException, IOException void find( Stroage, Connection, ServletOutputStream ); void insert( Stroage, Connection, ServletOutputStream ); void updata( Stroage, Connection, ServletOutputStream ); void delete( Stroage, Connection, ServletOutputStream ); void destroy(); void close(); @author Gang Wei @version 1.0 **/ import java.io.*; import javax.servlet.*; import javax.servlet.http.*; import java.sql.*; import java.util.*; public class Project2_DBservlet extends HttpServlet implements SingleThreadModel { private Connection conn;//for connection with JDBC private ServletConfig config;//servlet config private ServletOutputStream out;//output stream to client /**Purpose:To get parameters from http request and access the database by calling JDBC method. Input: HttpServletReques, HttpServletResponse; Output:void; **/ public void service(HttpServletRequest req, HttpServletResponse resp ) throws ServletException, IOException { resp.setContentType( "text/html" ); out = resp.getOutputStream(); Project2_Stroage stroage; //get parameters from the http request. String search = req.getParameter( "search" );//fro search? String insert = req.getParameter( "insert" );//for insert? String update = req.getParameter( "update" );//for updata? String delete = req.getParameter( "delete" );//for delete? String name = req.getParameter( "name" ); String job = req.getParameter( "job" );//job name of process String id = req.getParameter( "id" );//id String inputf, outputf, type, ann; long times, timed; try{ out.println( ""); out.println( "" + "Project2_Client html" + "" ); out.println(""); //reload the client, and put the returning data near it. out.println(""); out.println(""); try { Class.forName("oracle.jdbc.driver.OracleDriver");//get drive. //get connection with JDBC conn = DriverManager.getConnection("jdbc:oracle:thin:@carver.npac.syr.edu:1521:europe" , "gangwei", "gangwei"); if( search.compareTo( "ture" ) == 0 ){ //is for search stroage = new Project2_Stroage( name, job, id,null, null, 0, 0, null, null ); find( stroage, conn, out ); }//if else if( insert.compareTo( "ture" ) == 0 ) { //for insert, and get time, input, output files, type, annonation of process. times = Long.parseLong( req.getParameter( "times" ) ); timed = Long.parseLong( req.getParameter( "timed" ) ); inputf = req.getParameter( "inputf" ); outputf = req.getParameter( "outputf" ); type = req.getParameter( "type" ); ann = req.getParameter( "ann" ); stroage = new Project2_Stroage( name, job, id, inputf, outputf, times, timed, type, ann ); insert( stroage, conn, out ); }//else if else if( update.compareTo( "ture" ) == 0 ) { //updata the process. times = Long.parseLong( req.getParameter( "times" ) ); timed = Long.parseLong( req.getParameter( "timed" ) ); inputf = req.getParameter( "inputf" ); outputf = req.getParameter( "outputf" ); type = req.getParameter( "type" ); ann = req.getParameter( "ann" ); stroage = new Project2_Stroage( name, job, id, inputf, outputf, times, timed, type, ann); // binding input parameters to SQL call updata( stroage, conn, out ); }//else if else { stroage = new Project2_Stroage( name, job, id, null, null, 0, 0, null, null ); //delete the process delete( stroage, conn, out ); } out.println("
"); out.println(""); out.println( "" ); } catch ( SQLException e) { out.println("Error in dbase" + e.toString() ); out.println( "SQLState: " + e.getSQLState() ); out.println( "Message: " + e.getMessage() ); out.println( "Vender: " + e.getErrorCode() ); } }//ioexception catch (Exception ex) { out.println("ioexception "+ex.getMessage()); ex.printStackTrace(); } } /**Purpose:find the data in the database table and return to client Input:Stroage of object, Connection to JDBC of object, ServletOutputStream of object. Output:void **/ private void find( Project2_Stroage stroage, Connection conn, ServletOutputStream out ) { PreparedStatement pstmt; try{ try{ try{ ResultSet rs = null; //prepare to access JDBC if( !(stroage.name.equals( "" )) && stroage.job.equals( "" ) && stroage.id.equals( "" ) ){ out.println( stroage.name ); pstmt = conn.prepareStatement("select * from test_table where name = ?"); //if string has wildcard.switch it to '%' if( stroage.name.indexOf( '*' ) != -1 ) stroage.name = stroage.name.replace( '*', '%' ); if( stroage.job.indexOf( '?' ) != -1 ) stroage.name = stroage.name.replace( '?', '%' ); pstmt.setString( 1, stroage.name ); // Execute the SQL, ResultSet is row(s) of data returned by database query rs = pstmt.executeQuery(); } else if( !(stroage.name.equals( "" )) && !(stroage.job.equals( "" )) && stroage.id.equals( "" )){ //if string has wildcard.switch it to '%' if( stroage.job.indexOf( '*' ) != -1 ) stroage.job = stroage.job.replace( '*', '%' ); if( stroage.job.indexOf( '?' ) != -1 ) stroage.job = stroage.job.replace( '?', '%' ); pstmt = conn.prepareStatement("select * from test_table where name like ? and job like ? order by times"); pstmt.setString( 1, stroage.name ); pstmt.setString( 2, stroage.job ); // Execute the SQL, ResultSet is row(s) of data returned by database query rs = pstmt.executeQuery(); }//else if else if( stroage.name.equals( "" ) && stroage.id.equals( "" )){ //if string has wildcard switch it to '%'. if( stroage.job.indexOf( '*' ) != -1 ) stroage.job = stroage.job.replace( '*', '%' ); if( stroage.job.indexOf( '?' ) != -1 ) stroage.job = stroage.job.replace( '?', '%' ); pstmt = conn.prepareStatement("select * from test_table where job like ? "); pstmt.setString( 1, stroage.job ); // Execute the SQL, ResultSet is row(s) of data returned by database query rs = pstmt.executeQuery(); }//else if else { pstmt = conn.prepareStatement("select * from test_table where id = ? order by times"); pstmt.setInt( 1, Integer.parseInt( stroage.id )); // Execute the SQL, ResultSet is row(s) of data returned by database query rs = pstmt.executeQuery(); } // return the fields of the first row of the result set int havedata = 0; //for checking if there is data in the table. while( rs.next() ){ havedata++; stroage.name = rs.getString( 3 ); stroage.job = rs.getString( 4 ); stroage.id = Integer.toString( rs.getInt( 5 ) ); stroage.inputf = rs.getString( 6 ); stroage.outputf = rs.getString( 7 ); long longtimes = rs.getLong( 1 ); //get time sent java.util.Date date = new java.util.Date( longtimes ); stroage.times = longtimes; long longtimed = rs.getLong( 2 ); //get time done. java.util.Date dat = new java.util.Date( longtimed ); stroage.timed = longtimed; stroage.type = rs.getString( 8 ); stroage.ann = rs.getString( 9 ); out.println( "" ); } if( havedata == 0 ) out.println("No data in the table." ); pstmt.close();//closeing the statement. } catch ( SQLException e) { out.println("Error in dbase" + e.toString() ); out.println( "SQLState: " + e.getSQLState() ); out.println( "Message: " + e.getMessage() ); out.println( "Vender: " + e.getErrorCode() ); } }//ioexception catch (Exception ex) { out.println("ioexception "+ ex.toString()); ex.printStackTrace(); } } catch (Exception ext ){} } /**Purpose:insert a process into table Input:Stroage of object, Connection of object, ServletOutputStream of object Output:Void **/ private void insert( Project2_Stroage stroage, Connection conn, ServletOutputStream out )throws IOException { try{ try{ String s1 = "insert into test_table " + "( times, timed, name, job, id, inputf, outputf, type, annotation)" + "values ( ?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = conn.prepareStatement( s1 );//prepare statement pstmt.setString( 3, stroage.name ); pstmt.setString( 4, stroage.job); pstmt.setInt( 5, Integer.parseInt( stroage.id )); pstmt.setString( 6, stroage.inputf); pstmt.setString( 7, stroage.outputf); pstmt.setLong( 1, stroage.times ); pstmt.setLong( 2, stroage.timed ); pstmt.setString( 8, stroage.type ); pstmt.setString( 9, stroage.ann ); // Execute the SQL, ResultSet is integer. int r = pstmt.executeUpdate( s1 ); pstmt.close(); out.println("Insert sucessed!"); }//try catch ( SQLException e) { out.println("Error in dbase" + e.toString() ); out.println( "SQLState: " + e.getSQLState() ); out.println( "Message: " + e.getMessage() ); out.println( "Vender: " + e.getErrorCode() ); } }//ioexception catch (Exception ex) { out.println( "IOException:" + ex.toString() ); } } /**Purpose:updata a process already in the database Input:objects of Stroage, Connection, and ServletOutputStream. Output:void **/ private void updata( Project2_Stroage stroage, Connection conn, ServletOutputStream out ){ try{ try{ String s1 = "update test_table " + "set inputf = ?, outputf =?, times =?, timed = ?, type = ?, annotation = ?" + "where name = ? and job = ?"; PreparedStatement pstmt = conn.prepareStatement( s1 ); pstmt.setString(1, stroage.inputf); pstmt.setString(2, stroage.outputf ); pstmt.setLong( 3, stroage.times ); pstmt.setLong( 4, stroage.timed ); pstmt.setString( 5, stroage.type ); pstmt.setString( 6, stroage.ann ); pstmt.setString( 7, stroage.name ); pstmt.setString( 8, stroage.job ); // Execute the SQL, ResultSet is integer int r = pstmt.executeUpdate( s1 ); pstmt.close(); out.println( "Update: " + stroage.job + "Sucessed!" ); }//try catch ( SQLException e) { out.println("Error in dbase" + e.toString() ); out.println( "SQLState: " + e.getSQLState() ); out.println( "Message: " + e.getMessage() ); out.println( "Vender: " + e.getErrorCode() ); } }//ioexception, try catch (Exception ex) { } } /**Purpose:delete a process in the database. Input:object of ServletOutputStream, Stroage, and Connection. Output:void **/ private void delete( Project2_Stroage stroage, Connection conn, ServletOutputStream out ){ try{ try{ // binding input parameters to SQL call String s1 = "delete from test_table " + "where name = ? and job = ?"; PreparedStatement pstmt = conn.prepareStatement( s1 ); pstmt.setString(1, stroage.name ); pstmt.setString(2, stroage.job ); // Execute the SQL, ResultSet is integer int r = pstmt.executeUpdate( s1 ); pstmt.close(); out.println( "Deleting is suceessed!" ); } catch ( SQLException e) { out.println("Error in dbase" + e.toString() ); out.println( "SQLState: " + e.getSQLState() ); out.println( "Message: " + e.getMessage() ); out.println( "Vender: " + e.getErrorCode() ); } }//ioexception, try catch (Exception ex) { } } /**Purpose:destroy the servlet Input:void Output; void **/ public void destroy() { close(); } /**Purpose:close connection with database Input:void Output:void **/ private void close() { try { conn.close(); } catch (Exception e) {} } }