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

The following examples show how to use java.sql.CallableStatement#getObject() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: das   File: DalDirectClient.java    License: Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 2
Source Project: gemfirexd-oss   File: UDTTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * <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() );
}
 
Example 3
Source Project: gemfirexd-oss   File: DAProcedures.java    License: Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 4
Source Project: gemfirexd-oss   File: DAProcedures.java    License: Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 5
Source Project: tephra   File: ProcedureImpl.java    License: MIT License 6 votes vote down vote up
@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);
    }
}
 
Example 6
Source Project: vertx-jdbc-client   File: JDBCCallable.java    License: Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 7
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));
    
  

}
 
Example 8
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));
    
  

}
 
Example 9
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
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));
    
  

}
 
Example 10
@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 );
}
 
Example 11
Source Project: doma   File: AbstractResultSetType.java    License: Apache License 2.0 4 votes vote down vote up
@Override
protected ResultSet doGetValue(CallableStatement callableStatement, int index)
    throws SQLException {
  Object resultSet = callableStatement.getObject(index);
  return (ResultSet) resultSet;
}
 
Example 12
protected Object extractJson(CallableStatement statement, String name) throws SQLException {
    return statement.getObject(name);
}
 
Example 13
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);
}
 
Example 14
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	ps.execute();
	return ( ResultSet ) ps.getObject( 1 );
}
 
Example 15
Source Project: fixflow   File: SqlCommandExtend.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 执行存储过程(更新,查询数据[简单查询、非纪录集],返回输出参数[非纪录集])
 * 
 * @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;
}
 
Example 16
Source Project: mybatis   File: ObjectTypeHandler.java    License: Apache License 2.0 4 votes vote down vote up
@Override
public Object getNullableResult(CallableStatement cs, int columnIndex)
    throws SQLException {
  return cs.getObject(columnIndex);
}
 
Example 17
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	ps.execute();
	return (ResultSet) ps.getObject( 1 );
}
 
Example 18
@Override
public E getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	Object s = cs.getObject(columnIndex);
	return this.findObject(s);
}
 
Example 19
@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	return cs.getObject(columnIndex);
}
 
Example 20
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	ps.execute();
	return ( ResultSet ) ps.getObject( 1 );
}