import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class DbServletTrans extends HttpServlet {
DataSource pool;
public void init() throws ServletException {
Context env = null;
try {
env = (Context) new InitialContext().lookup("java:comp/env");
pool = (DataSource) env.lookup("jdbc/oracle-8i-athletes");
if (pool == null)
throw new ServletException(
"'oracle-8i-athletes' is an unknown DataSource");
} catch (NamingException ne) {
throw new ServletException(ne);
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, java.io.IOException {
Connection conn = null;
Statement stmt = null;
response.setContentType("text/html");
java.io.PrintWriter out = response.getWriter();
out
.println("Using transactions");
out.println("These SQL statements are part of a transaction
");
out.println("CallableStatement.executeUpdate()");
out.println("
");
out.println("Statement.executeUpdate()");
out.println("
");
try {
conn = pool.getConnection();
out.println("AutoCommit before setAutoCommit(): "
+ conn.getAutoCommit() + "
");
out.println("Transaction isolation level: ");
switch (conn.getTransactionIsolation()) {
case 0:
out.println("TRANSACTION_NONE
");
break;
case 1:
out.println("TRANSACTION_READ_UNCOMMITTED
");
break;
case 2:
out.println("TRANSACTION_READ_COMMITTED
");
break;
case 4:
out.println("TRANSACTION_REPEATABLE_READ
");
break;
case 8:
out.println("TRANSACTION_SERIALIZABLE
");
break;
default:
out.println("UNKNOWN
");
}
conn.setAutoCommit(false);
CallableStatement cs = null;
//Create an instance of the CallableStatement
cs = conn.prepareCall("{call addEvent (?,?,?)}");
cs.setString(1, "Salisbury Beach 5-Miler");
cs.setString(2, "Salisbury MA");
cs.setString(3, "14-Aug-2003");
//Call the inherited PreparedStatement.executeUpdate() method
cs.executeUpdate();
String sql = "update raceevent set racedate='13-Aug-2003' "
+ "where name='Salisbury Beach 5-Miler'";
int res = 0;
stmt = conn.createStatement();
res = stmt.executeUpdate(sql);
//commit the two SQL statements
conn.commit();
} catch (Exception e) {
try {
//rollback the transaction in case of a problem
conn.rollback();
} catch (SQLException sqle) {
}
throw new ServletException(e.getMessage());
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();//this returns the Connection to the
// Connection pool
} catch (SQLException sqle) {
}
}
out.println("");
out.close();
} //doGet
}