Java 下的OracleHelper包

JAVA下的OracleHelper包

在.NET平台下有个被使用的很广泛的SqlHelper工具类，是微软专门针对SQL Server数据库所编写的数据访问层中间件，很好的对ADO.NET进行了封装。在实际应用中，由于项目需求，当时我曾对SqlHelper进行了若干改造，变成了能够针对Oracle数据库进行访问的OracleHelper工具类，这些都是在.NET平台下使用C#实现的。

近期，项目存在向java平台下移植的可能，为了屏蔽JDBC底层API，使平移更加容易，FuWaer花了点时间对C#下OracleHelper进行了改造，改造的目的就是要基本保持原有函数接口形式和类型不变，为此，OracleHelper包由如下类组成：

共10个文件，其中，ey.db.oracle包中的OracleHelper.java实现了对JDBC的封装；ey.db.type包中的9个类是为了保持OracleHelper类函数能够和C#下的OracleHelper类函数的形式和参数类型一致而编写的。

全部内容可以通过以下链接下载：（写此文章时blog无法上传文件，稍后上传，如有需要也可留下您的email或自行编译文末源代码）

源文件：Java_OracleHelper_1_0_20071027_src.zip

JAR包：Java_OracleHelper_1_0_20071027_jar.jar

本工具包的使用实例可以参见《java下OracleHelper包使用实例》

接下来，FuWaer分别对每个类的功能进行简短的介绍，全部源码在文章末尾贴出了，如无法下载源代码，也可分别拷贝源码自行编译。
DataColumn、DataRow、DataTable

这三个类是仿照C#中的对应类实现的，实现了二维表格数据的对象化存储，目的在于对JDBC的ResultSet实现封装，后面将会介绍，OracleHelper.java中的ConvertResultSetToDataTable函数及实现了从ResultSet构造DataTable的过程。
DataSet

这也是仿照C#中的DataSet实现的，它由一个或多个DataTable实例构成，该类用于OracleHelper.java中的ExecuteDataSet函数的返回值类型。它将方便已经熟悉了C#下DataSet操作的程序人员，对数据库查询的结果集将不再以底层的ResultSet存在，应用了DataSet，开发人员可以完全不用知道ResultSet的存在。
OracleConnection

该类是仿照C#中OracleClient命名空间下的OracleConnection实现的。OracleConnection类是对 java.sql.Connection的封装，经过封装，开发人员将不用了解java.sql.Connection的相关内容。该类具有如下主要结构：

构造函数 public OracleConnection(String connectionString)

其中，connectionString是用来构造java.sql.Connection实例所必需的信息，规定connectionString应该具有如下格式：url;user;password，例如：jdbc:oracle:thin:@localhost:1521:db;system;manager

关闭数据库连接函数 public void Close()

启动事务函数 public OracleTransaction BeginTransaction()
OracleTransaction

该类是仿照C#中OracleClient命名空间下的OracleTransaction实现的。OracleTransaction用于事务控制，通常，该类的实例是通过OracleConnection.BeginTransaction()获得的。该类具有如下主要结构：

构造函数 public OracleTransaction(OracleConnection connection)

事务提交函数 public void Commit()

事务回滚函数 public void Rollback()
CommandType

这是个枚举类，用于指示访问数据库的是文本型sql语句还是存储过程，该枚举类定义如下：

public enum CommandType {
Text,
StoreProcedure
}
ParameterDirection

这是个枚举类，用于指示传递给存储过程的参数的方向性，该枚举类定义如下：

public enum ParameterDirection {
IN,
OUT
}
Parameter

这个类用于存储向oracle存储过程所传递的参数。
OracleHelper

该类是访问oracle数据库的核心类，该类以静态函数的形式提供了对数据库访问的功能函数，仿照C#下OracleHelper，提供了12个公有静态函数和1个私有静态函数，针对开发人员来说，主要是应用其中的12个公有静态函数，关于这些函数，这里就不再多说了，如果您对于C#下的 SqlHelper或OracleHelper比较熟悉的话，您一定能很快地知道这些函数该如何使用，这里FuWaer给个函数结构图：

好了，下面给出这10个文件的源代码

文件1：OracleHelper.java
package ey.db.oracle;

import ey.db.type.*;
import java.sql.*;

/**
*
* @author FuWaer
* @version 1.0
*
*/
public class OracleHelper {

    /**
     *
     * @param connectionString
     * @param commandType
     * @param commandText
     * @return
     * @throws Exception
     */
    public static DataSet ExecuteDataSet(String connectionString,
            CommandType commandType, String commandText) throws Exception {
        return ExecuteDataSet(new OracleConnection(connectionString),
                commandType, commandText);
    }

    /**
     *
     * @param connectionString
     * @param commandType
     * @param commandText
     * @param parameters
     * @return
     * @throws Exception
     */
    public static DataSet ExecuteDataSet(String connectionString,
            CommandType commandType, String commandText,
            Parameter... parameters) throws Exception {
        return ExecuteDataSet(new OracleConnection(connectionString),
                commandType, commandText, parameters);
    }

    /**
     *
     * @param connection
     * @param commandType
     * @param commandText
     * @return
     * @throws Exception
     */
    public static DataSet ExecuteDataSet(OracleConnection connection,
            CommandType commandType, String commandText) throws Exception {
        return ExecuteDataSet(connection, commandType, commandText,
                new Parameter[0]);
    }

    /**
     *
     * @param connection
     * @param commandType
     * @param commandText
     * @param parameters
     * @return
     * @throws Exception
     */
    public static DataSet ExecuteDataSet(OracleConnection connection,
            CommandType commandType, String commandText,
            Parameter... parameters) throws Exception {

        DataSet ds = new DataSet();
        if (commandType.equals(CommandType.Text)) {
            Statement s = connection.GetConnection().createStatement();
            ResultSet rs = s.executeQuery(commandText);
            ds.AddTable(ConvertResultSetToDataTable(rs));
        } else if (commandType.equals(CommandType.StoreProcedure)) {

            Parameter[] paras = parameters;

            String sql = "";
            for (int i = 0; i < paras.length; i++) {
                sql = sql + "?,";
            }
            if (sql.length() > 0) {
                sql = "(" + sql.substring(0, sql.length() - 1) + ")";
            }

            sql = "{ call " + commandText + sql + " }";
            CallableStatement proc = null;
            proc = connection.GetConnection().prepareCall(sql);
            for (int i = 0; i < paras.length; i++) {
                Parameter p = paras[i];
                if (p.parameterDirection == ParameterDirection.IN) {
                    proc.setObject(i + 1, p.Value, p.parameterType);
                } else if (p.parameterDirection == ParameterDirection.OUT) {
                    proc.registerOutParameter(i + 1, p.parameterType);
                }
            }
            try {
                proc.execute();

                for (int i = 0; i < paras.length; i++) {
                    Parameter p = paras[i];
                    if (p.parameterDirection == ParameterDirection.OUT) {
                        p.Value = proc.getObject(i + 1);
                        if (p.parameterType == oracle.jdbc.OracleTypes.CURSOR) {
                            ResultSet rs = (ResultSet) p.Value;
                            DataTable dt = ConvertResultSetToDataTable(rs);
                            DataSet _lds = new DataSet();
                            _lds.AddTable(dt);
                            p.Value = _lds;
                            ds.AddTable(dt);
                        }
                    }
                }
            } catch (Exception e) {
                throw e;
            } finally {
                connection.Close();
            }

        } else {
            throw new Exception("commandType is invalid");
        }

        return ds;
    }

    /**
     *
     * @param transaction
     * @param commandType
     * @param commandText
     * @return
     * @throws Exception
     */
    public static DataSet ExecuteDataSet(OracleTransaction transaction,
            CommandType commandType, String commandText) throws Exception {
        return ExecuteDataSet(transaction.Connection, commandType, commandText);
    }

    /**
     *
     * @param transaction
     * @param commandType
     * @param commandText
     * @param parameters
     * @return
     * @throws Exception
     */
    public static DataSet ExecuteDataSet(OracleTransaction transaction,
            CommandType commandType, String commandText,
            Parameter... parameters) throws Exception {
        return ExecuteDataSet(transaction.Connection, commandType, commandText,
                parameters);
    }

    /**
     *
     * @param connectionString
     * @param commandType
     * @param commandText
     * @throws Exception
     */
    public static void ExecuteNonQuery(String connectionString,
            CommandType commandType, String commandText) throws Exception {
        ExecuteNonQuery(new OracleConnection(connectionString), commandType,
                commandText);
    }

    /**
     *
     * @param connectionString
     * @param commandType
     * @param commandText
     * @param parameters
     * @throws Exception
     */
    public static void ExecuteNonQuery(String connectionString,
            CommandType commandType, String commandText,
            Parameter... parameters) throws Exception {
        ExecuteNonQuery(new OracleConnection(connectionString), commandType,
                commandText, parameters);
    }

    /**
     *
     * @param connection
     * @param commandType
     * @param commandText
     * @throws Exception
     */
    public static void ExecuteNonQuery(OracleConnection connection,
            CommandType commandType, String commandText) throws Exception {
        ExecuteNonQuery(connection, commandType, commandText, new Parameter[0]);
    }

    /**
     *
     * @param connection
     * @param commandType
     * @param commandText
     * @param parameters
     * @throws Exception
     */
    public static void ExecuteNonQuery(OracleConnection connection,
            CommandType commandType, String commandText,
            Parameter... parameters) throws Exception {
       
        if (commandType.equals(CommandType.Text)) {
            Statement s = connection.GetConnection().createStatement();
            s.execute(commandText);
           
        } else if (commandType.equals(CommandType.StoreProcedure)) {

            Parameter[] paras = parameters;

            String sql = "";
            for (int i = 0; i < paras.length; i++) {
                sql = sql + "?,";
            }
            if (sql.length() > 0) {
                sql = "(" + sql.substring(0, sql.length() - 1) + ")";
            }

            sql = "{ call " + commandText + sql + " }";
            CallableStatement proc = null;
            proc = connection.GetConnection().prepareCall(sql);
            for (int i = 0; i < paras.length; i++) {
                Parameter p = paras[i];
                if (p.parameterDirection == ParameterDirection.IN) {
                    proc.setObject(i + 1, p.Value, p.parameterType);
                } else if (p.parameterDirection == ParameterDirection.OUT) {
                    proc.registerOutParameter(i + 1, p.parameterType);
                }
            }
            try {
                proc.execute();

                for (int i = 0; i < paras.length; i++) {
                    Parameter p = paras[i];
                    if (p.parameterDirection == ParameterDirection.OUT) {
                        p.Value = proc.getObject(i + 1);
                        if (p.parameterType == oracle.jdbc.OracleTypes.CURSOR) {
                            ResultSet rs = (ResultSet) p.Value;
                            DataTable dt = ConvertResultSetToDataTable(rs);
                            DataSet _lds = new DataSet();
                            _lds.AddTable(dt);
                            p.Value = _lds;
                        }
                    }
                }
            } catch (Exception e) {
                throw e;
            } finally {
                connection.Close();
            }

        } else {
            throw new Exception("commandType is invalid");
        }   
    }

    /**
     *
     * @param transaction
     * @param commandType
     * @param commandText
     * @throws Exception
     */
    public static void ExecuteNonQuery(OracleTransaction transaction,
            CommandType commandType, String commandText) throws Exception {
        ExecuteNonQuery(transaction.Connection, commandType, commandText);
    }

    /**
     *
     * @param transaction
     * @param commandType
     * @param commandText
     * @param parameters
     * @throws Exception
     */
    public static void ExecuteNonQuery(OracleTransaction transaction,
            CommandType commandType, String commandText,
            Parameter... parameters) throws Exception {
        ExecuteNonQuery(transaction.Connection, commandType, commandText,
                parameters);
    }

    /**
     * 将ResultSet装换为DataTable的函数
     *
     * @param rs
     * @return
     * @throws Exception
     */
    private static DataTable ConvertResultSetToDataTable(ResultSet rs)
            throws Exception {
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();

        DataTable dt = new DataTable();
        while (rs.next()) {
            DataRow dr = new DataRow();
            for (int i = 1; i <= columnCount; i++) {
                DataColumn dc = new DataColumn(rsmd.getColumnName(i), rs
                        .getObject(i));
                dr.AddColumn(dc);
            }
            dt.AddRow(dr);
        }
        return dt;
    }
}

文件2：DataColumn.java
package ey.db.type;

/**
*
* @author FuWaer
* @version 1.0
*/
public class DataColumn extends Object {
    public DataColumn()
    {
    }
    public DataColumn(String n,Object v)
    {
        colName = n;
        colValue = v;
    }

    public Object colValue = null;
    public String colName = null;
   
}

文件3：DataRow.java
package ey.db.type;

/**
*
* @author FuWaer
* @version 1.0
*
*/
public class DataRow extends Object {
   
    public DataRow(DataColumn[] cols)
    {
        Columns = cols.clone();
    }
    public DataRow()
    {
       
    }
    public void AddColumn(DataColumn col)
    {
        DataColumn[] _cols = Columns.clone();
        Columns = null;
        Columns = new DataColumn[_cols.length+1];
        for(int i=0;i<_cols.length;i++)
        {
            Columns[i] = _cols[i];
        }
        Columns[Columns.length-1] = col;
        _cols = null;
    }
    public DataColumn [] Columns = new DataColumn[0];
   
}

文件4：DataTable.java
package ey.db.type;

/**
*
* @author FuWaer
* @version 1.0
*/
public class DataTable {
   
    public DataTable(DataRow[] rows)
    {
        Rows = rows.clone();
    }
   
    public DataTable()
    {
       
    }
   
    public void AddRow(DataRow row)
    {
        DataRow[] _rows = Rows.clone();
        this.Rows = null;
        this.Rows = new DataRow[_rows.length+1];
        for(int i=0;i<_rows.length;i++)
        {
            this.Rows[i] = _rows[i];
        }
        this.Rows[this.Rows.length-1] = row;
        _rows = null;
    }
   
    public DataRow[] Rows = new DataRow[0];
}

文件5：DataSet.java
package ey.db.type;

/**
*
* @author FuWaer
* @version 1.0
*/
public class DataSet {
   
    public DataSet(DataTable[] tables)
    {
        this.Tables = tables.clone();
    }
   
    public DataSet()
    {
       
    }
   
    public void AddTable(DataTable table)
    {
        DataTable[] _tables = this.Tables.clone();
        this.Tables = null;
        this.Tables = new DataTable[_tables.length+1];
        for(int i=0;i<_tables.length;i++)
        {
            this.Tables[i] = _tables[i];
        }
        this.Tables[this.Tables.length-1] = table;
        _tables = null;
    }
   
    public DataTable[] Tables = new DataTable[0];
}

文件6：OracleConnection.java
package ey.db.type;

import java.sql.*;

/**
*
* @author FuWaer
* @version 1.0
*/
public class OracleConnection {
   
    /**
     * OracleConnection构造函数
     * @param String connectionString :
     *                 数据库连接参数，格式：url;user;passworld.
     *                 例如：jdbc:oracle:thin:@localhost:1521:db;system;manager
     * @throws Exception
     */
    public OracleConnection(String connectionString) throws Exception
    {
        Class.forName(this._driver);
        String[] _sa = connectionString.split(";");
        this.connection = DriverManager.getConnection(_sa[0], _sa[1], _sa[2]);
    }
   
    /**
     * 关闭数据库连接
     * @throws Exception
     */
    public void Close() throws Exception
    {
        if(!connection.isClosed()&&connection.getAutoCommit())
        {
            this.connection.close();
        }
    }
   
    /**
     * 从OracleConnection类创建事务处理的OracleTransaction类实例
     * @return
     * @throws Exception
     */
    public OracleTransaction BeginTransaction() throws Exception
    {
        OracleTransaction tran = new OracleTransaction(this);
        return tran;
    }
   
    /**
     * 获取java.sql.Connection对象
     * @return java.sql.Connection对象
     */
    public Connection GetConnection()
    {
        return this.connection;
    }
   
    private String _driver = "oracle.jdbc.driver.OracleDriver";
    private Connection connection = null;

}


文件7：OracleTransaction.java
package ey.db.type;

/**
*
* @author FuWaer
* @version 1.0
*/
public class OracleTransaction {

    public OracleTransaction(OracleConnection connection) throws Exception
    {   
        this.Connection = connection;
        this.Connection.GetConnection().setAutoCommit(false);
    }
   
    /**
     * 事务提交
     * @throws Exception
     */
    public void Commit() throws Exception
    {
        try
        {
            this.Connection.GetConnection().commit();
        }
        catch(Exception e)
        {
            throw e;
        }
        finally
        {
            this.Connection.GetConnection().setAutoCommit(true);
            this.Connection.Close();
        }
    }
   
    /**
     * 事务回滚
     * @throws Exception
     */
    public void Rollback() throws Exception
    {
        try
        {
            this.Connection.GetConnection().rollback();
        }
        catch(Exception e)
        {
            throw e;
        }
        finally
        {
            this.Connection.GetConnection().setAutoCommit(true);
            this.Connection.Close();
        }
    }
   
    public OracleConnection Connection = null;

}

文件8：CommandType.java
package ey.db.type;

/**
* 命令类型枚举类
* @author FuWaer
* @version 1.0
*/
public enum CommandType {
    Text,
    StoreProcedure
}

文件9：ParameterDirection.java
package ey.db.type;

/**
*
* @author FuWaer
* @version 1.0
*/
public enum ParameterDirection {
    IN,
    OUT
}

文件10：Parameter.java
package ey.db.type;

/**
*
* @author FuWaer
* @version 1.0
*/
public class Parameter {
    public Parameter(
            String paraName,
            int paraType,
            Object paraValue,
            ParameterDirection paraDirection
            )
    {
        this.parameterName = paraName;
        this.parameterType = paraType;
        this.Value = paraValue;
        this.parameterDirection = paraDirection;
    }
   
    public Parameter(
            String paraName,
            int paraType,
            Object paraValue
            )
    {
        this.parameterName = paraName;
        this.parameterType = paraType;
        this.Value = paraValue;
        this.parameterDirection = ParameterDirection.IN;
    }
   
    public String parameterName = "";
    public Object Value = null;
    public ParameterDirection parameterDirection = null;
    public int parameterType = 0;
}