Link to home
Start Free TrialLog in
Avatar of dkim18
dkim18

asked on

JDBC Driver implmentation??

I have asked a similar question in another thread but I didn't get an answer I need.

Let me try this again if I can get kick start my brain.
I need to create a JDBC driver which will connect to Mysql and oracle.
I have to implement the Driver, Connection, Statement classes etc.
What I am trying to do is I want to hide I am using two different JDBC drivers to mysql and oracle.
What I am doing is I am making a fake driver in turn it will connect to mysql and oracle driver which are availble from vendors.

When I load myDirver my calling
Class.ForName("MyDriver")
String URL ="MYFakeURL"

Connection con = DriverManger.getConnection(URL, info)
Statement stm = con.createStatement
These will be called from my application.

When this is called I would like to make connections to two drivers(mysql & oracle ) and keep open.
I want to use either mysql or oracle connection depends on the sql qeury which I will parse to get the table names and use the correct connection to connect to the database.

I guess I need to load these two drivers in Connection Class like these
Class.ForName("oracle.jdbc.driver.OracleDriver")
Class.ForName("com.mysql.jdbc.Driver")

Connection mySqlcon = DriverManger.getConnection("jdbc:mysql://128.220.101.71/MyId", info)
Connection Oraclecon = DriverManger.getConnection("jdbc:oracle:thin:@MyAddress:1521:Somthing", info)

I created those in My Connection Class constructor.
I noticed when I create connection. It is using myDriver class and instead of their driver classes.

How do I create these two connection and should I do this in MyConnection Class.
I am not really sure what I need to do.
THanks much
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

You just need a facade for the Connection by the sound of things. Just return the appropriate Connection based on parsing the sql given to the facade
Super-naive implementation:

public Connection getConnectionForQuery(String sql) {
      if (sql.toLowerCase().indexOf("mysqltable") > -1) {
            return mySqlConnection;
      }
      else return oracleConnection;
}
Avatar of mrigank
mrigank

>> Class.ForName("oracle.jdbc.driver.OracleDriver")
>> Class.ForName("com.mysql.jdbc.Driver")

Wont work together. Only one Driver will get registered at one time, both will not  get registered.

- first get all the Oracle Connections and keep them in a pool
Class.ForName("oracle.jdbc.driver.OracleDriver")
Connection Oraclecon = DriverManger.getConnection("jdbc:oracle:thin:@MyAddress:1521:Somthing", info)

- then get all the MySQL Connections and then add them to the pool
Class.ForName("com.mysql.jdbc.Driver")
Connection mySqlcon = DriverManger.getConnection("jdbc:mysql://128.220.101.71/MyId", info)

No need to create your own Driver. Create two connection pools of Oracle and MySQL connections as above. Hide them behind a factory.
Parse the SQL to find the database and fetch the appropriate connection.

ConnectionPoolFactory.getConnectionPool(dbType).getConnection();
Implement Connection like wrapper.
In your driver that you write, when you are issued to make connection, construct your own connection, and construct one Oracle and one Mysql connections, then pass them as params to your connection.

On close of your connection, invoke closing the inside connections.

Implement your create statement to return either Statement from ORacle or MySQL.

I think this is best solution.
Avatar of dkim18

ASKER

mrigank,

I need to create my Driver for this task.

Borislavmarkov,

Can you show me some code ?
I am trying to do what you have mentioned.
I try to create two connections in Connection Class and it didn't work. It tried to use my Driver class instead.

How do I create two connection? This is what I need in Constructor of Connection Class
Class.ForName("oracle.jdbc.driver.OracleDriver")
Class.ForName("com.mysql.jdbc.Driver")

Connection mySqlcon = DriverManger.getConnection("jdbc:mysql://128.220.101.71/MyId", info)
Connection Oraclecon = DriverManger.getConnection("jdbc:oracle:thin:@MyAddress:1521:Somthing", info)

How do I return this to my Driver class?
I need a little more push to tackle this. I am going nowhere with this.



>>In your driver that you write, when you are issued to make connection, construct your own connection, and construct one Oracle and one Mysql connections, then pass them as params to your connection.
Do I do this in Connection Class method??

>>On close of your connection, invoke closing the inside connections.

>>Implement your create statement to return either Statement from ORacle or MySQL.
This Statement Class is instantiated in Connection class createStatement.
How do I return eith oracled or mysql?

Sorry to ask for every details.
Thanks much
Avatar of dkim18

ASKER

>> Create two connection pools of Oracle and MySQL connections as above. Hide them behind a factory.


How do I do this??
ASKER CERTIFIED SOLUTION
Avatar of borislavmarkov
borislavmarkov

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
of course you have to touch the code to distinguish your needs.


you have to touch:

private void checkStatement(String sql) throws SQLException {
            if (innerStatement == null) {
                //DISTINGUISH
                if (sql.indexOf("dual") != -1){
                    innerStatement = conn.insideOra.createStatement();
                } else {
                    innerStatement = conn.insideMysql.createStatement();
                }
            }
        }


and also:

public Connection connect(String url, Properties info) throws SQLException {
            if (!acceptsURL(url)) return null;
            return new WrappedConnection(
                    DriverManager.getConnection("jdbc:oracle:thin:@xxx:1521:xxx", info),
                    DriverManager.getConnection("jdbc:oracle:thin:@xxx:1521:xxx", info)
            );
        }
I typed oracle because I don't have mysql installed. Second connection should be mysql connection.
You have to decide whether your statement is constantly Oracle statement for its life or Mysql depending of SQL you type. Or you keep both statements mysql and oracle opened and waiting. In the above sample I typed first variant. The first time I can distinguish ORacle syntax or mysql I keep the statement till its closing.
I don't see how these suggestions differ from the one i made ...
Sorry If they don't.
I think they are more detailed.
Avatar of dkim18

ASKER

borislavmarkov,

I will get back to you after understanding your wrapper code.
I realized again that I don't think like OOProgrammer.
Thanks much for now.

CEHJ,

I thank you too.



Avatar of dkim18

ASKER

borislavmarkov,

Thanks millions for your help.
I believe I am making a progress.
I have a few more questions

1) I am implementing PreparedStatement and CallableStatement classes.

public class WrapperStatement implements Statement
{
 WrapperConnection con;
Statement innerStatement;
public Statement(WrapperConnection con)
{
this.con = con
}

{
public class WrapperPreparedStatement implements PreparedStatement
{
WrapperConnection con;
public WrapperPreparedStatement(WrapperConnection con)
{
super(con);                                        <=== is this correct since preparedStatement is inherited from Statement
this.con = con;

}
}
public class WrapperCallableStatement implements CallableStatement
{
WrappeConnection con;

public WrapperCallableStatement(WrapperConnection con)
{
super(con);                                  <=== is this correct since CallableStatement is inherited from preparedStatement
this.con = con;
}
}

2) Am I executing queries from my oracle and mysql drivers I should do it like below, right?
In your example, you used execute but I need to use executeQuery and excuteUpdate methods because my applicatios already used those statements.

Public class WrapperConnection implements Connection
{

ResultSet rs;
int rowcount;

public ResultSet executeQuery(String Sql) throws java.sql.SQLException
{

  rs = con.executeQuery(sql)    <<======
return rs;
}


public int executeUpdate(String Sql) throws java.sql.SQLException
{
rowcount = con.executeUpdate(sql);   <<======

return rowcount;
}

}

Thanks much.
>> <=== is this correct since preparedStatement is inherited from Statement

No. CallableStatement ,PreparedStatement, Statement are interfaces, there is no super code to be executed.

>> <=== is this correct since CallableStatement is inherited from preparedStatement

Again no.

>> executeQuery(String Sql),executeUpdate(String Sql)
I thought the idea was to distinguish the syntax here ORA/MYSQL
Do  you have same data in two databases ?

public int executeUpdate(String Sql) throws java.sql.SQLException
{
//DISTINGUISH
                if (sql.indexOf("something here to distinguish" != -1){
                    return insideOra.executeUpdate();
                } else {
                    return insideMysql.executeUpdate();
                }
}


you write executeQuery similar.

I don't know your original idea. If you have in both databases same data how do you keep 2 databases with same data ?
I think if you tell me your problem from inside we may think to solve it without rewriting JDBC driver.