column “name” not found 错误

本文探讨了在执行存储过程时遇到的字段名冲突问题,特别是在查询语句中出现多个同名字段时导致的错误,并提供了两种解决方案:一是通过使用getColumnLabel()方法代替getColumnName()来获取列名;二是调整数据库连接URL参数,启用旧别名元数据行为。

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

今天在写存储过程时遇到的问题,当查询的sql语句中出现多个name的查询字段就会报错column “name” not found
存储过程调用如下代码:

this.procedureTool.executeMysql("PROC_ORDER_COUNT", cid, pid, stime, etime, empid, countType, productid, employee.getUser().getUserType());
@Service
public class ProcedureTool {

	@Resource
	private PagedJdbcTemplate pagedJdbcTemplate;

	/*
	* 方法描述:
	*
	* @param name 储存过程名称
	* @param params 参数
	* @return 
	*/
	public List<Map<String, Object>> executeMysql(String name, Object... params) {
		List<Map<String, Object>> list = Lists.newArrayList();
		List<String> heards = Lists.newArrayList();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = pagedJdbcTemplate.getDataSource().getConnection();
			String param = "";
			for (int i = 0; i < params.length; i++) {
				param += ", ?";
			}
			
			String callStr = "";
			if(params.length == 0) {
				callStr = "{call " + name + "}";
			} else {
				callStr = "{call " + name + "(" + param.substring(2) + ")}";
			}
			
			CallableStatement proc = conn.prepareCall(callStr);
			for (int i = 0; i < params.length; i++) {
				proc.setObject(i + 1, params[i]);
			}
		    boolean hadResults = proc.execute();
		    while(hadResults) {
		    	rs = proc.getResultSet();
		    	if (rs != null) {
		    		if (heards.size() == 0) {
			    		ResultSetMetaData rsmd = rs.getMetaData();
						int count = rsmd.getColumnCount();
						for (int i = 1; i <= count; i++) {
							heards.add(rsmd.getColumnName(i));
						}
			    	}
		    		while(rs.next()) {
		    			Map<String, Object> m = Maps.newHashMap();
		    			for (String heard : heards) {
		    				m.put(heard, rs.getObject(heard));
						}
		    			list.add(m);
		    		}
		    	}
		    	hadResults = proc.getMoreResults(); 
		    }
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
		    	try {
					rs.close();
				} catch (SQLException e) {
				}
		    }
		    if (conn != null) {
		    	try {
		    		conn.close();
				} catch (SQLException e) {
				}
		    }
		}
		return list;
	}
	
	public <T> List<T> executeMysqlToEntity(final Class<T> dtoEntity, final String name, final Object... params) {
		List<T> list = Lists.newArrayList();
		Connection conn = null;
		ResultSet rs = null;
		try {
			List<T> resultList = pagedJdbcTemplate.execute(
					new CallableStatementCreator() {
						public CallableStatement createCallableStatement(Connection con) throws SQLException {
							String param = "";
							for (int i = 0; i < params.length; i++) {
								param += ", ?";
							}
							CallableStatement cs = con.prepareCall("{call " + name + "(" + param.substring(2) + ")}");
							for (int i = 0; i < params.length; i++) {
								cs.setObject(i + 1, params[i]);
							}
							return cs;
						}
					}, new CallableStatementCallback<List<T>>() {
						public List<T> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
							List<T> resultsMap = Lists.newArrayList();
							boolean hadResults = cs.execute();
							while (hadResults) {
								ResultSet rs = cs.getResultSet();
								if (rs != null) {
									resultsMap.addAll(new RowMapperResultSetExtractor<T>(BeanPropertyRowMapper.newInstance(dtoEntity)).extractData(rs));
								}
								if (rs != null) {
									try {
										rs.close();
									} catch (SQLException e) {
									}
								}
								hadResults = cs.getMoreResults();
							}
							return resultsMap;
						}
					});

			return resultList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
		    	try {
					rs.close();
				} catch (SQLException e) {
				}
		    }
		    if (conn != null) {
		    	try {
		    		conn.close();
				} catch (SQLException e) {
				}
		    }
		}
		return list;
	}

}

debugger调试时发现

heards.add(rsmd.getColumnName(i));

这里如果换成

heards.add(rsmd.getColumnLabel(i));

这样就不会报错了。
还有另外一种解决方法就是百度查的:

url="jdbc:mysql://127.0.0.1:3306/fengda?useOldAliasMetadataBehavior=true"
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值