Re: Class design for DB code

> The java.util.ResourceBundle class is one solution to this problem.  You
> put your SQL strings in a text file, separate from your source code,



I would recomend use more specific class to store SQL then
ResourceBundle
The following class allows:
1) to keep sql queries in well formed sql files (Tools like  SQL
Developer will  understend it).
2) modify queries without restarting application in debug mode just
edit .sql file.
3) build queries by template stored in .sql file.
4)...


Example of .sql file
-- Query: update_message_status
UPDATE MESSAGES SET STATUS = ?, STATUS_VERSION = ?, STATUS_TIME = ?
		WHERE (MESSAGE_ID = ?) AND ((STATUS_VERSION + 1) = ?);


-- Query: find_obsolete_messages
SELECT * FROM MESSAGES WHERE
	TS_DIFF_MSEC(CURRENT_TIMESTAMP, STATUS_TIME) > ?
		AND STATUS <> {0} -- MessageStatus.STAT_DRAFT
		AND STATUS <> {1}; -- MessageStatus.STAT_DELETED

-- Query: delete_obsolete_messages
DELETE FROM MESSAGES WHERE
	TS_DIFF_MSEC(CURRENT_TIMESTAMP, STATUS_TIME) > ?
	AND STATUS = {0}; -- MessageStatus.STAT_DELETED


Usage example within EJB container:

/**
 * Bean implementation class for Enterprise Bean: MessageService
 */
public class MessageServiceBean extends SessionSup****tBase {
   private static final Log log =
LogFactory.getLog(MessageServiceBean.class);
   private static final QueriesStorage queries =
QueriesStorage.newInstance(MessageServiceBean.class);


 ......

   public Long changeStatus(final String messageId, final Long
newStatus) {
      checkSecurity(messageId, "changeStatus");
      try {
          Message message = messagePersistence.findById(messageId);
          Long oldStatus  = message.getStatus();
	  if (log.isDebugEnabled()) {
              log.debug("Changing message status from: " + oldStatus +
", to: " + newStatus);
	  }
          final Date statusTime = new Date();
	  final Long version = message.getStatusVersion().longValue() + 1;
          message.setStatus(newStatus);
          message.setStatusVersion(version);
          message.setStatusTime(statusTime);



          jdbc.execute(new ConnectionHandler<Void,
DataAcces***ception>() {
              public Void onConnection(Connection connection) throws
SQLException {
////////////////////////////// HERE !!!!
 
jdbc.executeUpdate(queries.get("update_status_history"), messageId);
                   int updated =
jdbc.executeUpdate(queries.get("update_message_status"), newStatus,
                                   version, new
Timestamp(statusTime.getTime()), messageId, version);
                   if (updated != 0) {
                      throw new OptimisticLockException("Can't change
status: " + messageId);
                   }
                   return null;
              }
          });
         return oldStatus;
      } catch (RecordNotFoundException e) {
            throw new IllegalArgumentException("Failed to change
message status msgId:  " + messageId, e);
      }
   }
}



The class itself:

package ru.factor.toolkit.queries;

im****t java.io.*;
im****t java.text.MessageFormat;
im****t java.util.LinkedHashMap;
im****t java.util.Map;
im****t java.util.regex.Matcher;
im****t java.util.regex.Pattern;

im****t org.apache.commons.logging.Log;
im****t org.apache.commons.logging.LogFactory;

public class QueriesStorage {
	public static final Log log =
LogFactory.getLog(QueriesStorage.class);

	private Pattern pattern = Pattern.compile("\\p{Blank}*-+\\p{Blank}*"
+
			"Query:\\p{Blank}*(\\w+)\\s+([^;]+);");
	private Map<String, String> queries = new LinkedHashMap<String,
String>();

	public QueriesStorage() {
	}

	public QueriesStorage(Class resource) throws IOException {
		this(resource.getName().replace('.', '/') + ".sql");
	}

	public QueriesStorage(String resource) throws IOException {
		InputStream input = Thread.currentThread().getContextClassLoader()
			.getResourceAsStream(resource);
		if (input == null) {
			throw new IOException("Resource: '" + resource + "' has not been
found");
		}
		try {
			init(input);
			if (log.isDebugEnabled()) {
				StringBuilder message = new StringBuilder("\nQueriesStorage
loaded: ")
					.append(resource).append("\n\t");
				for (String query : queries.keySet()) {
					message.append(query).append(", ");
				}
				log.debug(message.toString());
			}
		} finally {
			input.close();
		}
	}

	public static final QueriesStorage newInstance(Class clazz) {
		try {
			return new QueriesStorage(clazz);
		} catch (IOException e) {
			log.error("Error while instantiating queries for class: " + clazz);
			throw new RuntimeException(e);
		}
	}

	public void init(InputStream input) throws IOException {
		InputStreamReader reader = new InputStreamReader(input, "UTF-8");
		char[] buffer = new char[1024 * 4];
		StringBuilder queries = new StringBuilder();
		while (reader.read(buffer) != -1) {
			queries.append(buffer);
		}
		this.queries.clear();
		init(queries);
	}

	private void init(CharSequence queries) {
		Matcher matcher = pattern.matcher(queries);
		while (matcher.find()) {
			this.queries.put(matcher.group(1), matcher.group(2));
		}
	}

	public String get(String name) {
		String sql = queries.get(name);
		if (sql == null) {
			throw new IllegalArgumentException("Query not found: " + name);
		}
		return sql;
	}

	public String get(String name, Object... params) {
		String sql = queries.get(name);
		if (sql == null) {
			throw new IllegalArgumentException("Query not found: " + name);
		}
		return MessageFormat.format(sql, params);
	}
}