Java & MySQL select query to return a two dimensional array of results

(vector of arrays actually)

When I started to do some Java again, I noticed that Java doesn’t really have an easy way of getting an array from a query, if you don’t always want to declare how many columns and rows you expect. To circumvent this problem I have a class in my toolkit that contains a method that returns a vector of result lines and each element contains an array of results…

This makes it really easy then to just write a query, like

Vector  myResultVector = selectQuery("select * from customerdetails");

, and get the Vector of arrays back.

To illustrate, here is the method:

public Vector selectQuery(String query) throws SQLException {
Statement st = connection.createStatement();
ResultSet res = stat.executeQuery(query);

ResultSetMetaData rsmd = result.getMetaData();
numCols = rsmd.getColumnCount();

               // Optional print out:
//System.out.println("The query was executed. The number of columns returned is: " + numCols);

        //We don't return this here in the same method, but this variable is declared earlier and can be returned through a different method.
for (int i=1; i<=numCols; i++) {
            columnNames.add(i-1, rsmd.getColumnName(i));
           // System.out.println(columnNames.elementAt(i-1));
}

while (result.next()) {
String[] arr1 = new String[numCols];
for (int t=1; t <=numCols; t++) {
arr1[t-1] = result.getString(t);
//System.out.println(arr1[t-1]);
}
results.add(arr1);
}
return results;
}

In the constructor of the actual class, I have the database connection, looking something like this:

 public mysqlConnector() {

try {
String userName = "YOURUSER";
String passWord = "YOURPASSWORD";
String url = "jdbc:mysql://YOURSERVER/YOURSCHEMA";
Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            connection = DriverManager.getConnection (url, userName, passWord);
            System.out.println ("Database connection established!");
}
catch (Exception err1) {
System.err.println ("Cannot connect to database server!");
err1.printStackTrace();
}
}

Maybe worth mentioning the global variables that I declare in this class:

 public Vector results = new Vector ();
public Vector columnNames = new Vector ();
public int numCols  = 0;

The results, because there are other methods that are using the same Vector, the columnNames and the numCols, because there is a method that returns them when I need them:

public Vector  getColumnHeaders(){
return columnNames;
}

public int getColumnNumber(){
return numCols;
}

It might also be good to close the database connection when you don’t need it anymore:

 public void disconnectionNotice() {
if (connection != null) {
try {
connection.close ();
System.out.println("Connection to DB closed!");
}
catch (Exception err2) {
//err2.printStackTrace();
}
}
}

Hope this helps some of you that might have struggled with the same problem. Email me if this isn’t all clear to you… Happy to help.

Author: Chris A. Matenaers

Working in Digital Marketing, strong liberal world-views & privacy advocate. My hobbies are scuba-diving and coding. I'm also a huge Star Wars fan.

Leave a Reply

Your email address will not be published. Required fields are marked *