jdbc mysql 存储过程查询_jdbc调用mysql存储过程,并获得结果集合

最近碰到一个需要Java调用mysql存储过程并获得存储过程结果集的场景.

存储过程:spilte_result:分割字符串存储到临时表,并返回临时表的结果

数据库执行结果

sum_interface_traffic_result:统计策略流量

现在使用JdbcTemplate进行调用存储过程并且获得结果

package com.cqs.demo.jdbc;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.CallableStatementCallback;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import org.springframework.stereotype.Component;

import javax.annotation.Resource;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

@Component

public class JdbcTemplateProcessCall {

@Resource

JdbcTemplate jdbcTemplate;

public List callProcessResultSet() {

List result = (List) jdbcTemplate.execute(

con -> {

String storedProc = "{call sum_interface_traffic_result()}";//SQL

CallableStatement cs = con.prepareCall(storedProc);

return cs;

}, (CallableStatementCallback) cs -> {

List list = new ArrayList<>();

boolean exist = cs.execute();

if (exist) {

ResultSet resultSet = cs.getResultSet();

//遍历结果集

while (resultSet.next()) {

// System.out.println("strategyId:" + resultSet.getInt(1) + "\tsum_in_traffic:" + resultSet.getFloat(2) + "\tsum_out_traffic:" + resultSet.getFloat(3));

SumStrategyTraffic sumStrategyTraffic = new SumStrategyTraffic();

sumStrategyTraffic.setStrategyId(resultSet.getInt(1));

sumStrategyTraffic.setSumInTraffic(resultSet.getFloat(2));

sumStrategyTraffic.setSumOutTraffic(resultSet.getFloat(3));

list.add(sumStrategyTraffic);

}

}

return list;

});

System.out.println("获得存储结果集:");

result.forEach(System.out::println);

return result;

}

//带有输入值的例子

public void callProcessResultSetWithInput() {

List result = (List) jdbcTemplate.execute(

con -> {

String storedProc = "{call split_result(?)}";// 调用的sql

CallableStatement cs = con.prepareCall(storedProc);

cs.setString(1, "783,268");// 设置输入参数的值

//cs.registerOutParameter(2, JDBCType.REF_CURSOR);// 注册输出参数的类型

return cs;

}, (CallableStatementCallback) cs -> {

List list = new ArrayList<>();

boolean exist = cs.execute();

ResultSet resultSet = cs.getResultSet();

if (exist) {

while (resultSet.next()) {

// System.out.println(resultSet.getString(1));

list.add(resultSet.getString(1));

}

}

return list;

});

System.out.println("存储过程结果:"+result);

}

//通过SimpleJdbcCall调用存储过程

public List callProcessResultSetWithSimpleJdbcCall(){

SimpleJdbcCall call = new SimpleJdbcCall(this.jdbcTemplate)

.withProcedureName("sum_interface_traffic_result")

.withoutProcedureColumnMetaDataAccess()

.returningResultSet("sumStrategyTraffic", BeanPropertyRowMapper.newInstance(SumStrategyTraffic.class));

Map map = call.execute();

@SuppressWarnings("unchecked")

List result = (List) map.get("sumStrategyTraffic");

System.out.println("存储过程结果:");

result.forEach(System.out::println);

return result;

}

}

package com.cqs.demo.jdbc;

public class SumStrategyTraffic {

private int strategyId;

private float sumInTraffic;

private float sumOutTraffic;

public int getStrategyId() {

return strategyId;

}

public void setStrategyId(int strategyId) {

this.strategyId = strategyId;

}

public float getSumInTraffic() {

return sumInTraffic;

}

public void setSumInTraffic(float sumInTraffic) {

this.sumInTraffic = sumInTraffic;

}

public float getSumOutTraffic() {

return sumOutTraffic;

}

public void setSumOutTraffic(float sumOutTraffic) {

this.sumOutTraffic = sumOutTraffic;

}

@Override

public String toString() {

return "SumStrategyTraffic{" +

"strategyId=" + strategyId +

", sumInTraffic=" + sumInTraffic +

", sumOutTraffic=" + sumOutTraffic +

'}';

}

}JUnit单元测试

package com.cqs.demo.jdbc;

import com.cqs.demo.base.BaseConfigurationTest;

import org.junit.Test;

import javax.annotation.Resource;

/**

* Created by cqs on 16-12-4.

*/

public class JdbcTemplateProcessCallTest extends BaseConfigurationTest {

@Resource

JdbcTemplateProcessCall jdbcTemplateProcessCall;

@Test

public void callProcessResultSet() throws Exception {

jdbcTemplateProcessCall.callProcessResultSet();

}

@Test

public void callProcessResultSetWithInput() throws Exception {

jdbcTemplateProcessCall.callProcessResultSetWithInput();

}

@Test

public void callProcessResultSetWithSimpleJdbcCall() throws Exception {

jdbcTemplateProcessCall.callProcessResultSetWithSimpleJdbcCall();

}

}

运行结果