今天在写存储过程时遇到的问题,当查询的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"