@WebMethod
  public List<Asset> getFasilitasAset(int idAset) throws SQLException {
    String query =
        "SELECT *  FROM "
            + FASILITAS_ASET_TABLE
            + " JOIN "
            + ASSET_TABLE
            + " ON "
            + FASILITAS_ASET_TABLE
            + ".id_fasilitas="
            + ASSET_TABLE
            + ".id"
            + " WHERE id_aset = "
            + idAset;
    System.out.println(query);
    ResultSet rs = executeQuery(query);

    List<Asset> assets = null;
    while (rs.next()) {
      if (rs.isFirst()) assets = new ArrayList<Asset>();
      Asset asset = new Asset();
      asset.setId(rs.getInt("id"));
      asset.setNama(rs.getString("nama"));
      asset.setKategori(rs.getString("kategori"));
      asset.setKategori(rs.getString("jenis"));
      asset.setTanggalMasuk(rs.getDate("tanggal_masuk"));
      asset.setKondisi(AssetCondition.getInstance(rs.getString("kondisi")));
      asset.setPemilik(rs.getString("pemilik"));
      asset.setVendor(getVendorAset(rs.getInt("id_vendor")));
      asset.setHarga(rs.getString("harga"));
      asset.setPublicAsset(rs.getBoolean("is_public"));
      assets.add(asset);
    }
    return assets;
  }
Example #2
1
  @Override
  public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
    Set<EmpVO> set = new LinkedHashSet<EmpVO>();
    EmpVO empVO = null;

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {

      Class.forName(driver);
      con = DriverManager.getConnection(url, userid, passwd);
      pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
      pstmt.setInt(1, deptno);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        empVO = new EmpVO();
        empVO.setEmpno(rs.getInt("empno"));
        empVO.setEname(rs.getString("ename"));
        empVO.setJob(rs.getString("job"));
        empVO.setHiredate(rs.getDate("hiredate"));
        empVO.setSal(rs.getDouble("sal"));
        empVO.setComm(rs.getDouble("comm"));
        empVO.setDeptno(rs.getInt("deptno"));
        set.add(empVO); // Store the row in the vector
      }

      // Handle any driver errors
    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
      // Handle any SQL errors
    } catch (SQLException se) {
      throw new RuntimeException("A database error occured. " + se.getMessage());
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (con != null) {
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace(System.err);
        }
      }
    }
    return set;
  }
 private void verifyDateResultSet(ResultSet rs, Date date, int rowCount) throws SQLException {
   for (int i = 0; i < rowCount; i++) {
     assertTrue(rs.next());
     if (tgtPH()) assertEquals(date, rs.getDate(1));
     else if (tgtSQ() || tgtTR()) assertEquals(date.toString(), rs.getDate(1).toString());
   }
   assertFalse(rs.next());
 }
 @Override
 public List<Ordine> listaOrdini() throws PersistenceException { // ok
   Connection connection = this.datasource.getConnection();
   PreparedStatement statement = null;
   List<Ordine> ordini = null;
   Ordine ordine = null;
   try {
     String str =
         "select ordini.codice as codice,stato,data,cliente,id "
             + "from ordini left outer join clienti on ordini.cliente=clienti.codice";
     statement = connection.prepareStatement(str);
     ResultSet result = statement.executeQuery();
     ordini = new ArrayList<Ordine>();
     while (result.next()) {
       ordine = new Ordine();
       ClienteDAOImpl cliente = new ClienteDAOImpl();
       ordine.setCliente(cliente.getClienteById(result.getInt("cliente")));
       ordine.setCodiceOrdine(result.getString("codice"));
       ordine.setData(new java.util.Date(result.getDate("data").getDate()));
       ordine.setStato(result.getString("stato"));
       ordine.setId(result.getInt("id"));
       ordini.add(ordine);
     }
   } catch (SQLException e) {
     throw new PersistenceException(e.getMessage());
   } finally {
     try {
       if (statement != null) statement.close();
       if (connection != null) connection.close();
     } catch (SQLException e) {
       throw new PersistenceException(e.getMessage());
     }
   }
   return ordini;
 }
 @Override
 public List<Ordine> getOrdiniPerCliente(Cliente cliente) throws PersistenceException { // ok
   Connection connection = this.datasource.getConnection();
   PreparedStatement statement = null;
   Ordine o = null;
   List<Ordine> lo = new LinkedList<Ordine>();
   try {
     String str = "select codice,data,stato,id from ordini where cliente=?";
     statement = connection.prepareStatement(str);
     statement.setInt(1, cliente.getId());
     ResultSet result = statement.executeQuery();
     while (result.next()) {
       o = new Ordine();
       o.setCliente(cliente);
       o.setCodiceOrdine(result.getString("codice"));
       o.setData(result.getDate("data"));
       o.setStato(result.getString("stato"));
       o.setId(result.getInt("id"));
       lo.add(o);
       // da completare per restituire ordine completo di righe
     }
   } catch (SQLException e) {
     throw new PersistenceException(e.getMessage());
   } finally {
     try {
       if (statement != null) statement.close();
       if (connection != null) connection.close();
     } catch (SQLException e) {
       throw new PersistenceException(e.getMessage());
     }
   }
   return lo;
 }
  /**
   * Method to build a vector from a ResultSet
   *
   * @param <code>ResultSet rsResult</code> the result from a query
   * @return Vector of objects from the ResultSet
   * @exception Throws Exception on error
   */
  protected Vector buildObjectVector(ResultSet rsResult) throws Exception {
    // vector for return data
    Vector vReturn = null;

    try {
      // init the vector
      vReturn = new Vector(super.VECT_INIT_SIZE, super.VECT_GROW_SIZE);

      // loop through the entire result set
      while (rsResult.next()) {
        // create a new one
        StageUsacForm objTmp = new StageUsacForm();

        // set the attributes
        // ADDING A NEW FIELD "ROWID"
        objTmp.ROWID = rsResult.getString("ROWID");

        objTmp.HDR_SPIN = rsResult.getLong("HDR_SPIN");
        objTmp.SPIN_NM = rsResult.getString("SPIN_NM");
        objTmp.RCPNT_EMAIL = rsResult.getString("RCPNT_EMAIL");
        objTmp.USAC_EMAIL = rsResult.getString("USAC_EMAIL");
        objTmp.RFRNC_NMBR = rsResult.getString("RFRNC_NMBR");
        objTmp.RCRD_CNT = rsResult.getLong("RCRD_CNT");
        objTmp.TOT_PAYMENT = rsResult.getDouble("TOT_PAYMENT");
        objTmp.USAC_PRCS_DAT = rsResult.getDate("USAC_PRCS_DAT");
        objTmp.RTRCT_FLAG = rsResult.getString("RTRCT_FLAG");
        objTmp.DTL_SPIN = rsResult.getLong("DTL_SPIN");
        objTmp.FRN = rsResult.getLong("FRN");
        objTmp.SDC_INV_NO = rsResult.getString("SDC_INV_NO");
        objTmp.AMT_PAID = rsResult.getDouble("AMT_PAID");
        objTmp.DSBRSMNT_TXT = rsResult.getString("DSBRSMNT_TXT");
        objTmp.EMAIL_DATE = rsResult.getDate("EMAIL_DATE");
        objTmp.FILENAME = rsResult.getString("FILENAME");
        objTmp.PROCESS_DATE = rsResult.getDate("PROCESS_DATE");
        objTmp.STATUS = rsResult.getLong("STATUS");

        // add it to the vector
        vReturn.addElement(objTmp);
      }

      return vReturn;
    } catch (Exception e) {
      throw new Exception("BuildObjectVector()\n" + e.getMessage());
    }
  }
  /**
   * aggregation - group by
   *
   * @throws Exception
   */
  @Test
  public void testDateGroupBy() throws Exception {
    printTestDescription();

    Date date = new Date(1);
    initATableValues(date);

    try {
      ResultSet rs;
      String query = null;
      if (tgtPH())
        query =
            "SELECT a_date, count(1) FROM atable WHERE organization_id='"
                + tenantId
                + "' group by a_date";
      else if (tgtSQ() || tgtTR())
        query =
            "SELECT a_date, count(1) FROM atable WHERE organization_id='"
                + tenantId
                + "' group by a_date order by 1";
      rs = getResultSet(conn, query);

      /* 3 rows in expected result:
       * 1969-12-31   3
       * 1970-01-01   3
       * 1970-01-02   3
       * */

      assertTrue(rs.next());
      if (tgtPH()) assertEquals(date, rs.getDate(1));
      else if (tgtSQ() || tgtTR()) assertEquals(date.toString(), rs.getDate(1).toString());
      assertEquals(3, rs.getInt(2));

      // the following assertions fails
      assertTrue(rs.next());
      assertEquals(3, rs.getInt(2));
      assertTrue(rs.next());
      assertEquals(3, rs.getInt(2));
      assertFalse(rs.next());

    } finally {
    }
  }
Example #8
0
 /**
  * Gets a value from a given column in a JDBC result set.
  *
  * @param i Ordinal of column (1-based, per JDBC)
  */
 private Object value(int i) throws SQLException {
   // MySQL returns timestamps shifted into local time. Using
   // getTimestamp(int, Calendar) with a UTC calendar should prevent this,
   // but does not. So we shift explicitly.
   switch (types[i]) {
     case Types.TIMESTAMP:
       return shift(resultSet.getTimestamp(i + 1));
     case Types.TIME:
       return shift(resultSet.getTime(i + 1));
     case Types.DATE:
       return shift(resultSet.getDate(i + 1));
   }
   return primitives[i].jdbcGet(resultSet, i + 1);
 }
 public List<Oriundo> listar() throws SQLException {
   List<Oriundo> resultado = new ArrayList<Oriundo>();
   conexao propCon = new conexao();
   Connection con =
       DriverManager.getConnection(
           new conexao().url,
           propCon.config.getString("usuario"),
           propCon.config.getString("senha"));
   PreparedStatement ps = null;
   ResultSet rs = null;
   String sqlListar = "SELECT * FROM oriundo Order by codigo DESC";
   Oriundo oriundo;
   try {
     ps = con.prepareStatement(sqlListar);
     rs = ps.executeQuery();
     // if(rs==null){
     // return null;
     // }
     while (rs.next()) {
       oriundo = new Oriundo();
       oriundo.setCodigo(rs.getInt("codigo"));
       oriundo.setDescricao(rs.getString("descricao"));
       oriundo.setData_cadastro(rs.getDate("data_cadastro"));
       oriundo.setDia_fechamento(rs.getInt("dia_fechamento"));
       oriundo.setDia_pag(rs.getInt("dia_pag"));
       resultado.add(oriundo);
     }
   } catch (NumberFormatException e) {
     JOptionPane.showMessageDialog(
         null, "NumberFormaterExeption Erro: " + e.getMessage(), "ClasseDAO Func.Localizar", 0);
     e.printStackTrace();
   } catch (NullPointerException e) {
     JOptionPane.showMessageDialog(
         null, "NullPointerException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
     e.printStackTrace();
   } catch (SQLException e) {
     JOptionPane.showMessageDialog(
         null, "SQLException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
     e.printStackTrace();
   } catch (Exception e) {
     JOptionPane.showMessageDialog(
         null, "Exception Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
     e.printStackTrace();
   } finally {
     ps.close();
     con.close();
   }
   return resultado;
 }
 public Oriundo localizar(Integer codigo) throws SQLException {
   Connection con =
       DriverManager.getConnection(
           new conexao().url,
           new conexao().config.getString("usuario"),
           new conexao().config.getString("senha"));
   PreparedStatement ps = null;
   ResultSet rs = null;
   String sqlLocalizar = "SELECT * FROM oriundo WHERE codigo=?";
   Oriundo oriundo = new Oriundo();
   try {
     ps = con.prepareStatement(sqlLocalizar);
     ps.setInt(1, codigo);
     rs = ps.executeQuery();
     // if(!rs.next()){
     // return null;
     // }
     oriundo.setCodigo(rs.getInt("codigo"));
     oriundo.setDescricao(rs.getString("descricao"));
     oriundo.setData_cadastro(rs.getDate("data_cadastro"));
     oriundo.setDia_fechamento(rs.getInt("dia_fechamento"));
     oriundo.setDia_pag(rs.getInt("dia_pag"));
     return oriundo;
   } catch (NumberFormatException e) {
     JOptionPane.showMessageDialog(
         null, "NumberFormaterExeption Erro: " + e.getMessage(), "ClasseDAO Func.Localizar", 0);
     e.printStackTrace();
   } catch (NullPointerException e) {
     JOptionPane.showMessageDialog(
         null, "NullPointerException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
     e.printStackTrace();
   } catch (SQLException e) {
     JOptionPane.showMessageDialog(
         null, "SQLException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
     e.printStackTrace();
   } catch (Exception e) {
     JOptionPane.showMessageDialog(
         null, "Exception Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
     e.printStackTrace();
   } finally {
     ps.close();
     con.close();
   }
   return oriundo;
 }
  private Asset _getAset(int idAset) throws SQLException {
    String query = "SELECT * FROM " + ASSET_TABLE + " WHERE id = " + idAset;
    ResultSet rs = executeQuery(query);
    Asset asset = null;
    if (rs.next()) {
      asset = new Asset();
      asset.setId(rs.getInt("id"));
      asset.setNama(rs.getString("nama"));
      asset.setKategori(rs.getString("kategori"));
      asset.setKategori(rs.getString("jenis"));
      asset.setTanggalMasuk(rs.getDate("tanggal_masuk"));
      asset.setKondisi(AssetCondition.getInstance(rs.getString("kondisi")));
      asset.setPemilik(rs.getString("pemilik"));
      asset.setVendor(getVendorAset(rs.getInt("id_vendor")));
      asset.setHarga(rs.getString("harga"));
      asset.setPublicAsset(rs.getBoolean("is_public"));
    }

    return asset;
  }
Example #12
0
  public static List<Movie> getAllMovies() {
    String query = "SELECT * FROM movie";
    List<Movie> movies = new LinkedList<>();
    try (Connection connection = Config.getConnection(); // step 1
        Statement statement = connection.createStatement(); // step 2
        ResultSet result = statement.executeQuery(query); ) { // step 3 and 4
      while (result.next()) { // step 5
        Movie movie = new Movie();
        // you should be validating the following,
        // this is just an example to get you started
        movie.setName(result.getString(1));
        movie.setYear(result.getDate(2).getYear());
        movie.setUrl(result.getString(3));
        movies.add(movie);
      }
    } catch (SQLException e) {
      System.err.println(e.getMessage());
      System.err.println(e.getStackTrace());
    }

    return movies;
  }
 @Override
 public Ordine getOrdineByCodice(String codice) throws PersistenceException {
   Ordine ordine = null;
   Connection connection = this.datasource.getConnection();
   PreparedStatement statement = null;
   try {
     String query =
         "select ordini.codice,ordini.id as id,data,stato,cliente,nome,partitaiva,indirizzo "
             + "from ordini LEFT OUTER JOIN clienti on cliente = clienti.id WHERE ordini.codice=?";
     statement = connection.prepareStatement(query);
     statement.setString(1, codice);
     ResultSet result = statement.executeQuery();
     if (result.next()) {
       ordine = new Ordine();
       ordine.setCodiceOrdine(codice);
       ordine.setStato(result.getString("stato"));
       ordine.setId(result.getInt("id"));
       ordine.setData(new java.util.Date(result.getDate("data").getTime()));
       Cliente cliente = new Cliente();
       cliente.setId(result.getInt("cliente"));
       cliente.setNome(result.getString("nome"));
       cliente.setIndirizzo(result.getString("indirizzo"));
       cliente.setPiva(result.getString("partitaiva"));
       ordine.setCliente(cliente);
     }
   } catch (SQLException e) {
     throw new PersistenceException(e.getMessage());
   } finally {
     try {
       if (statement != null) statement.close();
       if (connection != null) connection.close();
     } catch (SQLException e) {
       throw new PersistenceException(e.getMessage());
     }
   }
   return ordine;
 }
  @WebMethod
  public List<Asset> getAsetByJenis(String jenis) throws SQLException {
    String query = "SELECT * FROM " + ASSET_TABLE + " WHERE jenis= '" + jenis + "'";
    ResultSet rs = executeQuery(query);

    List<Asset> assets = null;
    while (rs.next()) {
      if (rs.isFirst()) assets = new ArrayList<Asset>();
      Asset asset = new Asset();
      asset.setId(rs.getInt("id"));
      asset.setNama(rs.getString("nama"));
      asset.setKategori(rs.getString("kategori"));
      asset.setKategori(rs.getString("jenis"));
      asset.setTanggalMasuk(rs.getDate("tanggal_masuk"));
      asset.setKondisi(AssetCondition.getInstance(rs.getString("kondisi")));
      asset.setPemilik(rs.getString("pemilik"));
      asset.setVendor(getVendorAset(rs.getInt("id_vendor")));
      asset.setHarga(rs.getString("harga"));
      asset.setPublicAsset(rs.getBoolean("is_public"));
      assets.add(asset);
    }

    return assets;
  }
Example #15
0
  private void loadCompany(Connection con, String unit_code) throws Exception {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String qry = null;

    JCompany obj = null;

    qry =
        " select unit_id, unit_code, unit_name, biz_lx, biz_ym, biz_fl, biz_yx, biz_ch, "
            + "        company_fullName, company_addr,company_district,unit_created "
            + "   from t_unit "
            + "  where unit_code=? ";
    pstmt = con.prepareStatement(qry);
    pstmt.setString(1, unit_code.toUpperCase());
    rs = pstmt.executeQuery();
    if (rs.next()) {
      Set<String> bizSet = null;
      String[] bizTypes = null;
      String szId, szCode, szName;

      bizSet = new HashSet<String>();
      szId = rs.getString("unit_id");
      szCode = rs.getString("unit_code");
      szName = JUtil.convertNull(rs.getString("unit_name"));

      {
        if (rs.getInt("biz_lx") == 1) bizSet.add("留学");
        if (rs.getInt("biz_ym") == 1) bizSet.add("移民");
        if (rs.getInt("biz_yx") == 1) bizSet.add("团组");
        if (rs.getInt("biz_fl") == 1) bizSet.add("外语");
        if (rs.getInt("biz_ch") == 1) bizSet.add("华文");
        bizTypes = new String[bizSet.size()];
        int i = 0;
        for (String biz : bizSet) {
          bizTypes[i++] = biz;
        }
      }

      obj = new JCompany(szId, szCode, szName, bizTypes);

      obj.setCompany_fullName(rs.getString("company_fullName"));
      obj.setCompany_address(rs.getString("company_addr"));
      obj.setCompany_district(rs.getString("company_district"));
      obj.setUnit_created(rs.getDate("unit_created"));
      obj.setBranch(getChildBranch(con, szCode));
    }
    rs.close();
    pstmt.close();

    if (obj == null) return;

    /** ******** 放到 列表 中 ****************** */
    String szId = obj.getCompany_id();
    String szCode = obj.getCompany_code();

    lsBizCompany.remove(szId);
    if (!szCode.startsWith("XT_NB.")) lsBizCompany.add(szId);

    lsOACompany.contains(szId);
    lsOACompany.add(szId);

    mapCompany.put(obj.getCompany_code(), obj);
    int idx = lsCompany.size();
    for (int i = 0; i < lsCompany.size(); i++) {
      JCompany com = lsCompany.get(i);
      if (com.getCompany_id().equals(szId)) {
        idx = i;
        lsCompany.remove(i);
        break;
      }
    }
    lsCompany.add(idx, obj);
  }
Example #16
0
  public void setContent(String cat) {
    cat = cat.trim();
    selectAllCB.setVisible(false);
    selectAllCB.setSelected(false);
    deleteBut.setVisible(false);
    restoreBut.setVisible(false);
    refreshBut.setVisible(true);
    Object columns[] = null;
    int count = 0;
    switch (cat) {
      case "Inbox":
        columns = new Object[] {"", "From", "Date", "Subject", "Content"};
        count = Database.getCount("Inbox");
        workingSet = db.getData("SELECT * FROM messages WHERE tag='inbox' ORDER BY msg_id desc");
        ;
        break;
      case "SentMail":
        columns = new Object[] {"", "To", "Date", "Subject", "Content"};
        count = Database.getCount("Sentmail");
        workingSet = db.getData("SELECT * FROM messages WHERE tag='sentmail' ORDER BY msg_id desc");
        break;
      case "Draft":
        columns = new Object[] {"", "To", "Date", "Subject", "Content"};
        count = Database.getCount("Draft");
        workingSet = db.getData("SELECT * FROM messages WHERE tag='draft' ORDER BY msg_id desc");
        break;
      case "Outbox":
        columns = new Object[] {"", "To", "Date", "Subject", "Content"};
        count = Database.getCount("Outbox");
        workingSet = db.getData("SELECT * FROM messages WHERE tag='outbox' ORDER BY msg_id desc");
        break;
      case "Trash":
        //                restoreBut.setVisible(true);
        columns = new Object[] {"", "To/From", "Date", "Subject", "Content"};
        count = Database.getCount("Trash");
        workingSet =
            db.getData(
                "SELECT * FROM messages,trash WHERE messages.tag='trash' and messages.msg_id=trash.msg_id ORDER BY deleted_at desc");
        break;
      default:
        System.out.println("in default case");
    }
    if (count > 0) {
      selectAllCB.setVisible(true);
      rows = new Object[count][];
      msgID = new int[count];
      try {
        workingSet.beforeFirst();
        for (int i = 0; i < count && workingSet.next(); i++) {
          msgID[i] = workingSet.getInt(1);
          rows[i] =
              new Object[] {
                false,
                workingSet.getString(2),
                workingSet.getDate(3),
                workingSet.getString(4),
                workingSet.getString(5)
              };
        }
      } catch (SQLException sqlExc) {
        JOptionPane.showMessageDialog(null, sqlExc, "EXCEPTION", JOptionPane.ERROR_MESSAGE);
        sqlExc.printStackTrace();
      }

      tableModel = new MyDefaultTableModel(rows, columns);
      table = new JTable(tableModel);
      table.getSelectionModel().addListSelectionListener(this);
      table.addMouseListener(this);
      table.getTableHeader().setOpaque(true);
      table.getTableHeader().setReorderingAllowed(false);
      //            table.getTableHeader().setBackground(Color.blue);
      table.getTableHeader().setForeground(Color.blue);
      //        table.setRowSelectionAllowed(false);
      //            table.setColumnSelectionAllowed(false);
      table.setFont(new Font(Font.SANS_SERIF, Font.PLAIN, 14));
      table.setRowHeight(20);
      table.setFillsViewportHeight(true);

      TableColumn column = null;
      for (int i = 0; i < 5; i++) {
        column = table.getColumnModel().getColumn(i);
        if (i == 0) {
          column.setPreferredWidth(6);
        } else if (i == 3) {
          column.setPreferredWidth(250);
        } else if (i == 4) {
          column.setPreferredWidth(450);
        } else {
          column.setPreferredWidth(40);
        }
      }
      table.setAutoResizeMode(JTable.AUTO_RESIZE_SUBSEQUENT_COLUMNS);

      remove(contentPan);
      contentPan = new JScrollPane(table);
      contentPan.setBackground(Color.orange);
      contentPan.setOpaque(true);
      contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
      add(contentPan, "Center");
      Home.home.homeFrame.setVisible(true);
    } else {
      JPanel centPan = new JPanel(new GridBagLayout());
      centPan.setBackground(new Color(52, 86, 70));
      JLabel label = new JLabel("No Messages In This Category");
      label.setFont(new Font(Font.SANS_SERIF, Font.BOLD, 22));
      label.setForeground(Color.orange);
      centPan.add(label);
      remove(contentPan);
      contentPan = new JScrollPane(centPan);
      contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
      add(contentPan, "Center");
      contentPan.repaint();
    }
  }
Example #17
0
  public void localTimestamps(String timeZone) throws Exception {
    final String testDateFormat = "yyyy-MM-dd HH:mm:ss";
    final List<String> datesToTest =
        Arrays.asList(
            "2015-09-03 12:00:00",
            "2015-06-30 23:59:58",
            "1997-06-30 23:59:59",
            "1997-07-01 00:00:00",
            "2012-06-30 23:59:59",
            "2012-07-01 00:00:00",
            "2015-06-30 23:59:59",
            "2015-07-01 00:00:00",
            "2005-12-31 23:59:59",
            "2006-01-01 00:00:00",
            "2008-12-31 23:59:59",
            "2009-01-01 00:00:00",
            "2015-06-30 23:59:60",
            "2015-07-31 00:00:00",
            "2015-07-31 00:00:01",

            // On 2000-03-26 02:00:00 Moscow went to DST, thus local time became 03:00:00
            "2000-03-26 01:59:59",
            "2000-03-26 02:00:00",
            "2000-03-26 02:00:01",
            "2000-03-26 02:59:59",
            "2000-03-26 03:00:00",
            "2000-03-26 03:00:01",
            "2000-03-26 03:59:59",
            "2000-03-26 04:00:00",
            "2000-03-26 04:00:01",

            // On 2000-10-29 03:00:00 Moscow went to regular time, thus local time became 02:00:00
            "2000-10-29 01:59:59",
            "2000-10-29 02:00:00",
            "2000-10-29 02:00:01",
            "2000-10-29 02:59:59",
            "2000-10-29 03:00:00",
            "2000-10-29 03:00:01",
            "2000-10-29 03:59:59",
            "2000-10-29 04:00:00",
            "2000-10-29 04:00:01");

    con.createStatement().execute("delete from testtimezone");
    Statement stmt = con.createStatement();

    for (int i = 0; i < datesToTest.size(); i++) {
      stmt.execute(
          "insert into testtimezone (ts, seq) values ('" + datesToTest.get(i) + "', " + i + ")");
    }

    // Different timezone test should have different sql text, so we test both text and binary modes
    PreparedStatement pstmt =
        con.prepareStatement("SELECT ts FROM testtimezone order by seq /*" + timeZone + "*/");

    Calendar expectedTimestamp = Calendar.getInstance();

    SimpleDateFormat sdf = new SimpleDateFormat(testDateFormat);

    for (int i = 0; i < PREPARE_THRESHOLD; i++) {
      ResultSet rs = pstmt.executeQuery();
      for (int j = 0; rs.next(); j++) {
        String testDate = datesToTest.get(j);
        Date getDate = rs.getDate(1);
        Timestamp getTimestamp = rs.getTimestamp(1);
        String getString = rs.getString(1);
        Time getTime = rs.getTime(1);
        expectedTimestamp.setTime(sdf.parse(testDate));

        assertEquals(
            "getTimestamp: "
                + testDate
                + ", transfer format: "
                + (i == 0 ? "text" : "binary")
                + ", timeZone: "
                + timeZone,
            sdf.format(expectedTimestamp.getTimeInMillis()),
            sdf.format(getTimestamp));

        assertEquals(
            "getString: "
                + testDate
                + ", transfer format: "
                + (i == 0 ? "text" : "binary")
                + ", timeZone: "
                + timeZone,
            sdf.format(expectedTimestamp.getTimeInMillis()),
            sdf.format(sdf.parse(getString)));

        expectedTimestamp.set(Calendar.HOUR_OF_DAY, 0);
        expectedTimestamp.set(Calendar.MINUTE, 0);
        expectedTimestamp.set(Calendar.SECOND, 0);

        assertEquals(
            "getDate: "
                + testDate
                + ", transfer format: "
                + (i == 0 ? "text" : "binary")
                + ", timeZone: "
                + timeZone,
            sdf.format(expectedTimestamp.getTimeInMillis()),
            sdf.format(getDate));

        expectedTimestamp.setTime(sdf.parse(testDate));
        expectedTimestamp.set(Calendar.YEAR, 1970);
        expectedTimestamp.set(Calendar.MONTH, 0);
        expectedTimestamp.set(Calendar.DAY_OF_MONTH, 1);

        assertEquals(
            "getTime: "
                + testDate
                + ", transfer format: "
                + (i == 0 ? "text" : "binary")
                + ", timeZone: "
                + timeZone,
            sdf.format(expectedTimestamp.getTimeInMillis()),
            sdf.format(getTime));
      }
      rs.close();
    }
  }
Example #18
0
  /**
   * This method must be overridden by the subclass to retrieve data and return the valorized value
   * object.
   *
   * @param valueObjectClass value object class
   * @return a VOResponse object if data loading is successfully completed, or an ErrorResponse
   *     object if an error occours
   */
  public Response loadData(Class valueObjectClass) {
    Statement stmt = null;
    try {
      // since this method could be invoked also when selecting another row on the linked grid,
      // the pk attribute must be recalculated from the grid...
      int row = gridFrame.getGrid().getSelectedRow();
      if (row != -1) {
        TestVO gridVO = (TestVO) gridFrame.getGrid().getVOListTableModel().getObjectForRow(row);
        pk = gridVO.getStringValue();
      }

      stmt = conn.createStatement();
      ResultSet rset =
          stmt.executeQuery(
              "select DEMO4.TEXT,DEMO4.DECNUM,DEMO4.CURRNUM,DEMO4.THEDATE,DEMO4.COMBO,DEMO4.CHECK_BOX,DEMO4.RADIO,DEMO4.CODE,"
                  + "DEMO4_LOOKUP.DESCRCODE,DEMO4.TA,DEMO4.FORMATTED_TEXT,DEMO4.URI,DEMO4.LINK_LABEL,DEMO4.YEAR,DEMO4.FILENAME "
                  + "from DEMO4,DEMO4_LOOKUP where TEXT='"
                  + pk
                  + "' and DEMO4.CODE=DEMO4_LOOKUP.CODE");
      if (rset.next()) {
        DetailTestVO vo = new DetailTestVO();
        vo.setCheckValue(
            rset.getObject(6) == null || !rset.getObject(6).equals("Y")
                ? Boolean.FALSE
                : Boolean.TRUE);
        vo.setCombo(new ComboVO());
        vo.getCombo().setCode(rset.getString(5));

        // this is a simplification: in a real situation combo v.o. will be retrieved from the
        // database...
        Domain d = ClientSettings.getInstance().getDomain("ORDERSTATE");
        if (vo.getCombo().getCode().equals("O")) vo.getCombo().setDescription("opened");
        else if (vo.getCombo().getCode().equals("S")) vo.getCombo().setDescription("sospended");
        else if (vo.getCombo().getCode().equals("D")) vo.getCombo().setDescription("delivered");
        else if (vo.getCombo().getCode().equals("C")) vo.getCombo().setDescription("closed");

        vo.setCurrencyValue(rset.getBigDecimal(3));
        vo.setDateValue(rset.getDate(4));
        vo.setNumericValue(rset.getBigDecimal(2));
        vo.setRadioButtonValue(
            rset.getObject(7) == null || !rset.getObject(7).equals("Y")
                ? Boolean.FALSE
                : Boolean.TRUE);
        vo.setStringValue(rset.getString(1));
        vo.setLookupValue(rset.getString(8));
        vo.setDescrLookupValue(rset.getString(9));
        vo.setTaValue(rset.getString(10));
        vo.setFormattedTextValue(rset.getString(11));
        vo.setUri(rset.getString(12));
        vo.setLinkLabel(rset.getString(13));
        vo.setYear(rset.getBigDecimal(14));
        vo.setFilename(rset.getString(15));
        vo.setTooltipURI(vo.getUri());

        try {
          if (vo.getFilename() != null) {
            File f = new File(vo.getFilename());
            BufferedInputStream in = new BufferedInputStream(new FileInputStream(f));
            byte[] bytes = new byte[(int) f.length()];
            in.read(bytes);
            in.close();
            vo.setFile(bytes);
          }
        } catch (Exception ex) {
          ex.printStackTrace();
        }

        stmt.close();
        stmt = conn.createStatement();
        rset =
            stmt.executeQuery(
                "select DEMO4_LIST_VALUES.CODE from DEMO4_LIST_VALUES where TEXT='" + pk + "'");
        ArrayList codes = new ArrayList();
        while (rset.next()) {
          codes.add(rset.getString(1));
        }
        vo.setListValues(codes);

        return new VOResponse(vo);
      } else return new ErrorResponse("No data found.");
    } catch (SQLException ex) {
      ex.printStackTrace();
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        stmt.close();
      } catch (Exception ex1) {
      }
    }
  }
Example #19
0
  public void testSetDate() throws Exception {
    for (int i = 0; i < PREPARE_THRESHOLD; i++) {
      con.createStatement().execute("delete from testtimezone");
      PreparedStatement insertTimestamp =
          con.prepareStatement("INSERT INTO testtimezone(seq,tstz,ts,d) VALUES (?,?,?,?)");

      int seq = 1;

      Date dJVM, dUTC, dGMT03, dGMT05, dGMT13 = null;

      // +0100 (JVM default)
      dJVM = new Date(1104534000000L); // 2005-01-01 00:00:00 +0100
      insertTimestamp.setInt(1, seq++);
      insertTimestamp.setDate(2, dJVM); // 2005-01-01 00:00:00 +0100
      insertTimestamp.setDate(3, dJVM); // 2005-01-01 00:00:00
      insertTimestamp.setDate(4, dJVM); // 2005-01-01
      insertTimestamp.executeUpdate();

      // UTC
      dUTC = new Date(1104537600000L); // 2005-01-01 00:00:00 +0000
      insertTimestamp.setInt(1, seq++);
      insertTimestamp.setDate(2, dUTC, cUTC); // 2005-01-01 00:00:00 +0000
      insertTimestamp.setDate(3, dUTC, cUTC); // 2005-01-01 00:00:00
      insertTimestamp.setDate(4, dUTC, cUTC); // 2005-01-01
      insertTimestamp.executeUpdate();

      // +0300
      dGMT03 = new Date(1104526800000L); // 2005-01-01 00:00:00 +0300
      insertTimestamp.setInt(1, seq++);
      insertTimestamp.setDate(2, dGMT03, cGMT03); // 2005-01-01 00:00:00 +0300
      insertTimestamp.setDate(3, dGMT03, cGMT03); // 2005-01-01 00:00:00
      insertTimestamp.setDate(4, dGMT03, cGMT03); // 2005-01-01
      insertTimestamp.executeUpdate();

      // -0500
      dGMT05 = new Date(1104555600000L); // 2005-01-01 00:00:00 -0500
      insertTimestamp.setInt(1, seq++);
      insertTimestamp.setDate(2, dGMT05, cGMT05); // 2005-01-01 00:00:00 -0500
      insertTimestamp.setDate(3, dGMT05, cGMT05); // 2005-01-01 00:00:00
      insertTimestamp.setDate(4, dGMT05, cGMT05); // 2005-01-01
      insertTimestamp.executeUpdate();

      if (min73) {
        // +1300
        dGMT13 = new Date(1104490800000L); // 2005-01-01 00:00:00 +1300
        insertTimestamp.setInt(1, seq++);
        insertTimestamp.setDate(2, dGMT13, cGMT13); // 2005-01-01 00:00:00 +1300
        insertTimestamp.setDate(3, dGMT13, cGMT13); // 2005-01-01 00:00:00
        insertTimestamp.setDate(4, dGMT13, cGMT13); // 2005-01-01
        insertTimestamp.executeUpdate();
      }

      insertTimestamp.close();

      // check that insert went correctly by parsing the raw contents in UTC
      checkDatabaseContents(
          "SELECT seq::text,tstz::text,ts::text,d::text from testtimezone ORDER BY seq",
          new String[][] {
            new String[] {"1", "2004-12-31 23:00:00+00", "2005-01-01 00:00:00", "2005-01-01"},
            new String[] {"2", "2005-01-01 00:00:00+00", "2005-01-01 00:00:00", "2005-01-01"},
            new String[] {"3", "2004-12-31 21:00:00+00", "2005-01-01 00:00:00", "2005-01-01"},
            new String[] {"4", "2005-01-01 05:00:00+00", "2005-01-01 00:00:00", "2005-01-01"},
            new String[] {"5", "2004-12-31 11:00:00+00", "2005-01-01 00:00:00", "2005-01-01"}
          });
      //
      // check results
      //

      seq = 1;
      PreparedStatement ps =
          con.prepareStatement("SELECT seq,tstz,ts,d FROM testtimezone ORDER BY seq");
      ResultSet rs = ps.executeQuery();

      assertTrue(rs.next());
      assertEquals(seq++, rs.getInt(1));
      assertEquals(dJVM, rs.getDate(2));
      assertEquals(dJVM, rs.getDate(3));
      assertEquals(dJVM, rs.getDate(4));

      assertTrue(rs.next());
      assertEquals(seq++, rs.getInt(1));
      assertEquals(dUTC, rs.getDate(2, cUTC));
      assertEquals(dUTC, rs.getDate(3, cUTC));
      assertEquals(dUTC, rs.getDate(4, cUTC));

      assertTrue(rs.next());
      assertEquals(seq++, rs.getInt(1));
      assertEquals(dGMT03, rs.getDate(2, cGMT03));
      assertEquals(dGMT03, rs.getDate(3, cGMT03));
      assertEquals(dGMT03, rs.getDate(4, cGMT03));

      assertTrue(rs.next());
      assertEquals(seq++, rs.getInt(1));
      assertEquals(dGMT05, rs.getDate(2, cGMT05));
      assertEquals(dGMT05, rs.getDate(3, cGMT05));
      assertEquals(dGMT05, rs.getDate(4, cGMT05));

      if (min73) {
        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals(dGMT13, rs.getDate(2, cGMT13));
        assertEquals(dGMT13, rs.getDate(3, cGMT13));
        assertEquals(dGMT13, rs.getDate(4, cGMT13));
      }

      assertTrue(!rs.next());
      ps.close();
    }
  }
  protected ReportParameterValue[] getParamValuesFromDataSource(
      ReportParameter param, Map<String, Object> parameters) throws ProviderException {
    Connection conn = null;
    PreparedStatement pStmt = null;
    ResultSet rs = null;

    try {
      ReportDataSource dataSource = param.getDataSource();
      conn = dataSourceProvider.getConnection(dataSource.getId());

      if (parameters == null || parameters.isEmpty()) {
        pStmt = conn.prepareStatement(param.getData());
      } else {
      }

      rs = pStmt.executeQuery();

      ResultSetMetaData rsMetaData = rs.getMetaData();

      boolean multipleColumns = false;
      if (rsMetaData.getColumnCount() > 1) multipleColumns = true;

      ArrayList<ReportParameterValue> v = new ArrayList<ReportParameterValue>();

      while (rs.next()) {
        ReportParameterValue value = new ReportParameterValue();

        if (param.getClassName().equals("java.lang.String")) {
          value.setId(rs.getString(1));
        } else if (param.getClassName().equals("java.lang.Double")) {
          value.setId(new Double(rs.getDouble(1)));
        } else if (param.getClassName().equals("java.lang.Integer")) {
          value.setId(new Integer(rs.getInt(1)));
        } else if (param.getClassName().equals("java.lang.Long")) {
          value.setId(new Long(rs.getLong(1)));
        } else if (param.getClassName().equals("java.math.BigDecimal")) {
          value.setId(rs.getBigDecimal(1));
        } else if (param.getClassName().equals("java.util.Date")) {
          value.setId(rs.getDate(1));
        } else if (param.getClassName().equals("java.sql.Date")) {
          value.setId(rs.getDate(1));
        } else if (param.getClassName().equals("java.sql.Timestamp")) {
          value.setId(rs.getTimestamp(1));
        }

        if (multipleColumns) {
          value.setDescription(rs.getString(2));
        }

        v.add(value);
      }

      rs.close();

      ReportParameterValue[] values = new ReportParameterValue[v.size()];
      v.toArray(values);

      return values;
    } catch (Exception e) {
      throw new ProviderException("Error retreiving param values from database: " + e.getMessage());
    } finally {
      try {
        if (pStmt != null) pStmt.close();
        if (conn != null) conn.close();
      } catch (Exception c) {
        log.error("Error closing");
      }
    }
  }
Example #21
0
  private void getChannelList() {
    Connection con = null;
    ChannelInfo ci;

    this.channel_data.clear();

    try {
      con = pool.getConnection(timeout);

      ResultSet rs;
      try (Statement s = con.createStatement()) {
        rs = s.executeQuery("SELECT * FROM `channels`");
        PreparedStatement s2;
        ResultSet rs2;
        while (rs.next()) {
          ci = new ChannelInfo(rs.getString("channel"));
          ci.setDefaultOptions();

          ci.setReactiveChatter(
              rs.getInt("reactive_chatter_level"), rs.getInt("chatter_name_multiplier"));

          ci.setRandomChatter(rs.getInt("random_chatter_level"));

          ci.setTwitterTimers(
              rs.getFloat("tweet_bucket_max"), rs.getFloat("tweet_bucket_charge_rate"));

          ci.setWarAutoMuzzle(rs.getBoolean("auto_muzzle_wars"));

          ci.velociraptorSightings = rs.getInt("velociraptor_sightings");
          ci.activeVelociraptors = rs.getInt("active_velociraptors");
          ci.deadVelociraptors = rs.getInt("dead_velociraptors");
          ci.killedVelociraptors = rs.getInt("killed_velociraptors");

          if (rs.getDate("last_sighting_date") != null) {
            long time = rs.getDate("last_sighting_date").getTime();
            ci.lastSighting = new java.util.Date(time);
          }

          s2 =
              con.prepareStatement(
                  "SELECT `setting`, `value` FROM `channel_chatter_settings` WHERE `channel` = ?");
          s2.setString(1, rs.getString("channel"));
          s2.executeQuery();

          rs2 = s2.getResultSet();
          while (rs2.next()) {
            ci.addChatterSetting(rs2.getString("setting"), rs2.getBoolean("value"));
          }

          s2.close();
          rs2.close();

          s2 =
              con.prepareStatement(
                  "SELECT `setting`, `value` FROM `channel_command_settings` WHERE `channel` = ?");
          s2.setString(1, rs.getString("channel"));
          s2.executeQuery();

          rs2 = s2.getResultSet();
          while (rs2.next()) {
            ci.addCommandSetting(rs2.getString("setting"), rs2.getBoolean("value"));
          }

          s2.close();
          rs2.close();

          s2 =
              con.prepareStatement(
                  "SELECT `account` FROM `channel_twitter_feeds` WHERE `channel` = ?");
          s2.setString(1, rs.getString("channel"));
          s2.executeQuery();

          rs2 = s2.getResultSet();
          while (rs2.next()) {
            ci.addTwitterAccount(rs2.getString("account"));
          }

          s2.close();
          rs2.close();

          this.channel_data.put(ci.channel, ci);

          this.saveChannelSettings(ci);
        }
      }
      rs.close();
    } catch (SQLException ex) {
      Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
      try {
        if (con != null) {
          con.close();
        }
      } catch (SQLException ex) {
        Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
  }
Example #22
0
  public void testGetDate() throws Exception {
    con.createStatement()
        .executeUpdate(
            "INSERT INTO testtimezone(tstz,ts,d) VALUES('2005-01-01 15:00:00 +0300', '2005-01-01 15:00:00', '2005-01-01')");

    PreparedStatement ps = con.prepareStatement("SELECT tstz,ts,d from testtimezone");
    for (int i = 0; i < PREPARE_THRESHOLD; i++) {
      ResultSet rs = ps.executeQuery();

      assertTrue(rs.next());
      checkDatabaseContents(
          "SELECT tstz::text,ts::text,d::text from testtimezone",
          new String[] {"2005-01-01 12:00:00+00", "2005-01-01 15:00:00", "2005-01-01"});

      Date d;

      // timestamptz: 2005-01-01 15:00:00+03
      d = rs.getDate(1); // 2005-01-01 13:00:00 +0100 -> 2005-01-01 00:00:00 +0100
      assertEquals(1104534000000L, d.getTime());
      d = rs.getDate(1, cUTC); // 2005-01-01 12:00:00 +0000 -> 2005-01-01 00:00:00 +0000
      assertEquals(1104537600000L, d.getTime());
      d = rs.getDate(1, cGMT03); // 2005-01-01 15:00:00 +0300 -> 2005-01-01 00:00:00 +0300
      assertEquals(1104526800000L, d.getTime());
      d = rs.getDate(1, cGMT05); // 2005-01-01 07:00:00 -0500 -> 2005-01-01 00:00:00 -0500
      assertEquals(1104555600000L, d.getTime());
      d = rs.getDate(1, cGMT13); // 2005-01-02 01:00:00 +1300 -> 2005-01-02 00:00:00 +1300
      assertEquals(1104577200000L, d.getTime());

      // timestamp: 2005-01-01 15:00:00
      d = rs.getDate(2); // 2005-01-01 00:00:00 +0100
      assertEquals(1104534000000L, d.getTime());
      d = rs.getDate(2, cUTC); // 2005-01-01 00:00:00 +0000
      assertEquals(1104537600000L, d.getTime());
      d = rs.getDate(2, cGMT03); // 2005-01-01 00:00:00 +0300
      assertEquals(1104526800000L, d.getTime());
      d = rs.getDate(2, cGMT05); // 2005-01-01 00:00:00 -0500
      assertEquals(1104555600000L, d.getTime());
      d = rs.getDate(2, cGMT13); // 2005-01-01 00:00:00 +1300
      assertEquals(1104490800000L, d.getTime());

      // date: 2005-01-01
      d = rs.getDate(3); // 2005-01-01 00:00:00 +0100
      assertEquals(1104534000000L, d.getTime());
      d = rs.getDate(3, cUTC); // 2005-01-01 00:00:00 +0000
      assertEquals(1104537600000L, d.getTime());
      d = rs.getDate(3, cGMT03); // 2005-01-01 00:00:00 +0300
      assertEquals(1104526800000L, d.getTime());
      d = rs.getDate(3, cGMT05); // 2005-01-01 00:00:00 -0500
      assertEquals(1104555600000L, d.getTime());
      d = rs.getDate(3, cGMT13); // 2005-01-01 00:00:00 +1300
      assertEquals(1104490800000L, d.getTime());

      assertTrue(!rs.next());
      rs.close();
    }
  }