Example #1
1
  @SuppressWarnings("unchecked")
  public static final List<Record> build(Config config, ResultSet rs) throws SQLException {
    List<Record> result = new ArrayList<Record>();
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    String[] labelNames = new String[columnCount + 1];
    int[] types = new int[columnCount + 1];
    buildLabelNamesAndTypes(rsmd, labelNames, types);
    while (rs.next()) {
      Record record = new Record();
      record.setColumnsMap(config.containerFactory.getColumnsMap());
      Map<String, Object> columns = record.getColumns();
      for (int i = 1; i <= columnCount; i++) {
        Object value;
        if (types[i] < Types.BLOB) value = rs.getObject(i);
        else if (types[i] == Types.CLOB) value = ModelBuilder.handleClob(rs.getClob(i));
        else if (types[i] == Types.NCLOB) value = ModelBuilder.handleClob(rs.getNClob(i));
        else if (types[i] == Types.BLOB) value = ModelBuilder.handleBlob(rs.getBlob(i));
        else value = rs.getObject(i);

        columns.put(labelNames[i], value);
      }
      result.add(record);
    }
    return result;
  }
  /**
   * INTERNAL: Get a timestamp value from a result set. Overrides the default behavior to
   * specifically return a timestamp. Added to overcome an issue with the oracle 9.0.1.4 JDBC
   * driver.
   */
  public Object getObjectFromResultSet(
      ResultSet resultSet, int columnNumber, int type, AbstractSession session)
      throws java.sql.SQLException {
    // Bug#3381652 10G Drivers return sql.Date instead of timestamp on DATE field
    if ((type == Types.TIMESTAMP) || (type == Types.DATE)) {
      return resultSet.getTimestamp(columnNumber);
    } else if (type == OracleTypes.TIMESTAMPTZ) {
      TIMESTAMPTZ tsTZ = (TIMESTAMPTZ) resultSet.getObject(columnNumber);

      // Need to call timestampValue once here with the connection to avoid null point
      // exception later when timestampValue is called in converObject()
      if ((tsTZ != null) && (tsTZ.getLength() != 0)) {
        Connection connection = getConnection(session, resultSet.getStatement().getConnection());
        // Bug#4364359  Add a wrapper to overcome TIMESTAMPTZ not serializable as of jdbc 9.2.0.5
        // and 10.1.0.2.
        // It has been fixed in the next version for both streams
        return new TIMESTAMPTZWrapper(tsTZ, connection, isTimestampInGmt(connection));
      }
      return null;
    } else if (type == OracleTypes.TIMESTAMPLTZ) {
      // TIMESTAMPLTZ needs to be converted to Timestamp here because it requires the connection.
      // However the java object is not know here.  The solution is to store Timestamp and the
      // session timezone in a wrapper class, which will be used later in converObject().
      TIMESTAMPLTZ tsLTZ = (TIMESTAMPLTZ) resultSet.getObject(columnNumber);
      if ((tsLTZ != null) && (tsLTZ.getLength() != 0)) {
        Timestamp ts =
            TIMESTAMPLTZ.toTimestamp(
                getConnection(session, resultSet.getStatement().getConnection()), tsLTZ.toBytes());

        // Bug#4364359  Add a separate wrapper for TIMESTAMPLTZ.
        return new TIMESTAMPLTZWrapper(
            ts,
            ((OracleConnection) getConnection(session, resultSet.getStatement().getConnection()))
                .getSessionTimeZone());
      }
      return null;
    } else if (type == OracleTypes.OPAQUE) {
      try {
        Object result = resultSet.getObject(columnNumber);
        if (!(result instanceof OPAQUE)) {
          // Report Queries can cause result to not be an instance of OPAQUE.
          return result;
        }

        return getXMLTypeFactory().getString((OPAQUE) result);
      } catch (SQLException ex) {
        throw DatabaseException.sqlException(ex, null, session, false);
      }
    } else {
      Object value = super.getObjectFromResultSet(resultSet, columnNumber, type, session);
      if (type == OracleTypes_NCLOB) {
        value = convertObject(value, ClassConstants.STRING);
      }
      return value;
    }
  }
  public static String getPassword(String email) throws SQLException {
    String Password = "";
    Connection l_conn = null;
    PreparedStatement l_prepStat = null;
    try {
      l_conn = createConnection();
      l_prepStat =
          l_conn.prepareStatement(
              "SELECT password FROM app_user WHERE email_id=? and  isfbaccount='N';");
      // Constants.logger.info("l_callStat::{}"+l_prepStat);
      l_prepStat.setString(1, email);

      Constants.logger.info("l_callStat ::{}" + l_prepStat);

      ResultSet l_rs = l_prepStat.executeQuery();
      ResultSetMetaData l_rsmd = l_rs.getMetaData();
      int l_noColumns = l_rsmd.getColumnCount();

      while (l_rs.next()) {
        for (int col = 1; col <= l_noColumns; col++) {
          Password = "" + l_rs.getObject("password");
          l_rs.getObject("password");
        }
      }
      l_rs.close();
      l_prepStat.close();
      l_conn.close();
    } catch (SQLException e) {
      l_prepStat.close();
      l_conn.close();
      Constants.logger.error("Error : " + e.getMessage());
    }
    return Password;
  }
 protected final void map(final ResultSet rs, final T entity) throws SQLException {
   entity.setOsId(rs.getInt("os"));
   entity.setDescription(rs.getString("description"));
   entity.setComment(rs.getString("free_text_comment"));
   entity.setCreationDate(DbFacadeUtils.fromDate(rs.getTimestamp("creation_date")));
   entity.setNumOfSockets(rs.getInt("num_of_sockets"));
   entity.setCpuPerSocket(rs.getInt("cpu_per_socket"));
   entity.setTimeZone(rs.getString("time_zone"));
   entity.setVmType(VmType.forValue(rs.getInt("vm_type")));
   entity.setUsbPolicy(UsbPolicy.forValue(rs.getInt("usb_policy")));
   entity.setFailBack(rs.getBoolean("fail_back"));
   entity.setDefaultBootSequence(BootSequence.forValue(rs.getInt("default_boot_sequence")));
   entity.setNiceLevel(rs.getInt("nice_level"));
   entity.setCpuShares(rs.getInt("cpu_shares"));
   entity.setPriority(rs.getInt("priority"));
   entity.setAutoStartup(rs.getBoolean("auto_startup"));
   entity.setStateless(rs.getBoolean("is_stateless"));
   entity.setDbGeneration(rs.getLong("db_generation"));
   entity.setIsoPath(rs.getString("iso_path"));
   entity.setOrigin(OriginType.forValue(rs.getInt("origin")));
   entity.setKernelUrl(rs.getString("kernel_url"));
   entity.setKernelParams(rs.getString("kernel_params"));
   entity.setInitrdUrl(rs.getString("initrd_url"));
   entity.setSmartcardEnabled(rs.getBoolean("is_smartcard_enabled"));
   entity.setDeleteProtected(rs.getBoolean("is_delete_protected"));
   entity.setSsoMethod(SsoMethod.fromString(rs.getString("sso_method")));
   entity.setTunnelMigration((Boolean) rs.getObject("tunnel_migration"));
   entity.setVncKeyboardLayout(rs.getString("vnc_keyboard_layout"));
   entity.setRunAndPause(rs.getBoolean("is_run_and_pause"));
   entity.setCreatedByUserId(Guid.createGuidFromString(rs.getString("created_by_user_id")));
   entity.setMigrationDowntime((Integer) rs.getObject("migration_downtime"));
   entity.setSerialNumberPolicy(
       SerialNumberPolicy.forValue((Integer) rs.getObject("serial_number_policy")));
   entity.setCustomSerialNumber(rs.getString("custom_serial_number"));
 }
 private void producto() {
   DefaultTableModel modelo = new DefaultTableModel();
   try {
     Statement estatuto = conex.getCn().createStatement();
     ResultSet resultado =
         estatuto.executeQuery(
             "SELECT idProducto Codigo, Producto, Precio, IF(Servicio = true, 'Servicio', Cantidad) Cantidad FROM producto order by Producto asc");
     String[] Titulos = {"Codigo", "Nombre", "Precio", "Cantidad"};
     modelo.setColumnIdentifiers(Titulos);
     while (resultado.next()) {
       Object[] fila = {
         resultado.getObject("Codigo"),
         resultado.getObject("Producto"),
         resultado.getObject("Precio"),
         resultado.getObject("Cantidad")
       };
       modelo.addRow(fila);
     }
     TableProducto.setModel(modelo);
   } catch (SQLException e) {
     setTitle(e.toString());
     JOptionPane.showMessageDialog(
         null, e.toString(), "Student Control", JOptionPane.INFORMATION_MESSAGE);
   }
 }
  /** ensures two tables (or views, that is) have the same content */
  private void ensureEqualContent(String tableNameLHS, String tableNameRHS) throws SQLException {

    ResultSet lhs = m_statement.executeQuery("SELECT * FROM \"" + tableNameLHS + "\"");
    ResultSet rhs = m_statement.executeQuery("SELECT * FROM \"" + tableNameRHS + "\"");
    ResultSetMetaData meta = lhs.getMetaData();

    while (lhs.next() && rhs.next()) {
      for (int col = 1; col <= meta.getColumnCount(); ++col) {
        assertEquals(
            "table content does not match: cp. "
                + tableNameLHS
                + "-"
                + tableNameRHS
                + ", row "
                + lhs.getRow()
                + ", col "
                + col,
            lhs.getObject(col),
            rhs.getObject(col));
      }
    }

    // lhs should be after last, rhs still on last
    assertTrue(
        "row count does not match: " + tableNameLHS + "-" + tableNameRHS,
        lhs.isAfterLast() && rhs.isLast());
  }
Example #7
0
 /** 封装结果. */
 private Object getResult(ResultSet resultSet, String resultType) throws SQLException {
   if ("simple".equals(resultType)) { // 单列单行
     return resultSet.next() ? resultSet.getObject(1) : null;
   } else if ("simples".equals(resultType)) { // 单列多行
     List<Object> rows = new ArrayList<>();
     while (resultSet.next()) {
       rows.add(resultSet.getObject(1));
     }
     return rows;
   } else if ("object".equals(resultType)) { // 多列单行
     ResultSetMetaData metaData = resultSet.getMetaData();
     Map<String, Object> row = null;
     if (resultSet.next()) {
       row = new HashMap<>();
       for (int i = 1; i <= metaData.getColumnCount(); i++) {
         String name = metaData.getColumnLabel(i);
         row.put(name, resultSet.getObject(name));
       }
     }
     return row;
   } else { // 多列多行
     ResultSetMetaData metaData = resultSet.getMetaData();
     List<Map<String, Object>> rows = new ArrayList<>(); // 封装结果
     while (resultSet.next()) {
       Map<String, Object> row = new HashMap<>();
       for (int i = 1; i <= metaData.getColumnCount(); i++) {
         String name = metaData.getColumnLabel(i); // 别名,getColumnName-列名
         row.put(name, resultSet.getObject(name));
       }
       rows.add(row);
     }
     return rows;
   }
 }
 public ClientDetails mapRow(ResultSet rs, int rowNum) throws SQLException {
   BaseClientDetails details =
       new BaseClientDetails(
           rs.getString(1),
           rs.getString(3),
           rs.getString(4),
           rs.getString(5),
           rs.getString(7),
           rs.getString(6));
   details.setClientSecret(rs.getString(2));
   if (rs.getObject(8) != null) {
     details.setAccessTokenValiditySeconds(rs.getInt(8));
   }
   if (rs.getObject(9) != null) {
     details.setRefreshTokenValiditySeconds(rs.getInt(9));
   }
   String json = rs.getString(10);
   if (json != null) {
     try {
       @SuppressWarnings("unchecked")
       Map<String, Object> additionalInformation = mapper.read(json, Map.class);
       details.setAdditionalInformation(additionalInformation);
     } catch (Exception e) {
       logger.warn("Could not decode JSON for additional information: " + details, e);
     }
   }
   String scopes = rs.getString(11);
   if (scopes != null) {
     details.setAutoApproveScopes(StringUtils.commaDelimitedListToSet(scopes));
   }
   return details;
 }
  /**
   * Find job in table 'jobs'. Get values of all fields in searched record. Create object of 'Job'
   * class by these values.
   *
   * @param jobId - id number of need job.
   * @return object of Job class.
   */
  protected Job createJobById(int jobId) {
    ResultSet resultSet = null;
    try {
      resultSet = find("jobs", Job.JOB_ID, jobId); // find record by id
      resultSet.first();

      // Getting values of fields.
      int id = (int) resultSet.getObject(Job.JOB_ID);
      String name = String.valueOf(resultSet.getObject(Job.JOB_NAME));
      String description = String.valueOf(resultSet.getObject(Job.DESCRIPTION));
      int percents = (int) resultSet.getObject(Job.PERCENTS);
      Date beginDate = (Date) resultSet.getObject(Job.BEGIN_DATE);
      Date endDate = (Date) resultSet.getObject(Job.END_DATE);
      Status status = createStatusById((int) resultSet.getObject(Job.STATUS_ID));
      Priority priority = createPriorityById((int) resultSet.getObject(Job.PRIORITY_ID));
      User worker = createUserById((int) resultSet.getObject(Job.WORKER_ID));
      User manager = createUserById((int) resultSet.getObject(Job.MANAGER_ID));

      // Create object of Job class by values.
      return new Job(
          id, name, description, percents, beginDate, endDate, status, priority, worker, manager);
    } catch (SQLException exception) {
      logger.getExceptionTextFileLogger().error(exception);
      return null;
    }
  }
 private void testSetObject(Connection conn) throws SQLException {
   Statement stat = conn.createStatement();
   stat.execute("CREATE TABLE TEST(C CHAR(1))");
   PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?)");
   prep.setObject(1, 'x');
   prep.execute();
   stat.execute("DROP TABLE TEST");
   stat.execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)");
   prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
   prep.setInt(1, 1);
   prep.setObject(2, 11);
   prep.setObject(3, null);
   prep.execute();
   prep.setInt(1, 2);
   prep.setObject(2, 101, Types.OTHER);
   prep.setObject(3, 103, Types.OTHER);
   prep.execute();
   PreparedStatement p2 = conn.prepareStatement("SELECT * FROM TEST ORDER BY ID");
   ResultSet rs = p2.executeQuery();
   rs.next();
   Object o = rs.getObject(2);
   assertTrue(o instanceof byte[]);
   assertTrue(rs.getObject(3) == null);
   rs.next();
   o = rs.getObject(2);
   assertTrue(o instanceof byte[]);
   o = rs.getObject(3);
   assertTrue(o instanceof Integer);
   assertEquals(103, ((Integer) o).intValue());
   assertFalse(rs.next());
   stat.execute("DROP TABLE TEST");
 }
 /**
  * Submits query via JDBC
  *
  * @param query SQL query string
  * @param statement sql statement to execute the query with
  * @param outputFilename name of file result set is to be written to
  * @param timeout time allowed for query execution
  * @throws Exception
  */
 public void submitQueryJDBC(
     String query, Statement statement, String outputFilename, long timeout) throws Exception {
   BufferedWriter writer = null;
   if (outputFilename != null) {
     writer = new BufferedWriter(new FileWriter(new File(outputFilename)));
   }
   ResultSet resultSet = null;
   try {
     RunThread runThread = new RunThread(statement, query);
     processThread(runThread, timeout);
     resultSet = runThread.getResultSet();
     if (resultSet == null) {
       throw runThread.getException();
     }
     if (outputFilename == null) {
       return;
     }
     int columnCount = resultSet.getMetaData().getColumnCount();
     columnLabels = new ArrayList<String>();
     for (int i = 1; i <= columnCount; i++) {
       columnLabels.add(resultSet.getMetaData().getColumnLabel(i));
     }
     Object[] types = new Object[columnCount];
     for (int i = 1; i <= columnCount; i++) {
       types[i - 1] = resultSet.getMetaData().getColumnType(i);
     }
     ColumnList.setTypes(types);
     LOG.debug("Result set data types:");
     LOG.debug(Utils.getTypesInStrings(ColumnList.getTypes()));
     while (resultSet.next()) {
       Object[] values = new Object[columnCount];
       for (int i = 1; i <= columnCount; i++) {
         try {
           if (resultSet.getObject(i) == null) {
             values[i - 1] = null;
             continue;
           }
           values[i - 1] = new String(resultSet.getBytes(i));
         } catch (Exception e) {
           if (resultSet.getMetaData().getColumnType(i) == Types.DATE) {
             values[i - 1] = resultSet.getDate(i);
           } else {
             values[i - 1] = resultSet.getObject(i);
           }
         }
       }
       ColumnList columnList = new ColumnList(values);
       if (writer != null) {
         writer.write(columnList + "\n");
       }
     }
     if (writer != null) {
       writer.close();
     }
   } finally {
     if (resultSet != null) {
       resultSet.close();
     }
   }
 }
  @Override
  public Vector<Object> getAllStudentInfo(String dmid)
      throws QueryResultIsNullException, DataBaseException {
    Statement stmt = DB.CreateStatement();
    String sql = "select * from View_Student_College_StuDormRoom where DormId = '" + dmid + "'";
    // String sql = "select * from View_Student_College " ;
    // System.out.println(sql);

    try {
      ResultSet rs = stmt.executeQuery(sql);
      if (rs != null && !rs.next()) {
        throw new QueryResultIsNullException();
      }
      Vector<Object> vectors = new Vector<Object>();
      Vector<Object> vector = new Vector<Object>();
      for (int i = 1; i <= 9; i++) {
        vector.add(rs.getObject(i));
      }
      vectors.add(vector);
      while (rs.next()) {
        vector = new Vector<Object>();
        for (int i = 1; i <= 9; i++) {
          vector.add(rs.getObject(i));
        }
        vectors.add(vector);
      }
      return vectors;
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DataBaseException();
    }
  }
  public java.lang.Object[] getListofActivities() {

    java.lang.Object[] listofActivities = null;

    java.util.Vector listActVector = new java.util.Vector(1, 1);

    try {

      //    java.sql.Connection connDB =
      // java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");

      java.sql.Statement stmt1 = connectDB.createStatement();

      java.sql.PreparedStatement pSet1 =
          connectDB.prepareStatement(
              "SELECT DISTINCT code,activity FROM pb_activity where activity_category ilike 'I%' order by code");

      java.sql.ResultSet rSet1 = pSet1.executeQuery();

      while (rSet1.next()) {
        System.out.println(rSet1.getObject(1).toString());
        listActVector.addElement(rSet1.getObject(1).toString());
      }

      // System.out.println(rSet1.getObject(1).toString());
    } catch (java.sql.SQLException sqlExec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
    }

    listofActivities = listActVector.toArray();
    System.out.println("Done list of activities ...");
    return listofActivities;
  }
Example #14
0
  public LinkedList<Trigger> listaTrigger(String nombreT, Connection con) throws SQLException {
    Statement s = con.createStatement();
    LinkedList<Trigger> lT = new LinkedList();
    LinkedList<String> nombTrigger = nombreTrigger(nombreT, s);

    ResultSet rs;
    String disparador = null;
    for (int i = 0; i < nombTrigger.size(); i++) {
      LinkedList<String> condTrigger = new LinkedList();
      rs =
          s.executeQuery(
              "SELECT event_manipulation,action_timing FROM information_schema.triggers where trigger_name ='"
                  + nombTrigger.get(i)
                  + "' order by event_manipulation");
      int u = 0;
      while (rs.next()) {
        u++;
      }
      rs =
          s.executeQuery(
              "SELECT event_manipulation,action_timing FROM information_schema.triggers where trigger_name ='"
                  + nombTrigger.get(i)
                  + "' order by event_manipulation");
      while (rs.next()) {
        disparador = rs.getObject("action_timing").toString();
        condTrigger.add(rs.getObject("event_manipulation").toString());
        // System.out.println("COND "+nombTrigger.get(i)+" -
        // "+rs.getObject("event_manipulation").toString());
      }
      lT.add(new Trigger(nombTrigger.get(i), condTrigger, disparador));
    }
    s.close();
    return lT;
  }
 // 测试Oracle连接是否成功
 public static void main(String[] args) throws SQLException {
   Connection connection = new OracleUtils().getConnection();
   System.out.println("连接成功:" + connection);
   Statement statement = connection.createStatement();
   // DOP线为分离
   // ResultSet rs =
   // statement.executeQuery("select GW as 岗位,'2015-10-20' as 日期,round(sum(BQUA),0) as 产量 ,
   // sum(case when Crtdate between to_date('2015-10-20 07:15:00','yyyy-mm-dd hh24:mi:ss') and
   // to_date('2015-10-20 15:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 早班 , sum(case
   // when Crtdate between to_date('2015-10-20 15:15:00','yyyy-mm-dd hh24:mi:ss') and
   // to_date('2015-10-20 23:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 中班 , sum(case
   // when Crtdate between to_date('2016-10-20 23:15:00','yyyy-mm-dd hh24:mi:ss') and
   // to_date('2015-10-21 07:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 晚班 from
   // baandb.ttiwcn108220 e,baandb.ttiwcn109220 f where e.BOXID=f.BOXID and ( f.DES1='封箱') and
   // f.CrtDate between to_date('2015-10-20 07:15:00','yyyy-mm-dd hh24:mi:ss') and
   // to_date('2015-10-21 07:15:00','yyyy-mm-dd hh24:mi:ss') group by GW");
   // DOP线已分离
   ResultSet rs =
       statement.executeQuery(
           "select (case when GW='DOP' then (case when CrtAcc in ('SCDOP1','SCDOP2','SCDOP3') then 'D15' else 'D10'  end)  else GW end)  as 岗位,'2015-11-05' as 日期,round(sum(BQUA),0) as 产量 ,sum(case when Crtdate between to_date('2015-11-05 07:15:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-11-05 15:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 早班 ,sum(case when Crtdate between to_date('2015-11-05 15:15:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-11-05 23:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 中班 ,sum(case when Crtdate between to_date('2015-11-05 23:15:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-11-06 07:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 晚班  from baandb.ttiwcn108220 e,baandb.ttiwcn109220 f where e.BOXID=f.BOXID and ( f.DES1='封箱') and f.CrtDate between to_date('2015-11-05 07:15:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-11-06 07:15:00','yyyy-mm-dd hh24:mi:ss') group by (case when GW='DOP' then (case when CrtAcc in ('SCDOP1','SCDOP2','SCDOP3') then 'D15' else 'D10'  end)  else GW end)");
   while (rs.next()) {
     System.out.println("岗位:" + rs.getObject(1));
     System.out.println("日期" + rs.getObject(2));
     System.out.println("产量" + rs.getObject(3));
     System.out.println(rs.getObject(4));
     System.out.println(rs.getObject(5));
     System.out.println(rs.getObject(6));
     System.out.println("---------------");
   }
 }
  private Table getHouseholdFactBaseCubeTable() throws SQLException {
    Table householdFactBaseCubeTable = new Table();
    householdFactBaseCubeTable.setSizeFull();
    householdFactBaseCubeTable.setImmediate(true);
    householdFactBaseCubeTable.setColumnCollapsingAllowed(true);

    householdFactBaseCubeTable.addContainerProperty("ACCOUNT CITY", String.class, null);
    householdFactBaseCubeTable.addContainerProperty("BRANCH CITY", String.class, null);
    householdFactBaseCubeTable.addContainerProperty("FISCAL QUARTER", String.class, null);
    householdFactBaseCubeTable.addContainerProperty("STATUS REASON", String.class, null);
    householdFactBaseCubeTable.addContainerProperty("PRIMARY BALANCE", String.class, null);
    householdFactBaseCubeTable.addContainerProperty("TRANSACTION COUNT", String.class, null);
    householdFactBaseCubeTable.addContainerProperty("ACCOUNT COUNT", String.class, null);

    DecimalFormat formatDecimal = new DecimalFormat("#.##");

    ResultSet resultSet = bankService.viewHouseholdBaseCube();
    int i = 1;
    while (resultSet.next()) {
      householdFactBaseCubeTable.addItem(
          new Object[] {
            resultSet.getObject(1).toString(),
            resultSet.getObject(2).toString(),
            resultSet.getObject(3).toString(),
            resultSet.getObject(4).toString(),
            formatDecimal.format((Double) resultSet.getObject(5)).toString(),
            resultSet.getObject(6).toString().replace(".0", ""),
            resultSet.getObject(7).toString().replace(".0", "")
          },
          new Integer(i));
      i++;
    }

    return householdFactBaseCubeTable;
  }
  public Object[][] getAllUnitInfo() {
    PreparedStatement getrelqty;
    ResultSet getResultSet;
    // float Qty = 0;
    Object[] UnitName;
    String strgetUnitRelativeQuantity =
        "select unit_id,unit_name,unit_relative_quantity from item_unit ";
    ArrayList<Object[]> data = new ArrayList<Object[]>();
    //      DBConnect getUnit = new DBConnect();
    try {
      initConnection();
      getrelqty = conn.prepareStatement(strgetUnitRelativeQuantity);
      //         getrelqty.setInt(1, UnitId);
      getResultSet = getrelqty.executeQuery();

      while (getResultSet.next()) {
        Object st[] =
            new Object[] {
              getResultSet.getObject("unit_id"),
              getResultSet.getObject("unit_name"),
              getResultSet.getObject("unit_relative_quantity")
            };
        data.add(st);
      }

    } catch (SQLException e) {
      JOptionPane.showMessageDialog(null, e + "form getUnitINfo" + getClass().getName());
    } finally {
      closeConnection();
    }
    return data.toArray(new Object[data.size()][]);
  }
  private void testUpdateLob() throws SQLException {
    Connection conn = getConnection();
    Statement stat = conn.createStatement();
    stat.execute(
        "CREATE TABLE object_index " + "(id integer primary key, object other, number integer)");

    PreparedStatement prep =
        conn.prepareStatement("INSERT INTO object_index (id,object)  VALUES (1,?)");
    prep.setObject(1, "hello", Types.JAVA_OBJECT);
    prep.execute();

    ResultSet rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
    rs.next();
    assertEquals("hello", rs.getObject(1).toString());
    stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
    rs.next();
    assertEquals("hello", rs.getObject(1).toString());
    rs.updateInt(2, 1);
    rs.updateRow();
    rs.close();
    stat = conn.createStatement();
    rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
    rs.next();
    assertEquals("hello", rs.getObject(1).toString());
    conn.close();
  }
Example #19
0
  public void checkSessionLogout() throws Exception {
    uti.writeAsteriskLog("- SYSTE  - Check DateTime Agent unLogout");
    String date = uti.getDate();
    String sql = "SELECT * FROM login_action WHERE CAST(datetime_login AS DATE) >=  '" + date + "'";
    ResultSet rs = sqlQuery(sql);
    String datenow = uti.getDatetime();
    while (rs.next()) {
      String datelogout = String.valueOf(rs.getObject("datetime_logout"));
      if (datelogout.equalsIgnoreCase("null")) {
        String agentid = String.valueOf(rs.getObject("agent_id"));
        String iface = String.valueOf(rs.getObject("interface"));
        String queue = String.valueOf(rs.getObject("queue"));
        String session = rs.getString("session");
        updateStatus(agentid, "NULL", "NULL");
        logoutAction(session, agentid);
        uti.writeAsteriskLog(
            "- SYSTE  - Update datetime agent unlogout\t" + agentid + "\t" + session);
        System.out.println("update success logout\t" + session);

        //                    sql = "UPDATE login_action SET datetime_logout ='"+datenow+"'"
        //                        + " WHERE session = '"+session+"'" ;
        //                    int rs2 = sqlExecute(sql);
        //                    if(rs2 != 0)
        //                        uti.writeAsteriskLog("- SYSTE  - Update datetime agent
        // unlogout\t"+agentid+"\t"+session);
        //                        System.out.println("update success\t"+session);
        //                    String sql2 = "UPDATE agent_status SET interface =null,queue=null
        // WHERE agent_id = '"+agentid+"'" ;
        //                    rs2 = sqlExecute(sql2);
      }
    }
  }
Example #20
0
  /**
   * puts the value for the column aginst the column Name
   *
   * @param os
   * @param rows
   * @param oldResultSet
   * @param index
   * @param modifiedColumns
   * @param columnName
   * @throws SQLException
   * @throws IOException
   */
  public void writeUpdate(
      Writer os,
      ResultSet rows,
      ResultSet oldResultSet,
      int index,
      HashMap modifiedColumns,
      String columnName,
      ArrayList encodedCols)
      throws SQLException, IOException {
    Object newObject = rows.getObject(index);
    Object oldObject = oldResultSet.getObject(index);

    if (newObject == null) {
      write(os, "NULL", encodedCols, columnName);
      if (oldObject != null) {
        modifiedColumns.put(columnName, "NULL");
      }
    } else {
      write(os, newObject, encodedCols, columnName);
      if (oldObject != null) {
        if (!(newObject.equals(oldObject))) {
          modifiedColumns.put(columnName, newObject);
        }
      } else {
        modifiedColumns.put(columnName, newObject);
      }
    }
  }
Example #21
0
 public String dlmResults(String sqlquery, boolean headers, String dlm) throws SQLException {
   stmt = conn.createStatement();
   ResultSet tempRs = stmt.executeQuery(sqlquery);
   ResultSetMetaData metaData = tempRs.getMetaData();
   int columnCount = metaData.getColumnCount();
   String TempStr = "";
   if (headers) {
     for (int i = 1; i <= columnCount; ++i) {
       if (i > 1) {
         TempStr += dlm + metaData.getColumnName(i);
       } else {
         TempStr += metaData.getColumnName(i);
       }
     }
   }
   while (tempRs.next()) {
     for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
       if (columnIndex > 1) {
         TempStr += dlm + tempRs.getObject(columnIndex);
       } else {
         TempStr += "\n" + tempRs.getObject(columnIndex);
       }
     }
   }
   tempRs.close();
   return TempStr;
 }
  @Override
  public Attachment map(int i, ResultSet resultSet, StatementContext statementContext)
      throws SQLException {
    Attachment attachment = new Attachment();
    attachment.setId((UUID) resultSet.getObject("id"));
    attachment.setTitle(resultSet.getString("title"));
    attachment.setDescription(resultSet.getString("description"));
    attachment.setSlug(resultSet.getString("slug"));
    attachment.setData(resultSet.getBinaryStream("data"));
    attachment.setExtension(resultSet.getString("extension"));
    attachment.setParentId((UUID) resultSet.getObject("parent_id"));

    if (MapperUtils.hasColumn("localization_data", resultSet)
        && !Strings.isNullOrEmpty(resultSet.getString("localization_data"))) {
      ObjectMapper mapper = new ObjectMapper();
      try {
        Map<Locale, Map<String, Object>> localizedVersions = Maps.newHashMap();
        Map[] data = mapper.readValue(resultSet.getString("localization_data"), Map[].class);
        for (Map map : data) {
          localizedVersions.put(
              LocaleUtils.toLocale((String) map.get("locale")), (Map) map.get("entity"));
        }
        attachment.setLocalizedVersions(localizedVersions);
      } catch (IOException e) {
        throw new SQLException("Failed to de-serialize localization JSON data", e);
      }
    }

    return attachment;
  }
Example #23
0
  public static String getRate(String voter, String ans) {

    DBConnector dBConnector = new DBConnector();
    Connection dbConnection = dBConnector.getConnection();
    String selectTableSQL = "select * from RATING where idvoter= ? and idanswer=?";
    String selectTableSQL2 = "select * from user where username= ? ";
    PreparedStatement statement;
    try {
      statement = dbConnection.prepareStatement(selectTableSQL2);
      statement.setString(1, voter);
      ResultSet rs = statement.executeQuery();
      while (rs.next()) {
        voter = rs.getObject(1) + "";
      }
      statement.close();

      statement = dbConnection.prepareStatement(selectTableSQL);
      statement.setString(1, voter);
      statement.setString(2, ans);
      rs = statement.executeQuery();
      int count = 0;
      while (rs.next()) {
        count++;
        return rs.getObject(4) + "";
      }
      statement.close();
    } catch (SQLException ex) {
      Logger.getLogger(Rating.class.getName()).log(Level.SEVERE, null, ex);
    }
    return "gagal";
  }
Example #24
0
  /** private method which actually will do all of our work for the sample */
  private void executeSample() {

    String query = "select anEmployee from staff2";
    try {
      Statement stmt = _con.createStatement();
      ;

      // Execute the query which will return an Employee object
      // We will cast this using the Person interface. Note the
      // Person interface class MUST be in your CLASSPATH. You
      // Do not need Employee in your CLASSPATH.
      ResultSet rs = stmt.executeQuery(query);

      output("***Using interface class\n");
      while (rs.next()) {
        Person aPerson = (Person) rs.getObject(1);
        displayMethods(aPerson.getClass());
        output(
            "The person is: "
                + aPerson.toString()
                + "\nFirst Name= "
                + aPerson.getFirstName()
                + "\nLast Name= "
                + aPerson.getLastName()
                + "\n");
      }
      // Now execute the same query, but this time we will use
      // reflection to access the class.  Again, only the interface
      // Person is required in the CLASSPATH
      rs = stmt.executeQuery(query);

      output("***Using reflection\n");
      Object theObj = null;
      while (rs.next()) {
        theObj = rs.getObject(1);
        output("The person is: " + theObj.toString() + "\n");
        Class theClass = theObj.getClass();
        displayMethods(theClass);
        Method m1 = theClass.getMethod("toString", new Class[0]);
        Method m2 = theClass.getMethod("getFirstName", new Class[0]);
        Method m3 = theClass.getMethod("getLastName", new Class[0]);
        output(
            "The person is: "
                + (Object) m1.invoke(theObj, new Object[0])
                + "\nFirst Name= "
                + (Object) m2.invoke(theObj, new Object[0])
                + "\nLast Name= "
                + (Object) m3.invoke(theObj, new Object[0])
                + "\n");
      }
      rs.close();
      stmt.close();
    } catch (SQLException sqe) {
      displaySQLEx(sqe);
    } catch (Exception e) {
      error("Unexpected exception : " + e.toString() + "\n");
      e.printStackTrace();
    }
  }
Example #25
0
 @Override
 public Quota mapRow(ResultSet rs, int rowNum) throws SQLException {
   QuotaE quota = new QuotaE();
   quota.setQuotaId((UUID) rs.getObject(1));
   quota.setClusterId((UUID) rs.getObject(2));
   quota.setProjectId((UUID) rs.getObject(3));
   return quota;
 }
 public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
   Account account = new Account();
   account.setId(rs.getInt("id"));
   account.setCurrency(rs.getObject("currency", Currency.class));
   account.setBalance(rs.getDouble("balance"));
   account.setAccountType(rs.getObject("accountType", AccountType.class));
   return account;
 }
  private void testArray() throws SQLException {
    trace("Test ARRAY");
    ResultSet rs;
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE ARRAY)");
    PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
    prep.setInt(1, 1);
    prep.setObject(2, new Object[] {new Integer(1), new Integer(2)});
    prep.execute();
    prep.setInt(1, 2);
    prep.setObject(2, new Object[] {11, 12});
    prep.execute();
    prep.close();
    rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
    rs.next();
    assertEquals(1, rs.getInt(1));
    Object[] list = (Object[]) rs.getObject(2);
    assertEquals(1, ((Integer) list[0]).intValue());
    assertEquals(2, ((Integer) list[1]).intValue());

    Array array = rs.getArray(2);
    Object[] list2 = (Object[]) array.getArray();
    assertEquals(1, ((Integer) list2[0]).intValue());
    assertEquals(2, ((Integer) list2[1]).intValue());
    list2 = (Object[]) array.getArray(2, 1);
    assertEquals(2, ((Integer) list2[0]).intValue());
    rs.next();
    assertEquals(2, rs.getInt(1));
    list = (Object[]) rs.getObject(2);
    assertEquals(11, ((Integer) list[0]).intValue());
    assertEquals(12, ((Integer) list[1]).intValue());

    array = rs.getArray("VALUE");
    list2 = (Object[]) array.getArray();
    assertEquals(11, ((Integer) list2[0]).intValue());
    assertEquals(12, ((Integer) list2[1]).intValue());
    list2 = (Object[]) array.getArray(2, 1);
    assertEquals(12, ((Integer) list2[0]).intValue());

    list2 = (Object[]) array.getArray(Collections.<String, Class<?>>emptyMap());
    assertEquals(11, ((Integer) list2[0]).intValue());

    assertEquals(Types.NULL, array.getBaseType());
    assertEquals("NULL", array.getBaseTypeName());

    assertTrue(array.toString().endsWith(": (11, 12)"));

    // free
    array.free();
    assertEquals("null", array.toString());
    assertThrows(ErrorCode.OBJECT_CLOSED, array).getBaseType();
    assertThrows(ErrorCode.OBJECT_CLOSED, array).getBaseTypeName();
    assertThrows(ErrorCode.OBJECT_CLOSED, array).getResultSet();

    assertFalse(rs.next());
    stat.execute("DROP TABLE TEST");
  }
 private void assertResultSetsEqual(Statement s1, Statement s2) throws SQLException {
   final TimeZone moscowTz = TimeZone.getTimeZone("Europe/Moscow");
   final Calendar moscowCalendar = Calendar.getInstance(moscowTz);
   final TimeZone alaskaTz = TimeZone.getTimeZone("America/Anchorage");
   final Calendar alaskaCalendar = Calendar.getInstance(alaskaTz);
   try (ResultSet rs1 = s1.getResultSet();
       ResultSet rs2 = s2.getResultSet()) {
     assertEquals(rs1.getMetaData().getColumnCount(), rs2.getMetaData().getColumnCount());
     int colCount = rs1.getMetaData().getColumnCount();
     while (rs1.next() && rs2.next()) {
       for (int i = 0; i < colCount; i++) {
         Object o1 = rs1.getObject(i + 1);
         Object o2 = rs2.getObject(i + 1);
         if (o1 instanceof Integer && o2 instanceof Short) {
           // Hsqldb returns Integer for short columns; we prefer Short
           o1 = ((Number) o1).shortValue();
         }
         if (o1 instanceof Integer && o2 instanceof Byte) {
           // Hsqldb returns Integer for tinyint columns; we prefer Byte
           o1 = ((Number) o1).byteValue();
         }
         if (o1 instanceof Date) {
           Date d1 = rs1.getDate(i + 1, moscowCalendar);
           Date d2 = rs2.getDate(i + 1, moscowCalendar);
           assertEquals(d1, d2);
           d1 = rs1.getDate(i + 1, alaskaCalendar);
           d2 = rs2.getDate(i + 1, alaskaCalendar);
           assertEquals(d1, d2);
           d1 = rs1.getDate(i + 1, null);
           d2 = rs2.getDate(i + 1, null);
           assertEquals(d1, d2);
           d1 = rs1.getDate(i + 1);
           d2 = rs2.getDate(i + 1);
           assertEquals(d1, d2);
         }
         if (o1 instanceof Timestamp) {
           Timestamp d1 = rs1.getTimestamp(i + 1, moscowCalendar);
           Timestamp d2 = rs2.getTimestamp(i + 1, moscowCalendar);
           assertEquals(d1, d2);
           d1 = rs1.getTimestamp(i + 1, alaskaCalendar);
           d2 = rs2.getTimestamp(i + 1, alaskaCalendar);
           assertEquals(d1, d2);
           d1 = rs1.getTimestamp(i + 1, null);
           d2 = rs2.getTimestamp(i + 1, null);
           assertEquals(d1, d2);
           d1 = rs1.getTimestamp(i + 1);
           d2 = rs2.getTimestamp(i + 1);
           assertEquals(d1, d2);
         }
         assertEquals(o1, o2);
       }
     }
     assertEquals(rs1.next(), rs2.next());
   }
 }
 private double fetchValueOfDouble(String fieldname) {
   // As with fetch...String, the point is to handle nulls gracefully.
   try {
     return (rs_instrument.getObject(fieldname) == null)
         ? 0
         : Double.parseDouble(rs_instrument.getObject(HEIGHT).toString());
   } catch (SQLException sqle) {
     System.out.println("Unable to fetch value of " + fieldname + ".\n" + sqle);
     return -1;
   }
 }
 private static Object getValue(final String columnName, final ResultSet resultSet)
     throws SQLException {
   Object result = resultSet.getObject(columnName);
   if (null == result) {
     result = resultSet.getObject(columnName.toUpperCase());
   }
   if (null == result) {
     result = resultSet.getObject(columnName.toLowerCase());
   }
   return result;
 }