Java: Database Helper Routines

Posted on Updated on

Any programmer who deals with a JDBC driver for accessing a database knows that there are many tedious tasks to perform time and time again. There are 3 such tasks that I would like to highlight:

  • CLOB to String Conversion
  • Processing Batch Return Results
  • Escaping SQL Strings

Of course there are many other tedious tasks than those, but these are the ones that can at least be generic enough to fit in a library of useful tools. Others which include stuff like commitment control, managing save points, building statements efficiently …etc tend to be more specific to the programmer / project. So I’ll focus on the 3 mentioned above and provide the source code to them.

CLOB to String Conversion
Quite simple and an ideal candidate for code reuse. Can be modified to add any extra set of requirements needed such as handling charsets or internationalization.

public String convertClobToString(Clob clob) throws IOException, SQLException
{
	Reader reader = clob.getCharacterStream();
	int c = -1;
	StringBuilder sb = new StringBuilder();
	while((c = reader.read()) != -1) {
		sb.append(((char)c));
	}
	
	reader.close();
	return sb.toString();
}

Processing Batch Return Results
When executing a batch of statements the return value is an array of integers. I’ve seen many programmers ignore this return value, but it is always important to check the status every statement that executed in the batch. The operation is quite tedious and really the only thing we would care about is if a statement failed execution, so we check for that. The decision to return a boolean was done for the sake of simplicity.

This method tells you if either the entire batch was successful or a failure (if at least 1 statement failed.) If you want to go into the trouble of finding out which statements in the batch failed and keep track of the indexes and return them, then your going to have to return an array of integers as well. And what if it was successful? you return an empty array? That is annoying. Plus if you are going to go to that much length to check the success of the batch then you shouldn’t be using batch statement in the first place.

public boolean isBatchSuccessful(int[] rets)
{
	boolean hasFailure = false;
	for(int i=0; i < rets.length; i++) {
		if(rets[i] == Statement.EXECUTE_FAILED) {
			hasFailure = true;
			break;
		}
	}

	return !hasFailure;
}

Escaping SQL Strings
This has troubled me for quite some years during my early career. I knew that PreparedStatements auto-escape SQL strings for me and that regular Statements did not. I also knew that it was more memory efficient to use PreparedStatements if I re-use them constantly. I then came to situations where I had complex SQL algorithms where I had to manually construct SQL strings and the cost of creating and destroying PreparedStatements all the time was high. But I needed to escape my SQL strings, what to do?

Dive in the source code of the MySQL JDBC driver of course! Yes, I went for software re-use instead of writing my own String escaping algorithm. I would have ended up with the same thing anyways and they spent the time to research the best way to do it. So I got a method that escapes SQL strings efficiently as if it was a PreparedStatement.

public String escapeSQL(String sql)
{
	return escapeSQL(sql, false);
}

public String escapeSQL(String sql, boolean usingAnsiMode)
{
	StringBuilder sb = new StringBuilder(sql.length());
	
	for(int i = 0; i < sql.length(); ++i) {
		char c = sql.charAt(i);
		switch (c) {
			case 0:
			{
				// Must be escaped for MySQL
				sb.append('\\');
				sb.append('0');
				break;
			}
			
			case '\n':
			{
				sb.append('\\');
				sb.append('n');
				break;
			}
			
			case '\r':
			{
				sb.append('\\');
				sb.append('r');
				break;
			}
			
			// does not need escaping for derby
			case '\\':
			{
				sb.append('\\');
				sb.append('\\');
				break;
			}
			
			case '\'':
			{
				sb.append('\'');
				sb.append('\'');
				break;
			}
			
			case '"':
			{
				//Better safe than sorry
				if (usingAnsiMode) {
					sb.append('\\');
				}
				
				sb.append('"');
				break;
			}
			
			case '\032':
			{
				//This gives problems on Win32
				sb.append('\\');
				sb.append('Z');
				break;
			}
			
			default:
			{
				sb.append(c);
			}
		}
	}
	
	return sb.toString();
}
Advertisements

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s