gddbcommon.java

j2ee程序。 spring、xml、 实现增加内容选项。

/**
 * 本类用来获取数据结果集，可以使用PreparedStatement，也可以使用Statement
 */
package com.gd.jdbc.impl;

import java.io.InputStream;
import java.io.Reader;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.CachedRowSet;

import com.gd.jdbc.DbCommon;
import com.gd.mvc.exception.VODataValidate;
import com.gd.mvc.io.InfoInAndOut;
import com.sun.rowset.CachedRowSetImpl;

public class GdDbCommon implements DbCommon {
	private Connection conn;
	private PreparedStatement pstmt = null;
	private Statement stmt = null;
	private boolean isStmt = true; 
	private String sql = "";
	private InfoInAndOut infoIn = null;
	
	/**
	 * 构造函数
	 */
	public GdDbCommon() {
	}
	
	/**
	 * 构造函数
	 */
	public GdDbCommon(Connection conn) {
		this.conn = conn;
	}
	
	/**
	 * 使用PreparedStatement
	 */
	public void setPstmt() {
		this.isStmt = false;
	}

	/* (non-Javadoc)
	 * @see com.gd.jdbc.dbCommon#executeUpdate(java.lang.String)
	 */
	public int executeUpdate(String sql) throws SQLException {
		this.sql = sql;
		int numRow = 0;
		stmt = null;
		pstmt = null;
		//判断连接是否为空或已关闭
		if (conn == null || conn.isClosed())
		    throw new  SQLException("请首先创建或获取一个连接");
		//判断传进来的sql是否为空
		if (sql == null || "".equals(sql.trim()))
			throw new  SQLException("sql为null");
		try {
			//判断是否使用Statement
			if (isStmt) {//使用Statement
				synchronized (this.conn) {//用来保证一个对象在多个线程中访问该方法时是线程安全的
	                stmt = this.conn.createStatement();
	            }
				numRow = stmt.executeUpdate(sql);//返回执行成功的笔数
				if (stmt != null) {
	                stmt.close();
	            }
			} else {//使用PreparedStatement
				synchronized (this.conn) {//用来保证一个对象在多个线程中访问该方法时是线程安全的
					pstmt = this.conn.prepareStatement(sql);
	            }
				numRow = pstmt.executeUpdate();//返回执行成功的笔数
				if (pstmt != null) {
					pstmt.close();
	            }
			}	
		} catch (SQLException e) {
			throw new SQLException("执行executeUpdate失败" + sql + e);
        } finally {
            return numRow;
        }
	}

	/* (non-Javadoc)
	 * @see com.gd.jdbc.dbCommon#getCachedRowSet(java.lang.String)
	 */
	public CachedRowSet getCachedRowSet(String sql) throws SQLException {
		CachedRowSetImpl crs = null;
		try {
			crs = new CachedRowSetImpl();
	        crs.populate(this.getResultSet(sql));//通过getResultSet（）方法实现CachedRowSet
		} catch (SQLException e) {
			throw new SQLException("执行getCachedRowSet失败" + sql + e);
        } finally {
        	return crs;
        } 
	}
	
	/* 
	 * 用来通过PreparedStatement，获取单笔数据结果集
	 * @return CachedRowSet
	 * @throws SQLException
	 */
	public CachedRowSet getCachedRowSet() throws SQLException {
		CachedRowSetImpl crs = null;
		try {
			crs = new CachedRowSetImpl();
	        crs.populate(this.getResultSet());//通过getResultSet（）方法实现CachedRowSet
		} catch (SQLException e) {
			throw new SQLException("执行getCachedRowSet失败" + e);
        } finally {
        	return crs;
        } 
	}

	/* (non-Javadoc)
	 * @see com.gd.jdbc.dbCommon#getResultSet(java.lang.String)
	 */
	public ResultSet getResultSet(String sql) throws SQLException {
		ResultSet rs = null;
		try {
			rs = this.getAllResultSet(sql, 1);//通过getAllResultSet（）方法实现ResultSet
		} catch (SQLException e) {
			throw new SQLException("执行getResultSet失败" + sql + e);
        } finally {
            return rs;
        }
	}
	
	/* 用来通过PreparedStatement，获取单笔数据结果集
	 * @return ResultSet
	 * @throws SQLException
	 */
	public ResultSet getResultSet() throws SQLException {
		ResultSet rs = null;
		try {
			rs = this.getAllResultSet();//通过getAllResultSet（）方法实现ResultSet
		} catch (SQLException e) {
			throw new SQLException("执行getResultSet失败" + e);
        } finally {
            return rs;
        }
	}
	
	/* (non-Javadoc)
	 * @see com.gd.jdbc.dbCommon#getAllCachedRowSet(java.lang.String)
	 */
	public CachedRowSet getAllCachedRowSet(String sql, int limit) throws SQLException {
		CachedRowSetImpl crs = null;
		try {
			crs = new CachedRowSetImpl();
	        crs.populate(this.getAllResultSet(sql, limit));//通过getAllResultSet（）方法实现CachedRowSet
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("执行getAllCachedRowSet失败" + sql + e);
        } finally {
        	return crs;
        } 
	}
	
	/* 用来通过PreparedStatement，获取多笔数据结果集
	 * @return CachedRowSet
	 * @throws SQLException
	 */
	public CachedRowSet getAllCachedRowSet() throws SQLException {
		CachedRowSetImpl crs = null;
		try {
			crs = new CachedRowSetImpl();
	        crs.populate(this.getAllResultSet());//通过getAllResultSet（）方法实现CachedRowSet
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("执行getAllCachedRowSet失败" + e);
        } finally {
        	return crs;
        } 
	}

	/* (non-Javadoc)
	 * @see com.gd.jdbc.dbCommon#getAllResultSet(java.lang.String)
	 */
	public ResultSet getAllResultSet(String sql, int limit) throws SQLException {
		this.sql = sql;
		stmt = null;
		pstmt = null;
		ResultSet rs = null;
		//判断连接是否为空或已关闭
		if (conn == null || conn.isClosed())
		    throw new  SQLException("请首先创建或获取一个连接");
		//判断传进来的sql是否为空
		if (sql == null || "".equals(sql.trim()))
			throw new  SQLException("sql为null");
		try {
			
			//判断是否使用Statement
			if (isStmt) {//使用Statement
				synchronized (this.conn) {//用来保证一个对象在多个线程中访问该方法时是线程安全的
	                stmt = this.conn.createStatement();
	            }
				if (limit > 0) {
	                stmt.setMaxRows(limit);
	            }
				rs = stmt.executeQuery(sql);
			} else {//使用PreparedStatement
				this.PreparedStatement(sql, limit);//通过PreparedStatement（）方法生成pstmt
				rs = pstmt.executeQuery();
			}
		} catch (SQLException e) {
			throw new SQLException("执行getAllResultSet失败" + sql + e);
        } finally {
            return rs;
        }
	}
	
	/**
	 *  用来通过PreparedStatement，获取多笔数据结果集
	 * @return ResultSet
	 * @throws SQLException
	 */
	public ResultSet getAllResultSet() throws SQLException {
		ResultSet rs = null;
		if (pstmt == null)//pstmt为空时必须先创建PreparedStatement
		    throw new  SQLException("请首先创建或获取一个PreparedStatement,即必须首先调用PreparedStatement（）方法");
		try {
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			throw new SQLException("执行getAllResultSet失败" + e);
        } finally {
            return rs;
        }
	}
	
	/**
	 * 用来获取单笔数据结果集
	 * @param sql
	 * @return Map
	 * @throws SQLException
	 */
	public Map queryMap(String sql) throws SQLException {
		Map map = null;
        try {
            //单笔通过多笔实现
            Map[] maps = this.queryAllMap(sql, 1);
            if (maps != null && maps.length == 1) {
            	map = maps[0];//只取一笔
            }
        } catch (SQLException e) {
        	throw new SQLException("执行queryMap失败" + sql + e);
        } finally {
            return map;  
        }
	}
	
	/**
	 * 用来通过PreparedStatement，获取单笔数据结果集
	 * @return Map
	 * @throws SQLException
	 */
	public Map queryMap() throws SQLException {
		Map map = null;
        try {
        	//单笔通过多笔实现
            Map[] maps = this.queryAllMap();
            if (maps != null && maps.length == 1) {
            	map = maps[0];//只取一笔
            }
        } catch (SQLException e) {
        	throw new SQLException("执行queryMap失败" + e);
        } finally {
            return map;  
        }
	}
	
	/**
	 * 用来获取多笔数据结果集
	 * @param sql
	 * @param limit,用来限制查询笔数，<=0则代表不限制
	 * @return Map[]
	 * @throws SQLException
	 */
	public Map[] queryAllMap(String sql, int limit) throws SQLException {
		Map[] map = null;
		CachedRowSet rs = null;
        List list = new ArrayList();
        try {
        	//通过getAllCachedRowSet获取rs，然后循环将每笔转换为map
            for (rs = this.getAllCachedRowSet(sql, limit); rs.next();) {
            	list.add(getMapFromRs(rs));//将每笔转换为map
            }
        } catch (SQLException e) {
        	throw new SQLException("执行queryAllMap失败" + sql + e);
        } finally {
            if (rs != null) {
            	rs.close();//关闭rs
            }
            if (list.size() != 0) {
                map = new Map[list.size()];
                list.toArray(map);//将list转换为map数组
            }
            return map;  
        }
	}
	
	/**
	 * 用来通过PreparedStatement，获取多笔数据结果集
	 * @return Map[]
	 * @throws SQLException
	 */
	public Map[] queryAllMap() throws SQLException {
		Map[] map = null;
		CachedRowSet rs = null;
        List list = new ArrayList();
        try {
//        	通过getAllCachedRowSet获取rs，然后循环将每笔转换为map
            for (rs = this.getAllCachedRowSet(); rs.next();) {
            	list.add(getMapFromRs(rs));//将每笔转换为map
            }
        } catch (SQLException e) {
        	throw new SQLException("执行queryAllMap失败" + e);
        } finally {
            if (rs != null) {
            	rs.close();//关闭rs
            }
            if (list.size() != 0) {
                map = new Map[list.size()];
                list.toArray(map);//将list转换为map数组
            }
            return map;  
        }
	}
	
	/**
	 * 用来获取单笔数据结果集,希望返回的是对象，可以转换为Javabean
	 * @param sql
	 * @return Object
	 * @throws SQLException
	 */
	public Object queryObj(String sql) throws SQLException {
		Object obj = null;
        try {
        	//单笔通过多笔实现
        	List list = this.queryAllObj(sql, 1);
            if (list != null && list.size() == 1) {
            	obj = list.get(0);//只取一笔
            }
        } catch (SQLException e) {
        	throw new SQLException("执行queryObj失败" + sql + e);
        } finally {
            return obj;  
        }
	}
	
	/**
	 * 用来通过PreparedStatement，获取单笔数据结果集,希望返回的是对象，可以转换为Javabean
	 * @return Object
	 * @throws SQLException
	 */
	public Object queryObj() throws SQLException {
		Object obj = null;
        try {
        	//单笔通过多笔实现
        	List list = this.queryAllObj();
            if (list != null && list.size() == 1) {
            	obj = list.get(0);//只取一笔
            }
        } catch (SQLException e) {
        	throw new SQLException("执行queryObj失败" + e);
        } finally {
            return obj;  
        }
	}
	
	/**
	 * 用来获取多笔数据结果集,希望返回的是对象，可以转换为Javabean
	 * @param sql
	 * @param limit,用来限制查询笔数，<=0则代表不限制
	 * @return List
	 * @throws SQLException
	 */
	public List queryAllObj(String sql, int limit) throws SQLException {
		CachedRowSet rs = null;
        List list = null;
        try {
        	list = new ArrayList();
        	//通过getAllCachedRowSet，取得rs，然后循环获取每个Object
            for (rs = this.getAllCachedRowSet(sql, limit); rs.next();) {
            	list.add(getObjFromRs(rs));//获取每个Object，将其存入list
            }
        } catch (SQLException e) {
        	throw new SQLException("执行queryAllObj失败" + sql + e);
        } finally {
            if (rs != null) {
            	rs.close();//关闭rs
            }
            return list;  
        }
	}
	
	/**
	 * 用来通过PreparedStatement，获取多笔数据结果集,希望返回的是对象，可以转换为Javabean
	 * @return List
	 * @throws SQLException
	 */
	public List queryAllObj() throws SQLException {
		CachedRowSet rs = null;
        List list = null;
        try {
        	list = new ArrayList();
        	//通过getAllCachedRowSet，取得rs，然后循环获取每个Object
            for (rs = this.getAllCachedRowSet(); rs.next();) {
            	list.add(getObjFromRs(rs));//获取每个Object，将其存入list
            }
        } catch (SQLException e) {
        	throw new SQLException("执行queryAllObj失败" + e);
        } finally {
            if (rs != null) {
            	rs.close();//关闭rs
            }
            return list;  
        }
	}
	
	/**
     * 该方法用来将获取的栏位名称和栏位内容相对应,可以由继承它的类实现，这样可以与VO相结合。
     * 
     * @param rs
     * @return Object
     */
	protected Object getObjFromRs(ResultSet rs) throws SQLException  {
		//获取infoIn中存储的在Xml中设定的对应信息
		List voId = (infoIn.get("voId") == null) ? new ArrayList() : (List)infoIn.get("voId");//获取Xml中设定的voId
	    List voClass = (infoIn.get("voClass") == null) ? new ArrayList() : (List)infoIn.get("voClass");//获取Xml中设定的voClass
	    List voType = (infoIn.get("voType") == null) ? new ArrayList() : (List)infoIn.get("voType");//获取Xml中设定的voType
	    List voTable = (infoIn.get("voTable") == null) ? new ArrayList() : (List)infoIn.get("voTable");//获取Xml中设定的voValidate
		//确定开发人员设定sql中的表名，这里只对第一个表中的值转换为VO
	    int startPos = sql.toUpperCase().indexOf("FROM") + 4;//取得第一个from中m的后一个字符的位置
		String strStart = sql.substring(startPos).trim();//取得从第一个表名后的sql字符串的值
		char[] sqlStr = strStart.toCharArray();//将其转换为char[]
		int endPos = 0;
		//对从第一个表名后的sql字符串的值进行一个一个的循环
		for (int i = 0; sqlStr != null && sqlStr.length > i; i++) {
			//如果从第一个表名后的sql字符串的值中有空格或,则停止循环，并记录此时的位置，则之前的字符串即为表名
			if (" ".equals(String.valueOf(sqlStr[i])) || ",".equals(String.valueOf(sqlStr[i]))) {
				endPos = i;
				break;
		    } else {
		    	endPos = i + 1;//如果整个从第一个表名后的sql字符串的值都为表名
		    }
		}