DBtool.java

?

package com.zte.upf.client.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.log4j.Logger;

public class DBTool {
	private static final Logger log = Logger.getLogger(DBTool.class);
	
	private static Properties config = new Properties();
	
	static{	
		try {
			config.load(DBTool.class.getClassLoader().getResourceAsStream("database/oracle.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 实现分页查询的功能
	 * @param sql 查询的SQL语句
	 * @param params SQL参数
	 * @param page 分页参数(要查询的页数、每页大小)
	 * @param isCount 是否查询总数
	 * @return 结果集，包括分页信息
	 */
	public Page queryDataOfPage(Page page, String sql,boolean isCount, Object ...params){
		List<Map<String,String>> resultList = new ArrayList<Map<String,String>>();
		try {
			if(isCount){
				page.setTotalCount(count(sql, params));
				if(page.getPageNum() > page.getPageCount()){
					page.setPageNum(page.getPageCount());
				}
			}
			String _pageHandle = config.getProperty("PageHandle");
			_pageHandle = _pageHandle.replace("#SQL#", sql);
			_pageHandle = _pageHandle.replace("#BEGININDEX#", "" + page.getBeginIndex());
			_pageHandle = _pageHandle.replace("#ENDINDEX#", "" + page.getEndIndex());
			resultList = (List<Map<String, String>>) this.doQuery(_pageHandle, params);
			page.setData(resultList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return page;
	}
	/**
	 * 查询总记录数
	 * @param sql 查询的SQL语句
	 * @param params SQL参数
	 * @return int 记录数
	 * @throws Exception
	 */
	public int count(String sql, Object ...params) throws Exception{
		StringBuffer sb = new StringBuffer("SELECT count(1) as count FROM (");
		sb.append(sql);
		sb.append(")");
		List<Map<String, String>> countList = doQuery(sb.toString(), params);
		if(countList.size() > 0){
			Map<String,String> countMap = countList.get(0);
			return Integer.parseInt(countMap.get("count"));
		}
		return 0;
	}

	
	/**
	 * 根据SQL查询结果集
	 * @param sql 查询的SQL语句
	 * @param objs SQL参数
	 * @return List<Map<String, String>> 查询结果列表
	 * @throws Exception
	 */
	public List<Map<String, String>> doQuery(String sql, Object ... params) throws Exception {
		Connection conn = null;
		//Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet result = null;
		try {
			// 取出一个数据库连接
			conn = ConnectionPool.getInstance().getConnection();
			// 取出游标
			pstmt = conn.prepareStatement(sql);
			//设置参数
			for(int i = 0; i < params.length; i++){
				pstmt.setObject(i + 1, params[i]);
			}
			
			// 执行SQL查询语句
			result = pstmt.executeQuery();
			// 获得结果集中字段类型属性
			ResultSetMetaData metaData = result.getMetaData();
			String[] names = new String[metaData.getColumnCount()];
			// 循环定位，获得字段名称和类型
			int i = 0;
			for (i = 0; i < metaData.getColumnCount(); i++) {
				names[i] = (metaData.getColumnLabel(i + 1)).toLowerCase();
			}
			String str = "";
			// 取出查询结果
			List<Map<String, String>> lst = new ArrayList<Map<String, String>>();
			HashMap<String, String> tmp = new HashMap<String, String>();
			while (result.next()) {
				// 重构输出结构
				tmp = new HashMap<String, String>();
				// 按字段名循环定制输出结构
				for (i = 0; i < names.length; i++) {
					// 不管什么数据类型，一律按字符串取出
					str = result.getString(i + 1);
					// 如果取出的字段是null值，将这个字符串置为空字符串
					str = str == null ? "" : str.trim();
					// 定制输出结构
					tmp.put(names[i], str);
				}
				lst.add(tmp);
			}
			return lst;
		} catch (Exception e) {
			log.error("sql:" + sql, e);
			throw e;
		} finally {
			try {
				// 关闭数据库查询结果集
				if (result != null) {
					result.close();
				}
				result = null;
				if (pstmt != null) {
					pstmt.c