Jdbc2Mf.java

package org.foo.jdbc2mf;

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.FieldPosition;
import java.text.Format;
import java.text.MessageFormat;
import java.text.ParsePosition;
import java.util.Date;
import java.util.Locale;

/**
 * Convert a JDBC-like parameterized query into java.text.MessageFormat.
 */
public class Jdbc2Mf {

	private static final int ORDINAL_BASE = 0;

	private final String pattern;
	private final MessageFormat mf;
	private DateFormat dateFormat = new TimestampDateFormat();

	// When >0, needs escaping. Temporarily pause escaping when parameter
	// surrounded with braces
	private int braceDepth;

	private int paramId;

	private int i;
	private Mode mode;
	private CharSequence a;
	private StringBuilder sb;

	/**
	 * Parameters of this class are not single-quoted and can be used as parameterized table or column
	 */
	public static class Identifier {
		private final String name;
		public Identifier(String name) {
			this.name = name;
		}

		@Override
		public String toString() {
			return name;
		}
	}

	public Jdbc2Mf(CharSequence jdbcQuery) {
		pattern = toPattern(jdbcQuery);
		mf = getMessageFormat(pattern);
	}

	public String getPattern() {
		return pattern;
	}

	public MessageFormat getMf() {
		return mf;
	}

	/**
	 * @return current date format. Defaults to {@link TimestampDateFormat}
	 */
	public DateFormat getDateFormat() {
		return dateFormat;
	}

	/**
	 * set current date format
	 * @param dateFormat must include surrounding quotes
	 */
	public void setDateFormat(DateFormat dateFormat) {
		this.dateFormat = dateFormat;
	}


	// Question mark treated as parameter only in default mode
	private enum Mode {
		DEFAULT, IDENTIFIER, LITERAL, COMMENT_LINE, COMMENT_BLOCK
	}

	private void readLB() {
		if (braceDepth == 0) {
			sb.append('\'');
		}
		braceDepth++;
	}

	private void readRB() {
		if (braceDepth != 0) {
			braceDepth--;
			if (braceDepth == 0) {
				sb.append('\'');
			}
		}
	}

	private String toPattern(CharSequence jdbcQuery) {
		mode = Mode.DEFAULT;
		braceDepth = 0;
		paramId = 0;
		a = jdbcQuery;
		sb = new StringBuilder(jdbcQuery.length());
		for (i = 0;; i++) {
			if (i == a.length()) {
				if (braceDepth != 0) {
					sb.append('\'');
				}
				break;
			}
			char c = a.charAt(i);
			switch (c) {
			case '?':
				if (mode == Mode.DEFAULT) {
					if (braceDepth != 0) {
						sb.append('\'');
					}
					sb.append('{').append(consumeOrdinal()).append('}');
					if (i == a.length() - 1) {
						braceDepth = 0;
					} else {
						if (braceDepth != 0) {
							sb.append('\'');
						}
					}
					continue;
				}
				break;
			case '{':
				readLB();
				break;
			case '}':
				sb.append(c);
				readRB();
				continue;
			case '*':
				if (mode == Mode.DEFAULT) {
					if (isPrevChar('/')) {
						mode = Mode.COMMENT_BLOCK;
					}
				}
				break;
			case '/':
				if (mode == Mode.COMMENT_BLOCK) {
					if (isPrevChar('*')) {
						mode = Mode.DEFAULT;
					}
				}
				break;
			case '-':
				if (mode == Mode.DEFAULT) {
					if (isPrevChar('-')) {
						mode = Mode.COMMENT_LINE;
					}
				}
				break;
			case '\n':
				if (mode == Mode.COMMENT_LINE) {
					mode = Mode.DEFAULT;
				}
				break;
			case '\'':
				if (mode == Mode.DEFAULT) {
					mode = Mode.LITERAL;
				} else if (mode == Mode.LITERAL) {
					// we don't need special treatment for escaped single quote
					mode = Mode.DEFAULT;
				}
				sb.append(c); // each single quote is duplicated in MF
				break;
			case '"':
				if (mode == Mode.DEFAULT) {
					mode = Mode.IDENTIFIER;
				} else if (mode == Mode.IDENTIFIER) {
					mode = Mode.DEFAULT;
				}
				break;
			}
			sb.append(c);
		}
		return sb.toString();
	}

	private int consumeOrdinal() {
		// i points to '?'
		int start = i + 1;
		int end = start;
		int lastIndex = a.length() - 1;
		for(;end != lastIndex; end++) {
			char c = a.charAt(end);
			if (!(c >= '0' && c <= '9')) {
				break;
			}
		}
		if (start != end) {
			i = end - 1;
			return Integer.parseInt(a.subSequence(start, end).toString()) - ORDINAL_BASE;
		} else {
			return paramId++;
		}
	}

	private boolean isPrevChar(char c) {
		return i != 0 && a.charAt(i - 1) == c;
	}

	private MessageFormat getMessageFormat(final String pattern) {
		final MessageFormat mf = new MessageFormat(pattern);
		mf.setLocale(Locale.US);
		final Format[] fmts = mf.getFormatsByArgumentIndex();
		MyFormat fmt = null;
		for (int i = 0; i < fmts.length; i++) {
			if (fmts[i] == null) {
				if (fmt == null) {
					fmt = new MyFormat();
				}
				fmts[i] = fmt;
			}
		}
		mf.setFormatsByArgumentIndex(fmts);
		return mf;
	}

	public static class TimestampDateFormat extends DateFormat {

		private static final long serialVersionUID = 1L;

		@Override
		public StringBuffer format(Date date, StringBuffer toAppendTo,
				FieldPosition fieldPosition) {
			if (!(date instanceof Timestamp || date instanceof java.sql.Date || date instanceof java.sql.Time)) {
				date = new Timestamp(date.getTime());
			}
			synchronized (toAppendTo) {
				return toAppendTo.append('\'').append(date).append('\'');
			}
		}

		@Override
		public Date parse(String source, ParsePosition pos) {
			throw new UnsupportedOperationException();
		}
	}

	private class MyFormat extends Format {

		private static final long serialVersionUID = 1L;

		@Override
		public StringBuffer format(final Object obj,
				final StringBuffer toAppendTo, final FieldPosition pos) {
			{
				String arg = null;
				Format subFormatter = null;
				if (obj == null) {
					arg = "null"; // actually, obj is never null
				} else if (obj instanceof Number) {
					// format number if can
					arg = obj.toString();
				} else if (obj instanceof Date) {
					// format a Date if can
					subFormatter = dateFormat;
				} else {
					arg = obj.toString();
					if (arg == null) {
						arg = "null";
					} else if (!(obj instanceof Identifier)) {
						arg = "'" + arg.replace("'", "''") + "'";
					}
				}
				if (subFormatter == null) {
					toAppendTo.append(arg);
				} else {
					subFormatter.format(obj, toAppendTo, pos);
				}
			}
			return toAppendTo;
		}

		@Override
		public Object parseObject(final String source, final ParsePosition pos) {
			throw new UnsupportedOperationException();
		}
	}
}

TestIdentifier.java

package org.foo.jdbc2mf;

import java.text.MessageFormat;

public class TestIdentifier {

	// valid PostgreSQL 9.4 query
	private static final String JDBC_QUERY = "select 1 from ? t where t.? = 1 ";

	public static void main(String[] args) throws Exception {
		Jdbc2Mf inst = new Jdbc2Mf(JDBC_QUERY);
		MessageFormat mf = inst.getMf();

		Object paramObj1 = new Jdbc2Mf.Identifier("abc");
		Object paramObj2 = new Jdbc2Mf.Identifier("def");

		String finalSql = mf.format(new Object[] { paramObj1,paramObj2 });

		System.out.println(finalSql);
	}

}

TestJdbc2Mf.java

package org.foo.jdbc2mf;

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.MessageFormat;
import java.util.Date;

import org.foo.jdbc2mf.Jdbc2Mf.TimestampDateFormat;

public class TestJdbc2Mf {
	private static final String AND_A_EQ_ = "and a = ";

	private static final String AND_B_EQ_ = "and b = ";

	private static final String AND_C_EQ_ = "and c = ";

	private static final String AND_D_EQ_ = "and d = ";

	// valid PostgreSQL 9.4 query
	private static final String JDBC_QUERY = "select " //
			+ "42 as \"Larry? \"\"Basher?\"\" O'Niven?\" " //
			+ " /* hello ? */ -- hello ? " //
			+ "\n    " //
			+ ", " //
			+ "'{0}' as \"not a placeholder {0} {1 /* not a commentary \" " //
			+ "from employee where 1=1 " //
			+ AND_A_EQ_ + "? " //
			+ AND_B_EQ_ + "? " //
			+ AND_C_EQ_ + "? " //
			+ AND_D_EQ_ + "? " //
			+ "and x = 'abc'";

	private static final String EXPECTED_MF_PATTERN = "select " //
			+ "42 as \"Larry? \"\"Basher?\"\" O''Niven?\" " //
			+ " /* hello ? */ -- hello ? " //
			+ "\n    " //
			+ ", " //
			+ "'''{0}''' as \"not a placeholder '{0}' '{1 /* not a commentary \" " //
			+ "from employee where 1=1 " //
			+ AND_A_EQ_ + "'{0}' " //
			+ AND_B_EQ_ + "'{1}' " //
			+ AND_C_EQ_ + "'{2}' " //
			+ AND_D_EQ_ + "'{3}' " //
			+ "and x = ''abc'''";

	public static void main(String[] args) throws Exception {
		Jdbc2Mf inst = new Jdbc2Mf(JDBC_QUERY);
		MessageFormat mf = inst.getMf();
		String pattern = inst.getPattern();
		if (!EXPECTED_MF_PATTERN.equals(pattern)) {
			throw new Exception("MF pattern not as expected:\n\n"
					+ EXPECTED_MF_PATTERN + "\n\n" + pattern + "\n");
		}
		// make sure EXPECTED_MF_PATTERN is itself valid
		StringBuilder sb = new StringBuilder(JDBC_QUERY);

		DateFormat df = new TimestampDateFormat();

		Integer paramObj0 = 123456; // 6 digits enough to test no grouping
		String paramObj1 = "Jerry's";
		Date paramObj2 = Timestamp.valueOf("1999-12-31 12:00:00");
		Object paramObj3 = null;

		String finalSql = mf.format(new Object[] { paramObj0, paramObj1,
				paramObj2, paramObj3 });

		replaceQuestion(sb, AND_A_EQ_, paramObj0.toString());
		replaceQuestion(sb, AND_B_EQ_, "'" + paramObj1.replace("'", "''") + "'");
		replaceQuestion(sb, AND_C_EQ_, df.format(paramObj2));
		replaceQuestion(sb, AND_D_EQ_, "null");

		String expFinalSql = sb.toString();
		if (!expFinalSql.equals(finalSql)) {
			throw new Exception("formatted query not as expected:\n\n"
					+ expFinalSql + "\n\n" + finalSql + "\n");
		}
	}

	private static void replaceQuestion(StringBuilder sb, String prefix,
			String replacement) {
		int i = sb.indexOf(prefix);
		sb.replace(i + prefix.length(), i + prefix.length() + 1, replacement);
	}
}

TestOrdinal.java

package org.foo.jdbc2mf;

import java.text.MessageFormat;

public class TestOrdinal {

	private static final String JPA_QUERY = "select 1 from ?1 t where t.?0 = 1 ";

	public static void main(String[] args) throws Exception {
		Jdbc2Mf inst = new Jdbc2Mf(JPA_QUERY);
		MessageFormat mf = inst.getMf();

		Object paramObj1 = new Jdbc2Mf.Identifier("abc");
		Object paramObj2 = new Jdbc2Mf.Identifier("def");

		String finalSql = mf.format(new Object[] { paramObj1,paramObj2 });

		System.out.println(finalSql);
	}

}