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
dkim18Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
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
0
CEHJCommented:
Super-naive implementation:

public Connection getConnectionForQuery(String sql) {
      if (sql.toLowerCase().indexOf("mysqltable") > -1) {
            return mySqlConnection;
      }
      else return oracleConnection;
}
0
mrigankCommented:
>> 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();
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

borislavmarkovCommented:
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.
0
dkim18Author Commented:
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
0
dkim18Author Commented:
>> Create two connection pools of Oracle and MySQL connections as above. Hide them behind a factory.


How do I do this??
0
borislavmarkovCommented:
Minimal implementation, so big, sorry:



import java.sql.*;
import java.util.Map;
import java.util.Properties;

public class Dummy {
    public static void main(String[] args)
            throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        DriverManager.registerDriver(new MyDriver());


        Connection c = DriverManager.getConnection("fake:MYFake", "xx","xx");
        Statement st = c.createStatement();
        st.execute("select 3 from dual");
        ResultSet rs = st.getResultSet();
        rs.next();
        System.out.println(rs.getInt(1));
        c.close();
    }

    public static class MyDriver implements Driver {
        public int getMajorVersion() {
            return 0;
        }

        public int getMinorVersion() {
            return 0;
        }

        public boolean jdbcCompliant() {
            return false;
        }

        public boolean acceptsURL(String url) throws SQLException {
            System.out.println("url = " + url);
            if (url.startsWith("fake")) {
                return true;
            } else {
                return false;
            }
        }

        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)
            );
        }

        public DriverPropertyInfo[] getPropertyInfo(String url, Properties info) throws SQLException {
            return new java.sql.DriverPropertyInfo[0];
        }
    };
    public static class WrappedConnection implements Connection {
        Connection insideOra;
        Connection insideMysql;

        public WrappedConnection(Connection insideOra, Connection insideMysql) {
            this.insideOra = insideOra;
            this.insideMysql = insideMysql;
        }

        public int getHoldability() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public int getTransactionIsolation() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void clearWarnings() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void close() throws SQLException {
            insideMysql.close();
            insideOra.close();
        }

        public void commit() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void rollback() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public boolean getAutoCommit() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public boolean isClosed() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public boolean isReadOnly() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void setHoldability(int holdability) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void setTransactionIsolation(int level) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void setAutoCommit(boolean autoCommit) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void setReadOnly(boolean readOnly) throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public String getCatalog() throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public void setCatalog(String catalog) throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public DatabaseMetaData getMetaData() throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public SQLWarning getWarnings() throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public Savepoint setSavepoint() throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public void releaseSavepoint(Savepoint savepoint) throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public void rollback(Savepoint savepoint) throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public Statement createStatement() throws SQLException {
            return new WrappedStatement(this);

        }

        public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public Map getTypeMap() throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public void setTypeMap(Map map) throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public String nativeSQL(String sql) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public CallableStatement prepareCall(String sql) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public PreparedStatement prepareStatement(String sql) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public PreparedStatement prepareStatement(String sql, int columnIndexes[]) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public Savepoint setSavepoint(String name) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }

        public PreparedStatement prepareStatement(String sql, String columnNames[]) throws SQLException {
            //return null;
            throw new UnsupportedOperationException();
        }
    }

    public static class WrappedStatement implements Statement {
        WrappedConnection conn;
        private Statement innerStatement;

        public WrappedStatement(WrappedConnection conn) {
            this.conn = conn;
        }

        public int getFetchDirection() throws SQLException {
            //return 0;
            throw new UnsupportedOperationException();
        }

        public int getFetchSize() throws SQLException {
            //return 0;
            throw new UnsupportedOperationException();
        }

        public int getMaxFieldSize() throws SQLException {
            //return 0;
            throw new UnsupportedOperationException();
        }

        public int getMaxRows() throws SQLException {
            //return 0;
            throw new UnsupportedOperationException();
        }

        public int getQueryTimeout() throws SQLException {
            //return 0;
            throw new UnsupportedOperationException();
        }

        public int getResultSetConcurrency() throws SQLException {
            //return 0;
            throw new UnsupportedOperationException();
        }

        public int getResultSetHoldability() throws SQLException {
            //return 0;
            throw new UnsupportedOperationException();
        }

        public int getResultSetType() throws SQLException {
            //return 0;
            throw new UnsupportedOperationException();
        }

        public int getUpdateCount() throws SQLException {
            //return 0;
            throw new UnsupportedOperationException();
        }

        public void cancel() throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public void clearBatch() throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public void clearWarnings() throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public void close() throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public boolean getMoreResults() throws SQLException {
            //return false;
            throw new UnsupportedOperationException();
        }

        public int[] executeBatch() throws SQLException {
            //return new int[0];
            throw new UnsupportedOperationException();
        }

        public void setFetchDirection(int direction) throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public void setFetchSize(int rows) throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public void setMaxFieldSize(int max) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void setMaxRows(int max) throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public void setQueryTimeout(int seconds) throws SQLException {
            //
            throw new UnsupportedOperationException();
        }

        public boolean getMoreResults(int current) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void setEscapeProcessing(boolean enable) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public int executeUpdate(String sql) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void addBatch(String sql) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public void setCursorName(String name) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public boolean execute(String sql) throws SQLException {
            checkStatement(sql);
            return innerStatement.execute(sql);
        }

        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();
                }
            }
        }

        public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public boolean execute(String sql, int autoGeneratedKeys) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public int executeUpdate(String sql, int columnIndexes[]) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public boolean execute(String sql, int columnIndexes[]) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public Connection getConnection() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public ResultSet getGeneratedKeys() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public ResultSet getResultSet() throws SQLException {
            return this.innerStatement.getResultSet();
        }

        public SQLWarning getWarnings() throws SQLException {
            throw new UnsupportedOperationException();
        }

        public int executeUpdate(String sql, String columnNames[]) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public boolean execute(String sql, String columnNames[]) throws SQLException {
            throw new UnsupportedOperationException();
        }

        public ResultSet executeQuery(String sql) throws SQLException {
            throw new UnsupportedOperationException();
        }
    }
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
borislavmarkovCommented:
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.
0
CEHJCommented:
I don't see how these suggestions differ from the one i made ...
0
borislavmarkovCommented:
Sorry If they don't.
I think they are more detailed.
0
dkim18Author Commented:
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.



0
dkim18Author Commented:
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.
0
borislavmarkovCommented:
>> <=== 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.