Java Code Examples for java.sql.CallableStatement#getObject()

protected static Object[] callListStructProcedureByTidList(Connection conn, String proc, 
    int oid, int tid) throws SQLException { 
  CallableStatement cs = null;
  cs = conn.prepareCall("{call " + proc + "(?, ?, ?)}");
  cs.registerOutParameter(3, Types.JAVA_OBJECT);
  cs.setInt(1, oid);
  cs.setInt(2, tid);
  
  cs.execute();
  Object[] rs = (Object[])cs.getObject(3);
  
  SQLWarning warning = cs.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rs;
}
 

private JsonArray convertOutputs(CallableStatement statement) throws SQLException {
  JsonArray result = new JsonArray();

  for (int i = 0; i < out.size(); i++) {
    Object var = out.getValue(i);

    if (var != null) {
      Object value = statement.getObject(i + 1);
      if (value == null) {
        result.addNull();
      } else if (value instanceof ResultSet) {
        result.add(helper.asList((ResultSet) value).toJson());
      } else {
        result.add(JDBCStatementHelper.convertSqlValue(value));
      }
    } else {
      result.addNull();
    }
  }

  return result;
}
 

@SuppressWarnings({"unchecked"})
@Override
public <T> T queryObject(String dataSource, String sql, Object[] args) {
    try {
        long time = System.currentTimeMillis();
        CallableStatement pstmt = newPreparedStatement(dataSource, Mode.Read, sql);
        setArgs(pstmt, args);
        int index = (validator.isEmpty(args) ? 0 : args.length) + 1;
        pstmt.registerOutParameter(index, Types.JAVA_OBJECT);
        pstmt.execute();
        T object = (T) pstmt.getObject(index);
        pstmt.close();

        if (logger.isDebugEnable())
            logger.debug("执行SQL[{}:{}:{}:{}]检索操作。", dataSource, sql, converter.toString(args), System.currentTimeMillis() - time);

        return object;
    } catch (SQLException e) {
        logger.warn(e, "执行SQL[{}:{}:{}]检索时发生异常！", dataSource, sql, converter.toString(args));

        throw new RuntimeException(e);
    }
}
 

protected static Object[] callListStructProcedureByTidList(Connection conn, String proc, 
    int oid, int tid) throws SQLException { 
  CallableStatement cs = null;
  cs = conn.prepareCall("{call " + proc + "(?, ?, ?)}");
  cs.registerOutParameter(3, Types.JAVA_OBJECT);
  cs.setInt(1, oid);
  cs.setInt(2, tid);
  
  cs.execute();
  Object[] rs = (Object[])cs.getObject(3);
  
  SQLWarning warning = cs.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rs;
}
 

/**
 * <p>
 * Verify that you can use UDTs as output parameters in database procedures.
 * </p>
 */
public void test_17_outputParameters() throws Exception
{
    Connection conn = getConnection();

    goodStatement( conn, "create type intArray_17 external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray' language java\n" );
    goodStatement
        ( conn,
          "create procedure changeIntArray_17\n" +
          "( in newSize int, inout oldIntArray intArray_17 )\n" +
          "language java parameter style java no sql\n" +
          "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.changeIntArray'\n" );
    
    CallableStatement cs = chattyPrepareCall( conn, "call changeIntArray_17( ?, ? )" );
    cs.registerOutParameter( 2, java.sql.Types.JAVA_OBJECT );
    cs.setInt( 1, 2 );
    cs.setObject( 2,  new IntArray( new int[ 5 ] ) );
    cs.execute();
    Object obj = cs.getObject( 2 );
    cs.close();

    assertEquals( "[ 0, 0 ]", obj.toString() );
}
 

private Map<String, Object> extractOutputParameters(CallableStatement statement,
        List<Parameter> callParameters) throws SQLException {

    Map<String, Object> returnedResults = new LinkedHashMap<String, Object>();
    for (Parameter parameter : callParameters) {
        Object value = parameter.getName() == null ? statement.getObject(parameter.getIndex())
                : statement.getObject(parameter.getName());

        parameter.setValue(value);
        if (value instanceof ResultSet) {
            value = parameter.getResultSetExtractor().extract(statement.getResultSet());
        }
        returnedResults.put(parameter.getName(), value);
    }
    return returnedResults;
}
 

public void testDataAwareProcedureWithoutResultSetsUsingGlobalIndex()
throws Exception {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE1 WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 

public void testDataAwareProcedureWithoutResultSets()
throws SQLException {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 

@Override
public ResultSet getResultSet(CallableStatement statement, int position) throws SQLException {
	if ( position != 1 ) {
		throw new UnsupportedOperationException( "PostgreSQL only supports REF_CURSOR parameters as the first parameter" );
	}
	return (ResultSet) statement.getObject( 1 );
}
 

public void testDataAwareProcedureWithoutResultSetsUsingGlobalIndex()
throws Exception {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE1 WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 

public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	ps.execute();
	return ( ResultSet ) ps.getObject( 1 );
}
 

private InputStream executeProc(Connection conn, String procedureName,
        LinkedHashMap<String, Object> parameters, String charSet, boolean usingReturn) throws SQLException {
    // the generated code sends a empty string rather than null.
    if (charSet != null && charSet.trim().isEmpty()) {
        charSet = null;
    }
    Object result = null;
    StringBuilder sb = new StringBuilder();
    sb.append("{ "); //$NON-NLS-1$
    if (usingReturn) {
        sb.append("? = "); //$NON-NLS-1$
    }
    sb.append("CALL ").append(procedureName); //$NON-NLS-1$
    sb.append("("); //$NON-NLS-1$
    boolean first = true;
    for (Map.Entry<String, Object> entry : parameters.entrySet()) {
        if (entry.getValue() == null) {
            continue;
        }
        if (!first) {
            sb.append(", "); //$NON-NLS-1$
        }
        first = false;
        sb.append(SQLStringVisitor.escapeSinglePart(entry.getKey())).append("=>?"); //$NON-NLS-1$
    }
    sb.append(") }"); //$NON-NLS-1$

    CallableStatement statement = conn.prepareCall(sb.toString());
    if (!parameters.isEmpty()) {
        int i = usingReturn ? 2 : 1;
        for (Object value : parameters.values()) {
            if (value == null) {
                continue;
            }
            statement.setObject(i++, value);
        }
    }

    final boolean hasResultSet = statement.execute();
    if (hasResultSet) {
        ResultSet rs = statement.getResultSet();
        if (rs.next()) {
            result = rs.getObject(1);
        } else {
            throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "Only result producing procedures are allowed");
        }
    } else if (usingReturn) {
        result = statement.getObject(1);
    }
    return handleResult(charSet, result);
}
 

/**
 * 执行存储过程(更新，查询数据[简单查询、非纪录集]，返回输出参数[非纪录集])
 * 
 * @param procName
 *            存储过程名称
 * @param parameters
 *            参数对象数组
 * @param os
 *            输出参数对象数组
 * @return 输出参数对象数组
 * @throws Exception
 */
public Object[] executeProcedureUpdate(String procName, Object[] parameters)
		throws Exception {
	logger.info("------------------------------------------------------------------------------------------------------");
	logger.info(" Run --> executeProcedureUpdate ##############   正在执行 存储过程: " + procName +"   ##############");
	CallableStatement cs = null;
	Object []returnVal = null;
	try {
	// 获取 存储过程 调用全名
	String fullPCallName = getProcedureCallName(procName,parameters.length);
	logger.info(" Run --> executeProcedureUpdate #   存储过程命令: " + fullPCallName +"   #");
	//获取存储过程参数信息
	ArrayList<HashMap<Object, Object>> p_Call_Info_List = getProcedureInfo(procName);
	//获取连接
	//getConnection();
	//创建 存储过程 执行对象
	cs = con.prepareCall(fullPCallName);
	//数组下标
	int index = 1;
	//输出参数下标 纪录
       ArrayList<Integer> outPutIndexList = new ArrayList<Integer>();
       logger.info(" Run --> executeProcedureUpdate #   参数个数是: " + parameters.length +"   #");
	for(HashMap<Object,Object> tempHash:p_Call_Info_List)
	{
		if("0".equals(tempHash.get("WAY")))
	    {
			//设置输入参数
			cs.setObject(index, parameters[index-1]);
			logger.info(" Run --> executeProcedureUpdate #   输入 Input: 编号:" + index +" 值: "+parameters[index-1]+" 类型: "+parameters[index-1].getClass()+"   #");
		}
		else
		{
			//注册输出参数
			cs.registerOutParameter(index, getDataType(tempHash.get("TYPENAME").toString()));
			//纪录输出参数的下标
			outPutIndexList.add(index);
			logger.info(" Run --> executeProcedureUpdate #   输出 OutPut: 编号:" + index +" 值: "+parameters[index-1]+" 类型: "+parameters[index-1].getClass()+"   #");
		}
		index++;
	}
	logger.info(" Run --> executeProcedureUpdate #   参数设置完毕,正在执行中 ... :   #");
	
	//-------------------- 执行 -----------------
	if(!cs.execute())
	{
		returnVal = new Object[outPutIndexList.size()];
		logger.info(" Run --> executeProcedureUpdate #   执行成功! :   #");
		//取输 出参数的 返回值
		for(int i = 0 ;i<outPutIndexList.size();i++)
		{
			returnVal[i] = cs.getObject(outPutIndexList.get(i));
			logger.info(" Run --> executeProcedureUpdate #   返回值 "+(i+1)+" "+returnVal[i]+"   #");
		}
		//con.commit();//提交
	}
	} catch (Exception e) {
		logger.info(" Run --> executeProcedureUpdate #   执行失败!事务回滚中... :   #");
		//con.rollback();
		throw e;
	} 
	logger.info("------------------------------------------------------------------------------------------------------");
	return returnVal;
}
 

@Override
public Object getNullableResult(CallableStatement cs, int columnIndex)
    throws SQLException {
  return cs.getObject(columnIndex);
}
 

@Override
protected ResultSet doGetValue(CallableStatement callableStatement, int index)
    throws SQLException {
  Object resultSet = callableStatement.getObject(index);
  return (ResultSet) resultSet;
}
 

@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	ps.execute();
	return (ResultSet) ps.getObject( 1 );
}
 

@Override
public E getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	Object s = cs.getObject(columnIndex);
	return this.findObject(s);
}
 

@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	return cs.getObject(columnIndex);
}
 

protected Object extractJson(CallableStatement statement, String name) throws SQLException {
    return statement.getObject(name);
}
 

public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	ps.execute();
	return ( ResultSet ) ps.getObject( 1 );
}