(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.
