CODEWITHSUNDEEP
javamysqljdbcconnection-poolinghikaricp
Santhosh Athreya
Santhosh Athreya

Reputation: 25

Connection closed error - MySQL , JDBC and Hikaricp

Have implemented a DAO pattern with Hikari connection pool Made the best use of the DAO design pattern. Have created singleton class which returns the object of a class with a public connection if the object of the singleton class is NULL> object is again created thereby getting the connection queries to call the static method of the singleton class to obtain public connection and prepared statements are closed in try-with-resources. application is live for more than 12hrs after few requests queries are not getting executed "The connection is closed."

public class DatabaseConnection {

//Constants 

String url = "jdbc:mysql://localhost:3306/";
String driver="com.mysql.jdbc.Driver";
String userName = "root";
String password = "#";

private static HikariDataSource dataSource;

public Connection conn;
public static DatabaseConnection db;
/**
 * A static method which uses HikariDataSource Connection Pooling library to connect with the MySQL Database. 
 * Accepts and Sets few JDBC details like User Name,Password,URL,Driver Name and many more.
 * @return This methods returns HikariDataSource 
 * @throws SQLException and CasbnException
 */

public DatabaseConnection() throws SQLException,CasbnExceptions {
    
    try {
        
        //create the object of HikariDataSource
        dataSource=new HikariDataSource();
        
        //set up the JDBC details (username,password,url,driver)
        System.out.println("Inside DatabaseConnection constructor..");
        
        dataSource.setDriverClassName(driver);
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        
        this.conn=dataSource.getConnection();
        
        
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }

}

public static  DatabaseConnection getCon() throws CasbnExceptions, SQLException
{
    if(db==null)
    {
        System.out.println("Inside getCon() and if block...");
        db=new DatabaseConnection();
    }
    
    
    return db;
}
}


 public class DAO_Implementation implements DAOFactory {


//Declare all the SQL queries as private static and final 

//getCompany query 
private static final String getCompanyName="SELECT company_name FROM #.company_register where email=?";


@Override
public String getCompanyName(String Email) throws SQLException {

    try
    (PreparedStatement pst = DatabaseConnection.getCon().conn
            .prepareStatement(getCompanyName)){

   pst.setString(1, Email);
    ResultSet rs = pst.executeQuery();
    
    
    rs.next();
    
    Name=rs.getString(1);
    

   }
    catch(Exception e)
    {
        e.printStackTrace();
    }
    return Name;
}
@Override
public Pojos Individual_Company_detail(String CompanyName) throws SQLException{

    try
    (PreparedStatement pst = DatabaseConnection.getCon().conn
            .prepareStatement(CompanyDetail);){
    pst.setString(1, CompanyName);
  
    ResultSet rs = pst.executeQuery();

    if(rs.next()==true)
    {
    Pojos Po=new Pojos();
    
    Po.setCompanyID(rs.getInt(1));
    Po.setUserCount(rs.getInt(2));
    Po.setPlan(rs.getString(3));
    Po.setDateofSub(rs.getString(4));
    Po.setSubscriptionID(rs.getString(5));
    Po.setVaildTill(rs.getString(6));
    
    
    
    return Po;
    
    }

    else
    {
        Pojos Po=new Pojos();
        Po.setErrorMessage(CompanyName +" Has not registered to of our Plan and no Recent Transactions");
       
        
        
        return Po;
        
    }
    
    
    }  

}

Upvotes: 1

Views: 4807

Answers (2)

Ori Marko
Ori Marko

Reputation: 58772

Currently you are returning the same connection all the time using .conn

remove conn from DatabaseConnection and add a different method which getConnection from Hikari datasource every time

This way Hikari will handle connection pooling

public static Connection getConnection() {
   return dataSource.getConnection();
}

And open Connection resource separately so it will be closed:

 try 
(Connection conn = DatabaseConnection.getConnection();
    PreparedStatement pst = conn.prepareStatement(CompanyDetail))

Upvotes: 2

Santhosh Athreya
Santhosh Athreya

Reputation: 25

public class DatabaseConnection {
String url = "jdbc:mysql://localhost:3306/";
//String dbName = ""
String driver="com.mysql.jdbc.Driver";
String userName = "root";
String password = "";

private static HikariDataSource dataSource;

public static DatabaseConnection db;

public DatabaseConnection() throws SQLException,CasbnExceptions {
    
    try {
        
        //create the object of HikariDataSource
        dataSource=new HikariDataSource();
        
        //set up the JDBC details (username,password,url,driver)
        System.out.println("Inside DatabaseConnection constructor..");
        
        dataSource.setDriverClassName(driver);
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        
    
        
        
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }

}

public static Connection getConnection() throws SQLException
{
    if(db==null)
    {
        db=new DatabaseConnection();
    }
    return dataSource.getConnection();
}

}

Upvotes: 0

Related Questions