Joining two databases in Java with mysql queries?

Question

I am trying to run an SQLquery in a java application. The sqlquery connects two databases (not on the same server). Is it correct what I am doing here:

the public function has:

    private DatabaseData externaldb = new DatabaseData("com.mysql.jdbc.Driver",
 "...", "...", "...");
    private DatabaseData localdb = new DatabaseData("com.mysql.jdbc.Driver",
 "...", "...", "...");
    private Connection externalconnection = null;
    private Connection localconnection = null;

    function(externalconnection, c_id, u_d);

the called function is:

private int function(Connection externalconnection, String c_Id, String u_Id) 
    throws SQLException{
                String query ="SELECT A.v_id, COUNT(I.v_id) AS v_count "
                        + "FROM externaldb.video_interaction I"
                        + " INNER JOIN localdb.video_additional A ON A.v_id = I.v_id"
                        + " WHERE I.c_id='" + c_id + "' AND I.user'" + u_Id +  "';";

                Statement stmt = externaldb.createStatement();
                ResultSet rs = stmt.executeQuery(query);

                int counter = 0;

                if (rs.next()){
                    counter = rs.getInt("video_count");
                }

                return counter;

            }

Thank you!

Have you tried the above code??what issues you are facing..share those details else we can'nt help you — Akshay, Jun 13 '17 at 10:58
Thank you. we are still in preparation of the server. that's why i dont have feedback at the moment. — Mohammad Zahrawy, Jun 13 '17 at 10:59
come back once you are facing an issue. otherwise we can also just speculate — XtremeBaumer, Jun 13 '17 at 11:00
No. Your query is a String that represents a SQL query, that will be passed to MySQL to be executed over whichever connection you use to run that query - the query itself cannot refer to another connection. In Microsoft SQL Server you can set up a linked server to do this; I'm not sure if MySQL has something similar. — Riaan Nel, Jun 13 '17 at 11:00

score 0 · Answer 1 · answered Jun 13 '17 at 11:02

0

You have two connections, you have two databases. Server local must have access to external and external to local. Then, create FEDERATED table, for example:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

(Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)

Source from: MySQL Cross Server Select Query

More informations: https://dev.mysql.com/doc/refman/5.7/en/federated-create-server.html

answered Jun 13 '17 at 11:02

barwnikk

950
8
14

Thank you for your reply. the problem is that i dont have authorization to create table on the external server. – Mohammad Zahrawy Jun 13 '17 at 11:07
Do you have authorization to create table on internal server? – barwnikk Jun 13 '17 at 11:43
1

Create federated table on internal server. Internal server will estabilishe external connection to external server, so external server must have in firewall allowed "estabilished TCP". – barwnikk Jun 13 '17 at 11:56

Joining two databases in Java with mysql queries?

1 Answers1