当前位置： 文江博客 话题详情

Java 中处理 ResultSet 的有效方法

发布于 2024-12-05 13:51:15 字数 579 浏览 0 评论 0 原文

我在 Java 中使用 ResultSet，但不确定如何正确关闭它。我正在考虑使用 ResultSet 构造 HashMap，然后关闭 ResultSet。这种 HashMap 技术是否有效，或者是否有更有效的方法来处理这种情况？我需要键和值，因此使用 HashMap 似乎是一个合理的选择。

如果使用 HashMap 是最有效的方法，那么我如何在代码中构造和使用 HashMap？

这是我尝试过的：

public HashMap resultSetToHashMap(ResultSet rs) throws SQLException {

  ResultSetMetaData md = rs.getMetaData();
  int columns = md.getColumnCount();
  HashMap row = new HashMap();
  while (rs.next()) {
     for (int i = 1; i <= columns; i++) {
       row.put(md.getColumnName(i), rs.getObject(i));
     }
  }
  return row;
}
原文

I'm using a ResultSet in Java, and am not sure how to properly close it. I'm considering using the ResultSet to construct a HashMap and then closing the ResultSet after that. Is this HashMap technique efficient, or are there more efficient ways of handling this situation? I need both keys and values, so using a HashMap seemed like a logical choice.

If using a HashMap is the most efficient method, how do I construct and use the HashMap in my code?

Here's what I've tried:

public HashMap resultSetToHashMap(ResultSet rs) throws SQLException {

  ResultSetMetaData md = rs.getMetaData();
  int columns = md.getColumnCount();
  HashMap row = new HashMap();
  while (rs.next()) {
     for (int i = 1; i <= columns; i++) {
       row.put(md.getColumnName(i), rs.getObject(i));
     }
  }
  return row;
}

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（9）

べ映画 2024-12-12 13:51:16

我改进了 RHT/Brad 女士和 Lestos 答案的解决方案。

我扩展了这两种解决方案，将状态留在发现它的地方。
所以我保存当前的结果集位置并在创建地图后恢复它。

rs 是结果集，它是一个字段变量，因此在我的解决方案片段中不可见。

我将 Brad Ms 解决方案中的特定地图替换为通用地图。

    public List<Map<String, Object>> resultAsListMap() throws SQLException
    {
        var md = rs.getMetaData();
        var columns = md.getColumnCount();
        var list = new ArrayList<Map<String, Object>>();

        var currRowIndex = rs.getRow();
        rs.beforeFirst();

        while (rs.next())
        {
            HashMap<String, Object> row = new HashMap<String, Object>(columns);
            for (int i = 1; i <= columns; ++i)
            {
                row.put(md.getColumnName(i), rs.getObject(i));
            }

            list.add(row);
        }

        rs.absolute(currRowIndex);

        return list;
    }

在Lestos解决方案中，我优化了代码。在他的代码中，他必须在 for 循环的每次迭代中查找映射。我将其减少为每次 for 循环迭代仅访问一个数组。因此，程序不得在每个迭代步骤中搜索该字符串键。

    public Map<String, List<Object>> resultAsMapList() throws SQLException
    {
        var md = rs.getMetaData();
        var columns = md.getColumnCount();
        var tmp = new ArrayList[columns];
        var map = new HashMap<String, List<Object>>(columns);

        var currRowIndex = rs.getRow();
        rs.beforeFirst();

        for (int i = 1; i <= columns; ++i)
        {
            tmp[i - 1] = new ArrayList<>();
            map.put(md.getColumnName(i), tmp[i - 1]);
        }

        while (rs.next())
        {
            for (int i = 1; i <= columns; ++i)
            {
                tmp[i - 1].add(rs.getObject(i));
            }
        }

        rs.absolute(currRowIndex);

        return map;
    }

i improved the solutions of RHTs/Brad Ms and of Lestos answer.

i extended both solutions in leaving the state there, where it was found.
So i save the current ResultSet position and restore it after i created the maps.

The rs is the ResultSet, its a field variable and so in my solutions-snippets not visible.

I replaced the specific Map in Brad Ms solution to the gerneric Map.

    public List<Map<String, Object>> resultAsListMap() throws SQLException
    {
        var md = rs.getMetaData();
        var columns = md.getColumnCount();
        var list = new ArrayList<Map<String, Object>>();

        var currRowIndex = rs.getRow();
        rs.beforeFirst();

        while (rs.next())
        {
            HashMap<String, Object> row = new HashMap<String, Object>(columns);
            for (int i = 1; i <= columns; ++i)
            {
                row.put(md.getColumnName(i), rs.getObject(i));
            }

            list.add(row);
        }

        rs.absolute(currRowIndex);

        return list;
    }

In Lestos solution, i optimized the code. In his code he have to lookup the Maps each iteration of that for-loop. I reduced that to only one array-acces each for-loop iteration. So the program must not seach each iteration step for that string-key.

    public Map<String, List<Object>> resultAsMapList() throws SQLException
    {
        var md = rs.getMetaData();
        var columns = md.getColumnCount();
        var tmp = new ArrayList[columns];
        var map = new HashMap<String, List<Object>>(columns);

        var currRowIndex = rs.getRow();
        rs.beforeFirst();

        for (int i = 1; i <= columns; ++i)
        {
            tmp[i - 1] = new ArrayList<>();
            map.put(md.getColumnName(i), tmp[i - 1]);
        }

        while (rs.next())
        {
            for (int i = 1; i <= columns; ++i)
            {
                tmp[i - 1].add(rs.getObject(i));
            }
        }

        rs.absolute(currRowIndex);

        return map;
    }
回复 原文
挽你眉间 2024-12-12 13:51:16

这是我从谷歌获得的代码，稍作修改 -

 List data_table = new ArrayList<>();
    Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(conn_url, user_id, password);
            Statement stmt = con.createStatement();
            System.out.println("query_string: "+query_string);
            ResultSet rs = stmt.executeQuery(query_string);
            ResultSetMetaData rsmd = rs.getMetaData();


            int row_count = 0;
            while (rs.next()) {
                HashMap<String, String> data_map = new HashMap<>();
                if (row_count == 240001) {
                    break;
                }
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    data_map.put(rsmd.getColumnName(i), rs.getString(i));
                }
                data_table.add(data_map);
                row_count = row_count + 1;
            }
            rs.close();
            stmt.close();
            con.close();

Here is the code little modified that i got it from google -

 List data_table = new ArrayList<>();
    Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(conn_url, user_id, password);
            Statement stmt = con.createStatement();
            System.out.println("query_string: "+query_string);
            ResultSet rs = stmt.executeQuery(query_string);
            ResultSetMetaData rsmd = rs.getMetaData();


            int row_count = 0;
            while (rs.next()) {
                HashMap<String, String> data_map = new HashMap<>();
                if (row_count == 240001) {
                    break;
                }
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    data_map.put(rsmd.getColumnName(i), rs.getString(i));
                }
                data_table.add(data_map);
                row_count = row_count + 1;
            }
            rs.close();
            stmt.close();
            con.close();
回复 原文
花桑 2024-12-12 13:51:16 
public static List<HashMap<Object, Object>> GetListOfDataFromResultSet(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int count = metaData.getColumnCount();
        String[] columnName = new String[count];
        List<HashMap<Object,Object>> lst=new ArrayList<>();
        while(rs.next()) {
            HashMap<Object,Object> map=new HashMap<>();
            for (int i = 1; i <= count; i++){
                   columnName[i-1] = metaData.getColumnLabel(i);
                   map.put(columnName[i-1], rs.getObject(i));
            }
            lst.add(map);

        }
        return lst;
    }

public static List<HashMap<Object, Object>> GetListOfDataFromResultSet(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int count = metaData.getColumnCount();
        String[] columnName = new String[count];
        List<HashMap<Object,Object>> lst=new ArrayList<>();
        while(rs.next()) {
            HashMap<Object,Object> map=new HashMap<>();
            for (int i = 1; i <= count; i++){
                   columnName[i-1] = metaData.getColumnLabel(i);
                   map.put(columnName[i-1], rs.getObject(i));
            }
            lst.add(map);

        }
        return lst;
    }
回复 原文
枕头说它不想醒 2024-12-12 13:51:16

您可以使用下面的示例来运行并了解它的基础知识。非常容易理解的完全可操作的代码/示例。

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DynamicRows {

    public static void main(String[] args) {
        DynamicRows dr = new DynamicRows();
        List<Map<String, Object>> rows = dr.getSearchRecords();
    }

    public List<Map<String, Object>> getSearchRecords() {
        ResultSet rs = null;
        Connection conn = null;
        List<Map<String, Object>> rows = null;
        try {
            conn = openConnection();
            if (conn != null) {

                java.sql.PreparedStatement ps = conn.prepareStatement("select * from online_users");
                rs = ps.executeQuery();

                if (rs != null) {
                    rows = new ArrayList<Map<String, Object>>();
//Code get resultset metadata information
                    ResultSetMetaData metaData = rs.getMetaData();
//To get column count in result set
                    int columnCount = metaData.getColumnCount();

                    while (rs.next()) {
                        Map<String, Object> columns = new LinkedHashMap<String, Object>();
//System.out.println("=======Row Start Here===========");
                        for (int i = 1; i <= columnCount; i++) {
                            // To get Column Name
                            // System.out.println(metaData.getColumnLabel(i)+"->"+rs.getObject(i));
                            columns.put(metaData.getColumnLabel(i), rs.getObject(i));
                        }

                        rows.add(columns);
                    }
                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            closeConnection(conn);
        }
System.out.println(rows);
        return rows;
    }

    private Connection openConnection() throws SQLException {
        Connection conn = null;
        try {
            System.out.println("Load JDBC Driver");
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {

            System.out.println("Fail to Load JDBC Driver ");
            e.printStackTrace();
            return null;
        }

        System.out.println("JDBC Driver Registered .");

        conn = employee.DbConnection.getDatabaseConnection();

        if (conn != null) {
            System.out.println("JDBC connection is Successful !");
        } else {
            System.out.println("JDBC Connection failed !");
        }
        return conn;
    }

    private void closeConnection(Connection conn) {
        if (conn != null) {
            try {

                conn.close();
                System.out.println("Connection closed successfully");
            } catch (SQLException ex) {
                System.out.println("Failed to close JDBC Connection !");
                ex.printStackTrace();
            }
        } else {

            System.out.println("No JDBC connection to close !");
        }
    }

}

输出：

Load JDBC Driver
JDBC Driver Registered .
JDBC connection is Successful !
Connection closed successfully
[{USERNAME=test, ISACTIVE=False, LOGIN_TIME=2023-08-15 19:01:30.0, LOGOUT_TIME=2023-08-15 19:01:46.0}]

You can use below example to run and understand it's basics. Very easy fully operational code/example to understand.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DynamicRows {

    public static void main(String[] args) {
        DynamicRows dr = new DynamicRows();
        List<Map<String, Object>> rows = dr.getSearchRecords();
    }

    public List<Map<String, Object>> getSearchRecords() {
        ResultSet rs = null;
        Connection conn = null;
        List<Map<String, Object>> rows = null;
        try {
            conn = openConnection();
            if (conn != null) {

                java.sql.PreparedStatement ps = conn.prepareStatement("select * from online_users");
                rs = ps.executeQuery();

                if (rs != null) {
                    rows = new ArrayList<Map<String, Object>>();
//Code get resultset metadata information
                    ResultSetMetaData metaData = rs.getMetaData();
//To get column count in result set
                    int columnCount = metaData.getColumnCount();

                    while (rs.next()) {
                        Map<String, Object> columns = new LinkedHashMap<String, Object>();
//System.out.println("=======Row Start Here===========");
                        for (int i = 1; i <= columnCount; i++) {
                            // To get Column Name
                            // System.out.println(metaData.getColumnLabel(i)+"->"+rs.getObject(i));
                            columns.put(metaData.getColumnLabel(i), rs.getObject(i));
                        }

                        rows.add(columns);
                    }
                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            closeConnection(conn);
        }
System.out.println(rows);
        return rows;
    }

    private Connection openConnection() throws SQLException {
        Connection conn = null;
        try {
            System.out.println("Load JDBC Driver");
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {

            System.out.println("Fail to Load JDBC Driver ");
            e.printStackTrace();
            return null;
        }

        System.out.println("JDBC Driver Registered .");

        conn = employee.DbConnection.getDatabaseConnection();

        if (conn != null) {
            System.out.println("JDBC connection is Successful !");
        } else {
            System.out.println("JDBC Connection failed !");
        }
        return conn;
    }

    private void closeConnection(Connection conn) {
        if (conn != null) {
            try {

                conn.close();
                System.out.println("Connection closed successfully");
            } catch (SQLException ex) {
                System.out.println("Failed to close JDBC Connection !");
                ex.printStackTrace();
            }
        } else {

            System.out.println("No JDBC connection to close !");
        }
    }

}

Output :

Load JDBC Driver
JDBC Driver Registered .
JDBC connection is Successful !
Connection closed successfully
[{USERNAME=test, ISACTIVE=False, LOGIN_TIME=2023-08-15 19:01:30.0, LOGOUT_TIME=2023-08-15 19:01:46.0}]
回复 原文
旧街凉风 2024-12-12 13:51:15
  1. 迭代 ResultSet
  2. 为每一行创建一个新对象，以存储您需要的字段
  3. 将这个新对象添加到 ArrayList 或 Hashmap 或任何您喜欢的对象中
  4. 关闭 ResultSet、Statement 和 DB 连接

完成

编辑：现在您已经发布了代码，我对其进行了一些更改。

public List resultSetToArrayList(ResultSet rs) throws SQLException{
  ResultSetMetaData md = rs.getMetaData();
  int columns = md.getColumnCount();
  ArrayList list = new ArrayList(50);
  while (rs.next()){
     HashMap row = new HashMap(columns);
     for(int i=1; i<=columns; ++i){           
      row.put(md.getColumnName(i),rs.getObject(i));
     }
      list.add(row);
  }

 return list;
}
  1. Iterate over the ResultSet
  2. Create a new Object for each row, to store the fields you need
  3. Add this new object to ArrayList or Hashmap or whatever you fancy
  4. Close the ResultSet, Statement and the DB connection

Done

EDIT: now that you have posted code, I have made a few changes to it.

public List resultSetToArrayList(ResultSet rs) throws SQLException{
  ResultSetMetaData md = rs.getMetaData();
  int columns = md.getColumnCount();
  ArrayList list = new ArrayList(50);
  while (rs.next()){
     HashMap row = new HashMap(columns);
     for(int i=1; i<=columns; ++i){           
      row.put(md.getColumnName(i),rs.getObject(i));
     }
      list.add(row);
  }

 return list;
}
回复 原文
故事与诗 2024-12-12 13:51:15

我刚刚清理了 RHT 的答案以消除一些警告，并认为我会分享。 Eclipse 完成了大部分工作：

public List<HashMap<String,Object>> convertResultSetToList(ResultSet rs) throws SQLException {
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    List<HashMap<String,Object>> list = new ArrayList<HashMap<String,Object>>();

    while (rs.next()) {
        HashMap<String,Object> row = new HashMap<String, Object>(columns);
        for(int i=1; i<=columns; ++i) {
            row.put(md.getColumnName(i),rs.getObject(i));
        }
        list.add(row);
    }

    return list;
}

I just cleaned up RHT's answer to eliminate some warnings and thought I would share. Eclipse did most of the work:

public List<HashMap<String,Object>> convertResultSetToList(ResultSet rs) throws SQLException {
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    List<HashMap<String,Object>> list = new ArrayList<HashMap<String,Object>>();

    while (rs.next()) {
        HashMap<String,Object> row = new HashMap<String, Object>(columns);
        for(int i=1; i<=columns; ++i) {
            row.put(md.getColumnName(i),rs.getObject(i));
        }
        list.add(row);
    }

    return list;
}
回复 原文
不必你懂 2024-12-12 13:51:15

RHT 几乎拥有它。或者您可以使用 RowSetDynaClass 让其他人做所有的工作:)

RHT pretty much has it. Or you could use a RowSetDynaClass and let someone else do all the work :)

回复 原文
北凤男飞 2024-12-12 13:51:15

这是我的替代解决方案，我使用的是列表地图，而不是地图列表。
在远程数据库上的 5000 个元素的表上进行测试，两种方法的时间约为 350 毫秒。

private Map<String, List<Object>> resultSetToArrayList(ResultSet rs) throws SQLException {
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    Map<String, List<Object>> map = new HashMap<>(columns);
    for (int i = 1; i <= columns; ++i) {
        map.put(md.getColumnName(i), new ArrayList<>());
    }
    while (rs.next()) {
        for (int i = 1; i <= columns; ++i) {
            map.get(md.getColumnName(i)).add(rs.getObject(i));
        }
    }

    return map;
}

this is my alternative solution, instead of a List of Map, i'm using a Map of List.
Tested on tables of 5000 elements, on a remote db, times are around 350ms for eiter method.

private Map<String, List<Object>> resultSetToArrayList(ResultSet rs) throws SQLException {
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    Map<String, List<Object>> map = new HashMap<>(columns);
    for (int i = 1; i <= columns; ++i) {
        map.put(md.getColumnName(i), new ArrayList<>());
    }
    while (rs.next()) {
        for (int i = 1; i <= columns; ++i) {
            map.get(md.getColumnName(i)).add(rs.getObject(i));
        }
    }

    return map;
}
回复 原文
メ斷腸人バ 2024-12-12 13:51:15

有几件事可以增强其他答案。首先，你永远不应该返回一个HashMap，它是一个特定的实现。返回一个普通的旧java.util.Map。但无论如何，这对于这个例子来说实际上是不正确的。您的代码仅将 ResultSet 的最后一行作为（哈希）映射返回。相反，您想要返回一个 List>。考虑一下应该如何修改代码来做到这一点。 （或者你可以接受戴夫·牛顿的建议）。

A couple of things to enhance the other answers. First, you should never return a HashMap, which is a specific implementation. Return instead a plain old java.util.Map. But that's actually not right for this example, anyway. Your code only returns the last row of the ResultSet as a (Hash)Map. You instead want to return a List<Map<String,Object>>. Think about how you should modify your code to do that. (Or you could take Dave Newton's suggestion).

回复 原文
~没有更多了~

关于作者

爱本泡沫多脆弱

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

Gabu-gabumon

文章 0 评论 0

qq_CgiN62

文章 0 评论 0

荔枝明

文章 0 评论 0

赏烟花じ飞满天

文章 0 评论 0

独守阴晴ぅ圆缺

文章 0 评论 0

¤→小豸慧

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文