Spring StoredProcedure Cursor 使用

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

Java Code

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import oracle.jdbc.OracleTypes;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;



public class GetUserActionsOnV2 extends StoredProcedure {
	
	public GetUserActionsOnV2(DataSource dataSource) {
		setDataSource(dataSource);
		setSql("GETUSERACTIONSONV2");
		final DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		declareParameter(new SqlOutParameter("active_rs", OracleTypes.CURSOR, new RowMapper() {
		   public Map mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			   Map<String,Object> map = new HashMap<String,Object>();
			   map.put("SOEID", rs.getString("SOEID"));
			   map.put("IOIID", rs.getString("IOIID"));
			   map.put("NAME", rs.getString("NAME"));
			   map.put("VALUE", rs.getString("VALUE"));
			   map.put("DESTINATIONUSER", rs.getString("DESTINATIONUSER"));
			   map.put("ADD_TS", df.format(rs.getTimestamp("ADD_TS")));
			   map.put("UPDATE_TS", df.format(rs.getTimestamp("UPDATE_TS")));
			   return map;
		}
	}));
		declareParameter(new SqlOutParameter("return_sql", OracleTypes.VARCHAR));
		
		declareParameter(new SqlParameter("V_SOEID",OracleTypes.VARCHAR));
		declareParameter(new SqlParameter("V_IOIID", OracleTypes.VARCHAR));
		declareParameter(new SqlParameter("V_NAME",OracleTypes.VARCHAR));
		declareParameter(new SqlParameter("V_VALUE",OracleTypes.VARCHAR));
		declareParameter(new SqlParameter("V_DESTINATIONUSER",OracleTypes.VARCHAR));
		
		
		compile();
	}
		
	public List<Map<String,Object>> executeGetUserActions(Map<String,String> param) throws Exception {
		Map<String, Object> inParameters = new HashMap<String, Object>();
		inParameters.put("V_SOEID", param.get("SOEID"));
		inParameters.put("V_IOIID", param.get("IOIID"));
		inParameters.put("V_NAME", param.get("NAME"));
		inParameters.put("V_VALUE", param.get("VALUE"));
		inParameters.put("V_DESTINATIONUSER", param.get("DESTINATIONUSER"));
		try {
			Map result = execute(inParameters);
			String sql = (String) result.get("return_sql");
			logger.info("GetUserActionsOnV2 sql=" + sql);
			
			List<Map<String,Object>> activeList = (List<Map<String, Object>>) result.get("active_rs");
			return activeList;
		} catch (DataAccessException e) {
			logger.error("Failed to GetUserActionsOnV2 !");
			throw new Exception(e);
		}
	}
}

SP code

/*
#ident "%W%"
*/

CREATE OR REPLACE PROCEDURE GETUSERACTIONSONV2(
  active_rs OUT ×××××_TYPES.cur_type,
  return_sql OUT VARCHAR2,
  V_SOEID IN VARCHAR2,
  V_IOIID IN VARCHAR2,
  V_NAME IN VARCHAR2,
  V_VALUE IN VARCHAR2,
  V_DESTINATIONUSER IN VARCHAR2
)
IS

v_sql VARCHAR2(1000);

BEGIN
 v_sql := 'SELECT * FROM USER_ACTION_V2 WHERE 1 = 1 ';
 
  if V_SOEID is not null THEN
    v_sql := v_sql || 'AND (SOEID = '''|| upper(V_SOEID) || ''' OR DESTINATIONUSER = ''' || upper(V_SOEID) || ''') ';
  end if;
 
  if V_IOIID is not null THEN
  	v_sql := v_sql || 'AND IOIID =''' || V_IOIID || ''' ';
  end if;
 
  if V_NAME is not null THEN
  	v_sql := v_sql || 'AND NAME =''' || upper(V_NAME) || ''' ';
  end if;
  
  if V_VALUE is not null THEN
  	v_sql := v_sql || 'AND VALUE =''' || V_VALUE || ''' ';
  end if;

  v_sql := v_sql || 'ORDER BY ADD_TS DESC';
  
  return_sql := v_sql;
  OPEN active_rs FOR v_sql;
END;
/

注意问题：

（1）Java  StoredProcedure 中定义in，out参数的顺序要与SP sql中定义的in，out参数顺序一致

declareParameter(new SqlOutParameter(“return_sql”, OracleTypes.VARCHAR));
declareParameter(new SqlParameter(“V_SOEID”,OracleTypes.VARCHAR));
declareParameter(new SqlParameter(“V_IOIID”, OracleTypes.VARCHAR));

sp sql：

                return_sql OUT VARCHAR2,
  V_SOEID IN VARCHAR2,
  V_IOIID IN VARCHAR2,

（2）如果sp中out参数返回是cursor，必须定义RowMapper对cursor返回结果进行处理，否则get的结果就为空。

final DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		declareParameter(new SqlOutParameter("active_rs", OracleTypes.CURSOR, new RowMapper() {
		   public Map mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			   Map<String,Object> map = new HashMap<String,Object>();
			   map.put("SOEID", rs.getString("SOEID"));
			   map.put("IOIID", rs.getString("IOIID"));
			   map.put("NAME", rs.getString("NAME"));
			   map.put("VALUE", rs.getString("VALUE"));
			   map.put("DESTINATIONUSER", rs.getString("DESTINATIONUSER"));
			   map.put("ADD_TS", df.format(rs.getTimestamp("ADD_TS")));
			   map.put("UPDATE_TS", df.format(rs.getTimestamp("UPDATE_TS")));
			   return map;
		}
	}));