blocks|key|2526426|text|不应该返回resultSets，应该将结果集的结果读入某种容器对象中。ResultSet是数据库游标的包装器，当连接关闭时它就会消失。这是一种你可以立即阅读并关闭的东西，而不是你可以在你的应用程序中传递的东西。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2526427|看看spring是如何做到的。您实现了传递给JdbcTemplate上的方法的一个JdbcTemplate。|2526428|几点意见：|2526429|您不需要使用Spring来使用spring。然而，我认为自己重新实现这个东西没有什么价值。|unordered-list-item|2526430|读取ResultSet以打开和关闭连接的不是代码的工作，而是其他地方的工作。|2526431|2526432|2526433|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$]]|$1|I|3|J|5|H|7|S|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

You shouldn't be returning resultSets, you should read the results from the resultset into some kind of container object. A ResultSet is a wrapper around a database cursor, it goes away when the connection closes. It's something you read from and close right away, not something you can pass around your application.

Look at how spring-jdbc does it. You implement a resultSetMapper that is passed to the method on the JdbcTemplate. 

Several observations:

<ul>
<li>You don't need to use Spring to use spring-jdbc. However, I see very little value in reimplementing this stuff yourself.</li>
<li>It's not the job of the code that reads the ResultSet to open and close connections, that needs to be elsewhere.</li>
</ul>

blocks|key|3562958|text|我建议您看看Spring。不要自己写这样的东西。已经做好了，而且做得很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3562959|例如，我不喜欢您返回List+of+Strings的想法。这样你就会失去很多信息。我将返回一个Map+of+Lists+(列视图)或List+of+Maps+(行视图)。|offset|length|style|CODE|3562960|如果必须的话，这里有一些数据库实用程序可以帮助您开始工作。|3562961|package+persistence;

import+java.sql.*;
import+java.util.*;

/**
+*+util.DatabaseUtils
+*+User:+Michael
+*+Date:+Aug+17,+2010
+*+Time:+7:58:02+PM
+*/
public+class+DatabaseUtils+{
/*
++++private+static+final+String+DEFAULT_DRIVER+=+"oracle.jdbc.driver.OracleDriver";
++++private+static+final+String+DEFAULT_URL+=+"jdbc:oracle:thin:@host:1521:database";
++++private+static+final+String+DEFAULT_USERNAME+=+"username";
++++private+static+final+String+DEFAULT_PASSWORD+=+"password";
*/
/*
++++private+static+final+String+DEFAULT_DRIVER+=+"org.postgresql.Driver";
++++private+static+final+String+DEFAULT_URL+=+"jdbc:postgresql://localhost:5432/party";
++++private+static+final+String+DEFAULT_USERNAME+=+"pgsuper";
++++private+static+final+String+DEFAULT_PASSWORD+=+"pgsuper";
*/
++++private+static+final+String+DEFAULT_DRIVER+=+"com.mysql.jdbc.Driver";
++++private+static+final+String+DEFAULT_URL+=+"jdbc:mysql://localhost:3306/party";
++++private+static+final+String+DEFAULT_USERNAME+=+"party";
++++private+static+final+String+DEFAULT_PASSWORD+=+"party";

++++public+static+void+main(String[]+args)+{
++++++++long+begTime+=+System.currentTimeMillis();

++++++++String+driver+=+((args.length+>+0)+?+args[0]+:+DEFAULT_DRIVER);
++++++++String+url+=+((args.length+>+1)+?+args[1]+:+DEFAULT_URL);
++++++++String+username+=+((args.length+>+2)+?+args[2]+:+DEFAULT_USERNAME);
++++++++String+password+=+((args.length+>+3)+?+args[3]+:+DEFAULT_PASSWORD);

++++++++Connection+connection+=+null;

++++++++try+{
++++++++++++connection+=+createConnection(driver,+url,+username,+password);
++++++++++++DatabaseMetaData+meta+=+connection.getMetaData();
++++++++++++System.out.println(meta.getDatabaseProductName());
++++++++++++System.out.println(meta.getDatabaseProductVersion());

++++++++++++String+sqlQuery+=+"SELECT+PERSON_ID,+FIRST_NAME,+LAST_NAME+FROM+PERSON+ORDER+BY+LAST_NAME";
++++++++++++System.out.println("before+insert:+"+%2B+query(connection,+sqlQuery,+Collections.EMPTY_LIST));

++++++++++++connection.setAutoCommit(false);
++++++++++++String+sqlUpdate+=+"INSERT+INTO+PERSON(FIRST_NAME,+LAST_NAME)+VALUES(?,?)";
++++++++++++List+parameters+=+Arrays.asList("Foo",+"Bar");
++++++++++++int+numRowsUpdated+=+update(connection,+sqlUpdate,+parameters);
++++++++++++connection.commit();

++++++++++++System.out.println("#+rows+inserted:+"+%2B+numRowsUpdated);
++++++++++++System.out.println("after+insert:+"+%2B+query(connection,+sqlQuery,+Collections.EMPTY_LIST));
++++++++}+catch+(Exception+e)+{
++++++++++++rollback(connection);
++++++++++++e.printStackTrace();
++++++++}+finally+{
++++++++++++close(connection);
++++++++++++long+endTime+=+System.currentTimeMillis();
++++++++++++System.out.println("wall+time:+"+%2B+(endTime+-+begTime)+%2B+"+ms");
++++++++}
++++}

++++public+static+Connection+createConnection(String+driver,+String+url,+String+username,+String+password)+throws+ClassNotFoundException,+SQLException+{
++++++++Class.forName(driver);
++++++++if+((username+==+null)+%7C%7C+(password+==+null)+%7C%7C+(username.trim().length()+==+0)+%7C%7C+(password.trim().length()+==+0))+{
++++++++++++return+DriverManager.getConnection(url);
++++++++}+else+{
++++++++++++return+DriverManager.getConnection(url,+username,+password);
++++++++}
++++}

++++public+static+void+close(Connection+connection)+{
++++++++try+{
++++++++++++if+(connection+!=+null)+{
++++++++++++++++connection.close();
++++++++++++}
++++++++}+catch+(SQLException+e)+{
++++++++++++e.printStackTrace();
++++++++}
++++}


++++public+static+void+close(Statement+st)+{
++++++++try+{
++++++++++++if+(st+!=+null)+{
++++++++++++++++st.close();
++++++++++++}
++++++++}+catch+(SQLException+e)+{
++++++++++++e.printStackTrace();
++++++++}
++++}

++++public+static+void+close(ResultSet+rs)+{
++++++++try+{
++++++++++++if+(rs+!=+null)+{
++++++++++++++++rs.close();
++++++++++++}
++++++++}+catch+(SQLException+e)+{
++++++++++++e.printStackTrace();
++++++++}
++++}

++++public+static+void+rollback(Connection+connection)+{
++++++++try+{
++++++++++++if+(connection+!=+null)+{
++++++++++++++++connection.rollback();
++++++++++++}
++++++++}+catch+(SQLException+e)+{
++++++++++++e.printStackTrace();
++++++++}
++++}

++++public+static+List<Map<String,+Object>>+map(ResultSet+rs)+throws+SQLException+{
++++++++List<Map<String,+Object>>+results+=+new+ArrayList<Map<String,+Object>>();
++++++++try+{
++++++++++++if+(rs+!=+null)+{
++++++++++++++++ResultSetMetaData+meta+=+rs.getMetaData();
++++++++++++++++int+numColumns+=+meta.getColumnCount();
++++++++++++++++while+(rs.next())+{
++++++++++++++++++++Map<String,+Object>+row+=+new+HashMap<String,+Object>();
++++++++++++++++++++for+(int+i+=+1;+i+<=+numColumns;+%2B%2Bi)+{
++++++++++++++++++++++++String+name+=+meta.getColumnName(i);
++++++++++++++++++++++++Object+value+=+rs.getObject(i);
++++++++++++++++++++++++row.put(name,+value);
++++++++++++++++++++}
++++++++++++++++++++results.add(row);
++++++++++++++++}
++++++++++++}
++++++++}+finally+{
++++++++++++close(rs);
++++++++}
++++++++return+results;
++++}

++++public+static+List<Map<String,+Object>>+query(Connection+connection,+String+sql,+List<Object>+parameters)+throws+SQLException+{
++++++++List<Map<String,+Object>>+results+=+null;
++++++++PreparedStatement+ps+=+null;
++++++++ResultSet+rs+=+null;
++++++++try+{
++++++++++++ps+=+connection.prepareStatement(sql);

++++++++++++int+i+=+0;
++++++++++++for+(Object+parameter+:+parameters)+{
++++++++++++++++ps.setObject(%2B%2Bi,+parameter);
++++++++++++}
++++++++++++rs+=+ps.executeQuery();
++++++++++++results+=+map(rs);
++++++++}+finally+{
++++++++++++close(rs);
++++++++++++close(ps);
++++++++}
++++++++return+results;
++++}

++++public+static+int+update(Connection+connection,+String+sql,+List<Object>+parameters)+throws+SQLException+{
++++++++int+numRowsUpdated+=+0;
++++++++PreparedStatement+ps+=+null;
++++++++try+{
++++++++++++ps+=+connection.prepareStatement(sql);

++++++++++++int+i+=+0;
++++++++++++for+(Object+parameter+:+parameters)+{
++++++++++++++++ps.setObject(%2B%2Bi,+parameter);
++++++++++++}
++++++++++++numRowsUpdated+=+ps.executeUpdate();
++++++++}+finally+{
++++++++++++close(ps);
++++++++}
++++++++return+numRowsUpdated;
++++}
}|code-block|syntax|javascript|3562962|entityMap^0|0|A|4|I|7|1B|3|1I|5|1U|4|22|4|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]|$D|U|E|V|F|G]|$D|W|E|X|F|G]|$D|Y|E|Z|F|G]|$D|10|E|11|F|G]|$D|12|E|13|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|15|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|P|$]]

I'd recommend looking at Spring JDBC. Don't write such a thing yourself. It's already been done, and quite well. 

For example, I don't like your idea of returning a <code>List</code> of <code>Strings</code>. You lose a lot of info that way. I'd return a <code>Map</code> of <code>Lists</code> (column view) or <code>List</code> of <code>Maps</code> (row view).

If you must, here are some database utilities that would get you started.

<pre><code>package persistence;

import java.sql.*;
import java.util.*;

/**
 * util.DatabaseUtils
 * User: Michael
 * Date: Aug 17, 2010
 * Time: 7:58:02 PM
 */
public class DatabaseUtils {
/*
 private static final String DEFAULT_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DEFAULT_URL = "jdbc:oracle:thin:@host:1521:database";
 private static final String DEFAULT_USERNAME = "username";
 private static final String DEFAULT_PASSWORD = "password";
*/
/*
 private static final String DEFAULT_DRIVER = "org.postgresql.Driver";
 private static final String DEFAULT_URL = "jdbc:postgresql://localhost:5432/party";
 private static final String DEFAULT_USERNAME = "pgsuper";
 private static final String DEFAULT_PASSWORD = "pgsuper";
*/
 private static final String DEFAULT_DRIVER = "com.mysql.jdbc.Driver";
 private static final String DEFAULT_URL = "jdbc:mysql://localhost:3306/party";
 private static final String DEFAULT_USERNAME = "party";
 private static final String DEFAULT_PASSWORD = "party";

 public static void main(String[] args) {
 long begTime = System.currentTimeMillis();

 String driver = ((args.length &gt; 0) ? args[0] : DEFAULT_DRIVER);
 String url = ((args.length &gt; 1) ? args[1] : DEFAULT_URL);
 String username = ((args.length &gt; 2) ? args[2] : DEFAULT_USERNAME);
 String password = ((args.length &gt; 3) ? args[3] : DEFAULT_PASSWORD);

 Connection connection = null;

 try {
 connection = createConnection(driver, url, username, password);
 DatabaseMetaData meta = connection.getMetaData();
 System.out.println(meta.getDatabaseProductName());
 System.out.println(meta.getDatabaseProductVersion());

 String sqlQuery = "SELECT PERSON_ID, FIRST_NAME, LAST_NAME FROM PERSON ORDER BY LAST_NAME";
 System.out.println("before insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));

 connection.setAutoCommit(false);
 String sqlUpdate = "INSERT INTO PERSON(FIRST_NAME, LAST_NAME) VALUES(?,?)";
 List parameters = Arrays.asList("Foo", "Bar");
 int numRowsUpdated = update(connection, sqlUpdate, parameters);
 connection.commit();

 System.out.println("# rows inserted: " + numRowsUpdated);
 System.out.println("after insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));
 } catch (Exception e) {
 rollback(connection);
 e.printStackTrace();
 } finally {
 close(connection);
 long endTime = System.currentTimeMillis();
 System.out.println("wall time: " + (endTime - begTime) + " ms");
 }
 }

 public static Connection createConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {
 Class.forName(driver);
 if ((username == null) || (password == null) || (username.trim().length() == 0) || (password.trim().length() == 0)) {
 return DriverManager.getConnection(url);
 } else {
 return DriverManager.getConnection(url, username, password);
 }
 }

 public static void close(Connection connection) {
 try {
 if (connection != null) {
 connection.close();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }


 public static void close(Statement st) {
 try {
 if (st != null) {
 st.close();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }

 public static void close(ResultSet rs) {
 try {
 if (rs != null) {
 rs.close();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }

 public static void rollback(Connection connection) {
 try {
 if (connection != null) {
 connection.rollback();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }

 public static List&lt;Map&lt;String, Object&gt;&gt; map(ResultSet rs) throws SQLException {
 List&lt;Map&lt;String, Object&gt;&gt; results = new ArrayList&lt;Map&lt;String, Object&gt;&gt;();
 try {
 if (rs != null) {
 ResultSetMetaData meta = rs.getMetaData();
 int numColumns = meta.getColumnCount();
 while (rs.next()) {
 Map&lt;String, Object&gt; row = new HashMap&lt;String, Object&gt;();
 for (int i = 1; i &lt;= numColumns; ++i) {
 String name = meta.getColumnName(i);
 Object value = rs.getObject(i);
 row.put(name, value);
 }
 results.add(row);
 }
 }
 } finally {
 close(rs);
 }
 return results;
 }

 public static List&lt;Map&lt;String, Object&gt;&gt; query(Connection connection, String sql, List&lt;Object&gt; parameters) throws SQLException {
 List&lt;Map&lt;String, Object&gt;&gt; results = null;
 PreparedStatement ps = null;
 ResultSet rs = null;
 try {
 ps = connection.prepareStatement(sql);

 int i = 0;
 for (Object parameter : parameters) {
 ps.setObject(++i, parameter);
 }
 rs = ps.executeQuery();
 results = map(rs);
 } finally {
 close(rs);
 close(ps);
 }
 return results;
 }

 public static int update(Connection connection, String sql, List&lt;Object&gt; parameters) throws SQLException {
 int numRowsUpdated = 0;
 PreparedStatement ps = null;
 try {
 ps = connection.prepareStatement(sql);

 int i = 0;
 for (Object parameter : parameters) {
 ps.setObject(++i, parameter);
 }
 numRowsUpdated = ps.executeUpdate();
 } finally {
 close(ps);
 }
 return numRowsUpdated;
 }
}
</code></pre>

blocks|key|3363527|text|您可以编写解析ResultSet并将其转换为ArrayList或数组甚至对象字段的助手函数。例如，假设您有一个订单表，然后查询返回特定用户(客户)的该表的所有行。然后我们可以做这样的事情：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3363528|static+List<Order>+parseOrder(ResultSet+rs)+{
+++ArrayList<Order>+orderList+=+new+ArrayList<>();
+++while(rs.next()+)+{
++++++Order+order+=+new+Order();
++++++order.setID(rs.getInt(1));
++++++order.setCustomerID(rs.getInt(2));
++++++order.setItemName(rs.getString(3));
++++++orderList.add(order);
++}
++return+orderList;|code-block|syntax|javascript|3363529|}|3363530|简单地将结果集转换为一个由对象数组组成的数组可能会更通用，但可能不太有用。|3363531|我将由调用函数来关闭这个ResultSet，并可能关闭PreparedStatement+(或语句)和数据库连接。|3363532|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You can write helper functions that parse a ResultSet and convert it into an ArrayList or an array or even the fields of an object. For instance, lets say you have a table of orders and then a query returns all of the rows of that table for a particular user (customer). We could then do something like this:

<pre><code>static List&lt;Order&gt; parseOrder(ResultSet rs) {
 ArrayList&lt;Order&gt; orderList = new ArrayList&lt;&gt;();
 while(rs.next() ) {
 Order order = new Order();
 order.setID(rs.getInt(1));
 order.setCustomerID(rs.getInt(2));
 order.setItemName(rs.getString(3));
 orderList.add(order);
 }
 return orderList;
</code></pre>

}

Simply turning the result set into an array of an array of Objects would be more general, but probably less useful.

I would leave it up to the calling function to close this ResultSet and possible the PreparedStatement (or Statement) and database connection.

I've ran into a problem of having to run a number of different queries on the DB (different return types, different number of columns, etc).
While writing that i started to wonder if there's a proper way of writing a helper function.
It seemed that it's really easy to write a function that returns a <code>ResultSet</code>.

However since it a) doesn't close connection b) doesn't close the result set it seems as a possibly working, but improper solution. Is there any place to dump in all results so that they can be returned safely.

(Only thing i could come up with, is just returning a 2D string array (after converting all data to strings) and then converting it all back)

EDIT : Sorry for not writing clear, was wondering if there's any way to just store the result of the query as is (don't need to modify it) without writing a separate method for every possible return type.

The idea behind a 2d string list is being able to store the query values as is.

<pre><code>Col1 Row1 | Col2 Row1 | Col3 Row1
Col1 Row2 | Col2 Row2 | Col3 Row2
</code></pre>

EDIT 2 Thank you for replies, i guess i'll just write a small parser for it.

Working with ResultSets in Java

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我遇到了一个问题，就是必须在DB上运行许多不同的查询(不同的返回类型、不同的列数等等)。当我写这篇文章时，我开始怀疑是否有一种正确的方法来编写帮助函数。似乎编写返回ResultSet的函数非常容易。但是，由于它( a)不关闭连接( b)不关闭结果集，这似乎是一种可行的解决方案，但不正确。是否有任何地方可以转储所有的结果...

问在Java中使用ResultSets
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Java中使用ResultSetsEN