How to implement connection-pooling efficiently?

Question

My code to connect to database is using BasicDataSource as following, referring to Ans I suppose my code creates a physical connection to database ONCE and then provide pool connections afterward. My question is that, does my code provide pool connection?, if it does, should I keep the connection section of the code in the following method or in the constructor to provide efficient pooling ? and how should be the annotations for the state of the class ? @Stateful?

        // Connection section start
        BasicDataSource bs = new BasicDataSource();
        PreparedStatement ps = null;
        Connection con = null;
        try {
            bs.setDriverClassName("com.mysql.jdbc.Driver");
            bs.setUrl("jdbc:mysql://localhost/myDb");
            bs.setUsername("root");
            bs.setPassword("");
            con = bs.getConnection();
            System.out.println("Connecting");
        // Connection section end

            ps = con.prepareStatement("Select *"
                    + " from Client WHERE Username = ? ");
            ps.setString(1, username);
            ResultSet r = ps.executeQuery();
            if (r.next()) {
                con.close();
                return "true";
            }

Use the Apache Commonds DBCP connection pool. Don't write this stuff yourself. — user207421, Feb 12 '13 at 01:12

score 4 · Accepted Answer · answered Feb 12 '13 at 07:22

BasicDataSource implements connection pooling. However, if you create a new pool each time then it's useless. Create the data source once and reuse it everywhere.

Usually, in Java EE you inject the data source. That is, the container manages the data source for you. Injecting a datasource in a bean is as simple as

@Resource
private DataSource movieDatabase;

See this example. Configuring the datasource depends on the container you are using, though. The same data source can be used for stateful and statless beans.

How to implement connection-pooling efficiently?

1 Answers1