asked on

what is mysql connection pool

Hi,
I have a nodejs MVC project in which a myql connection pool is created. Although i am working on a similar Spring MVC java project in which i have used JDBC. wondering whats the use of creating a mysql connection pool and how to do the same in spring MVC.

Here is node js code which create connection pool :

var createConnection = function () {
    var pool = mysql.createPool({
        connectionLimit: 10,
        host: config.db.host,
        user: config.db.user,
        password: config.db.password
    });
    pool.on("connection", function (connection) {
        connection.query("USE " + config.db.database, function (error, result) {
            if (error) {
                util.log("Couldn't USE db because: " + error);
            }
        });
    });
    return pool;
}
var connection = createConnection();

Open in new window

whereas in my spring MVC application i am having the following code in root-context.xml which creates connection and i autowire the things and use it directly in my code :

 <context:property-placeholder
            location="classpath:properties/${FLOCK_APPS_CONFIG}/database.properties"
            ignore-unresolvable="true"/>

    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="${snippet.jdbc.url}"/>
        <property name="username" value="${snippet.jdbc.username}"/>
        <property name="password" value="${snippet.jdbc.password}"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="ds"></property>
    </bean>

Open in new window

I want to create a connection pool in my spring MVC code .
Please let me know how to do it and what are the advantages ?

Thanks

ASKER CERTIFIED SOLUTION

mccarl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mccarl

You can go for a higher version if you don't need to support older Java/JDBC versions. The main DBCP page that I linked to above gives the list of what versions are supported under which versions of Java/JDBC.

Rohit Bajaj

ASKER

Here is what i understood please correct my understanding :
1) In my code there is only one connection maintained to the mysql server... ?
If the connection gets broken will it try to reconnect automatically ?

2) In maintain connection pool as i understand the application will maintain say 5 connections to the same mysql server and if there is a db hit it will check if a connection is alive otherwise it will give another alive connection from the pool ?
what happens if a connection in a pool of 5 connections get broken ? does it automatically get renewed in background ?

Thanks

SOLUTION

mccarl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rohit Bajaj

ASKER

Thanks I will go through the link.
Also in my case where i am using DriverManagerDataSource and JdbcTemplate .. Is this ThreadSafe ?
What if two threads try to upate the same row ... In this case each will be opening a new connection... and then performing an update...
I guess this case will be handled by Database itself ?

Rohit Bajaj

ASKER

When you say SingleConnectionDataSource is not ThreadSafe and it maintains a single connection...
what i understand is that during multiple hits to web Application There will be multiple DB hits and for each user there will be a different thread performing it. so different threads can cause same row to update... may result in conflict....

SOLUTION

mccarl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rohit Bajaj

ASKER

Hi,
Is there any way to verify like in my current codebase that each time i am making a query a new connection is made and then closed ?
something like i use hashcode of the connection object..and see it changes with every request.

SOLUTION

mccarl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rohit Bajaj

ASKER

Hi,
I am using the dbcp2 like :

 <bean id="ds" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="${snippet.jdbc.url}"/>
        <property name="username" value="${snippet.jdbc.username}"/>
        <property name="password" value="${snippet.jdbc.password}"/>
        <property name="initialSize" value="10"/>
    </bean>

Open in new window

included the following in pom.xml

 <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.1.1</version>
        </dependency>

Open in new window

I need to understand what other parameters i should put in the bean apart from initialSize ?
As per my understanding initialSize=10 will create 10 connection objects.
If i see on https://commons.apache.org/proper/commons-dbcp/apidocs/index.html
it says - The pool is initialized the first time one of the following methods is invoked: getConnection, setLogwriter, setLoginTimeout, getLoginTimeout, getLogWriter

* So does that mean that the connection wont be ready beforehand and pool of 10 connections will be made after the first query to database ?

* what are borrows connection as mentioned in setMaxTotal

* How do i set Max number of connections top limit ? Will just setting initial size to 10 mean maximum connection objects will be 10 and does dbcp automatically renews a connection object if the connection gets broken ?

* what exactly is abandoned connection do i need to specify something to remove it ?