调用两个数据库服务器的最佳方式 [英] best way to call two database servers
问题描述
我想一次调用不同数据库的两个表.数据库A的表包含用户ID,数据库B的表包含用户位置和用户ID,所以我想加入这两个表并获取user_id对应的位置.database A 和 B 位于两个不同的服务器中.那么我怎样才能加入这两个表.如果不可能加入这个,有没有什么有效的方法可以做到这一点.请帮忙.我正在尝试使用 java、mysql 来做到这一点.
i want to call two table of different database at once.table of database A contains user id and table of database B contains user location with the user id, so i want to join these two tables and get the location corresponding to user_id.database A and B are in two different servers. so how can i join these two tables. if it is not possible to join this, is there any efficient way to do this.please help.i'm trying to do this using java, mysql.
ps = con.prepareStatement("SELECT user_id FROM A.users");
rs = ps.executeQuery();
while(rs.next())
{
//call select statement for database B to get the location for each user id
}
请提出一种有效的方法来做到这一点
please suggest an efficient way to do this
Id User_id
===========
1 44
2 23
User_id location
====================
44 india
23 us
推荐答案
假设 user_id
是一个 long
.
PreparedStatement psUserLocation = conB.prepareStatement("SELECT location FROM B.users WHERE user_id = ?");
while(rs.next()) {
//call select statement for database B to get the location for each user id
long userId = rs.getLong(user_id);
psUserLocation.setLong(1, userId)
ResultSet userLocation = ps.executeQuery();
// Do whatever with the location(s)
}
EDIT:针对所有用户的一个查询,而不是每个用户一个查询:
EDIT: one query for all users instead of one query per user:
private final static String QUERY = "SELECT user_id, location FROM B.users WHERE user_id IN (%a)";
StringBuilder userList = new StringBuilder();
while(rs.next()) {
long userId = rs.getLong(user_id);
userList.append(userId);
if (!rs.isLast()) {
userList.append(",");
}
}
String usersLocationQuery = QUERY.replaceAll("%a", userList.toString());
PreparedStatement psUsersLocation = conB.prepareStatement(usersLocationQuery);
ResultSet usersLocation = psUsersLocation.executeQuery();
// Do whatever with the locations
请记住,这可能会失败/出错,因为大多数数据库对 SQL IN
子句可以包含的项目数有限制.此外,第二种方法可能允许对 %a
替换进行 SQL 注入.
Keep in mind this can fail/work wrong because most DB have a limit for how many items an SQL IN
clause can include. Also this second method might allow an SQL injection on the %a
replacement.
这篇关于调用两个数据库服务器的最佳方式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!