?
Solved

JDBC Driver implmentation??

Posted on 2006-04-11
13
Medium Priority
?
651 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:dkim18
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 16426954
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 16426980
Super-naive implementation:

public Connection getConnectionForQuery(String sql) {
      if (sql.toLowerCase().indexOf("mysqltable") > -1) {
            return mySqlConnection;
      }
      else return oracleConnection;
}
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16427041
>> 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:borislavmarkov
ID: 16427051
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
 

Author Comment

by:dkim18
ID: 16427282
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
 

Author Comment

by:dkim18
ID: 16427419
>> Create two connection pools of Oracle and MySQL connections as above. Hide them behind a factory.


How do I do this??
0
 
LVL 2

Accepted Solution

by:
borislavmarkov earned 2000 total points
ID: 16427728
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
 
LVL 2

Expert Comment

by:borislavmarkov
ID: 16427787
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 16427809
I don't see how these suggestions differ from the one i made ...
0
 
LVL 2

Expert Comment

by:borislavmarkov
ID: 16427839
Sorry If they don't.
I think they are more detailed.
0
 

Author Comment

by:dkim18
ID: 16427968
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
 

Author Comment

by:dkim18
ID: 16437545
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
 
LVL 2

Expert Comment

by:borislavmarkov
ID: 16443169
>> <=== 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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question