How to create a JDBC MySQL connection pool in Tomcat7 installed on Amazon EC2?

Question

I am new and any help will be highly appreciated. I have a Webapp installed on Amazon EC2. I have installed Tomcat7 and MySQL 5.5 on Amazon EC2.

I am using following code in servlet for JDBC-MySQL connection

        Connection connection;
        Statement statement;
        ResultSet rs = null;
        try {
            // load Connector/J
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(JDBC_MYSQL_STRING);
            System.out.println(TAG + " Connection with MySQL established");
            statement = connection.createStatement();
            ....
            ....
            ....
            rs.close();
            statement.close();
            connection.close();
        } catch (ClassNotFoundException ex) {
            Log.log(Level.SEVERE, "ClassNotFoundException", ex);
        } catch (SQLException e) {
            for (Throwable t : e) {
                System.out.println(t.getMessage());
            }

I have included mysql-connector-java-5.1.13-bin.jar for Connector/J in the Library folder for Netabeans and deployed war files on tomcat7 in Amazon ec2.

My Question I have learned that JDBC-MySQL connection is very time consuming operation and connection pools can be used to keep live connection which will reduce time consumed in connection. I am new to this and have read my blogs but unable to understand how to set up connection pool and how to use it in the servlets?

Thanks.

Answer 1

You need to setup the DB Connection in server.xml

Follow this: http://www.mulesoft.com/tomcat-mysql

The URL of your database would take this form:

"jdbc:mysql://yourdatabasename.foo.us-east-1.rds.amazonaws.com:3306/"