CS6320:  SW Engineering of Web Based Systems

 

Servlet connecting to Database example (direct conneciton through JDBC)

 

package grewe.TestGlassfish_JDBC;
         import java.io.IOException;
         import javax.servlet.ServletException;
         import javax.servlet.annotation.WebServlet;
         import javax.servlet.http.HttpServlet;
         import javax.servlet.http.HttpServletRequest;
         import javax.servlet.http.HttpServletResponse;
         import java.sql.*;
         import java.io.*;
/**
         * Servlet implementation class DBServeltDump
         */
         @WebServlet("/DBServeltDump")
         public class DBServeltDump extends HttpServlet {
         private static final long serialVersionUID = 1L;
   
         /**
         * @see HttpServlet#HttpServlet()
         */
         public DBServeltDump() {
                 super();
         // TODO Auto-generated constructor stub
         }
 /**
         * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
         */
         protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                 // TODO Auto-generated method stub
                 doPost(request,response);
         }
 /**
         * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
         */
         protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                 // TODO Auto-generated method stub
   
                 PrintStream out = new PrintStream(response.getOutputStream()); 
                 // Load the Oracle JDBC driver
                 try {Class.forName ("oracle.jdbc.driver.OracleDriver");
   
                 }
                 catch (Exception e){
   
                         out.println("problem loading driver");
                         out.println(e.getMessage());
                 }
   
 
                 try{
   
                 // Connect to the database
   
                 Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@mcsdb1.sci.csueastbay.edu:1521:MCSDB1","LOGIN","PASSWORD");
 
                 // Create a Statement
                 Statement stmt = conn.createStatement ();
                 // Select the all (*) from the table JAVATEST
                 ResultSet rset = stmt.executeQuery("select * from dogs");
                 out.println(rset);
   
                 // Iterate through the result and print the employee names
                 while (rset.next ())  //get next row of table returned
                 { for(int i=1; i<rset.getMetaData().getColumnCount(); i++) //visit each column
                         out.print(rset.getString(i) + " | ");
                   out.println("  ");
                   out.println("========================================");
                 } 
   
                 //Close access to everything...will otherwise happen when disconnect
                 // from database.
                 rset.close();
                 stmt.close();
                 conn.close();
         }catch(SQLException ex)
                 {  out.println("\n--- SQLException caught ---\n");
                    while (ex != null) {
                    out.println("Message:   " + ex.getMessage ());
                    out.println("SQLState:  " + ex.getSQLState ());
                    out.println("ErrorCode: " + ex.getErrorCode ());
                    ex = ex.getNextException();
                    out.println("");
                 }
         }
 
    }
}
       

Results running this on Dog database (see it working)

example

 

 

© Lynne Grewe