CODEWITHSUNDEEP
javamysqljsch
user9109814
user9109814

Reputation: 175

Connection Pooling MySQL Connection with SSH(JSch)

I am developing a java project for university that connects to a MySQL database. I've set up my connection no problem with Jsch taking care of the SSH part. Now when I insert data into my database it's very slow as it reopens the connection each time.

My question is what is the best(easiest) way for me to fix my speed problem? I've been reading about connection pooling but all the answers are different and I am unsure how to apply any of them to my own code. Would I need to create two pools, one for the SSH and one for MySQL connection?

Here is the connection part of my code, appreciate any help.

int lport=5656;
String rhost="REDACTED";
String host="REDACTED"; //
int rport=3306;
String user="REDACTED";
String password="REDACTED";
String dbuserName = "REDACTED";
String dbpassword = "REDACTED";
String url = "jdbc:mysql://localhost:"+lport+"/db";
String driverName="com.mysql.jdbc.Driver";
Connection conn = null;
Session session= null;
Statement stmt = null;

public void init() throws SQLException {
    try{
        //Set StrictHostKeyChecking property to no to avoid UnknownHostKey issue
        java.util.Properties config = new java.util.Properties(); 
        config.put("StrictHostKeyChecking", "no");
        JSch jsch = new JSch();
        session=jsch.getSession(user, host, 21098);
        session.setPassword(password);
        session.setConfig(config);
        session.connect();
        System.out.println("Connected");
        int assinged_port=session.setPortForwardingL(lport, rhost, rport);
        System.out.println("localhost:"+assinged_port+" -> "+rhost+":"+rport);
        System.out.println("Port Forwarded");

        //mysql database connectivity
        Class.forName(driverName).newInstance();
        conn = DriverManager.getConnection (url, dbuserName, dbpassword);
        System.out.println ("Database connection established");
        System.out.println("DONE");

Upvotes: 1

Views: 633

Answers (1)

arjunsv3691
arjunsv3691

Reputation: 829

To avoid re-connecting database, you can make use of apache commons library it has DBCP package which enables you to create pool, you can check this sample code: 

import java.sql.Connection;

import org.apache.commons.dbcp.BasicDataSource;

public class DBConnectionPool() throws SQLException {

  public void init() throws SQLException {
   try {


    //Set StrictHostKeyChecking property to no to avoid UnknownHostKey issue
    java.util.Properties config = new java.util.Properties();
    config.put("StrictHostKeyChecking", "no");
    JSch jsch = new JSch();
    session = jsch.getSession(user, host, 21098);
    session.setPassword(password);
    session.setConfig(config);
    session.connect();
    System.out.println("Connected");
    int assinged_port = session.setPortForwardingL(lport, rhost, rport);
    System.out.println("localhost:" + assinged_port + " -> " + rhost + ":" + rport);
    System.out.println("Port Forwarded");

    BasicDataSource basicDataSource = new BasicDataSource();

    basicDataSource.setDriverClassName(driverName);
    basicDataSource.setUrl(url);
    basicDataSource.setUsername(dbuserName);
    basicDataSource.setPassword(dbpassword);

    basicDataSource.setMaxActive(10);
    basicDataSource.setMaxIdle(5);
    basicDataSource.setMaxWait(100 * 4);
    Connection conn = basicDataSource.getConnection()

   }
  }

Upvotes: 1

Related Questions