public List<HashMap> searchQoneba(String searchText) throws SQLException, AppException {
List<HashMap> result = new ArrayList<HashMap>();
OracleResultSet rset = null;
OracleCallableStatement stmt = null;
try {
stmt =
(OracleCallableStatement)
conn.prepareCall(
"begin ?:=" + TgisDBUtils.SCHEMA + "SEARCH_PKG.search_qoneba( ? ); end;");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.setString(2, searchText);
stmt.execute();
rset = (OracleResultSet) stmt.getCursor(1);
DBUtils.fetchOraSysRefCursor(rset, result, true, null);
return result;
} finally {
DBUtils.clear(rset, stmt);
}
}
/**
* 存储过程基本
*
* @param processName --过程名
* @param paList --输入参数
* @param paTypeList --输入参数类型(默认Types.VARCHAR)
* @param paInputList --IN(默认)/OUT类型
* @param rsType --封装游标集合的VO类型(默认HashMap)
* @return
* @throws Exception
*/
private static List executeProcess(
String processName,
List<Object> valueList,
List<Integer> dbTypeList,
List<Integer> inOutList,
Class rsType,
Connection con)
throws Exception {
// 1,参数检查
DBTool.checkProcessParameters(processName, valueList, dbTypeList, inOutList);
// 2,处理过程
OracleCallableStatement call = null;
try {
// 2.1,获得Call
call =
(OracleCallableStatement) con.prepareCall(DBTool.GetCallString(processName, valueList));
// 2.2,绑定变量
if (valueList != null) {
// "OUT"需要设定输出参数,"IN"需要输入参数绑定,"IN/OUT"全都需要
for (int i = 0; i < dbTypeList.size(); i++) {
// Java对象
Object javaValue = valueList.get(i);
// 需要对应的数据库类型
int db_type = dbTypeList.get(i);
// 注册
DBTool.regedit(con, call, i + 1, javaValue, inOutList.get(i), db_type);
}
}
/** ---------------后台打印---------------- */
P.printCall(DBTool.GetCallString(processName, valueList), valueList);
/** ---------------后台打印---------------- */
// 3, 执行
call.execute();
// 4, 结果处理
for (int i = 0; i < valueList.size(); i++)
if (inOutList.get(i) > C.CALL_PARAMETER_TYPE_IN) {
Object obj = DBTool.GetJavaValueByDBMeta(call, dbTypeList.get(i), i + 1);
if (obj.getClass() == ResultSet.class || dbTypeList.get(i) == OracleTypes.CURSOR)
valueList.set(i, DBTool.GetRsList((ResultSet) obj, rsType));
else valueList.set(i, obj);
}
// 关闭存储过程默认的游标
call.close();
return valueList;
} catch (Exception e) {
throw e;
} finally {
if (call != null) call.close();
}
}
public List<HashMap> searchRoads(String searchText) throws SQLException, AppException {
List<HashMap> result = new ArrayList<HashMap>();
OracleResultSet rset = null;
OracleCallableStatement stmt = null;
try {
stmt =
(OracleCallableStatement)
conn.prepareCall(
"begin ?:=" + TgisDBUtils.SCHEMA + "SEARCH_PKG.search_roads( ? ); end;");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.setString(2, searchText);
stmt.execute();
rset = (OracleResultSet) stmt.getCursor(1);
DBUtils.fetchOraSysRefCursor(rset, result, true, null);
for (HashMap road : result) {
List<HashMap> roadParts = (List<HashMap>) road.get("geometry");
if (roadParts.size() == 1) {
road.put("geometry", ((HashMap) roadParts.get(0)).get("geometry"));
} else {
if (roadParts.size() > 1) {
StringBuilder joinedRoadWkt = new StringBuilder("MULTILINESTRING (");
for (HashMap roadPart : roadParts) {
String roadPartWkt = (String) roadPart.get("geometry");
joinedRoadWkt.append(
roadPartWkt.replaceFirst("LINESTRING ", "").replaceFirst("MULTILINESTRING ", ""));
joinedRoadWkt.append(",");
}
joinedRoadWkt.append(")");
road.put("geometry", joinedRoadWkt.toString());
} else {
road.put("geometry", null);
}
}
}
return result;
} finally {
DBUtils.clear(rset, stmt);
}
}
/**
* 函数(不考虑复杂的用法,只考虑函数的基本功能)
*
* @param callName --函数名称
* @param paList --输入参数
* @param returnType --返回种类
* @param rsClass --封装游标集合的类
* @return
* @throws Exception
*/
private static Object executeFunction(
String callName, List<Object> paList, int returnType, Class rsType, Connection con)
throws Exception {
OracleCallableStatement call = null;
// 防止依赖注入
if (paList != null && paList.size() > 0)
for (int i = 0; i < paList.size(); i++)
if (paList.get(i) != null && paList.get(i).getClass() == String.class)
if (((String) paList.get(i)).contains("'"))
paList.set(i, UtilTool.ClearInfuse((String) paList.get(i)));
// 执行函数的字符串
String CallFunction = "{? = " + DBTool.GetCallString(callName, paList).substring(1);
try {
call = (OracleCallableStatement) con.prepareCall(CallFunction);
// 绑定变量,只考虑使用函数返回一个结果 "{?="
call.registerOutParameter(1, returnType);
// 填充参数,注意从第2个"?"开始
List<Integer> paTypeList = DBTool.getPaTypeList(paList);
for (int i = 0; paList != null && i < paList.size(); i++)
DBTool.CallBindValue(con, call, paList.get(i), paTypeList.get(i), i + 2);
/** ---------------*后台打印----------------------- */
P.printFunction(CallFunction, paList);
/** ---------------*后台打印----------------------- */
// 执行
call.execute();
Object returnObj = DBTool.GetJavaValueByDBMeta(call, returnType, 1);
if (returnObj.getClass() == ResultSet.class || returnType == OracleTypes.CURSOR)
return DBTool.GetRsList((ResultSet) returnObj, rsType);
else return returnObj;
} catch (Exception e) {
throw e;
} finally {
if (call != null) call.close();
}
}
public Map<Long, Regions> getTopThree() throws DAOException {
Connection connexion = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Map<Long, Regions> mapTopThree = new TreeMap<Long, Regions>();
try {
connexion = daoFactory.getConnection();
OracleCallableStatement cs =
(OracleCallableStatement) connexion.prepareCall("{call REGIONTOPREMB(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
resultSet = cs.getCursor(1);
// preparedStatement = initialisationRequetePreparee( connexion, SQL_SELECT_TOP_3,
// false);
//
// resultSet = preparedStatement.executeQuery();
/* Parcours de la ligne de donnees retournee dans le ResultSet */
while (resultSet.next() != false) {
Regions bean = new Regions();
bean = map(resultSet);
mapTopThree.put(resultSet.getLong("CNT"), bean);
}
return mapTopThree;
} catch (SQLException e) {
throw new DAOException(e);
} finally {
fermeturesSilencieuses(resultSet, preparedStatement, connexion);
}
}