从一个SQL表中获取与另一个表相关联的数据数量，并在Java中使用

Question

从一个SQL表中获取与另一个表相关联的数据数量，并在Java中使用

3

我有两个数据库表格，分别是GameInfo和Characters。程序的运行方式是一个游戏包含四张地图，每个添加到游戏中的角色必须被分配到其中一张地图上。现在我有一个SQL语句，返回了一个名为“Expr1001”的结果集，其中包含了每个地图上角色的数量。然后我需要将这些信息添加到一个jTable中，并将每个角色在地图上的对应数量与地图名称关联起来。我的查询结果集可以返回每个地图上角色数量的信息：

ResultSet qs = dbm.queryDatabase("SELECT Expr1001 FROM (SELECT GameInfo.mapname, SUM(IIF(Map = GameInfo.mapname,1,0)) FROM (SELECT * FROM [Character] INNER JOIN Player ON Character.PlayerID=Player.[ID])  AS A RIGHT JOIN GameInfo ON A.Character.map = GameInfo.mapname GROUP BY GameInfo.mapname)  AS [%$##@_Alias]");

从GameInfo表中获取游戏信息的整个方法仅包含GameID和MapName两个字段。

 public Game[] getGameInfo(){
       Game[] arr = null; //Creates an array of Games


    try { //getting list from database
        ResultSet rs = dbm.queryDatabase("Select Count(GameID) as NumGames from GameInfo" );
        //While there are still more rows to read from the database.
        rs.next();
        int count = rs.getInt("NumGames");
        arr = new Game[count];
        String sql = "Select * from GameInfo";
       // System.out.println(sql);

        rs = dbm.queryDatabase(sql);
        //Take the info from the current row
        //Add the info to the array
         ResultSet qs = dbm.queryDatabase("SELECT Expr1001 FROM (SELECT GameInfo.mapname, SUM(IIF(Map = GameInfo.mapname,1,0)) FROM (SELECT * FROM [Character] INNER JOIN Player ON Character.PlayerID=Player.[ID])  AS A RIGHT JOIN GameInfo ON A.Character.map = GameInfo.mapname GROUP BY GameInfo.mapname)  AS [%$##@_Alias]");
        for(int i = 0; rs.next(); i++){

            arr[i] = new Game(
                    rs.getInt("GameInfo.GameID"), 
                    rs.getString("GameInfo.mapname"), 
                 qs.getInt(i));
        }//Creates a Game from the currently selected info


    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, "Failed to get Games");
        e.printStackTrace();
    }
    return arr;
}

然后将数据添加到jTable中，该jTable位于GameInfoPanel类的一个面板上：

 public void refreshTable() {
    //remove old stuff
    refreshing = true;

    Game[] arr = gim.getGameInfo();
    DefaultTableModel model = (DefaultTableModel) GameInfoTable.getModel();
    while (model.getRowCount() > 0) {
        model.removeRow(0);

    }
    for (int i = 0; i < arr.length; i++) {
        model.addRow(new Object[]{
            arr[i].getNumberOfCharacters(),
           arr[i].getID(),
            arr[i].getMapName()});
    }
    refreshing = false;

    //load new data from database using manager
}

每当我尝试运行程序时，总是会出现指向ResultSet qs行的错误："用户缺少特权或找不到对象：A.CHARACTER.MAP"，即使当我将此语句复制到Microsoft Access中运行时，它也是正常的。

请帮忙解决！谢谢。

（我还在上学，对此并不是很精通，请见谅如果我做了一些愚蠢的事情）

- James

尝试用CHARACTER.MAP替换A.CHARACTER.MAP - Adam Martin

@AdamMartin 现在的错误是：用户缺少权限或对象未找到：CHARACTER.MAP - James

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，

- Andreas · Accepted Answer

不要首先运行select count(*)来获取游戏数量以分配数组。在List中构建结果，它会根据需要自动扩展。如果需要，您可以随时将列表转换为数组。当一个查询可以完成任务时，不要运行两个查询，特别是当您已经加入了相关的表时。您的SQL不可读，因此以下是更易读的格式：

String sql = "SELECT Expr1001" +
              " FROM (SELECT GameInfo.mapname" +
                          ", SUM(IIF(Map = GameInfo.mapname,1,0))" +
                      " FROM (SELECT *" +
                              " FROM [Character]" +
                             " INNER JOIN Player ON Character.PlayerID=Player.[ID]" +
                            ")  AS A" +
                     " RIGHT JOIN GameInfo ON A.Character.map = GameInfo.mapname" +
                     " GROUP BY GameInfo.mapname" +
                    ")  AS [%$##@_Alias]";

外部查询没有任何作用。去掉它。
不要使用 SELECT *。选择你需要的列，例如 Character.map。
由于你需要 GameID，将其添加到 GROUP BY 中。
为 SUM 值指定别名。

public Game[] getGameInfo(){
    String sql = " SELECT GameInfo.GameID" +
                       ", GameInfo.mapname" +
                       ", SUM(IIF(C.map = GameInfo.mapname,1,0)) AS CharacterCount" +
                   " FROM ( SELECT Character.map" +
                            " FROM [Character]" +
                            " JOIN Player ON Player.[ID] = Character.PlayerID" +
                         ") C" +
                  " RIGHT JOIN GameInfo ON GameInfo.mapname = C.map" +
                  " GROUP BY GameInfo.GameID" +
                          ", GameInfo.mapname";
    try (ResultSet rs = dbm.queryDatabase(sql)) {
        List<Game> games = new ArrayList<>();
        while (rs.next())
            games.add(new Game(rs.getInt("GameID"),
                               rs.getString("mapname"),
                               rs.getInt("CharacterCount")));
        return games.toArray(new Game[games.size()]);
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, "Failed to get Games");
        e.printStackTrace();
        return null;
    }
}