我的程序有 2 个数据库表:GameInfo 和 Characters。 其工作原理是，一个游戏有 4 张名称不同的 map ，并且必须为添加到游戏中的每个角色分配这 4 张 map 中的任何一张。现在我有一个 sql 语句，它返回一个名为“Expr1001”的结果集，每个 map 上的字符数。然后我需要将此信息添加到 jTable 并将 map 上每个字符的相应数量与 map 名称链接起来。

我的结果集包含返回每个 map 上字符数量的查询:

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表的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;
}

然后将数据添加到 GameInfoPanel 类中面板上的 jTable:

 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 并运行它，它很好。

请帮忙! 谢谢。

(我还在上学，所以在这方面根本不是天才所以如果我做了一些愚蠢的事情请怜悯)

最佳答案

不要先运行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;
    }
}

关于java - 从一个 SQL 表中获取与另一个表有关系的数据计数并在 Java 中使用它，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32976298/