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.

Labels: java, sql