*
The moose likes JDBC and the fly likes JAVA SQL Joining 2 tables from 2 different connections with one single statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JAVA SQL Joining 2 tables from 2 different connections with one single statement" Watch "JAVA SQL Joining 2 tables from 2 different connections with one single statement" New topic
Author

JAVA SQL Joining 2 tables from 2 different connections with one single statement

michelle hassil
Greenhorn

Joined: May 27, 2012
Posts: 6

Please help me URGENT!!!

I would like to know how to conenct two different tables from 2 different connections using JDBC ODBC:
Normally what we do is
Connection conn = Driver Manger.getConnection(..jdbc odbc..)
and
Statement st =conn.execute query

But now i need to pass
connection conn2 =Driver.Manager.Getconnection(.jdbc odbc..)
conn2 also to statement st
Since i need to merge these two conn1 and conn2 .How DO I DO THIS?
My query will be something like select.....from table 1 (conn1),table 2(conn2) where.....
but i dont know how to pass these two objects to same statement and execute it once with same object.

Thanks in advance
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19651
    
  18

michelle hassil wrote:Please help me URGENT!!!

Please EaseUp. There is no such thing as urgent around here.

I would like to know how to conenct two different tables from 2 different connections using JDBC ODBC:
Normally what we do is
Connection conn = Driver Manger.getConnection(..jdbc odbc..)
and
Statement st =conn.execute query

But now i need to pass
connection conn2 =Driver.Manager.Getconnection(.jdbc odbc..)
conn2 also to statement st
Since i need to merge these two conn1 and conn2 .How DO I DO THIS?
My query will be something like select.....from table 1 (conn1),table 2(conn2) where.....
but i dont know how to pass these two objects to same statement and execute it once with same object.

Thanks in advance

You can't do this with two different connections. However, you may be able to do this slightly differently, if both databases are on the same database server*. You use one single connection, and use the fully qualified table name from the other database. For example, in MS SQL Server:

* In MS SQL Server, you can "import" a database from another MS SQL Server machine by using linked servers. The full database name then becomes LINKED_SERVER_NAME.DATABASE_NAME.SCHEMA_NAME.TABLE_NAME. For example, MyOtherServer.MyDatabase.dbo.MyTable. You won't be able to leave out the schema name as in my earlier example. Other database servers like Oracle and MySQL may have similar techniques.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
michelle hassil
Greenhorn

Joined: May 27, 2012
Posts: 6
I am doing it using JDBC ODBC Bridge driver.
THIS IS MY CODE
MY PROBLEM STARTS HERE,HOW TO GIVE THESE TWO CONNECTIONS INTO SHEET1 AND SHEET 2 A,HOW TO GIVE THIS OBJECT CONN1 AND CONN2 TO STATEMENT.COULD YOU PLEASE HELP ME CORRECT THE CODE A LITTLE PLEASEEE

Thanks in advance
michelle hassil
Greenhorn

Joined: May 27, 2012
Posts: 6

ALSO I WOULD LIKE TO MENTION THAT i am quite new to java,its been only a few months that i started coding(2-3 months). So please tell me if theres somethings i should refer and learn before i do this if its a lot.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19651
    
  18

I already told you, you cannot join tables from two different connections. Believe me, I tried once. I got some nasty SQL errors.

Also, could you please UseCodeTags? Thanks. Also, please KeepItDown. There's no need for all-uppercase in posts.
michelle hassil
Greenhorn

Joined: May 27, 2012
Posts: 6
import java.sql.*;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class retrieveexcel1 {
//connecting to database using driver jdbc odbc
public static void connectToDatabase() throws SQLException, InstantiationException, IllegalAccessException, IOException
{
// Attempt to load database driver
try
{
// Load Sun's jdbc-odbc driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
System.out.println("loaded");
}
catch (ClassNotFoundException cnfe) // driver not found
{
System.err.println ("Unable to load database driver");
System.err.println ("Details : " + cnfe);
System.exit(0);
}
}
//setting up and getting connection to excel sheets
public static Connection setconnection() throws SQLException
{
Connection conn1 = DriverManager.getConnection("jdbcdbc:trialchr1mergedhg18andhg19","","");
return conn1;
}
public static Connection getconnection() throws SQLException
{
return setconnection();
}
public static Connection setconnection2() throws SQLException
{
Connection conn2 = DriverManager.getConnection("jdbcdbc:chr1tobemapped","","");
return conn2;
}
public static Connection getconnection2() throws SQLException
{
return setconnection2();
}
//return conn1;

//}
//querying and writing result set to a new file
public static void retrieve( Connection c,Connection c1) throws SQLException, IOException {
Connection conn1=c;
Connection conn2= c1;
String query="Select chrom,hg18strt,hg18end,hg19strt,hg19end from [sheet1$],[sheet2$]"
+"where hg18strt='491'";

Statement st = (Statement) conn1.createStatement();


ResultSet rs=((java.sql.Statement) st).executeQuery(query);
System.out.println("done");
String home=System.getProperty("user.dir")+"/";
System.out.println(home);
File f = new File(home+"merged.txt");
System.out.println("created file");
FileWriter fstream = new FileWriter(f);
BufferedWriter out = new BufferedWriter (fstream);


while(rs.next()){
out.write(rs.getString("hg18strt"));
out.newLine();
}

out.close();
rs.close();
st.close();
}

//closing both connecton
public static void closeConnection(java.sql.Connection c,java.sql.Connection c1) {
try {
c.close();
c1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//main class
public static void main(String[] args) throws SQLException, IOException, InstantiationException, IllegalAccessException {
retrieveexcel1 re = new retrieveexcel1();
re.connectToDatabase();
java.sql.Connection c =getconnection();
java.sql.Connection c1 =getconnection2();
System.out.println("connecting");
retrieve(c,c1);
closeConnection(c,c1);
System.out.println("connected");
}

}
DEar Rob,
I understood what you tried telling me that i cant do this way but i really don't get it how to do it.Can you explain me step by step starting with my 2 excel files. as these files some of it was experimentally generated and not just from database. I did'nt get the part of mysql server because here i did not try connecting it to mysql server
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

What you will have to do is
1, run a select against 1 connection, The select will be only based upon the tables on that database.
2, get a row from the result set
3, use the results from the first select in the select using the second connection.

here is psuedo code
michelle hassil
Greenhorn

Joined: May 27, 2012
Posts: 6
Thank you so much Rob.i am working on code.

I also have another doubt, when we use excel files using JDBC ODBC bridge driver , and query it should we call the table as sheet1$??? because when i tried using the name of my excel sheet it does not work gives some errors.and i am confused since now i have 2 excel files , can i call it sheet2$ and sheet1$ , or by names .

Please help
michelle hassil
Greenhorn

Joined: May 27, 2012
Posts: 6
Is there something called HasNext???
When i give resultset object.hasnext it is asking me to change to next !!!
also after getting to connection while giving connect1.statement(query), it is asking me to cast statement , change type to cast. I dont understand this.

public static void retrieve() throws sqlexception,iOEXCEPTION
{
connection conn1 =getconnection() //i already have a method of set n get where get method has a return statement n it will get connection
connection conn1 =getconnection2()
Statement st1=conn1.getStatement(""my query"")//but over here it asked me to cast to prepare statement n i avoided errors thpough i dont get it why.
Resultset rs1=st1.select() //unavoidable errror , it says to cast st1/select is undefined statement for statement
and then rs1.hasnext...it says change to next
}
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Please read Oracle's JDBC tutorial. You don't need to read it in a specific order, there is a section on ResultSets, for example, but in the end, you should read all of it if you want to use JDBC.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19651
    
  18

I wouldn't use JDBC to read an Excel file. I'd use a proper Excel reading library like Apache POI or JExcelAPI.
 
wood burning stoves
 
subject: JAVA SQL Joining 2 tables from 2 different connections with one single statement
 
Similar Threads
Multiple databases in a single query
jdbc transaction mangement and behaviour
Network Adapter
file export
Transactions