方便管理工程中的SQL语句

引用

    之前在项目开发时候，经理要求把所有的SQL语句挑出文档化管理，也就是说要把项目中的SQL语句全部写到一起，方便以后维护。经理是一个资深技术人员，对新技术、框架没有关注的缘故吧，因为现成有很多这样的框架，hibernate，iBATIS 等都具有这个功能。这些框架，我也只是在以前用过一小段时间，不是很熟练，为了完成要求，为此特意设计一下简易、方便的SQL管理，使用方法。
    模仿properties文件的性质。把所有SQL文件放在一个文件中，分别加上注释；使用一个SqlReader类静态加载所有SQL。在使用的地方直接获取其KEY就能得到该SQL语句。这样就很好，很方便的SQL语句含义注释，解决后期维护的问题。
    下面分别贴出类properties的train.sql文件，SqlReader类，具体项目中调用SQL的TrainSqlReader类，和是一个TrainDao类。来演示这种方法使用。

1. train.sql文件

#查询火车车次信息
select_train_detail=select t.train_name from tmp_train_time t WHERE t.train_name=? AND t.station_serial=?

#插入火车车次信息
insert_train_detail=INSERT INTO tmp_train_time t (t.train_name,t.station_serial,t.station_name,t.arrive_time,t.start_time,t.elapsed_time,t.distance,t.hard_based_seat,t.soft_based_seat,t.hard_sleeper,t.soft_sleeper) VALUES (?,?,?,?,?,?,?,?,?,?,?)

#查询未采集车次站点价格的车次
#select_train_names=SELECT train_name FROM tmp_train_time LEFT JOIN  tmp_train_price ON train_name=che_ci WHERE che_ci IS NULL GROUP BY train_name ORDER BY train_name
select_train_names=SELECT train_name FROM tmp_train_time GROUP BY train_name ORDER BY train_name

#查询一趟车次所有站名
select_train_station=SELECT station_name FROM tmp_train_time WHERE train_name=? ORDER BY TO_NUMBER(station_serial)



#查询火车车次站点价格
select_train_price=SELECT che_ci FROM tmp_train_price WHERE che_ci=? AND chu_fa_zhan=? AND dao_da_zhan=?

#插入火车车次站点价格
insert_train_price=INSERT INTO tmp_train_price (che_ci,chu_fa_zhan,dao_da_zhan,ying_zuo,ruan_zuo,ying_wo_shang,ying_wo_zhong,ying_wo_xia,ruan_wo_shang,ruan_wo_xia,yi_deng_zuo,er_deng_zuo,gao_ji_ruan_wo_shang,gao_ji_ruan_wo_xia) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)


2.SqlReader.java类。解析sql文件，根据key获取sql语句。

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;

/**
 * 读取SQL文件指定键名的SQL语句。
 * 
 * @作者 华龙
 *
 * @日期：2010-03-12
 */
public class SqlReader {
	
	private HashMap<String, String> prop = null;
	
	private static Logger log = Log4jFactory.getLogger(SqlReader.class);

	
	/**
	 * 加载读取sql语句的配置文件。
	 * @param sqlFilePath sql语句配置文件
	 */
	public void initial(String sqlFilePath) {
		if (prop == null) {
			prop = new HashMap<String, String>();
			try {
				initial(new FileReader(sqlFilePath));
			} catch (Exception e) {
				e.e.printStackTrace()			}
		}
	}
	
	/**
	 * 加载配置。
	 * 
	 * @param sqlReader 含配置的字符输入流。
	 * @throws IOException IO异常。
	 */
	private void initial(Reader sqlReader) throws IOException {
		BufferedReader br = (sqlReader instanceof BufferedReader) ? (BufferedReader) sqlReader
				: new BufferedReader(sqlReader);
		String line = null;
		line = br.readLine();
		for (int i = 0; line != null; i++) {
			line = line.trim();// 去除一行中的前后空格
			if(!"".equals(line)){
				String key = getKey(line);
				String value = getValue(line);
				if (key != null && !line.startsWith("#")) {
					prop.put(key, value);
				}
			}
			line = br.readLine();
		}
		br.close();
	}
	
	/**
	 * 获取指定行键的名。
	 * 
	 * @param line 配置文件中的一行字符串。
	 * @return 该行中键的名。
	 */
	private static String getKey(String line) {
		if (line == null || line.equals("")) {
			return null;
		}
		if (line.indexOf("=") < 0) {
			return null;
		}
		return line.substring(0, line.indexOf("=")).trim();
	}

	/**
	 * 获取指定行键的值。
	 * 
	 * @param line 配置文件中的一行字符串。
	 * @return 该行中键的值。
	 */
	private static String getValue(String line) {
		if (line == null || line.equals("")) {
			return null;
		}
		if (line.indexOf("=") < 0) {
			return null;
		}
		return line.substring(line.indexOf("=") + 1).trim();
	}
	
	/**
	 * 获取指定键名的SQL语句。
	 * @param key 指定键名。
	 * @return SQL语句。
	 */
	public String getSql(String key) {
		return prop.get(key);
	}
}

3.TrainSqlReader.java类，跟据sql文件路径，加载sql文件。

import SqlReader

public class TrainSqlReader {

	static boolean isLoad=false;
	static SqlReader sqlReader=new SqlReader(); 
	
	public static void load(){
		if(isLoad==false){
			sqlReader.initial("./train/sql/train.sql");
			isLoad=true;
		}
	}
	
	public static String getSql(String key){
		load();
		return sqlReader.getSql(key);
	}
}

4. TrainDao.java类，这个类使用了一些其他的类没有上传，但是不影响本文的演示。

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


import org.apache.log4j.Logger;

import com.dzd.time.train.db.TrainDBConnPool;
import com.dzd.time.train.db.TrainSqlReader;
import com.dzd.time.train.log.TrainLogger;
import com.dzd.time.train.po.TrainBean;

public class TrainDao {

	static Logger log=TrainLogger.getLogger(TrainDao.class);
	
	public List<String> getTrainNames(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		List<String> trainNames=new ArrayList<String>();
		try {
			
			conn=TrainDBConnPool.getConnection();
			
			String sql=TrainSqlReader.getSql("select_train_names");
			ps=conn.prepareStatement(sql);
			
			rs=ps.executeQuery();
			while(rs.next()){
				trainNames.add(rs.getString("train_name"));
			}
			
		} catch (Exception e) {
			log.error("查询所有车次名称时异常",e);
		}
		finally{
			TrainDBConnPool.close(rs, ps, conn);
		}
		
		return trainNames;
	}
	
	
	public List<String> getTrainStations(String cheCi){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		List<String> trainStations=new ArrayList<String>();
		try {
			
			conn=TrainDBConnPool.getConnection();
			
			String sql=TrainSqlReader.getSql("select_train_station");
			ps=conn.prepareStatement(sql);
			
			int m=0;
			ps.setString(++m, cheCi);
			
			rs=ps.executeQuery();
			while(rs.next()){
				trainStations.add(rs.getString("station_name"));
			}
			
		} catch (Exception e) {
			log.error("查询自定车次 "+cheCi+" 的站名时异常",e);
		}
		finally{
			TrainDBConnPool.close(rs, ps, conn);
		}
		
		return trainStations;
	}
	
	
	public int insert(List<TrainBean> trainList){
		Connection conn=null;
		PreparedStatement sps=null;
		PreparedStatement ips=null;
		ResultSet rs=null;
		
		try {
			conn=TrainDBConnPool.getConnection();
			
			String ssql=TrainSqlReader.getSql("select_train_detail");
			String isql=TrainSqlReader.getSql("insert_train_detail");
			
			sps=conn.prepareStatement(ssql);
			ips=conn.prepareStatement(isql);
			for (TrainBean trainBean : trainList) {

				int m=0;
				sps.setString(++m, trainBean.getTrainName());
				sps.setString(++m, trainBean.getStationSerial());				
				
				rs=sps.executeQuery();
				if(rs.next()){
					continue;
				}
				
				int n=0;
				ips.setString(++n, trainBean.getTrainName());
				ips.setString(++n, trainBean.getStationSerial());
				ips.setString(++n, trainBean.getStationName());
				
				ips.setString(++n, trainBean.getArriveTime());
				ips.setString(++n, trainBean.getStartTime());
				ips.setString(++n, trainBean.getElapsedTime());
				
				ips.setString(++n, trainBean.getDistance());
				
				ips.setString(++n, trainBean.getHardBasedSeat());
				ips.setString(++n, trainBean.getSoftBasedSeat());
				ips.setString(++n, trainBean.getHardSleeper());
				ips.setString(++n, trainBean.getSoftSleeper());
				
				ips.addBatch();
			}
			
			// 执行插入
			int flag[]=ips.executeBatch();
			// 判断是否插入成功
			return flag != null && flag.length >= 0 ? 1 : -1;
			
		} catch (Exception e) {
			log.error("插入火车车次查询结果异常",e);
		}
		finally{
			TrainDBConnPool.close(rs, ips, conn);
			TrainDBConnPool.close(rs, sps, conn);
		}
		
		return -1;
	}
}