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 void registrarOT(ot ordentrabajo) throws SQLException {
try {
System.out.println("INICIO del Stored Procedure de insercion OT");
OracleCallableStatement cs =
(OracleCallableStatement)
Conexion.con.prepareCall("begin registrarOT(?,?,?,?,?,?,?); end;");
System.out.println("AQUI YA LLAME AL STORED PROCEDURE");
cs.setInt(1, ordentrabajo.getID_OT());
cs.setString(2, ordentrabajo.getPATENTE());
cs.setString(3, ordentrabajo.getRUT_ADMINISTRADOR());
cs.setString(4, ordentrabajo.getRUT_MECANICO());
cs.setDate(5, ordentrabajo.getFECHA_INICIO());
cs.setDate(6, ordentrabajo.getFECHA_TERMINO());
cs.setString(7, ordentrabajo.getTIPOTRABAJO());
cs.executeUpdate();
System.out.println("\nOT succesfully inserted");
Conexion.con.commit();
System.out.println("TERMINO del Stored Procedure de insercion OT");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
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);
}
}
public void actualizarOTSERVICIO(int idot, String patente, int id_servicio)
throws SQLException, DocumentException, FileNotFoundException {
try {
System.out.println("INICIO del Stored Procedure de insercion Conductor");
OracleCallableStatement cs =
(OracleCallableStatement)
Conexion.con.prepareCall("begin registrarOTSERVICIO(?,?,?); end;");
System.out.println("AQUI YA LLAME AL STORED PROCEDURE");
cs.setInt(1, idot);
cs.setString(2, patente);
cs.setInt(3, id_servicio);
cs.executeUpdate();
System.out.println("\nOTSERVICIO succesfully inserted");
Conexion.con.commit();
System.out.println("TERMINO del Stored Procedure de insercion OTSERVICIO");
GeneratePDF gpdf = new GeneratePDF();
// Obtencion datos personal de la orden_trabajo
String query =
"select ot.fecha_inicio as fechainicio, mec.nombre as mecnombre, mec.apellido_paterno as mecapepa, mec.apellido_materno as mecamema, adm.nombre as adnombre, adm.apellido_paterno as adapepa, adm.apellido_materno as adamema, ot.id_ot as idot, ot.patente as patente from orden_trabajo ot, mecanico mec, administrador adm where ot.id_ot = "
+ idot
+ " and ot.patente = '"
+ patente
+ "' and ot.rut_mecanico = mec.rut_mecanico and ot.rut_administrador = adm.rut_administrador";
ResultSet rs = Conexion.ejecutarQuery(query);
String query2 =
"select s.id_servicio, s.nombre from orden_trabajo_servicio ots, servicio s where ots.id_ot = "
+ idot
+ " and ots.patente = '"
+ patente
+ "' and ots.id_servicio = s.id_servicio";
ResultSet rs2 = Conexion.ejecutarQuery(query2);
// Creacion de PDF
gpdf.crearDocumento("ORDEN DE TRABAJO N°" + idot + "-PATENTE-" + patente);
// Asignacion de contenido PDF a la clase GeneratePDF
gpdf.ContenidoDocumento(
"SOPRAF S.A. SOFTWARE AMRV",
"O R D E N D E T R A B A J O",
"S E R V I C I O S",
rs,
rs2);
} catch (SQLException ex) {
ex.printStackTrace();
}
}
/**
* 函数(不考虑复杂的用法,只考虑函数的基本功能)
*
* @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);
}
}
public void RegistrarRepuestoOT(
int NUM_ORDEN,
String PATENTE,
int ID_REPUESTO,
String RUT_ENCARGADO,
String OBSERVACIONES,
int CANTIDAD)
throws SQLException {
try {
System.out.println("INICIO del Stored Procedure de insercion OTREPUESTO");
OracleCallableStatement cs =
(OracleCallableStatement)
Conexion.con.prepareCall("begin registrarOTREP(?,?,?,?,?,?); end;");
System.out.println("AQUI YA LLAME AL STORED PROCEDURE");
System.out.println(
"AQUI YA RECIBI PARAMETROS DESDE RETIROREPUESTOS: "
+ NUM_ORDEN
+ " , "
+ PATENTE
+ " , "
+ ID_REPUESTO
+ " , "
+ OBSERVACIONES
+ " , "
+ CANTIDAD
+ " , "
+ RUT_ENCARGADO);
cs.setInt(1, NUM_ORDEN);
cs.setString(2, PATENTE);
cs.setInt(3, ID_REPUESTO);
cs.setString(4, RUT_ENCARGADO);
cs.setString(5, OBSERVACIONES);
cs.setInt(6, CANTIDAD);
cs.executeUpdate();
System.out.println("\nSuccesfully inserted");
Conexion.con.commit();
System.out.println("TERMINO del Stored Procedure de insercion OTREPUESTO");
JOptionPane.showMessageDialog(
null,
"Datos Ingresados Satisfactoriamente",
"Mensajero",
JOptionPane.INFORMATION_MESSAGE);
} catch (SQLException ex) {
ex.printStackTrace();
JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
}
}