I'm looking for a way to query a SQL database with Java and return an Object [][]. Here is the SQL query:
private static Object result[][] = null;
result = run.query(conn, "select * from TREEDATA", rsh);
Here is the sample of what the data should mirror:
Object[][] table = {
{1, null, "Root"}, //i=0
{2, 1, "Node2"}, //i=1
{3, 2, "Node3"}, //i=2
{4, 3, "Node4"}, //i=3
{5, 4, "Node5"}, //i=4
{6, 5, "Node6"}, //i=5
{7, 6, "Node7"}, //i=6
{8, 1, "Node8"}, //i=7
{9, 1, "Node9"}, //i=8
{10, 9, "Node10"},}; //i=9
Here is the result set handler with the code that I cannot figure out:
public Object[][] handle(ResultSet rs) throws SQLException {
if (!rs.next()) {
System.out.println("result set is null");
return null;
}
ResultSetMetaData meta = rs.getMetaData();
int rows = 0;
while (rs.next()) {
rows++;
}
Object[][] result = new Object[rows];
int i = 0;
while (rs.next()) {
//How do I do what I need to do in order to return result[][]
result[i][] = rs.getObject(3);
System.out.println(result[i][2].toString());
}
return result;
}
Here is the implementation like you started it:
public Object[][] handle(ResultSet rs) throws SQLException {
if (!rs.last()) { //If false, the result set is empty.
System.out.println("result set is null");
return null;
}
int rowCount = rs.getRow(); // You are pointing on the last row, so this will get the row number of the last row.
rs.beforeFirst(); // Reset your cursor.
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
Object[][] result = new Object[rowCount][columnCount];
int i = 0;
while (rs.next()) {
for (int j = 0; j < columnCount; j++) {
result[i][j] = rs.getObject(j);
}
}
return result;
}
But personally I would rather have the function return ArrayList<Object[]>
or even better List<TreeData>
but then you need to implement and fill the TreeData
object with row values manually. Which is where JPA/Hibernate comes in.. But that might be overkill again depending on your application.