如何使这个工作?显然,我不了解一些非常基本的工作人员关于 Java 中其他 SQL 查询中的 SQL 查询,但四处搜索并没有帮助!
提前谢谢你
try (Connection con = L2DatabaseFactory.getInstance().getConnection())
{
PreparedStatement stm = con.prepareStatement("SELECT count,owner_id FROM items WHERE item_id=57 order by count desc limit 10");
ResultSet rSet = stm.executeQuery();
while (rSet.next())
{
int owner_id = rSet.getInt("owner_id");
int count = rSet.getInt("count");
if (count == 0)
{
continue;
}
PreparedStatement stm1 = con.prepareStatement("SELECT char_name,accesslevel,online FROM characters WHERE obj_Id=" + owner_id);
ResultSet rSet1 = stm1.executeQuery();
while (rSet1.next())
{
int accessLevel = rSet.getInt("accesslevel");
if (accessLevel > 0)
{
continue;
}
String pl = rSet.getString("char_name");
int online = rSet.getInt("online");
String status = online == 1 ? "<font color="00FF00">Online</font>" : "<font color="FF0000">Offline</font>";
sb.append("<tr><td>"+ pl +"</td><td>"+ count +"</td><td>"+ status +"</td></tr>");
}
}
}
catch (Exception e)
{
_log.log(Level.SEVERE, "Error", e);
}
看起来您正在尝试使用 Java 代码连接两个表。这不是一个好主意,也不利于性能。让数据库为您做连接 - 它是这方面的专家。不要在 Java 中编写"内部连接"代码。
除此之外:准备好的语句没有被关闭,这迟早会导致操作系统资源的麻烦。
我的建议是创建一个带有inner join
或select in
语句的查询,并使用 try with resources 关闭所有预准备语句。大致如下:
private String test() throws SQLException {
StringBuilder sb = new StringBuilder();
int count = 0;
try (Connection con = L2DatabaseFactory.getInstance().getConnection()) {
try (PreparedStatement stm1 = con.prepareStatement(
"SELECT char_name,accesslevel,online FROM characters WHERE obj_Id in (SELECT owner_id FROM items WHERE item_id=57 order by count desc limit 10)")) {
ResultSet rSet = stm1.executeQuery();
while (rSet.next()) {
count++;
int accessLevel = rSet.getInt("accesslevel");
if (accessLevel > 0) {
continue;
}
String pl = rSet.getString("char_name");
int online = rSet.getInt("online");
String status = online == 1 ? "<font color="00FF00">Online</font>" : "<font color="FF0000">Offline</font>";
sb.append("<tr><td>" + pl + "</td><td>" + count + "</td><td>" + status + "</td></tr>");
}
}
} catch (Exception e) {
Logger.getLogger("test").log(Level.SEVERE, "Error", e);
}
return sb.toString();
}