Camel jdbc: How can I get a reset data source if mysql connection closes

Question

We developed the Camel package (deployed to Karaf), which is expected to retrieve data from MySQL every 24 hours and click on S3. But since MySQL internally closes the connection if it has been idle for 8 hours, therefore, at the next scheduled execution, it starts to throw an error. See snippets of our code below.

Properties:

MySqlDriver=com.mysql.jdbc.Driver
MySqlDatabaseURL=jdbc:mysql://x.x.x.x/dbname?autoReconnect=true
MySqlUsername=sm*****
MySqlPassword=*******

Activator:

public class Activator implements BundleActivator {

    public CamelContext context = null;

    public void start(BundleContext bundleContext) throws Exception {
        DataSource dataSource = UDMSUtils.createDataSource(UDMSUtils.getProperty(UDMSConstants.MYSQL_DATABASE_URL));

        SimpleRegistry simpleRegistry = new SimpleRegistry();
        simpleRegistry.put(UDMSConstants.UDMS_DATA_SOURCE, dataSource);

        context = new OsgiDefaultCamelContext(bundleContext, simpleRegistry);
        context.addRoutes(new CreativeRoutes());
        context.start();
    }

}

Building data source:

public static DataSource createDataSource(String connectURI) {
    BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(getProperty(UDMSConstants.MYSQL_DRIVER));
    ds.setUsername(getProperty(UDMSConstants.MYSQL_USERNAME));
    ds.setPassword(getProperty(UDMSConstants.MYSQL_PASSWORD));
    ds.setUrl(connectURI);
    ds.setMaxWait(-1);  // Waits indefinately
    return ds;
}

Routes

from("timer://Timer?repeatCount=1").to("direct:record_count").end();

from("direct:record_count")
    .process(new Processor() {
        @Override
        public void process(Exchange exchange) throws Exception {
            exchange.getIn().setBody(query);
        }
    })    
    .routeId("record_count")
    .to("jdbc:" + UDMSConstants.UDMS_DATA_SOURCE)
    .process(new Processor() {
        @Override
        public void process(Exchange exchange) throws Exception {
            // ...
        }
    );

Can anyone suggest what changes need to be done in the above code so that the connection remains active as long as we need.

Please note: we do not have rights to change mysql.properties, so we need to process this in our code.

Answer 1

. VikingSteve , . OSGI Blueprint, XML, .

1) DBCP Apache Commons pom:

<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>

2) / :

<bean id="MydataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" scope="singleton" >
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://DB-001:3306/Customer"/>
    <property name="username" value="sys_ETL"/>
    <property name="password" value="Blah"/>
    <property name="initialSize" value="4"/>
    <property name="maxActive" value="32"/>
    <property name="maxIdle" value="16"/>
    <property name="minIdle" value="8"/>
    <property name="timeBetweenEvictionRunsMillis" value="1800"/>
    <property name="minEvictableIdleTimeMillis" value="1800"/>
    <property name="testOnBorrow" value="true"/>
    <property name="testWhileIdle" value="true"/>
    <property name="testOnReturn" value="true"/>
    <property name="validationQuery" value="SELECT 1"/>
    <property name="maxWait"  value="1000"/>
    <property name="removeAbandoned" value="true"/>
    <property name="logAbandoned" value="true"/>
    <property name="removeAbandonedTimeout" value="30000"/>
</bean>

bean, . bean Mydatasource. . , . , , .

3) POJO :

public class AccountInformationToDatabase {


private BasicDataSource dataSource;
public BasicDataSource getDataSource() {
    return dataSource;
}
public void setDataSource(BasicDataSource dataSource) {
    this.dataSource = dataSource;
}
@Handler
public void PersistRecord
(
        @Body AccountRecordBindy msgBody
        , @Headers Map hdr
        , Exchange exch
) throws Exception
{

    Connection conn = null;
    PreparedStatement stmt=null;



    try 
    {


        conn= dataSource.getConnection();
        stmt =conn.prepareStatement("SOME INSERT STATEMENT");   

        stmt.setString(1,msgBody.getAccountNumber().trim());
        stmt.setString(2,msgBody.getRecordType().trim() );
        stmt.setString(3,msgBody.getSequenceNumber().trim());
        stmt.setString(4,msgBody.getTitle().trim());
        stmt.setString(5,msgBody.getCustomerType().trim());
        stmt.setString(6,msgBody.getName().trim());
        stmt.setString(7,msgBody.getAccountAddress1().trim());


        stmt.executeUpdate();        






    }
    catch (Exception e)
    {

        throw new Exception(e.getMessage());

    }

    finally
    {
        try
        {
                if (stmt!=null)
                {
                    stmt.close();
                    stmt= null;
                }
                if (conn!=null)
                {
                    conn.close();
                    conn= null;
                }
        }
        catch(SQLException e)
        {

            throw new Exception(e.getMessage());

        }

    }


}

}

POJO , datasource, org.apache.commons.dbcp.BasicDataSource. Mydatasource bean POJO, .

4) POJO bean :

<bean id="AccountPersist"   class="AccountInformationToDatabase">
    <property name="dataSource" ref="MydataSource"/>
</bean>

, ..

Answer 2

JDBC, HikariCP. jdbc4ConnectionTest connectionTestQuery, , . connectionTestQuery:

"" , API JDBC4 Connection.isValid(). , , , . , (, "VALUES 1" )