sqlbuilder.java

本文论述了一个前台笔记本销售系统的开发过程

package com.set.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

/**
 * reads all sql files and keeps them in memery for future usage.
 * 
 * @author tommy
 * @version 1.0
 */
public class SQLBuilder {
	private static Map<String, String> statementMaps = new HashMap<String, String>();

	private static Logger log = Logger.getLogger(SQLBuilder.class);

	private static SQLBuilder instance = null;

	private final String SQL_FILE_BASE = "/sql/";

	private String FILE_EXT = ".sql";

	private String FILE_BASE = "";

	public static SQLBuilder getInstance() {
		if (null == instance) {
			instance = new SQLBuilder();
		}
		return instance;
	}

	private SQLBuilder() {
		FILE_BASE = SQLBuilder.class.getResource(SQL_FILE_BASE).getFile();
		init();

	}

	public String getStatement(String key) {
		String statement = statementMaps.get(key);
		if (null == statement) {
			log.warn("file " + key + " can't be found right now");
		}
		return statement;
	}

	public String getStatement(String key, String extraSql, List sqlReplacement) {

		String statement = getStatement(key);

		StringBuffer buf = new StringBuffer(statement);

		if (null != sqlReplacement) {
			Iterator it = sqlReplacement.iterator();
			for (; it.hasNext();) {
				String replace = (String) it.next();
				int start = buf.indexOf("@");
				buf.replace(start, start + 1, replace);
			}
		}
		if (null != extraSql && !"".equals(extraSql))
			buf.append(" order by " + extraSql);
		return buf.toString();
	}

	public String getStatement(String key, String extraSql) {
		return getStatement(key, extraSql, null);
	}

	public String getStatement(String key, List sqlReplacement) {
		return getStatement(key, null, sqlReplacement);
	}

	private void init() {
		String paths[] = getSQLFileNames();
		if (null == paths) {
			log.warn("no sql file can be found in " + SQL_FILE_BASE);
			return;
		}
		String source = "";
		try {
			for (int i = 0; i < paths.length; i++) {
				source = paths[i];

				/** @todo 加入缓存检查 */
				statementMaps.put(source, parseStatement(source));

			}
		} catch (Exception ex) {
			log.error(ex);
			;
		}
	}

	private String getStatementMap(String id) throws DBAccessException {
		String sm = (String) statementMaps.get(id);
		if (null == sm) {
			log.warn("文件ID:" + id + "不存在");
			throw new DBAccessException("error.idNotFound");
		}
		return sm;
	}

	private String parseStatement(String key) {
		String sql = getSQL(key);
		if (log.isDebugEnabled()) {
			log.debug(sql);
		}
		return sql;
	}

	private String getSQL(String key) {
		StringBuffer sql = new StringBuffer();
		BufferedReader reader = null;
		try {
			reader = new BufferedReader(new FileReader(FILE_BASE + key
					+ FILE_EXT));
			String s;
			while ((s = reader.readLine()) != null) {
				sql.append(" ").append(s);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			if (null != reader) {
				try {
					reader.close();
				} catch (IOException e) {
				}
			}
		}
		return sql.toString().trim();
	}

	private String[] getSQLFileNames() {
		String fileBase = "";

		fileBase = FILE_BASE;

		if (null == fileBase || fileBase.equals("")) {
			log.error(SQL_FILE_BASE + " can't be found in classpath.");
			return null;
		}

		List list = new ArrayList();
		File f = new File(fileBase);
		if (f.isDirectory()) {
			String tmp;
			String[] paths = f.list();
			int len = paths.length;
			for (int i = 0, j = 0; i < len; i++) {
				tmp = paths[i];
				if (tmp.toLowerCase().endsWith(FILE_EXT.toLowerCase())) {
					list
							.add(tmp.substring(0, tmp.length()
									- FILE_EXT.length()));
				}
			}

		}
		return (String[]) list.toArray(new String[0]);
	}

}