Spring JdbcTemplate如何实现存储过程有无返回值统一调用

最新推荐文章于 2024-04-18 10:12:09 发布

qmdweb

最新推荐文章于 2024-04-18 10:12:09 发布

阅读量2.2k

点赞数

本文详细介绍使用Spring的JdbcTemplate调用数据库存储过程的方法，包括无返回值、返回out参数和返回结果集三种常见场景，并提供自定义封装函数简化调用流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

https://blog.youkuaiyun.com/cl05300629/article/details/19325347

https://blog.youkuaiyun.com/pzasdq/article/details/52175331

https://blog.youkuaiyun.com/dancelonely/article/details/9363939

https://blog.youkuaiyun.com/chuangxin/article/details/80832178

本文介绍JdbcTemplate调用数据库存储过程的几种常见用法及一个自定义封装函数：execProc，通过调用execProc可实现只需要少量代码即可完成JdbcTemplate对存储过程的调用。

环境：mysql5.6，jdk1.8，spring3.2.5。

一、数据库中创建存储过程（函数）

1、proc_jdbc_student_add，新增一条学生记录，无返回值
---------------------

CREATE DEFINER=`user`@`%` PROCEDURE `proc_jdbc_student_add`(IN `name` varchar(30), IN age int)
begin
insert into jdbc_student (name, age) values(name, age);
end

2、proc_jdbc_student_outpara，通过out para返回name，age

CREATE DEFINER=`user`@`%` PROCEDURE `proc_jdbc_student_outpara`(IN in_id int, OUT out_name varchar(30), OUT out_age int)
begin
select name, age into out_name, out_age from jdbc_student where id = in_id;
end

3、proc_jdbc_student_resultset，返回结果集

CREATE DEFINER=`user`@`%` PROCEDURE `proc_jdbc_student_resultset`(IN in_age_min int, IN in_age_max int)
BEGIN
select * from jdbc_student where age between in_age_min and in_age_max;
end

二、jdbcTemplage调用存储过程常见3种情形

1、无返回值

public static void main(String[] args) {
	AbstractApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
	JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
		
	proc_no_return(jdbcTemplate);
		
	context.close();
}
public static void proc_no_return(JdbcTemplate jdbcTemplate) {
	String sql = "call proc_jdbc_student_add('user2',28)";
	jdbcTemplate.execute(sql);
	//
	Map<String, Object> map = 
		jdbcTemplate.queryForMap("select * from jdbc_student where name = 'user2'");
	System.out.println(map);	//{id=11, name=user2, age=28}
}

2、有返回值，out类型参数值

public static void main(String[] args) {
	AbstractApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
	JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
		
	proc_with_outparas(jdbcTemplate);
		
	context.close();
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public static void proc_with_outparas(JdbcTemplate jdbcTemplate) {   
        String result = (String) jdbcTemplate.execute(   
           new CallableStatementCreator() {   
              public CallableStatement createCallableStatement(Connection con) throws SQLException {   
                 String storedProc = "{call proc_jdbc_student_outpara (?,?,?)}";
                 CallableStatement cs = con.prepareCall(storedProc);   
                 cs.setInt(1, 1);// 设置输入参数的值   
                 cs.registerOutParameter(2, Types.VARCHAR);	// 注册输出参数的类型
                 cs.registerOutParameter(3, Types.INTEGER);
                 return cs;
              }   
           }, new CallableStatementCallback() {   
               public Object doInCallableStatement(CallableStatement cs) 
            		   throws SQLException, DataAccessException {   
                 cs.execute();
                 return String.format("name = %s, age = %d", cs.getString(2), cs.getInt(3));   
           }   
        });
        System.out.println(result);	//name = lizhuo, age = 34
}

备注：

1）返回out参数，通常可以通过select 语句转化为返回结果集；

2）个别数据库可以通过out参数返回结果集的，那么需要将out参数转换为ResultSet，然后遍历ResultSet得到记录集

3、有返回值，返回结果集

public static void main(String[] args) {
	AbstractApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
	JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
		
	proc_with_resultset(jdbcTemplate);
		
	context.close();
}
@SuppressWarnings({ "unchecked", "rawtypes" })
    public static void proc_with_resultset(JdbcTemplate jdbcTemplate) {   
        jdbcTemplate.execute(   
           new CallableStatementCreator() {   
              public CallableStatement createCallableStatement(Connection con) throws SQLException {   
                 String storedProc = "{call proc_jdbc_student_resultset (?,?)}";   
                 CallableStatement cs = con.prepareCall(storedProc);   
                 cs.setInt(1, 30);	//设置输入参数的值
                 cs.setInt(2, 35);
                 return cs;
              }   
           }, new CallableStatementCallback() {   
               public Object doInCallableStatement(CallableStatement cs) 
            		   throws SQLException, DataAccessException {   
                 cs.execute();
                 ResultSet rs = cs.getResultSet();
                 if(rs!=null) {
                	 while(rs.next()) {
                	 	System.out.println(String.format("id = %d, name = %s, age = %d", 
                	 		rs.getInt("id"), rs.getString("name"), rs.getInt("age")));
                	 }
                 }
                 return null;
           }
        });
        // 执行结果:
        // id = 1, name = lizhuo, age = 34
        // id = 6, name = BB, age = 34
        // id = 8, name = DD, age = 32
}

三、自定义封装，实现存储过程统一调用

1、ProcResult.java，存储过程调用返回结果

package com.marcus.spring.jdbc;
 
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
public class ProcResult<T> {
	/* 首影响行数 */
	private int updateCount = 0;
	
	/* 返回结果集 */
	private List<T> resultSet;
	
	/* 返回单个对象 */
	private T resultObj;
	
	/* 返回out参数 */
	private Map<Integer, Object> outParas = new HashMap<Integer, Object>();
 
	public int getUpdateCount() {
		return this.updateCount;
	}
 
	public void setUpdateCount(int updateCount) {
		this.updateCount = updateCount;
	}
	
	public void setResultSet(List<T> list) {
		this.resultSet = list;
	}	
	
	public List<T> getResultSet() {
		return this.resultSet;
	}
	
	public void setResultObj(T obj) {
		this.resultObj = obj;
	}
	
	public T getResultObj() {
		if (this.resultObj != null) {
			return this.resultObj;
		} else {
			return this.resultSet != null && this.resultSet.size() > 0 ? this.resultSet.get(0) : null;
		}
	}
	
	public Map<Integer, Object> getOutParas() {
		return outParas;
	}
 
	public void setOutParas(Map<Integer, Object> outParas) {
		this.outParas = outParas;
	}
}

2、BaseJdbcDao.java, 统一调用函数

package com.marcus.spring.jdbc;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
 
public class BaseJdbcDao {
	private JdbcTemplate jdbcTemplate;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	@SuppressWarnings("unchecked")
	public <T> List<T> queryForList(String sql, Class<T> tClass, Object... args) {
		RowMapper<T> rowMapper = null;
		if (Map.class.isAssignableFrom(tClass)) {
			rowMapper = (RowMapper<T>) new ColumnMapRowMapper();
		} else if (String.class.equals(tClass) || Integer.class.equals(tClass) || Long.class.equals(tClass)) {
			rowMapper = new SingleColumnRowMapper<T>(tClass);
		} else {
			rowMapper = new BeanPropertyRowMapper<T>(tClass);
		}
		List<T> list = jdbcTemplate.query(sql, rowMapper, args);
		return list;
	}
	
	public <T> T queryForObject(String sql, Class<T> tClass, Object... args) {
		List<T> list = queryForList(sql, tClass, args);
		return list == null || list.isEmpty() ? null : list.get(0);
	}
	
	/**
	 * @see BaseJdbcDao#execProc(String, Class, Map, Object...)
	 */
	@SuppressWarnings("rawtypes")
	public  ProcResult execProc(String sql, Map<Integer, Integer> outParas, Object... inParas) {
		return execProc(sql, Object.class, outParas, inParas);
	}
	
	/**
	 * 调用存储过程.
	 * @param sql 如：{call proc_jdbc_student_getname (?,?,?)}
	 * @param tClass	返回结果集对象类型
	 * @param outParas out参数类型, Map<参数索引位置, java.sql.Types>
	 * @param inParas in类型参数值
	 * @return ProcResult<T>
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> ProcResult<T> execProc(String sql, Class<T> tClass, Map<Integer, Integer> outParas, Object... inParas) {
		final ProcResult<T> procResult = new ProcResult<T>();
		final String final_sql = sql;
		final Object[] final_inparas = inParas;
		final Map<Integer, Integer> final_outparas = outParas;
		final Class<T> final_tClass = tClass;
		jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				CallableStatement cs = con.prepareCall(final_sql);
				if (final_inparas != null && final_inparas.length > 0) {
					for (int i = 0; i < final_inparas.length; i++) {
						cs.setObject(i + 1, final_inparas[i]);
					}
				}
				if (final_outparas != null) {
					for (Integer key : final_outparas.keySet()) {
						cs.registerOutParameter(key, final_outparas.get(key));
					}
				}
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				if (final_outparas != null) {
					Map<Integer, Object> outParaResult = new HashMap<Integer, Object>();
					for (Integer key : final_outparas.keySet()) {
						outParaResult.put(key, cs.getObject(key));
					}
					procResult.setOutParas(outParaResult);
				}
				procResult.setUpdateCount(cs.getUpdateCount());
				ResultSet result = cs.getResultSet();
				List<T>  list = new ArrayList<T>();
				if(result!=null) {
					RowMapper<T> rowMapper = null;
					if (Map.class.isAssignableFrom(final_tClass)) {
						rowMapper = (RowMapper<T>) new ColumnMapRowMapper();
					} else if (String.class.equals(final_tClass) || Integer.class.equals(final_tClass) || Long.class.equals(final_tClass)) {
						rowMapper = new SingleColumnRowMapper<T>(final_tClass);
					} else {
						rowMapper = new BeanPropertyRowMapper<T>(final_tClass);
					}
					int rowNum = 1;
					while(result.next()) {
						list.add(rowMapper.mapRow(result, rowNum++));
					}
					procResult.setResultSet(list);
				}
				return null;
			}
		});
		return procResult;
	}
}

3、存储过程统一调用示例

public static void main(String[] args) {
	AbstractApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
	BaseJdbcDao jdbcDao = (BaseJdbcDao) context.getBean("jdbcDao");
	
	// 无返回值
	System.out.println("无返回值，procResult.getUpdateCount获取受影响行数: ");
	String sql = "{call proc_jdbc_student_add (?,?)}";
	ProcResult procResult1 = jdbcDao.execProc(sql, null, "user3", 31);
	System.out.println("updateCount: " + procResult1.getUpdateCount());
	System.out.println();
	
	// 有返回值 out paras
	System.out.println("有返回值，通过out参数体现，procResult.getOutParas获取：");
	sql = "{call proc_jdbc_student_outpara (?,?,?)}";
	Map<Integer, Integer> outParas = new HashMap<Integer, Integer>();
	outParas.put(2, Types.VARCHAR);
	outParas.put(3, Types.INTEGER);
	ProcResult procResult2 = jdbcDao.execProc(sql, outParas, 1);
	System.out.println(procResult2.getOutParas());
	System.out.println();
	
	// 返回结果集List<Student>，procResult.getResultSet()获取
	System.out.println("有返回值，返回结果集List<Student>，procResult.getResultSet()获取: ");
	ProcResult<Student> procResult3 = jdbcDao.execProc("{call proc_jdbc_student_resultset (?,?)}", 
			Student.class, null, 30, 35);
	List<Student> list = procResult3.getResultSet();
	System.out.println(list);
	
	// 取单个返回对象Student
	System.out.println("取单个返回对象Student: ");
	Student student = procResult3.getResultObj();
	System.out.println(student);
}

控制台输出如下：

无返回值，procResult.getUpdateCount获取受影响行数: 
updateCount: 1
 
有返回值，通过out参数体现，procResult.getOutParas获取：
{2=lizhuo, 3=34}
 
有返回值，返回结果集List<Student>，procResult.getResultSet()获取: 
[Student [id=1, name=lizhuo, age=34], Student [id=6, name=BB, age=34], Student [id=8, name=DD, age=32], Student [id=13, name=user3, age=31], Student [id=14, name=user3, age=31]]
取单个返回对象Student: 
Student [id=1, name=lizhuo, age=34]

四、jdbc.xml, spring bean配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context" 
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-3.2.xsd">
    
   <!-- Initialization for data source -->
   <bean id="dataSource" 
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
      <property name="url" value="jdbc:mysql://47.*.*.*:3306/lmdgh_demo?useUnicode=true&characterEncoding=utf-8"/>
	  <property name="username" value="user" />
	  <property name="password" value="pwd" />
   </bean>
 
   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
      <property name="dataSource" ref="dataSource"></property> 
   </bean>
   
   <bean id="jdbcDao" class="com.marcus.spring.jdbc.BaseJdbcDao">  
      <property name="jdbcTemplate" ref="jdbcTemplate"></property>
   </bean>
</beans>