异曲同工 7个Android应用程序源代码打包下载（与10个不一样）

http://www.eoeandroid.com/thread-34054-1-1.html

http://www.javaeye.com/topic/390347

http://www.eoeandroid.com/thread-22331-1-1.html

zxh--有用

1. 建立对象.由于要操作的都是对象或者对象组成的列表,所以需要在java代码中和oracle中定义相对应的对象.这里以STBid集合为例,首先是数据库端定义字符串(stbid)对象

CREATE OR REPLACE TYPE TYPE_STB_ID AS OBJECT

(

STBID VARCHAR2(64)

)

再定义字符串(stbid)集合

CREATE OR REPLACE TYPE STB_ID_CONNECTIONS

AS TABLE OF TYPE_STB_ID

接下来在java中定义与字符串对象对应的对象:

import java.sql.Connection;

import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

import oracle.jpub.runtime.MutableStruct;

import oracle.sql.Datum;

import oracle.sql.ORAData;

import oracle.sql.ORADataFactory;

import oracle.sql.STRUCT;

public class StbUpdate implements ORAData, ORADataFactory

{

public static final String _SQL_NAME = "TYPE_STB_ID";

private String stbId = "";// NOPMD

public String getStbId()

{

return stbId;

}

public void setStbId(String stbId)

{

this.stbId = stbId;

try

{

_struct.setAttribute(0, this.stbId);

}

catch (final SQLException e)

{

}

/**

* toString

* @return String

@Override

public String toString()

{

return this.stbId;

}

public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

protected MutableStruct _struct;

static int[] _sqlType = {OracleTypes.VARCHAR};

static ORADataFactory[] _factory = new ORADataFactory[1];

static StbUpdate _stbUpdate=new StbUpdate();

public StbUpdate(String stbid)

{

_struct = new MutableStruct(new Object[1], _sqlType, _factory);

this.stbId = stbid;

try

{

_struct.setAttribute(0, this.stbId);

}

catch (final SQLException e)

{

}

public Datum toDatum(final Connection conn)

throws SQLException

{

if (null == _struct)

{

_struct = new MutableStruct(new Object[1], _sqlType, _factory);

try

{

_struct.setAttribute(0, this.stbId);

}

catch (final SQLException e)

{

}

final Datum d = _struct.toDatum(conn, _SQL_NAME);

return d;

}

public ORAData create(final Datum d, final int sqlType)

throws SQLException

{

return create(null, d, sqlType);

}

protected ORAData create(StbUpdate o, final Datum d, final int sqlType)

throws SQLException

{

if (d == null)

{

return null;

}

if (o == null)

{

o = new StbUpdate();

}

o._struct = new MutableStruct((STRUCT)d, _sqlType, _factory);

return o;

}

public static ORADataFactory getORADataFactory()

{

return _stbUpdate;

}

这里需要引进两个oracle的包,一个是ojdbc.jar(MQMC用的是dbchecker.jar),上面所引的oracle下的类全都是来自这个包,还有一个是charsets.jar,这个是oracle的多语言包,用来解决varchar类型字段编码乱码问题的.

至于Java代码中与字符串集合对应的对象,要在调用存储过程的方法中设置.

2. Oracle中的存储过程,总体思路就是先将获得的stbid集合放入一张临时表中,再根据关联关系将数据查询出来存放进另一个集合中,最后将该集合返回.

CREATE OR REPLACE PROCEDURE PRC_QUERY_STB_INFOS

(

STBIDS IN STB_ID_CONNECTIONS,

STBINFOS OUT STB_INFO_CONNECTIONS

)

V_TOTALCOUNT number(10);

TSTBIDS const.varchararraytyp;

MSTATES const.numarraytyp;

MSRIGHTS const.numarraytyp;

MPRECISIONSTATES const.numarraytyp;

MLEVELS const.numarraytyp;

ASTATES const.numarraytyp;

AISRIGHTS const.numarraytyp;

ALEVELS const.numarraytyp;

SSTATES const.numarraytyp;

SASTATES const.numarraytyp;

SLEVELS const.numarraytyp;

v_sql VARCHAR2(4096);

BEGIN

v_sql := 'insert into T_STB_ID_TMP(stbid) values (:1)';

V_TOTALCOUNT := STBIDS.COUNT;

STBINFOS := STB_INFO_CONNECTIONS();

FOR i IN 1 .. V_TOTALCOUNT LOOP

EXECUTE IMMEDIATE v_sql

using STBIDS(i).STBID;

END LOOP ;

v_sql := 'SELECT t.stbid, t.manualstate,t.manualisright,t.manualprecisionstate,t.manuallevel,t.autostate,

t.autoisright,t.autolevel,t.signalstate,t.samplestate,t.samplelevel from T_STB_ID_TMP s,T_TASK_STB t

where t.stbid=s.stbid';

EXECUTE IMMEDIATE v_sql BULK COLLECT

INTO TSTBIDS, MSTATES, MSRIGHTS, MPRECISIONSTATES, MLEVELS, ASTATES, AISRIGHTS, ALEVELS, SSTATES, SASTATES, SLEVELS;

FOR i IN 1..TSTBIDS.COUNT LOOP

STBINFOS.extend;

STBINFOS(STBINFOS.count) := TYPE_STB_INFO(TSTBIDS(i),MSTATES(i),MSRIGHTS(i),MPRECISIONSTATES(i),

MLEVELS(i),ASTATES(i),AISRIGHTS(i),ALEVELS(i),SSTATES(i),SASTATES(i),SLEVELS(i));

END LOOP ;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

STBINFOS:=STB_INFO_CONNECTIONS();

END PRC_QUERY_STB_INFOS;

3. java代码调用存储过程并获得查询结果.这里分两种情况说明.

a. 纯JDBC调用,代码如下:

public static void callprocedure()

{

Connection conn = null;

PreparedStatement ps = null;

CallableStatement callableStatement = null;

ResultSet rs = null;

try

{

// 获取数据库连接

conn = DriverManager.getConnection("jdbc:oracle:thin:@10.40.70.10:1521:orcl", "sqmlwx", "sqmlwx");

if (null == conn)

{

System.out.println("get conn is null");

return;

}

else

{

System.out.println("get conn not null");

}

// 预调用存储过程

callableStatement = conn.prepareCall("{ call PRC_QUERY_STB_INFOS(?,?)}");

// 构造传入参数

List lstStb=new ArrayList();

for (int i = 1; i <= 700; i++)

{

lstStb.add("stb_ptest_" + i);

}

StbUpdate[] strArray=new StbUpdate[700];

for (int i = 0; i < strArray.length; i++)

{

strArray[i] = new StbUpdate(lstStb.get(i));

}

// stbid集合对象

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("STB_ID_CONNECTIONS",

conn);

// 构造ARRAY

ARRAY parameter = new ARRAY(desc, conn, strArray);

// 设置传入参数

callableStatement.setArray(1, parameter);

// 设置返回结果

callableStatement.registerOutParameter(2, Types.ARRAY, "STB_INFO_CONNECTIONS");

// 获取操作结果

Array array = (Array)callableStatement.getObject(2);

// 为空校验

if (null == array)

{

System.out.println(" array is null");

}

// 不为空则打印获得结果

else

{

System.out.println(" array is not null");

Object obj;

String ss="";

//获取存储过程返回的结果集

final Object[] reasonValueList = (Object[])array.getArray();

//判断返回的结果集是否为空,如果不为空则输出结果集记录条数

System.out.println(reasonValueList==null ? "null " : " not null: "+reasonValueList.length);

for (final Object reasonValueResult : reasonValueList)

{

final Struct s = (Struct)reasonValueResult;

obj=s.getAttributes()[0];

ss=obj.toString();

System.out.println("=======================================: "+ss+" : "+s.getAttributes()[1]);

}

catch (SQLException e)

{

e.printStackTrace();

}

catch (Exception e)

{

e.printStackTrace();

}

finally

{

try

{

if (null != rs)

{

rs.close();

}

if (ps != null)

{

ps.close();

}

if (null != conn)

{

conn.close();

}

catch (SQLException e)

{

e.printStackTrace();

}

采用这种方式获得的集合reasonValueList里面存放的是stb对象的属性集合,需要自己将其再封装成对应的对象(Stb).

b. IBATIS调用,首先要配置sql文件

<parameter property="idList" jdbcType="STB_ID_CONNECTIONS"

javaType="java.util.ArrayList" mode="IN"

typeHandler="com.netom.common.device.StbInfoHandler" />

<parameter property="result" jdbcType="ARRAY" javaType="java.sql.Array"

typeName="STB_INFO_CONNECTIONS" mode="OUT"

typeHandler="com.netom.common.device.StbInfoHandler" />

parameterMap>

{ call PRC_QUERY_STB_INFOS(?, ?) }

procedure>

先是参数映射关系parameterMap,其中包含两个参数,一个为传入参数,另一个为结果参数,都为集合,最后一个是定义存储过程procedure.

其次,需要多写一个对象处理类(即配置文件中的StbInfoHandler),里面主要是两个方法:第一个是传入参数重新编码的方法,另一个是获得集合后封装对象的方法.

import java.math.BigDecimal;

import java.sql.Array;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Struct;

import java.util.ArrayList;

import java.util.List;

import oracle.jdbc.OracleTypes;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;

import com.ibatis.sqlmap.client.extensions.ResultGetter;

import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

import com.ibatis.sqlmap.engine.type.JdbcTypeRegistry;

import com.netom.interceptor.logger.Logger;

public class StbInfoHandler implements TypeHandlerCallback

{

private static final String EMP_SALARY_REC ="TYPE_STB_ID";

private static final String EMP_SALARY_TAB = "STB_ID_CONNECTIONS";

static

{

JdbcTypeRegistry.setType(EMP_SALARY_REC, OracleTypes.STRUCT);

JdbcTypeRegistry.setType(EMP_SALARY_TAB, OracleTypes.ARRAY);

};

/**

* 获得结果

* @param getter 结果

* @return Object

* @throws SQLException 异常

@Override

public Object getResult(ResultGetter getter) throws SQLException

{

final List result = new ArrayList();

Stb stbInfo = null;

try

{

final Array array = (Array)getter.getObject();

final Object[] reasonValueList = (Object[])array.getArray();

for (final Object reasonValueResult : reasonValueList)

{

final Struct s = (Struct)reasonValueResult;

try

{

final int two = 2;

final int three = 3;

final int four = 4;

final int five = 5;

final int six = 6;

final int seven = 7;

final int eight = 8;

final int nine = 9;

final int ten = 10;

stbInfo = new Stb();

stbInfo.setStbID((String)s.getAttributes()[0]);

stbInfo.setManualState(((BigDecimal)s.getAttributes()[1]).intValue());

stbInfo.setManualIsRight(((BigDecimal)s.getAttributes()[two]).intValue());

stbInfo.setManualPrecisionState(((BigDecimal)s.getAttributes()[three]).intValue());

stbInfo.setManualLevel(((BigDecimal)s.getAttributes()[four]).intValue());

stbInfo.setAutoState(((BigDecimal)s.getAttributes()[five]).intValue());

stbInfo.setAutoIsRight(((BigDecimal)s.getAttributes()[six]).intValue());

stbInfo.setAutoLevel(((BigDecimal)s.getAttributes()[seven]).intValue());

stbInfo.setSignalState(((BigDecimal)s.getAttributes()[eight]).intValue());

stbInfo.setSampleState(((BigDecimal)s.getAttributes()[nine]).intValue());

stbInfo.setSampleLevel(((BigDecimal)s.getAttributes()[ten]).intValue());

result.add(stbInfo);

}

catch (final Exception e)

{

// 可能由于底层数据不合格，导致空指针异常

// 避免一条记录失败引起全部记录失败

Logger.error("Reason Error:" + stbInfo);

}

catch (final Exception e)

{

Logger.error("VMOSLevelHandler Error:" + e.getMessage());

}

return result;

}

/**

* 设置参数

* @param ps 设置

* @param param 参数

* @throws SQLException 异常

@SuppressWarnings("unchecked")

public void setParameter(final ParameterSetter setter, Object parameter)

throws SQLException

{

if (parameter instanceof ArrayList)

{

final List arr = (ArrayList)parameter;

StbUpdate[] strArray=new StbUpdate[arr.size()];

for (int i = 0; i < strArray.length; i++)

{

strArray[i] = arr.get(i);

}

final Statement stmt = setter.getPreparedStatement();

final org.apache.commons.dbcp.PoolableConnection conn = (org.apache.commons.dbcp.PoolableConnection)stmt.getConnection();

final java.sql.Connection realConn = conn.getDelegate();

final ArrayDescriptor desc = ArrayDescriptor.createDescriptor(EMP_SALARY_TAB,

realConn);

parameter = new ARRAY(desc, realConn, strArray);

}

setter.setObject(parameter);

}

@Override

public Object valueOf(String arg0)

{

// TODO Auto-generated method stub

return null;

}

最后,调用存储过程的方法中的代码

// 获取stbid队列

for (final StbTaskBean stbTaskBean : lstUpdate)

{

tmpStbID = stbTaskBean.getStbRegisterReq().getStbId();

idList.add(new StbUpdate(tmpStbID));

reqMap.put(tmpStbID,

stbTaskBean.getStbRegisterReq());

}

final Map<String, Object> conditions = new HashMap<String, Object>();

conditions.put("idList", idList);

// 查询stb信息

final Map result = dbHelper.dynamicQueryMap("Device.queryStbTaskidListInfo",

conditions);

if (!result.isEmpty())

{

stbList = (List) result.get(IndexConstants.KEY_RESULT);

if (null != stbList)

{

……