Simplify Database Connection Updates

To simplify changing database connection strings I've found it's quite useful to create a function in the Code Templates. This way I can just use that for each time I need to put a database call and whenever I need to change the connection string I have one single spot to modify instead of dozens.

function ODBConn(state) {
  if(state=="open"){
    importPackage(java.sql);
    var dbConn = DriverManager.getConnection('URL','Username','Password');
  }
return dbConn;
}

This example is specific to using prepared statements for database interaction, but the principal is the same for any type or method of connection. 

To use the function for connecting, simply use something like this:

var conn = ODBConn('open');
...
var preparedQuery = conn.prepareStatement("some prepared query string");
  preparedQuery.setString(1,firstParameter);
  preparedQuery.setString(2,secondParameter);
  preparedQuery.executeQuery();

One thing to note about this script is that it does not close the connection when finished as shown. Fortunately closing the connection is very simple, it should just be done at once the need for a connection is complete.

Just append this to the end of your script to close :

conn.close();

Voila! You have a reusable database connection function and whenever you need to change a database password or connection url! It will now be in one convenient place instead of having to search through every channel destination and transformer script to update your information.