java - 怎么同时查询100个 数据库 里面的同一个表名字的内容。

Question

用同一个sql语句查询。
100个数据库里面的表的结构都是相同的。

我现在要做的就是：：需要同时链接100个数据库进行查询。查出一条符合 条件的就显示一条。。

Class.forName("com.mysql.jdbc.Driver");

          conn=DriverManager.getConnection("jdbc:mysql://192.168.20.236:3306/express001","root" ,"123456");
         ps=conn.prepareStatement("select * from sf where f29=? or f30=? or f40=? or f41=?");
       

这是连接1个数据库的时候。。。
有什么好办法可以解决嘛

多线程能解决吗？？我没有接触过多线程，，如果可以，还请麻烦给点提示 谢谢。。。

Answer 1

No test, you try it

public class Test100Conn {

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args) throws Throwable {
        String f29 = "haha"; //或者其他类似条件？
        
        String db ;
        for (int i = 1; i <= 100; i++) {
            if (i < 10) {
                db = "00" + i;
            }
            else if (i >= 10 && i < 100) {
                db = "0" + i;
            }else{
                db = "100";
            }
            Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.20.236:3306/express" + db, "root", "123456");
            PreparedStatement stmt = conn.prepareStatement("select * from sf where f29=? or f30=? or f40=? or f41=?");
            stmt.setString(1, "");//下面还有几个，这个1看你具体需求
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                System.out.println("这是第"+db+"个数据库开始");
                if(rs.getString(1).equals(f29)){  //判断条件放在这里判断
                    System.out.println(rs.getString(1));
                    break;
                }
                System.out.println("这是第"+db+"个数据库结束");
            }
            rs.close();
            stmt.close();
            conn.close();
        }

    }
}

Answer 2

I think multi-threading can solve it...otherwise it will be executed synchronously.

Answer 3

Is there any good way to do this? Connect them one by one. The number of threads should be the number of CPU cores. Too many are a waste of resources. Use a thread pool.

Answer 4

Since you want to operate 100 libraries, you naturally need 100 different connections. This is a hard condition.

Give the poster an open source project from Dangdang.com.
sharding-jdbc

Answer 5

If it is Oracle, you can use dblink. If you are not the original poster, you can use mysql. You may only be able to connect to 100 databases to query separately, and finally summarize them. If you use multiple threads to query these 100 databases separately, you need to pay attention to thread safety issues during aggregation