JDBC connection and retrieval of info

Question

If you are wondering why I ask, it is because I have numerous different methods all with very similar code (the only difference in the code in each method is what is between the generate resultset comments). I have tried any amount of times to try and shorten this but I am out of ideas. What I have been trying to get is to have the repeating code in the main method (so therefore it won't be repeating), and then to be able to call the resultSet methods from here.

/**
 * This is the Main Method.
 * 
 * @param args
 */
public static void main(String[] args) {
// call method resultSet1
resultSet1();
}
/**
 * This Method shows Resultset1.
 */
public static void resultSet1() {
// declare my variables and assign values to them
String url = "jdbc:mysql://lamp.eeecs.qub.ac.uk/mmeaklim01";
Connection connection;
// declare my prepared statement and assign a value to it
PreparedStatement updateSalaries;
String updateSalariesString = "update Staff_Members set salary = ? where idStaff = ?";
// declare two arrays and put values in them
int[] staffSalaries = { 45000, 40000, 35000, 42000, 29000, 29500,
 47000, 34000, 30500, 42000, 44500, 41000, 29500, 29500, 30000,
 34000 };
String[] staffIDs = { "M001", "M002", "M003", "M004", "M005", "M006",
 "M007", "M008", "M009", "M010", "M011", "M012", "M013", "M014",
 "M015", "M016" };
// try catch block to handle exceptions
try {
 // connect to the database
 connection = DriverManager.getConnection(url, "mmeaklim01",
 "xxx");
 // start the update
 updateSalaries = connection.prepareStatement(updateSalariesString);
 // declare my variable and assign a value to it
 int length = staffIDs.length;
 // for loop to take the values from my two arrays and use them to
 // update the Staff_Members table
 for (int loop = 0; loop < length; loop++) {
 updateSalaries.setInt(1, staffSalaries[loop]);
 updateSalaries.setString(2, staffIDs[loop]);
 updateSalaries.executeUpdate();
 }
 // generate resultset1
 ResultSet resultSet1 = updateSalaries
 .executeQuery("SELECT name, position, salary FROM Staff_Members");
 // while loop
 System.out.println("Resultset 1\n");
 System.out.println("Name\t\tPosition\tSalary");
 System.out.println("----------\t----------\t----------");
 while (resultSet1.next()) {
 String name = resultSet1.getString("name");
 String position = resultSet1.getString("position");
 int salary = resultSet1.getInt("salary");
 // print out resultset1
 System.out.println(name + "\t" + position + "\t" + salary
 + "\t");
 } // end of resultset1
 // close the update
 updateSalaries.close();
 // close the connection
 connection.close();
 // add a linebreak
 System.out.println();
 // output a message to the user to indicate that the program has
 // executed correctly
 System.out.println("The Method has executed correctly.");
} catch (SQLException sqlException) {
 // catch block
 System.out.println("Error: " + sqlException.getMessage());
}
}

And all I want the resultSet Methods to have, ideally, would be:

 // generate resultset1
 ResultSet resultSet1 = updateSalaries
 .executeQuery("SELECT name, position, salary FROM Staff_Members");
 // while loop
 System.out.println("Resultset 1\n");
 System.out.println("Name\t\tPosition\tSalary");
 System.out.println("----------\t----------\t----------");
 while (resultSet1.next()) {
 String name = resultSet1.getString("name");
 String position = resultSet1.getString("position");
 int salary = resultSet1.getInt("salary");
 // print out resultset1
 System.out.println(name + "\t" + position + "\t" + salary
 + "\t");
 } // end of resultset1

I'm sure there is probably an easy way around this but I can't seem to figure it out.

• h.j.k.: connection = DriverManager.getConnection(url, "mmeaklim01", "xxx..."); please tell us that is not your real password... or change it immediately.
• user90700: No I have made it up.

Answer 1

Take a look as DBUtils and JDBC Template (the latter is more popular). It will give you some hints how to organize such code. Usually you want select query to be separate method and to return list of objects - PreparedStatement and ResultRow shouldn't be visible outside. While using JDBC directly it is also easy to leak DB connections, in your code sample connection.close() should be inside finally block after catch section.

Answer 2

Try-catch

When you use a try-catch with a resource that you need to close after using it you should use a try-catch-with-resources. If your class implements AutoCloseable, which is the case of Connection you will automatically close without cluttering your code with it.

Connection String

You have an hard coded connection here String url = "jdbc:mysql://lamp.eeecs.qub.ac.uk/mmeaklim01"; which make you code bound to that particular database. I would try to use a properties file to extract this at launch. This will help down the line when you want to change the database without creating a new release.

Comments

Comments should be use to tell something unusual about the code, not what the code is doing. When your code is clear and have good name you can drop comments like // while loop (and this is a good example since that comment is not 3 statements before having an actual while loop).

System.out

I'm always wary about using System.out since it's not guaranteed that you'll have it available or it will be convenient to use. I suggest you look at using a logging framework for this task since it take not that much work to setup and will be way useful down the line in a project (even small one).