jdbc.java

webwork study w ebwork study

package jaction.utility;

import java.util.*;
import java.sql.*;
import javax.sql.DataSource;
import java.lang.reflect.*;


import jaction.datapool.Result;

/**
 *	类名：      JDBC<br>
 *	说明：	    数据库操作类<br>
 * @author yanger
 * @version 1.0
 */

public class JDBC {


	/**
	 * 资源绑定对象
	 */
	private static  MessageResources resource = MessageResources.getMessageResources("jaction.JactionConfig");
	/**
	 * JTA JNDI 配置名称
	 */
	public static final String JACTION_DATASOURCE=resource.getMessage("jaction.jndi.datasource");
	
	
	/**
	 * 从连接池中取得一个空闲的数据库连接
	 * @return  Connection 数据库连接
	 * @exception  Exception 数据库异常
	 */
	public static Connection getConnection() throws Exception{

		Connection conn = null;

    	try {
    		javax.naming.InitialContext ctx = new javax.naming.InitialContext();
    		DataSource ds = (DataSource)ctx.lookup(JACTION_DATASOURCE);
    		conn= ds.getConnection();
		} catch (java.lang.SecurityException se) {
			SysLogger.error("JDBC","getConnection","数据库连接错误，描述信息："+se);
			se.printStackTrace();
			throw se;
        } catch(Exception e) {
         	SysLogger.error("JDBC","getConnection","数据库连接错误，描述信息："+e);
         	e.printStackTrace();
			throw e;
        }

    	return conn;

  	}


	/**
	 * 释放一个空闲的数据库连接至连接池
	 * @param  conn 数据库连接
	 */
	public static void freeConnection(Connection conn) {

		try{
			conn.close();
    		conn=null;
		}catch(Exception e){
			SysLogger.error("JDBC", "freeConnection","释放数据库连接错误,描述信息:"+e);
		}

    }


	/**
	 * 设置指定的数据库连接的提交方式(true/false)，并返回该连接
	 * @param  conn Connection
	 * @param  commitFlag 提交方式(true:自动提交/false:非自动提交)
	 * @return  Connection 数据库连接
	 * @exception  Exception
	 */
	public static Connection setAutoCommit(Connection conn, boolean commitFlag) throws Exception {

		try {
			conn.setAutoCommit(commitFlag);
			return conn;
		} catch (SQLException sx) {
			SysLogger.error("JDBC","setAutoCommit","数据库错误，描述信息："+sx);
			throw sx;
		}

	}

	//commit connection
	/**
	 * 对指定的连接进行提交
	 * @param  conn 数据库连接
	 * @exception  Exception
	 */
	public static void commit(Connection conn) throws Exception {

		try {
			conn.commit();
			setAutoCommit(conn, true);
		}catch (SQLException ex){
			SysLogger.error("JDBC","commit","数据库错误，描述信息："+ex);
			throw ex;
		}

	}

	//rollback connection
	/**
	 * 对指定的连接进行rollback
	 * @param  conn 数据库连接
	 * @exception  Exception
	 */
	static public void rollback(Connection conn) throws Exception {

		try{
			conn.rollback();
		} catch (SQLException ex) {
           SysLogger.error("JDBC","rollback","数据库错误，描述信息："+ex);
           throw ex;
		}
	}

	//execute update sql
	/**
	 * 对指定的sql语句进行JDBC的update操作
	 * @param  query update sql语句
	 * @return  返回成功执行的记录数
	 * @exception  Exception
	 */
	static public int executeUpdate(String query) throws Exception{

		Connection conn = getConnection();
		try{
			return executeUpdate(query, conn);
		} catch(Exception e) {
			throw e;
		} finally {
			freeConnection(conn);
		}

	}

	//execute update sql use connection
	/**
	 * 通过指定的连接对指定的sql语句进行JDBC的update操作
	 * @param  query update sql语句
	 * @param  conn 数据库连接
	 * @return  返回成功执行的记录数
	 * @exception  Exception
	 */
	static public int executeUpdate(String query, Connection conn) throws Exception{

		SysLogger.infoLog(query);

        Statement stmt = null;

		try {
			stmt = conn.createStatement();
			if (conn == null || stmt == null) {
				SysLogger.error("JDBC","executeUpdate","数据库连接错误！");
				return -1;
			}
			int i = stmt.executeUpdate(query);//执行数据库操作
			return i;
		} catch (SQLException ex) {
			SysLogger.error("JDBC","executeUpdate","数据库执行错误！" + query + " SQLException : " + ex);
			 throw ex;
			//return -1;
		} finally {
			try{
				stmt.close();
				stmt = null;
			} catch (SQLException se) {
				SysLogger.error("JDBC","executeUpdate","数据库statement关闭执行错误！" + se);
				throw se;
			}
		}

	}

	//execute query sql
	/**
	 * 对指定的sql语句进行JDBC的query操作
	 * @param  query query sql语句
	 * @return  Result 查询结果集
	 * @exception  Exception
	 */
	static public Result executeQuery(String query) throws Exception {

		Connection conn = getConnection();

		try {
			return executeQuery(query, conn);
		} catch (Exception e) {
			throw e;
		} finally {
			freeConnection(conn);
		}


	}

	//execute query sql use connection
	/**
	 * 通过指定的连接对指定的sql语句进行JDBC的query操作
	 * @param  query query sql语句
	 * @param  conn 数据库连接
	 * @return  Result 查询结果集
	 * @exception  Exception
	 */
	static public Result executeQuery(String query,Connection conn) throws Exception {

		Statement stmt = null;
		ResultSet rs = null;
		ResultSetMetaData metaData = null;
		Vector rows = new Vector();
		Object newRow[];
		Object objectRows[][];

		stmt = conn.createStatement();

		if (conn == null || stmt == null) {
			SysLogger.error("JDBC","executeQuery","数据库连接错误！");
			return null;
		}

		SysLogger.info("JDBC","executeQuery",query);
		try {
			rs = stmt.executeQuery(query);
			metaData = rs.getMetaData();
			int numberOfColumns =  metaData.getColumnCount();
			String  columnNames[] = new String[numberOfColumns];

			// Get the column names and cache them.
			// Then we can close the connection.
			for(int column = 0; column < numberOfColumns; column++) {
				columnNames[column] = metaData.getColumnLabel(column+1);
			}

			// Get all rows.
			//weigang modify 20021104
			//boolean columnWritten = true;
			rows.addElement(columnNames);
			while (rs.next()) {
				/*if (columnWritten)
				{
					rows.addElement(columnNames);
					columnWritten = false;
				}*/
				newRow = new Object[numberOfColumns];
				for (int i = 1; i <= numberOfColumns; i++) {
					Object obj=rs.getObject(i);
					newRow[i - 1] =(obj instanceof String)?	 (String)obj :obj;
				}
				rows.addElement(newRow);
			}
		} catch (SQLException ex) {
			SysLogger.error("JDBC","executeQuery","数据库查询错误"+query + " SQLException " + ex);
			throw ex;
		}finally{

			try{
				rs.close();
			}catch (SQLException se) {
				SysLogger.error("JDBC","executeQuery","数据库resultSet 关闭错误:"+se);
				throw se;
			}

			try{
				stmt.close();
			} catch (SQLException se) {
				SysLogger.error("JDBC","executeQuery","数据库statement关闭错误:"+se);
				throw se;
			}
		}

		if (rows.size() == 0) {
			objectRows = null;
		} else {
			objectRows = new Object[rows.size()][];
			for (int i = 0; i < rows.size(); i++) {
				objectRows[i] = (Object[])rows.elementAt(i);
			}
		}

		Result res = new Result(objectRows);

		return res;

	}

	/**
	 * 对指定的sql语句进行JDBC的query操作，返回从结果集中指定行开始的指定记录数
     *
     * @param	query	数据库查询sql语句
     * @param	beginRows 查询结果的起始位置
     * @param  pageRows 查询记录数
     * @return  Result 查询结果集
	 * @exception  Exception
     */
  	/*weigang modify 20021104*/
    static public Result executeQuery(String query, int beginRows, int pageRows ) throws Exception{
		try {
			if (beginRows <=0 || pageRows <= 0) {
				throw new Exception("beginRows <=0 || pageRows <= 0");
			}

			Result res = executeQuery(query);	//查询结果集

			if (res == null) {
     			return res;
          	}

			int rowNumber = res.rowNum();	//结果集二位数组的行数
			int colNumber = res.colNum();	//结果集二位数组的列数

			if (rowNumber <= beginRows) {
				return null;	//没有符合条件的查询结果
         	}

       		Object[][] oArray = res.getData();	//结果集二维数组

         	/*从结果集中得到需要的记录*/
          	Object[][] newArray;
          	if (rowNumber < beginRows + pageRows - 1) {
  				newArray = new Object[rowNumber - beginRows + 2][];
          	} else {
          			newArray = new Object[pageRows + 1][];
          	}

          	newArray[0] = res.getColumnName();

          	for (int i = 1; i < newArray.length; i++) {
          		newArray[i] = oArray[beginRows + i - 2];
          	}

 			Result newRes = new Result(newArray);
			return newRes;
      	} catch (Exception e) {
			throw e;
		}
	}

	/**
	 * 根据表名和Where条件建立查询语句
	 * @param  tableName 表名
	 * @param  whereStmt where条件
	 * @return  生成的查询语句
	 * @exception  Exception
	 */
	public static String queryStatementFromClass(String tableName, String whereStmt)throws Exception{
		if(tableName==null){
            throw new Exception("tableName==null");
        }

		//Class resultClass=getClassFromTableName(tableName);
		String  [] fields=getFieldFromTableName(tableName);
		String uStmt="select ";
		for(int i=0;i<fields.length;i++)
		{
			if(i!=0)uStmt += ", ";
			uStmt += fields[i]+" ";
		}
		uStmt += " FROM "+tableName;

		if(whereStmt!=null)
		{
			uStmt += " where " + whereStmt ;
		}
		return uStmt;
	}

	/**
	 * 根据名字, 返回类对象。
	 * @param  tableName 表的名字。
	 * @return  类对象的名字。
	 * @exception  Exception
	 */
	public static Class getClassFromTableName(String tableName)throws Exception{
		// 是否定制

		String strClassName = tableName;
		//if (strClassName.equals(tableName)) strClassName = tableName;
		return Class.forName("jaction.resource."+strClassName);
	}

	/**
	 * 根据表名称得到所有字段名称
	 * @param  tableName 表的名字
	 * @return  字段数组
	 * @exception  Exception
	 * @add by zjh 2002-11-1
	 */
	public static String[] getFieldFromTableName(String tableName) throws Exception{
		Connection          connection = null;
		Statement           statement = null;
		ResultSet           resultSet = null;
		ResultSetMetaData   metaData = null;
		int i=0;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("Select * from "+tableName);
			if (resultSet == null){
				throw new Exception("resultSet == null");