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.Or acleDriver ")
Class.ForName("com.mysql.j dbc.Driver ")
Connection mySqlcon = DriverManger.getConnection ("jdbc:mys ql://128.2 20.101.71/ MyId", info)
Connection Oraclecon = DriverManger.getConnection ("jdbc:ora cle:thin:@ MyAddress: 1521:Somth ing", 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
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
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
Class.ForName("com.mysql.j
Connection mySqlcon = DriverManger.getConnection
Connection Oraclecon = DriverManger.getConnection
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
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(Stri ng sql) {
if (sql.toLowerCase().indexOf ("mysqltab le") > -1) {
return mySqlConnection;
}
else return oracleConnection;
}
public Connection getConnectionForQuery(Stri
if (sql.toLowerCase().indexOf
return mySqlConnection;
}
else return oracleConnection;
}
>> Class.ForName("oracle.jdbc .driver.Or acleDriver ")
>> Class.ForName("com.mysql.j dbc.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.Or acleDriver ")
Connection Oraclecon = DriverManger.getConnection ("jdbc:ora cle:thin:@ MyAddress: 1521:Somth ing", info)
- then get all the MySQL Connections and then add them to the pool
Class.ForName("com.mysql.j dbc.Driver ")
Connection mySqlcon = DriverManger.getConnection ("jdbc:mys ql://128.2 20.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.getC onnectionP ool(dbType ).getConne ction();
>> Class.ForName("com.mysql.j
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
Connection Oraclecon = DriverManger.getConnection
- then get all the MySQL Connections and then add them to the pool
Class.ForName("com.mysql.j
Connection mySqlcon = DriverManger.getConnection
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.getC
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.
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.
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.Or acleDriver ")
Class.ForName("com.mysql.j dbc.Driver ")
Connection mySqlcon = DriverManger.getConnection ("jdbc:mys ql://128.2 20.101.71/ MyId", info)
Connection Oraclecon = DriverManger.getConnection ("jdbc:ora cle:thin:@ MyAddress: 1521:Somth ing", 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
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
Class.ForName("com.mysql.j
Connection mySqlcon = DriverManger.getConnection
Connection Oraclecon = DriverManger.getConnection
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
ASKER
>> Create two connection pools of Oracle and MySQL connections as above. Hide them behind a factory.
How do I do this??
How do I do this??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.createState ment();
} else {
innerStatement = conn.insideMysql.createSta tement();
}
}
}
and also:
public Connection connect(String url, Properties info) throws SQLException {
if (!acceptsURL(url)) return null;
return new WrappedConnection(
DriverManager.getConnectio n("jdbc:or acle:thin: @xxx:1521: xxx", info),
DriverManager.getConnectio n("jdbc:or acle: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.
you have to touch:
private void checkStatement(String sql) throws SQLException {
if (innerStatement == null) {
//DISTINGUISH
if (sql.indexOf("dual") != -1){
innerStatement = conn.insideOra.createState
} else {
innerStatement = conn.insideMysql.createSta
}
}
}
and also:
public Connection connect(String url, Properties info) throws SQLException {
if (!acceptsURL(url)) return null;
return new WrappedConnection(
DriverManager.getConnectio
DriverManager.getConnectio
);
}
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.
I think they are more detailed.
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.
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.
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(WrapperConnectio n con)
{
this.con = con
}
{
public class WrapperPreparedStatement implements PreparedStatement
{
WrapperConnection con;
public WrapperPreparedStatement(W rapperConn ection con)
{
super(con); <=== is this correct since preparedStatement is inherited from Statement
this.con = con;
}
}
public class WrapperCallableStatement implements CallableStatement
{
WrappeConnection con;
public WrapperCallableStatement(W rapperConn ection 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.
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(WrapperConnectio
{
this.con = con
}
{
public class WrapperPreparedStatement implements PreparedStatement
{
WrapperConnection con;
public WrapperPreparedStatement(W
{
super(con); <=== is this correct since preparedStatement is inherited from Statement
this.con = con;
}
}
public class WrapperCallableStatement implements CallableStatement
{
WrappeConnection con;
public WrapperCallableStatement(W
{
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.
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.