Discussions

Industry news: Connection Pooling using jdbc-struts!!!

  1. Connection Pooling using jdbc-struts!!! (2 messages)

    Connection Pool:-
    Creating a connection to the database server is expensive. It is even more expensive if the server is located on another machine(web applications). Connection pool contains a number of open database connections with minimum and maximum connections, that means the connection pool has open connections between minimum and maximum number that you specify. The pool expands and shrinks between minimum and maximum size depending on incremental capacity. You need to give minimum, maximum and incremental sizes as properties to the pool in order to maintain that functionality. You get the connection from the pool rather directly .For example, if you give properties like min, max and incremental sizes as 3, 10 and 1 then pool is created with size 3 initially and if it reaches it's capacity 3 and if a client requests a connection concurrently, it increments its capacity by 1 till it reaches 10 and later on it puts all its clients in a queue.
    There are a few choices when using connection pool.
    1. You can depend on application server (Ex-TomCat) if it supports this feature, generally all the application servers support connection pools. Application server creates the connection pool on behalf of you when it starts. You need to give properties like min, max and incremental sizes to the application server.
    2. You can use JDBC 2.0 interfaces, ConnectionPoolDataSource and PooledConnection if your driver implements these interfaces
    3. Or you can create your own connection pool if you are not using any application server or JDBC 2.0 compatible driver.
    By using any of these options, you can increase performance significantly. You need to take care of properties like min, max and incremental sizes. The maximum number of connections to be given depends on your application's requirement that means how many concurrent clients can access your database and also it depends up on your database's capability to provide maximum number of connections.



    JNDI:-
    JNDI is a generic interface for accessing objects stored in a naming and directory service.

    Examples of naming and directory services include:-
    · DNS services
    · LDAP services
    · NIS


    Their job is to associate names with information.

    DNS, for example, associates a computer name like “www.bsil.com” with an IP address like “209.98.32.12”. The advantage of maintaining this association is twofold:-

    · “www.bsil.com” is clearly simpler to remember than “209.98.32.12”.
     
    · The physical location of the machine can be changed without impacting its name. In other words, the Web pages served at www.imaginary.com can be moved to “209.98.32.13” without anything breaking. After all, people look up those Web pages using the name “www.bsil.com”. Because DNS manages the mapping of the computer name to the IP address, the change is invisible to end users.




















    JDBC Vs. JNDI:-
    Its natural to store all of the environment-specific information for database connections in a naming and directory service and access it by name. Using this approach, a programmer would not have to know any of the information required to make a database connection; it would simply get some reference to the database from the naming and directory service that contains all of the information. Anyway even if something changes it won’t make any difference to the code though we have to change the environment variable that is comparatively easier.
    The key to JNDI support in JDBC is the “javax.sql.DataSource” nterface. A JDBC driver(2.0,not 1.2) provides an implementation of this interface. The implementation will have as its attributes any information necessary to make a connection to a database in the database engine it supports.
    For example, the DataSource implementation for the MySQL-JDBC has attributes that store the name of the machine on which the database runs, the port it is listening to, and the name of the database to which you wish to connect. This DataSource object is then stored in a JNDI directory service with all of these attributes. Your application code needs to know only the name under which this DataSource object is stored.
    The code to make a connection using the JDBC support is now this:
    javax.naming.Context ctx =
         new InitialContext();
    javax.sql.DataSource ds =
         (DataSource)ctx.lookup("example");
    java.sql.Connection conn =
         ds.getConnection();


    This way requires no knowledge about the database in question. All of that data is associated with a DataSource in the JNDI directory. If you change anything about your environment, such as the machine on which the database server is running, you only change the entry in the JNDI directory. You do not need to make any application code changes.



    Alternative Method(added in Struts1.1):-
    Struts can define the datasources for an application from within its standard configuration file. A simple JDBC connection pool is also provided.
    Advantage of this method:- Removed bootstrapping of JNDI. JNDI bindings will occur if a jndi.properties file and a valid external provider is available. You can also access a DataSource by invoking findDataSource(null), which returns the default DataSource (first one listed in struts-config.xml), and by findDataSource(dbname), which returns the DataSource with that name.

    Sample Code(struts-config.xml:):-


    Struts-config.xml
      <data-sources>
        <data-source>
          <set-property property="autoCommit"
                         value="false"/>
          <set-property property="description"
                         value="MySQL"/>
          <set-property property="driverClass"
                            value="org.gjt.mm.mysql.Driver"/>
          <set-property property="maxCount"
                         value="4"/>
          <set-property property="minCount"
                         value="2"/>
          <set-property property="password"
                         value="pass"/>
          <set-property property="url"
                     value="jdbc:mysql://localhost:3306/test"/>
          <set-property property="user"
                         value="servlet"/>
        </data-source>
      </data-sources>












    Then, you can set up a TestAction.java action class with a perform method like this:
    (Note: - You need to download commons-dbcp.jar and import the classes you use for this code to execute.)

    public final class TestAction extends Action {
        public ActionForward perform(
    ActionMapping mapping,
    ActionForm form,
    HttpServletRequest request,
             HttpServletResponse response)
                throws IOException, ServletException {
             Connection connection = null;
             DataSource dataSource = null;
             Statement statement = null;
             ResultSet resultSet = null;

             try {
             dataSource = servlet.findDataSource(null);
    //It is null because it’s using only one datasource in .xml
    //It can name the key of datasource in case of multiple datasources.
              connection = dataSource.getConnection();
              statement = connection.createStatement();
              resultSet=statement.executeQuery(
    "SELECT FirstName,LastName FROM People "
                        + "ORDER BY LastName,FirstName");
              resultSet.close(); // just a test
            } catch (SQLException sqle) {
              getServlet().log("Connection.process", sqle);
            } finally {
    //Always close connection
              try {
              connection.close();
              } catch (SQLException e) {
              getServlet().log("Connection.close", e);
              }
            }

            // Forward control to the specified success URI
            return (mapping.findForward("success"));
        }
    } // End TestAction
  2. Great,

    but is there any way to get a datasource reference in a smarter way than by means of ActionForward's perform?
    Does this code really have to be replicated in EVERY Action class?

    Thanks,
    Paolo
  3. no replication required!![ Go to top ]

    Dear paolo,

    Sorry for late reply..i was in a holiday.

    Ofcourse u dont need to replicate it everytime.

    U can anyway make a class whose function u call by this statement->
     
    resultSet=MyDSClassName.getResultSet(servlet.findDataSource(null),"select * from MyTable");

    *note-servlet.findDataSource(null) returns datasource.

    and in that class u can import necessary libraries and work with it.

    Hope this will help!!

    regards,
    amit