SQLUtils.java

package me.xhh.utils;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.lang.StringEscapeUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Utility for SQL database operations.
 */
public class SQLUtils {

	/**
	 * Database names.
	 */
	public enum Database {
		TEST,
	}

	private static final Logger log = LoggerFactory.getLogger(SQLUtils.class);

	static {
		// Initialize MySQL driver
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			log.error("Can not find MySQL Driver: com.mysql.jdbc.Driver");
		}
	}

	/**
	 * Format and escape an SQL clause. See examples below. <br/>
	 * Notice that if something should not be escaped (like table names), use
	 * <code>@0</code> or <code>@0NUM</code> (e.g. <code>@03</code> ).<br/>
	 * <br/>
	 * Examples:<br/>
	 * Format "<code>SELECT * FROM @0 WHERE ID=@ AND NAME=@</code>" <br/>
	 * with parameters [<code>"users", 123, "abc '"</code>], result: <br/>
	 * "<code>SELECT * FROM users WHERE ID='123' AND NAME='abc '''</code>". <br/>
	 * Format "<code>INSERT INTO @01 (NAME, AGE, EMAIL) VALUES(@2,@3,@4)</code>" <br/>
	 * with parameters ["<code>users", "abc '", null, ""</code>], result: <br/>
	 * "<code>INSERT INTO users (NAME, AGE, EMAIL) VALUES('abc ''',NULL,'')</code>".
	 * 
	 * @param sql
	 *            <strong>Note</strong>: be careful the "<code>@</code>"
	 *            characters in the sql, they would be formatted.<br/>
	 *            If you want to reserve a <code>@</code> character, duplicate
	 *            it once (i.e. <code>@@</code> becomes <code>@</code>).<br/>
	 *            Parameter replacing rules: <code>@</code> or <code>@0</code>
	 *            for each parameters from the first to the last;<br/>
	 *            <code>@NUM</code> or <code>@0NUM</code> (e.g. <code>@1</code>,
	 *            <code>@10</code>, <code>@012</code>) for the parameter at the
	 *            <code>NUM</code> index (1 based).<br/>
	 *            Note: insert a <code>0</code> after <code>@</code> to avoid
	 *            escaping the parameter value.
	 * @param params
	 *            the parameters to be used for formatting. Make sure there are
	 *            enough parameters according to the sql clause.
	 */
	public static String formatSQL(String sql, Object... params) {
		if (sql == null)
			return null;
		if (params == null)
			return sql;

		StringBuffer formatted = new StringBuffer();
		final int len = sql.length();
		int pos = 0;
		int paramIdx = 0;
		while (pos < len) {
			char c = sql.charAt(pos);
			if (c == '@') {
				if (pos == len - 1) {
					// iterator to next
					Object val = params[paramIdx++];
					if (val == null)
						formatted.append("NULL");
					else
						formatted.append("'").append(StringEscapeUtils.escapeSql(val.toString())).append("'");
					break;
				}
				char next = sql.charAt(pos + 1);
				if (next == '@') {
					formatted.append('@');
					pos += 2;
					continue;
				}
				boolean escape = true;
				if (next == '0') {
					escape = false;
					do {
						pos++;
					} while (pos < len - 1 && (next = sql.charAt(pos + 1)) == '0');
				}
				int idx = 0;
				if (pos < len - 1) {
					int digit;
					do {
						digit = (int) next - (int) '0';
						if (digit >= 0 && digit <= 9) {
							idx = idx * 10 + digit;
							pos++;
							if (pos < len - 1) {
								next = sql.charAt(pos + 1);
								digit = (int) next - (int) '0';
							} else {
								break;
							}
						} else {
							break;
						}
					} while (true);
				}
				Object value;
				if (idx == 0) // "@" or "@0"
					value = params[paramIdx++]; // iterator to next
				else if (idx > params.length)
					throw new IllegalArgumentException("Illegal SQL string, out of bounds: @" + idx);
				else // "@NUM", e.g. "@1"
					value = params[idx - 1];
				if (value == null)
					formatted.append("NULL");
				else if (escape)
					formatted.append("'").append(StringEscapeUtils.escapeSql(value.toString())).append("'");
				else
					formatted.append(value.toString());
			} else {
				formatted.append(c);
			}
			pos++;
		}

		return formatted.toString();
	}

	/**
	 * @return whether the operation is completed successfully
	 */
	public static boolean close(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				log.error("close(Connection)", e);
				return false;
			}
		}
		return true;
	}
	
}