Return an array after querying database in Java

问题:

I have to query a MSSQL database and I want the result of the query to returned as a Array or ArrayList.

I have a this code now, but it gives an error. I have a connection to the database so that's not the problem.

public ArrayList<Array> queryResult(String q) throws SQLException {

   ArrayList<Array> array = new ArrayList<>();
   Statement statement = this.getConnection().createStatement();
   ResultSet rs = statement.executeQuery(q);

   while(rs.next()) {

      Array n = rs.getArray(rs.getRow());
      System.out.println(n);
      array.add(n);

   }
   return array;
}

I get the following error

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: This operation is not supported.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
      at com.microsoft.sqlserver.jdbc.SQLServerResultSet.NotImplemented(SQLServerResultSet.java:750)
      at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getArray(SQLServerResultSet.java:2625)
      at server.Database.queryResult(Database.java:52)
      at server.Server.listen(Server.java:57)
      at server.Server.run(Server.java:34) at
      server.Server.<init>(Server.java:28) at
      server.Server.main(Server.java:94) Java Result: 1

回答1:

getArray() returns the value of a particular column of the current row as an array. See this - http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#getArray%28int%29

If you want to get row values as array then you have to write code for that some thing like this.

while (rs.next()){
     java.util.ArrayList alRowData = new java.util.ArrayList();
     java.sql.ResultSetMetaData rsmd = rs.getMetaData();
     int numberOfColumns = rsmd.getColumnCount();
     for(int columnIndex = 1; columnIndex <= numberOfColumns; columnIndex ++){
          alRowData.add(rs.getObject(columnIndex));
     }
     System.out.println(alRowData);
}

回答2:

Your exception trace shows that:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: This operation is not supported.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.NotImplemented(SQLServerResultSet.java:750)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getArray(SQLServerResultSet.java:2625)

Seems like the SQLDriver you used, did not implement the getArray(int) method, and hence is throwing this exception.

You may require the latest driver, support or an alternative solution to fix this issue.

EDIT:
If you really are looking to fetch current Row or Record of ResultSet then your call to getRow() and getArray(..) will not work and AFAIK such implementation is not available.