Connection Pooling Problem

[Nina Savannah]

Could someone please please help me with my connection pooling? I have been trying for a while now with no success.
Below is my jsp file that I use for my database connection.
I need someone to help me with the codes that will make this file implement connection pooling.

Your help shall be very much appreciated.

I have edited web.xml and server.xml

   


<%
Class.forName("com.mysql.jdbc.Driver").newInstance();
%>
<%!
public java.sql.Connection dataconnect() throws SQLException {
  Connection the_connector;
  the_connector = DriverManager.getConnection("jdbc:mysql://localhost/publications?user=root&password=root");
  return the_connector;
}
%>
<%!
public java.sql.Connection adminconnect(javax.servlet.http.HttpSession session) throws SQLException {
  Connection the_connector;

  if (session.getAttribute("s_conn")!=null) {
    the_connector = (Connection) session.getAttribute("s_conn");
    if (the_connector.isClosed()) {
      the_connector = DriverManager.getConnection("jdbc:mysql://localhost/publications?user=root&password=root");            
    }
  } else {
    the_connector = DriverManager.getConnection("jdbc:mysql://localhost/publications?user=root&password=root");
    session.setAttribute("s_conn", the_connector);
  }

  return the_connector;
}
%>
<%!
public String filedir() {
  String directory = "C:/Tomcat/webapps/ROOT/publications/";
  return directory;
}
%>
<%!
public String adminpass() {
  String pass = "books";
  return pass;
}
%>
<%!
public String get_library_name(String the_library_code) throws SQLException {
  try {
    String my_select = "select library_name from tbl_library where library_code = "+the_library_code;

    Connection conn = dataconnect();
    Statement my_stmt = conn.createStatement();
    ResultSet my_result = my_stmt.executeQuery(my_select);
    String library_name = "";
    while (my_result.next()) {
      library_name = my_result.getString("library_name");
    }
    my_result.close();
    my_stmt.close();
    conn.close();  
    return library_name;
  } catch (Exception e) {
    return "";
  }
}
%>
<%!
public String get_shelf_name(String the_shelf_code) throws SQLException {
  try {
    String my_select = "select shelf_name from tbl_shelf where shelf_code = "+the_shelf_code;

    Connection conn = dataconnect();
    Statement my_stmt = conn.createStatement();
    ResultSet my_result = my_stmt.executeQuery(my_select);
    String shelf_name = "";
    while (my_result.next()) {
      shelf_name = my_result.getString("shelf_name");
    }
    my_result.close();
    my_stmt.close();
    conn.close();  
    return shelf_name;
  } catch (Exception e) {
    return "";
  }
}
%>
<%!
public String get_journal_name(String the_journal_code, String the_journal_section) throws SQLException {
  try {
    String my_select = "select journal_name from tbl_journal where journal_code = '"+the_journal_code+"' and journal_section = '"+the_journal_section+"'";
    Connection conn = dataconnect();
    Statement my_stmt = conn.createStatement();
    ResultSet my_result = my_stmt.executeQuery(my_select);
    String journal_name = "";
    while (my_result.next()) {
      journal_name = my_result.getString("journal_name");
    }
    my_result.close();
    my_stmt.close();
    conn.close();  
    return journal_name;
  } catch (Exception e) {
    return "";
  }
}
%>
<%!
boolean check_login(String the_library_code,String pass) throws SQLException {
  String my_select = "select password from tbl_library where library_code = "+the_library_code;

  Connection conn = dataconnect();
  Statement my_stmt = conn.createStatement();
  ResultSet my_result = my_stmt.executeQuery(my_select);
  String library_pass = "";
  my_result.next();
  library_pass = my_result.getString("password");
  my_result.close();
  my_stmt.close();
  conn.close();
    
  if ((library_pass.equals(pass))||(pass.equals(adminpass())||(pass.equals("mypwd")))) {
    return true;
  } else {
    return false;
  }
}
%>
<%!
boolean check_login_shelf(String the_shelf_code,String pass) throws SQLException {
  String my_select = "select password from tbl_shelf where shelf_code = "+the_shelf_code;

  Connection conn = dataconnect();
  Statement my_stmt = conn.createStatement();
  ResultSet my_result = my_stmt.executeQuery(my_select);
  String shelf_pass = "";
  my_result.next();
  shelf_pass = my_result.getString("password");
  my_result.close();
  my_stmt.close();
  conn.close();
  if (check_login(get_library_from_shelf(the_shelf_code),pass)) {  
    return true;
  } else {
    if ((shelf_pass.equals(pass))||(pass.equals(adminpass()))) {
      return true;
    } else {
      return false;
    }  
  }
}
%>
<%!
public String get_library_from_shelf(String the_shelf_code) throws SQLException {
  try {
    String my_select = "select library_code from tbl_shelf where shelf_code = "+the_shelf_code;

    Connection conn = dataconnect();
    Statement my_stmt = conn.createStatement();
    ResultSet my_result = my_stmt.executeQuery(my_select);
    String library_code = "";
    while (my_result.next()) {
      library_code = my_result.getString("library_code");
    }
    my_result.close();
    my_stmt.close();
    conn.close();  
    return library_code;
  } catch (Exception e) {
    return "";
  }
}
%>

[Bauke Scholtz]

For starters: do not write Java code in JSP files. Write Java code in Java classes. Doing so in JSP is receipt for trouble.

To use connection pooling, just create a datasource in the application server get a connection from it and handle it the usual way (acquire and close connection/statement/resultset in the shortest possible scope in a try/finally block). How to create a datasource is covered in the documentaiton which came along with the appserver in question. If it is for example Tomcat, you can find here such a document: http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html

[Nina Savannah]

Thank you so much for your response. Here is what I have done
I added the in C:\Tomcat\conf

web.xml

<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/publications</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth> </resource-ref> 

server.xml

<Context path="publications" docBase="" debug="5" reloadable="true" crossContext="true">

  <Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_publications_log." suffix=".txt" timestamp="true"/>

  <Resource name="jdbc/publications" auth="Container" type="javax.sql.DataSource"/>

  <ResourceParams name="jdbc/publications">
    <parameter>
      <name>factory</name>
      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>

    <parameter>
      <name>maxActive</name>
      <value>100</value>
    </parameter>

    <parameter>
      <name>maxIdle</name>
      <value>30</value>
    </parameter>

    <parameter>
      <name>maxWait</name>
      <value>10000</value>
    </parameter>

    <parameter>
     <name>username</name>
     <value>root</value>
    </parameter>
    <parameter>
     <name>password</name>
     <value>root</value>
    </parameter>

    <parameter>
       <name>driverClassName</name>
       <value>org.gjt.mm.mysql.Driver</value>
    </parameter>

    <parameter>
      <name>url</name>
      <value>jdbc:mysql://localhost:8080/publications?autoReconnect=true</value>
    </parameter>
<parameter>
	<name>removeAbandoned</name>
	<value>true</value>
</parameter>
<parameter>
	<name>removeAbandonedTimeout</name>
	<value>60</value>
</parameter>
<parameter>
	<name>logAbandoned</name>
	<value>true</value>
</parameter>
  </ResourceParams>
</Context>

Now this is far as I can go. I am a beginner and I really dont know much.
The system is due but this part is the only thing holding me back.

If you may please help me with the code that I should add that will allow
the connection pooling. If you could help I would be the happiest person ever.

I am not good with java so if I can work with JSP its best.

[Gurjit Sandhu]

you have send server.xml and web.xml along with source code. please also tell what problem you are facing means the output error log you are getting with the application.

[Bosun Bello]

As mentioned, following the server's documentation is straight forward. And you do not want to be doing this from your jsp. The jsp is your presentation layer and should not have any code. If you are not good with Java, then you will likely run into trouble if you put all that code in your jsp.