// An example for using servlets and access a relational database using JDBC // The HTML file that calls this servlet is DBServlet.html import java.sql.*; // for SQL import javax.servlet.*; // for servlets import javax.servlet.http.*; import java.io.*; // to use PrintWriter and IOExceptions import java.util.*; // to use Vector public class DBServlet extends HttpServlet { private Connection connection; public void doPost( HttpServletRequest request,HttpServletResponse response ) throws ServletException, IOException { //String url = "jdbc:oracle:thin:@coit-db:1521:coit"; //String url = "jdbc:oracle:thin:@itora2:1521:itora"; // String url = "jdbc:oracle:thin:@itora1:1521:rsch"; String url = "jdbc:oracle:thin:@cci-ora02:1521:class"; String username = "xwu"; String password = "qwe123"; // Load the driver to allow connection to the database try { Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection(url, username, password ); } catch ( ClassNotFoundException cnfex ) { System.err.println("Failed to load JDBC/ODBC driver." ); cnfex.printStackTrace(); System.exit( 1 ); // terminate program } catch ( SQLException sqlex ) { System.err.println( "Unable to connect" ); sqlex.printStackTrace(); System.exit( 1 ); // terminate program } // prepare the response and return it back to the client response.setContentType( "text/html" ); // content type PrintWriter responseOutput = response.getWriter(); StringBuffer buf = getDataFromDatabase(request); responseOutput.println(buf.toString() ); responseOutput.close(); } private StringBuffer getDataFromDatabase(HttpServletRequest request) { Statement statement; ResultSet resultSet; StringBuffer buf = new StringBuffer(); try { // read input parameter String value = request.getParameter( "name" ); System.out.println("Parameter = " + value); String query; //if (value=="ALL") // query = "SELECT * FROM Sailors"; //else query = "SELECT * FROM Sailors WHERE sname like '"+value+ "%'" ; buf.append( "\n" ); buf.append( "Servlet Response!\n" ); buf.append( "

Results:\n

" );

              System.out.println("Query = " +query);
              buf.append("
" + query +"
");
               
	        statement = connection.createStatement();
                           
              resultSet = statement.executeQuery(query);
               
	         
              // prepare the response to return back to the client
              
              // position to first record
              boolean moreRecords = resultSet.next();
          
              // If there are no records, display a message
              //if ( ! moreRecords ) {
              //    System.err.println("No records to display." );
              //    buf.append("
No records to view
");
              //    return buf;
               //}

               Vector columnHeads = new Vector();

              // get column heads
	          ResultSetMetaData rsmd = resultSet.getMetaData();
	          for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
                  columnHeads.addElement( rsmd.getColumnName( i ) );

             // get row data
	         while (moreRecords) {
	                   for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) {
                            switch( rsmd.getColumnType( i ) ) {
                                      case Types.VARCHAR:
                                      case Types.LONGVARCHAR:
                                                      buf.append("
"+rsmd.getColumnName(i)+" : " +
                                                      resultSet.getString(i));
                                                      break;
                                      case Types.INTEGER:
                                      case 2:
                                                      buf.append("
"+rsmd.getColumnName(i)+" : " +
                                                      resultSet.getLong(i));
                                                      break;
                                      case Types.FLOAT:
                                                      buf.append("
"+rsmd.getColumnName(i)+" : " +
                                                      resultSet.getDouble(i));
                                                      break;
                                      default:
                                                     System.out.println("Type was:" + rsmd.getColumnTypeName(i) );
                                                     
                            }
                      }
                      buf.append("
");
                      moreRecords = resultSet.next();
              }

              buf.append( "
\n" ); statement.close(); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); buf.append("

An error occured in here

"); } return buf; } }