Java CallableStatement.setObject方法代码示例

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Helper method for *SetObject* tests.
 * Insert data into the given PreparedStatement, or any of its subclasses, with the following structure:
 * 1 - `id` INT
 * 2 - `ot1` VARCHAR
 * 3 - `ot2` BLOB
 * 4 - `odt1` VARCHAR
 * 5 - `odt2` BLOB
 * 
 * @param pstmt
 * @return the row count of inserted records.
 * @throws Exception
 */
private int insertTestDataOffsetDTTypes(PreparedStatement pstmt) throws Exception {
    pstmt.setInt(1, 1);
    pstmt.setObject(2, testOffsetTime, JDBCType.VARCHAR);
    pstmt.setObject(3, testOffsetTime);
    pstmt.setObject(4, testOffsetDateTime, JDBCType.VARCHAR);
    pstmt.setObject(5, testOffsetDateTime);
    assertEquals(1, pstmt.executeUpdate());

    if (pstmt instanceof CallableStatement) {
        CallableStatement cstmt = (CallableStatement) pstmt;

        cstmt.setInt("id", 2);
        cstmt.setObject("ot1", testOffsetTime, JDBCType.VARCHAR);
        cstmt.setObject("ot2", testOffsetTime);
        cstmt.setObject("odt1", testOffsetDateTime, JDBCType.VARCHAR);
        cstmt.setObject("odt2", testOffsetDateTime);
        assertEquals(1, cstmt.executeUpdate());

        return 2;
    }

    return 1;
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
private void execProcBug49831(Connection c) throws Exception {
    CallableStatement cstmt = c.prepareCall("{call pTestBug49831(?)}");
    cstmt.setObject(1, "abc", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.setObject(1, "def", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.executeBatch();
    assertEquals(2, getRowCount("testBug49831"));
    this.rs = this.stmt.executeQuery("SELECT * from testBug49831 ORDER BY VAL ASC");
    this.rs.next();
    assertEquals("abc", this.rs.getString(1));
    this.rs.next();
    assertEquals("def", this.rs.getString(1));
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public int execCallUpdate(String sql, Object[] params) throws SQLException {
	CallableStatement call = conn.prepareCall(sql);
	for (int i = 0; i < params.length; i++) {
		call.setObject(i + 1, params[0]);
	}
	return call.executeUpdate();
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public List<Object[]> execCallQuery(String sql, Object[] params)
		throws SQLException {
	CallableStatement call = conn.prepareCall(sql);
	for (int i = 0; i < params.length; i++) {
		call.setObject(i + 1, params[0]);
	}
	return readResultSet(call.executeQuery());
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testB() throws SQLException, ClassNotFoundException {

        Statement statement = con.createStatement();

        statement.execute(
            "CREATE TABLE IF NOT EXISTS users (id INTEGER, name VARCHAR(25), PRIMARY KEY(id))");
        statement.executeUpdate("INSERT INTO users VALUES(1, 'Ramiro')");
        statement.executeUpdate("INSERT INTO users VALUES(2, 'Chanukya')");

        String storedProcedure1 =
            "CREATE PROCEDURE sp_say_hi(IN greeting_p VARCHAR(10)) "
            + "READS SQL DATA DYNAMIC RESULT SETS 2 " + "BEGIN ATOMIC "
            + "DECLARE result CURSOR WITH RETURN FOR SELECT COALESCE(greeting_p, 'Hi')+' '+name as greeting FROM users FOR READ ONLY; "
            + "DECLARE result1 CURSOR WITH RETURN FOR SELECT * FROM users FOR READ ONLY; "
            + "OPEN result; " + "OPEN result1; " + "END";

        statement.execute(storedProcedure1);

        String            sqlCall           = "CALL sp_say_hi(?)";
        CallableStatement callableStatement = con.prepareCall(sqlCall);

        callableStatement.setObject("GREETING_P", "Hola");

        boolean result = callableStatement.execute();

        if (!result) {
            int value = callableStatement.getUpdateCount();

            assertTrue(value == 0);
            result = callableStatement.getMoreResults();
            assertTrue(result);
            ResultSet result1 = callableStatement.getResultSet();
            result = callableStatement.getMoreResults();
            assertTrue(result);
            ResultSet result2 = callableStatement.getResultSet();
            result = callableStatement.getMoreResults();
            assertFalse(result);
            value = callableStatement.getUpdateCount();
            assertTrue(value == -1);

        }
    }
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/************************************************************
 * Call Procedure(One only Out Parameter)
 ************************************************************/
public int CallProcedure(String sql, List<CallableParameter> binds) throws Exception{		
	CallableStatement cStmt = null;
	try{
		if (conn == null) {
			conn = DBCPPoolManager.getConnection(PoolName);
			
		}

		cStmt = conn.prepareCall(sql);
		int retIdx = 0;
		if (binds != null) {
			for (int i = 1; i<= binds.size(); i++) {
				if (binds.get(i - 1).getType() == CallableType.IN){
					cStmt.setObject(i, binds.get(i - 1).getValue());
				}else{
					retIdx = i;
					cStmt.registerOutParameter(i, binds.get(i - 1).getOutValueType());
				}
				
			}	
		}
		
		int ret = cStmt.executeUpdate();
		
		if (conn != null && IsTransaction == false) {
			conn.commit();
		}
		
		if (retIdx != 0){
			binds.get(retIdx).setValue(cStmt.getObject(retIdx));
		}
		
		return ret;
	}catch(SQLException e){
		LogUtils.error(CommonUtil.getStackTrace(e.getNextException()),DataAdapter.class);
		throw e;
	}finally {
		CloseConn(cStmt);
	}
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
private static StatementWrapper buildProcedureCallableStatement(String sql,Map<String, Object> pmap,Connection conn){
	try {
		Map<String,Object> paramMap=new LinkedHashMap<String,Object>();
		int leftParnPos=sql.indexOf("(");
		int rightParnPos=sql.indexOf(")");
		String paramStr="";
		if(leftParnPos>-1 && rightParnPos>-1){
			paramStr=sql.substring(leftParnPos+1,rightParnPos);				
		}
		int oracleCursorIndex=-1,paramIndex=0;
		String[] str=paramStr.split(",");
		for(String param:str){
			paramIndex++;
			param=param.trim();
			if(param.toLowerCase().equals("oracle")){
				sql=sql.replaceFirst(param, "?");
				oracleCursorIndex=paramIndex;
				continue;
			}else if(!param.startsWith(":")){
				continue;
			}
			sql=sql.replaceFirst(param, "?");
			String paramName=param.substring(1,param.length());
			Object paramValue=pmap.get(paramName);
			paramMap.put(paramName, (paramValue==null ? "" : paramValue));
		}
		String procedure="{"+sql+"}";
		CallableStatement cs= conn.prepareCall(procedure);
		int index=1;
		for(String name:paramMap.keySet()){
			Object value=paramMap.get(name);
			if(value instanceof String){
				cs.setString(index,(String)value);									
			}else if(value instanceof Date){
				Date date=(Date)value;
				cs.setDate(index, new java.sql.Date(date.getTime()));
			}else if(value instanceof Integer){
				cs.setInt(index, (Integer)value);
			}else if(value instanceof Float){
				cs.setFloat(index, (Float)value);
			}else if(value instanceof Double){
				cs.setDouble(index, (Double)value);
			}else{
				cs.setObject(index, value);
			}
			index++;
		}
		if(oracleCursorIndex>-1){
			cs.registerOutParameter(oracleCursorIndex, -10);
		}
		return new StatementWrapper(cs,oracleCursorIndex);
	} catch (SQLException e) {
		throw new ReportException(e);
	}
}