Question

Write a program that creates a Singleton class to connect to two databases. The program must provide two instances of this class. One instance for connecting to MySQL database and the other for connecting to Oracle database.

Answer 1

HI,

As per your question you want two connection from singleton class but then what the meaning of singleton class?

Having a Singleton which will return many connections defies the purpose of the Singleton, whose task is to provide the same instance whenever it is called.the main purpose of singleton class to provide one instance only.

i am attaching code to create a singleton class and you can chnage the DB connection parameters in that class as per Oracle or MySql.

//DB connection singleton class

public class DBConnection{

    private static DBConnection instance;
    private String url="jdbc:oracle://localhost:3306/";
    private String login="root";
    private String pass="root";
        String dbName = "mydb";

        // if you need mysql connection then uncomment below details
        
    //private String url="jdbc:mysql://localhost:3306/";
   
         
    private DBConnection(){

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        if (instance == null) {
            instance = new DBConnection();
            System.out.println(" Connection  - - - - - - - -  New DBConnection created");
        }
        try {
            return DriverManager.getConnection(instance.url+instance.dbName, instance.login,instance.pass);
        } catch (SQLException e) {
            throw e;
        }
    }

    public static void close(Connection connection)
    {
        try {
            if (connection != null) {
                connection.close();
                connection=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

Main class:

import java.sql.*;


public class CreateConnection {

    public static void main(String[] args) {
        Connection c = null;
            String sql = "SELECT * FROM employees";
            try {
                c = DBConnection.getConnection();
                Statement s = c.createStatement();
                ResultSet rs = s.executeQuery(sql);
                while (rs.next()) {
                    System.out.println("Print row here...");
                }
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                DBConnection.close(c);
            }
    }
}

But there could be a way to create two instance of different database by supplying arrgument in getInstance(Database d) method of singleton class but it will only create single instance for any one DB which is main purpose of singleton class.

If you have any questions or any doubt then please feel free to ask questions by comment box.

Thanks