blogger templates blogger widgets
This is part of a list of blog posts.
To browse the contents go to

JDBC web application - (unique) connections using DriverManager

This doesn't involve many changes from our previous servlet. All we had to do is move the connection object declaration into doGet method and modify createConnection() method.

@WebServlet(description = "This is primary product servlet", urlPatterns = { "/ProductServlet" })
public class ProductServlet extends HttpServlet {
 
 private static final long serialVersionUID = 1L;
       

    public ProductServlet() {
        super();
       
    }


 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  System.out.println("doGet");
  response.setContentType(request.getContentType());
  String caseID = request.getParameter("caseID");
  Connection conn = null;
  try {
   conn = createConnection();
   conn.setAutoCommit(false);
   if(caseID.equals("1")){
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into products values('3','htc')");
    System.out.println("3");
    try {
     Thread.sleep(100);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    stmt.executeUpdate("insert into products values('4','nexus')");
    System.out.println("4");
    try {
     Thread.sleep(10);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    conn.commit();
   }else{
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into products values('5','galaxy')");
    System.out.println("5");
    try {
     Thread.sleep(2000);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    
    stmt.executeUpdate("insert into products values('6','xolo')");
    System.out.println("6");
    try {
     Thread.sleep(10);
    } catch (InterruptedException e) {
     System.out.println("interrupted");
    }
    conn.rollback();
   }
   RequestDispatcher dispatch = request.getRequestDispatcher("/index.jsp");
   dispatch.forward(request, response);
  } catch (SQLException e) {
   e.printStackTrace();
  }finally {
   closeConnection(conn);
  }
  
 }

 public Connection createConnection() throws SQLException{
  Connection conn = null;  
  try {
   Class.forName("oracle.jdbc.OracleDriver");
   conn = DriverManager
     .getConnection("jdbc:oracle:thin:@localhost:1521:XE",
       "SYSTEM", "password");
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }

  return conn;
 }
 public void closeConnection(Connection conn){
  if(conn!=null){
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
} 

You may download this sample here

Ouput:
doGet
5
doGet
3
4
6

Let's check products table.
SQL> select * from Products;

ID TITLE
---------- -------------------------
1 ipad
2 galaxytab
3 htc
4 nexus

Again notice how the threads got executed. JVM switched threads back and forth but we get the output as expected.

Hurray.

Transactions work as expected when we don't share connection objects.


Any new problems?

Well, for very, very small programs like the one we are doing now - it does not matter, but when we start writing real world programs we will witness a new problem rising - Server performance hit

There are 2 primary reasons for this:


  • Acquiring a new connection is expensive. Some database management systems even start a completely new server process for each connection. And the JDBC driver maintains active TCP/IP connections with these database processes.
  • Maintaining many idle connections is expensive for a database management system.



Solution?

Database connection pooling.



There are 2 ways you can handle pooling -

  1. Program managed (traditional)
  2. Framework managed
  3. Container managed


Nowadays, in most of the web-applications, pooling is done by using facilities provided by the container/server or it's handled by using frameworks like apache -commons-dbcp, DBpool or hibernate. But, we'll look into traditional method of manually writing the connection pooling first.

Continue reading: JDBC connections using DataSouce (own implementation)

No comments:

Post a Comment