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();
    }
  }
Exemple #3
0
  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);
    }
  }
Exemple #5
0
  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();
    }
  }
Exemple #7
0
  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);
    }
  }
Exemple #8
0
  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);
    }
  }