/** ==============================================================
Projekt: Java Servlet
File: JdbcTest.java
Beschreibung: Java Servlet to print the table SCOTT.EMP from
Oracle 9i Database on Akadia's Website
================================================================
*/
import java.io.*;
import java.sql.*;
import java.math.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class JdbcTest extends HttpServlet {
// Instance variables
private Connection dbConn;
/*
* -------------------------------------------------------
* Initialization when a server loads the servlet
* -------------------------------------------------------
*/
public void init(ServletConfig config)
throws ServletException {
super.init(config);
// Load Oracle driver (other version, see below)
// DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
// Register Oracle JDBC Thin driver
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(java.lang.ClassNotFoundException e) {
// log output will be printed in JServ log file
log("Exception class not found: " + e.getMessage());
}
// Connect to the database using the Oracle JDBC Thin driver
this.getConnection();
}
/*
* -------------------------------------------------------
* Destroy the servlet during removing it from the service
* -------------------------------------------------------
*/
public void destroy() {
// Check for open database connection
if (dbConn == null) {
return;
}
// Close connection
try {
dbConn.close();
}
catch (SQLException e) {
while (e != null) {
log("SQLException: ");
log(e.getSQLState());
log(e.getMessage());
log(String.valueOf(e.getErrorCode()));
e = e.getNextException();
}
}
catch (Exception e) {
e.printStackTrace();
}
}
/*
* -------------------------------------------------------
* Got a client request, give response
* -------------------------------------------------------
*/
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
// First set the content type header of response
res.setContentType("text/html");
// Get the response's PrintWriter to return text to the client
PrintWriter toClient = res.getWriter();
// Print header
this.printHeader(req, toClient);
// Check for open, non-broken database connection
try {
// Try to create statement object
Statement sqlStmt = dbConn.createStatement();
sqlStmt.close();
}
catch (Exception e) {
// Try to (re-) connect
this.getConnection();
if (dbConn == null) {
toClient.println("<pre>");
toClient.println("No or broken connection to the database.");
this.printFooter(toClient);
return;
}
}
// Connection seems to be ok
try {
// Show settings of already opened connection
toClient.println("<pre>");
toClient.println("Connection settings:");
toClient.println(" getAutoCommit = " + dbConn.getAutoCommit());
toClient.println(" getCatalog = " + dbConn.getCatalog());
toClient.println(" getTransactionIsolation = "
+ dbConn.getTransactionIsolation());
toClient.println(" isClosed = " + dbConn.isClosed());
toClient.println(" isReadOnly = " + dbConn.isReadOnly());
toClient.println("</pre>");
// Get the search criteria (defined as parameter)
String ename = null;
Enumeration enum = req.getParameterNames();
if (enum != null && enum.hasMoreElements()) {
while (enum.hasMoreElements()) {
String name = (String) enum.nextElement();
if (name.equalsIgnoreCase("ename")) {
ename = req.getParameter(name);
}
}
}
// Build the select statement
String strQuery = "SELECT empno, ename, job,"
+ " NVL(TO_CHAR(mgr), '---') \"mgr\","
+ " TO_CHAR(hiredate, 'DD.MM.YYYY') \"hiredate\","
+ " NVL(TO_CHAR(sal), '0') \"sal\","
+ " NVL(TO_CHAR(comm), '0') \"comm\","
+ " RPAD(TO_CHAR(deptno), 6, ' ') \"deptno\""
+ " FROM emp";
if ((ename != null) && (ename.length() > 0)) {
strQuery += " WHERE UPPER(ename) like UPPER('" + ename + "')";
}
strQuery += " ORDER BY ename";
// Execute the select statement
Statement sqlStmt = dbConn.createStatement();
ResultSet resSet = sqlStmt.executeQuery(strQuery);
int count = 0;
// Iterate through the result rows and print out the values obtained
toClient.println("<p>Return result:</p>");
toClient.println("<table border=1 cellpadding=2 cellspacing=0 width=500");
while (resSet.next()) {
String empNumber = String.valueOf(resSet.getInt("empno"));
String empName = resSet.getString("ename");
String job = resSet.getString("job");
String mgrNumber = resSet.getString("mgr");
String hireDate = resSet.getString("hiredate");
String salary = resSet.getString("sal");
String commission = resSet.getString("comm");
String deptNumber = resSet.getString("deptno");
toClient.println("<tr>");
toClient.println("<td>" + empNumber + "</td>");
toClient.println("<td>" + empName + "</td>");
toClient.println("<td>" + job + "</td>");
toClient.println("<td>" + mgrNumber + "</td>");
toClient.println("<td>" + hireDate + "</td>");
toClient.println("<td>" + salary + "</td>");
toClient.println("<td>" + commission + "</td>");
toClient.println("<td>" + deptNumber + "</td>");
toClient.println("</tr>");
count++;
}
toClient.println("</table>");
toClient.println("<p>" + String.valueOf(count) + " row(s) found.</p>");
// Close statement
sqlStmt.close();
}
catch (Exception e) {
toClient.println("Exception: " + e.getMessage());
}
// Print footer and end HTML
this.printFooter(toClient);
// Close the writer; the response is done
toClient.close();
}
/*
* -------------------------------------------------------
* Print HTML header
* -------------------------------------------------------
*/
private void printHeader(HttpServletRequest req, PrintWriter toClient) {
String serverName = req.getServerName();
int serverPort = req.getServerPort();
toClient.println("<html>");
toClient.println("<head><title>Access to Oracle9i Database on Akadias Site</head></title>");
toClient.println("<LINK rel=\"stylesheet\" href=\"https://www.akadia.com/docroot/css/website.css\" type=\"text/css\">");
toClient.println("<body>");
toClient.println("<h1>Select from table EMP</h1>");
toClient.println("<hr>");
}
/*
* -------------------------------------------------------
* Print footer and end HTML
* -------------------------------------------------------
*/
private void printFooter(PrintWriter toClient) {
toClient.println("<hr>");
toClient.println("<p><a href=\"/jsp/WEB-INF/classes/JdbcTest.txt\" target=\"_top\">"
+ "Show Java Servlet source</a></p>");
toClient.println("</body></html>");
}
/*
* ---------------------------------------------------------
* Connect to the database using the Oracle JDBC Thin driver
* ---------------------------------------------------------
*/
private void getConnection() {
// Setup initial parameters
String strHost = "opal";
String strPort = "1521";
String strSid = "RAB1";
String strUid = "scott";
String strPwd = "tiger";
// You must put a database name after the @ sign in the connection URL.
// You can use either the fully specified SQL*net syntax or a short cut
// syntax as <host>:<port>:<sid>. The example uses the short cut syntax.
// Connect to the database using the Oracle JDBC Thin driver
String strUrl = "jdbc:oracle:thin:@" + strHost + ":" + strPort + ":" + strSid;
try {
dbConn = DriverManager.getConnection(strUrl, strUid, strPwd);
log("Connection established");
}
catch (Exception e) {
dbConn = null;
log("Exception getConnection: " + e.getMessage());
log("(host=" + strHost + ", port=" + strPort + ", sid="
+ strSid + ", uid=" + strUid + ", pwd=" + strPwd + ")");
}
}
}
|