SpringJDBC 调用存储过程

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zjw10wei321/article/details/44151751


项目中持久层用的是SpringJDBC,在调用存储过程时,网上大致搜了些,自己总结下。

public class StoredService extends BaseService {

    private Map<String, Object> param = new HashMap<String, Object>();
    {
        param.put("c_id", "234");
        param.put("c_name", "dddd");
    }

    public void test1() {
        PersonPostResult result = getQuickSupportDao().getQuickJdbcTemplate().execute(
                new CallableStatementCreator() {
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        String storedProc = "{call proc_test(?,?,?,?)}";// 调用的存储过程
                        CallableStatement cs = con.prepareCall(storedProc);
                        cs.setString(1, "111");
                        cs.setString(2, "mingzi");
                        cs.registerOutParameter(3, java.sql.Types.VARCHAR);
                        cs.registerOutParameter(4, java.sql.Types.VARCHAR);
                        return cs;
                    }
                }, new CallableStatementCallback<PersonPostResult>() {
                    public PersonPostResult doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                        cs.execute();
                        String status = cs.getString(5);
                        String msg = cs.getString(6);
                        PersonPostResult tempResult = new PersonPostResult(status, msg);
                        return tempResult;
                    }
                });
        System.out.println(result);
    }

    public void test() {
        StoredProcedureTemplate t = new StoredProcedureTemplate(
                getQuickSupportDao().getQuickJdbcTemplate(), "proc_test");

        t.setValue("c_id", "111");
        t.setValue("c_name", "mingZi");
        t.setVarcharParam("c_id");
        t.setVarcharParam("c_name");
        t.setVarcharOutParam("status");
        t.setVarcharOutParam("msg");
        Map map = t.execute();
        System.out.println(map);
    }

    public void test2() {
        SimpleJdbcCall call = new SimpleJdbcCall(getQuickSupportDao().getQuickJdbcTemplate());
        call.withProcedureName("proc_test").declareParameters(new SqlParameter[]{
                new SqlParameter("c_id", Types.VARCHAR),
                new SqlParameter("c_name", Types.VARCHAR),
                new SqlOutParameter("status", Types.VARCHAR),
                new SqlOutParameter("msg", Types.VARCHAR)
        }).returningResultSet("message", ParameterizedBeanPropertyRowMapper.newInstance(Message.class));
        Map<String, Object> ming = call.execute(param);
        System.out.println(ming);
    }

    public void test3() {
        SimpleJdbcCall call = new SimpleJdbcCall(getQuickSupportDao().getQuickJdbcTemplate());
        call.withProcedureName("proc_test").declareParameters(new SqlParameter[]{
                new SqlParameter("c_id", Types.VARCHAR),
                new SqlParameter("c_name", Types.VARCHAR),
                new SqlOutParameter("status", Types.VARCHAR),
                new SqlOutParameter("msg", Types.VARCHAR)
        });

        String ming = call.executeObject(String.class, param);
        //Message message = call.executeObject(Message.class, param);
        System.out.println(ming);
    }

    class Message{
        private String status;

        private String msg;

        public void setStatus(String status) {
            this.status = status;
        }

        public void setMsg(String msg) {
            this.msg = msg;
        }

        public String getStatus() {

            return status;
        }

        public String getMsg() {
            return msg;
        }

        @Override
        public String toString() {
            return "Message{" +
                    "status='" + status + '\'' +
                    ", msg='" + msg + '\'' +
                    '}';
        }
    }


}

test1用的是jdbcTemple的execute方法,test采用的是继承抽象类storedProcedure归根到底用的还是jdbcTemple的call方法,

test2用的是SimpleJdbcCall的execute, 

test3的executeObject,本来以为能直接返回对象给我,出现错误信息,String不能转成***对象,意思是只取第一个返回值。

10:11:03,267  WARN CallMetaDataContext:251 - Accessing single output value when procedure has more than one output parameter

public String getScalarOutParameterName() {
		if (isFunction()) {
			return getFunctionReturnName();
		}
		else {
			if (this.outParameterNames.size() > 1) {
				logger.warn("Accessing single output value when procedure has more than one output parameter");
			}
			return (this.outParameterNames.size() > 0 ? this.outParameterNames.get(0) : null);
		}
	}


没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试

关闭