Connection using ConnectionPoolDataSource

Question

To connect my required Database. I am planning to use ConnectionPoolDataSource class. But How can i set the details regarding the database name(to which i want it to be connected) on using this instance. Please Help on this occassion.

Answer 1

Try to read this documentation and example

EDIT

just modified example from above links

prepared steps: - download MySQL Server - download mySQL java driver - download Apache Commons Pool - download Commons DBCP - Open MySQL Client like MySQL Workbench and create DB using next script

delimiter $$

CREATE DATABASE `test_stackoverflow` /*!40100 DEFAULT CHARACTER SET utf8 */$$

delimiter $$

CREATE TABLE `test_table` (
  `idtest_table` int(11) NOT NULL,
  `test_field` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idtest_table`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

INSERT INTO `test_stackoverflow`.`test_table` (`idtest_table`, `test_field`) VALUES (1, 'test1');
INSERT INTO `test_stackoverflow`.`test_table` (`idtest_table`, `test_field`) VALUES (2, 'test2');

• create java project, add to class path , myscl connector, pool and dbcp (you just download all these jars)

add next classes

import org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS;
import org.apache.commons.dbcp.datasources.SharedPoolDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author Sergii.Zagriichuk
 */
public class Pool {
    private static DataSource ds;

    static {
        DriverAdapterCPDS cpds = new DriverAdapterCPDS();
        try {
            cpds.setDriver("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        cpds.setUrl("jdbc:mysql://localhost:3306/test_stackoverflow");
        cpds.setUser("root");
        cpds.setPassword("root");

        SharedPoolDataSource tds = new SharedPoolDataSource();
        tds.setConnectionPoolDataSource(cpds);
        tds.setMaxActive(10);
        tds.setMaxWait(50);

        ds = tds;
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

user name and pass should be changed to your db user/password

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MainClass {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = Pool.getConnection();
            // Do work with connection
            statement = connection.createStatement();
            String selectEmployeesSQL = "select * from test_table";
            resultSet = statement.executeQuery(selectEmployeesSQL);

            while (resultSet.next()) {
                printTestTable(resultSet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
        }
    }

    private static void printTestTable(ResultSet resultSet) throws SQLException {
        System.out.print(resultSet.getInt("idtest_table")+", ");
        System.out.print(resultSet.getString("test_field"));
    }

}

Just run main method and you will see printed test values to console!!!

Answer 2

You could use an instance of DriverAdapterCPDS. This will need to add two libraries, Apache Commons Pool and Apache Commons DBCP. It is very useful when the driver you use does not include an implementation of connection pooling.

You can find a example in http://massapi.com/class/dr/DriverAdapterCPDS.html