Ejemplo n.º 1
1
  /**
   * Get <em>phase_dependency</em> records from database.
   *
   * @param criteria the <em>WHERE</em> clause to filter the records.
   * @return all the records meet the criteria
   * @throws Exception to JUnit.
   */
  public static DBRecord[] getDependencyRecords(String criteria) throws Exception {
    Connection connection = createConnection();
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(SELECT_PHASE_DEPENDENCY + criteria);

    List records = new ArrayList();
    while (resultSet.next()) {
      DBRecord record = new DBRecord();
      record.addValue("dependency_phase_id", new Long(resultSet.getLong("dependency_phase_id")));
      record.addValue("dependent_phase_id", new Long(resultSet.getLong("dependent_phase_id")));
      record.addValue("dependency_start", new Boolean(resultSet.getBoolean("dependency_start")));
      record.addValue("dependent_start", new Boolean(resultSet.getBoolean("dependent_start")));
      record.addValue("lag_time", new Long(resultSet.getLong("lag_time")));
      /*
       * record.addValue("create_user", resultSet.getString("create_user"));
       * record.addValue("create_date", resultSet.getTimestamp("create_date"));
       * record.addValue("modify_user", resultSet.getString("modify_user"));
       * record.addValue("modify_date", resultSet.getTimestamp("modify_date"));
       */
      records.add(record);
    }

    resultSet.close();
    statement.close();
    connection.close();

    return (DBRecord[]) records.toArray(new DBRecord[0]);
  }
 public void assign(ResultSet rs) throws SQLException {
   currencyid = rs.getLong("currencyid");
   facilityid = rs.getLong("facilityid");
   ratevts = rs.getLong("ratevts");
   ratexts = rs.getLong("ratexts");
   rateyts = rs.getLong("rateyts");
   ratezts = rs.getLong("ratezts");
   tariffid = rs.getLong("tariffid");
   vendorid = rs.getLong("vendorid");
   vendorvts = rs.getLong("vendorvts");
   vendorxts = rs.getLong("vendorxts");
   vendoryts = rs.getLong("vendoryts");
   vendorzts = rs.getLong("vendorzts");
   handlingisperc = rs.getBoolean("handlingisperc");
   othersisperc = rs.getBoolean("othersisperc");
   specialisperc = rs.getBoolean("specialisperc");
   specialrate = rs.getLong("specialrate");
   surcharge1isperc = rs.getBoolean("surcharge1isperc");
   surcharge1rate = rs.getLong("surcharge1rate");
   surcharge2isperc = rs.getBoolean("surcharge2isperc");
   surcharge2rate = rs.getLong("surcharge2rate");
   currencyname = rs.getString("currencyname");
   departure = rs.getLong("departure");
   handlingrate = rs.getLong("handlingrate");
   othersrate = rs.getLong("othersrate");
   portid = rs.getLong("portid");
   surcharge1name = rs.getString("surcharge1name");
   surcharge2name = rs.getString("surcharge2name");
   terminalid = rs.getLong("terminalid");
   vendorname = rs.getString("vendorname");
   vendorpriority = rs.getString("vendorpriority");
   durationfrom = rs.getDate("durationfrom");
   durationto = rs.getDate("durationto");
   transittimedays = rs.getLong("transittimedays");
 }
Ejemplo n.º 3
1
 @Override
 public User mapRow(ResultSet rs, int rowNum) throws SQLException {
   // id, username, email, password, salt, identity_verified, nickname,
   // gender, job, phone, telephone, avatar, signature, speciality,
   // preference, province, city, district, address, created_on,
   // version, enable
   User user = new User();
   user.setId(rs.getInt("id"));
   user.setUsername(rs.getString("username"));
   user.setEmail(rs.getString("email"));
   user.setPassword(rs.getString("password"));
   user.setSalt(rs.getString("salt"));
   user.setIdentityVerified(rs.getBoolean("identity_verified"));
   user.setNickname(rs.getString("nickname"));
   user.setGender(rs.getString("gender"));
   user.setJob(rs.getString("job"));
   user.setPhone(rs.getString("phone"));
   user.setTelephone(rs.getString("telephone"));
   user.setAvatar(rs.getString("avatar"));
   user.setSignature(rs.getString("signature"));
   user.setSpeciality(rs.getString("speciality"));
   user.setPreference(rs.getString("preference"));
   user.setProvince(rs.getString("province"));
   user.setCity(rs.getString("city"));
   user.setDistrict(rs.getString("district"));
   user.setAddress(rs.getString("address"));
   user.setCreatedOn(rs.getTimestamp("created_on"));
   user.setEnable(rs.getBoolean("enable"));
   return user;
 }
Ejemplo n.º 4
1
 private void checkTokenLoad(String datadir) {
   System.err.println("Checking Token table load");
   SQLDB.loadFile(datadir + "loadTKTest", "Token");
   try {
     ResultSet t = SQLDB.queryDatabase("SELECT * FROM Token");
     t.last();
     assertTrue(t.getInt("TokenId") == 1);
     assertTrue(t.getInt("TokenType") == 1);
     assertTrue(t.getInt("SlotId") == 1);
     assertTrue(t.getInt("SlotIndex") == 1);
     assertTrue(t.getLong("PartialPlanId") == 1L);
     assertTrue(t.getBoolean("IsFreeToken"));
     assertTrue(t.getBoolean("IsValueToken"));
     assertTrue(t.getInt("StartVarId") == 1);
     assertTrue(t.getInt("EndVarId") == 1);
     assertTrue(t.getInt("DurationVarId") == 1);
     assertTrue(t.getInt("StateVarId") == 1);
     assertTrue(t.getString("PredicateName").equals("1"));
     assertTrue(t.getInt("ParentId") == 1);
     assertTrue(t.getString("ParentName").equals("1"));
     assertTrue(t.getInt("ObjectVarId") == 1);
     assertTrue(t.getString("ParamVarIds").equals("1"));
     assertTrue(t.getString("ExtraData").equals("1"));
   } catch (SQLException sqle) {
     sqle.printStackTrace();
     System.exit(-1);
   }
   SQLDB.updateDatabase("DELETE FROM Token");
 }
 public Funcionario findById(int id) {
   Connection conexao = new Conexao().getConexao();
   Funcionario funcionario = null;
   try {
     PreparedStatement preparedStatement =
         conexao.prepareStatement(
             "SELECT * FROM funcionarios INNER JOIN enderecos on (endereco_id = enderecos.id) WHERE id = ?;");
     preparedStatement.setInt(1, id);
     ResultSet resultSet = preparedStatement.executeQuery();
     resultSet.next();
     funcionario =
         new Funcionario(
             resultSet.getInt("id"),
             resultSet.getString("nome"),
             resultSet.getString("sobrenome"),
             resultSet.getString("cpf"),
             resultSet.getString("telefone"),
             new EnderecoDAO().findById(resultSet.getInt("endereco_id")),
             resultSet.getString("email"),
             resultSet.getDate("nascimento"),
             resultSet.getDouble("salario"),
             resultSet.getBoolean("gerente"),
             resultSet.getString("senha"),
             resultSet.getBoolean("status"),
             new Date(resultSet.getTimestamp("created").getTime()),
             new Date(resultSet.getTimestamp("edited").getTime()));
   } catch (SQLException ex) {
     Logger.getLogger(FuncionarioDAO.class.getName()).log(Level.SEVERE, null, ex);
   }
   return funcionario;
 }
Ejemplo n.º 6
1
 public void testBoolean() throws Throwable {
   // String crtab = "create table #testBigInt (a bigint)";
   String crtab = "create table #testBit (a BIT NULL)";
   dropTable("#testBit");
   Statement stmt = con.createStatement();
   stmt.executeUpdate(crtab);
   stmt.executeUpdate("insert into #testBit values (NULL)");
   stmt.executeUpdate("insert into #testBit values (0)");
   stmt.executeUpdate("insert into #testBit values (1)");
   ResultSet rs = stmt.executeQuery("select * from #testBit where a is NULL");
   rs.next();
   rs.getBoolean(1);
   rs = stmt.executeQuery("select * from #testBit where a  = 0");
   rs.next();
   rs.getBoolean(1);
   rs = stmt.executeQuery("select * from #testBit where a = 1");
   rs.next();
   rs.getBoolean(1);
   stmt.close();
   PreparedStatement pstmt = con.prepareStatement("insert into #testBit values (?)");
   pstmt.setBoolean(1, true);
   assertTrue(!pstmt.execute());
   assertTrue(pstmt.getUpdateCount() == 1);
   pstmt.setBoolean(1, false);
   assertTrue(!pstmt.execute());
   assertTrue(pstmt.getUpdateCount() == 1);
   pstmt.setNull(1, java.sql.Types.BIT);
   assertTrue(!pstmt.execute());
   assertTrue(pstmt.getUpdateCount() == 1);
   pstmt.close();
 }
  private void testPreparedSubquery(Connection conn) throws SQLException {
    Statement s = conn.createStatement();
    s.executeUpdate("CREATE TABLE TEST(ID IDENTITY, FLAG BIT)");
    s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(0, FALSE)");
    s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(1, FALSE)");
    PreparedStatement u = conn.prepareStatement("SELECT ID, FLAG FROM TEST ORDER BY ID");
    PreparedStatement p = conn.prepareStatement("UPDATE TEST SET FLAG=true WHERE ID=(SELECT ?)");
    p.clearParameters();
    p.setLong(1, 0);
    assertEquals(1, p.executeUpdate());
    p.clearParameters();
    p.setLong(1, 1);
    assertEquals(1, p.executeUpdate());
    ResultSet rs = u.executeQuery();
    assertTrue(rs.next());
    assertEquals(0, rs.getInt(1));
    assertTrue(rs.getBoolean(2));
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertTrue(rs.getBoolean(2));

    p = conn.prepareStatement("SELECT * FROM TEST WHERE EXISTS(SELECT * FROM TEST WHERE ID=?)");
    p.setInt(1, -1);
    rs = p.executeQuery();
    assertFalse(rs.next());
    p.setInt(1, 1);
    rs = p.executeQuery();
    assertTrue(rs.next());

    s.executeUpdate("DROP TABLE IF EXISTS TEST");
  }
 public ArrayList<Funcionario> list() {
   ArrayList<Funcionario> funcionarios = new ArrayList();
   Connection conexao = new Conexao().getConexao();
   try {
     PreparedStatement preparedStatement =
         conexao.prepareStatement("SELECT * FROM funcionarios ORDER BY id;");
     ResultSet resultSet = preparedStatement.executeQuery();
     while (resultSet.next()) {
       funcionarios.add(
           new Funcionario(
               resultSet.getInt("id"),
               resultSet.getString("nome"),
               resultSet.getString("sobrenome"),
               resultSet.getString("cpf"),
               resultSet.getString("telefone"),
               new EnderecoDAO().findById(resultSet.getInt("endereco_id")),
               resultSet.getString("email"),
               resultSet.getDate("nascimento"),
               resultSet.getDouble("salario"),
               resultSet.getBoolean("gerente"),
               resultSet.getString("senha"),
               resultSet.getBoolean("status"),
               new Date(resultSet.getTimestamp("created").getTime()),
               new Date(resultSet.getTimestamp("edited").getTime())));
     }
     preparedStatement.close();
     conexao.close();
   } catch (SQLException ex) {
     Logger.getLogger(FuncionarioDAO.class.getName()).log(Level.SEVERE, null, ex);
   }
   return funcionarios;
 }
Ejemplo n.º 9
0
 @Override
 public final boolean readFromRs(final ResultSet rs) throws SQLException {
   while (rs.next()) {
     Mail mail = new Mail();
     mail.setMailId(rs.getLong(1));
     mail.setPayMail(rs.getBoolean(2));
     mail.setRead(rs.getBoolean(3));
     mail.setSenderId(rs.getInt(4));
     mail.setReceiverId(rs.getInt(5));
     mail.setSubject(rs.getString(6));
     mail.setContent(rs.getString(7));
     try {
       mail.setAttachment(Item.readItem(rs.getString(8)));
     } catch (Exception e) {
       Log.error(Log.ERROR, "SendMailListData.readFromRs", "邮件反序列化失败!", e);
     }
     mail.setGold(rs.getInt(9));
     mail.setPayGold(rs.getInt(10));
     mail.setSendTime(rs.getTimestamp(11).getTime());
     mail.setLevel(rs.getByte(12));
     mail.setReceiverName(rs.getString(13));
     sendMailList.add(mail);
   }
   return true;
 }
  public PersonnelDetails getPersonnelDetails(String personnelID) {
    PersonnelDetails pd = new PersonnelDetails();
    PreparedStatement ps;
    ResultSet rs;
    if (dbc.connect()) {
      try {
        String sqlQuery = "select * from " + PERSONNEL_INFO + " where MaNV = '" + personnelID + "'";
        ps = dbc.getConnection().prepareStatement(sqlQuery);
        rs = ps.executeQuery();

        // retrieve data from db by PersonnelID
        while (rs.next()) {

          pd.setPersonnelID(rs.getString(1));
          pd.setlName(rs.getString(2));
          pd.setfName(rs.getString(3));
          pd.setNickName(rs.getString(4));

          pd.setStatusID(rs.getShort(5));
          pd.setKindID(rs.getShort(6));
          pd.setContractID(rs.getShort(7));
          pd.setPosID(rs.getShort(8));
          pd.setOfficeID(rs.getShort(9));

          pd.setEnterDate(rs.getDate(10));

          pd.setStartDate(rs.getDate(11));
          pd.setSex(rs.getBoolean(12));
          pd.setPhoneNum(rs.getString(13));
          pd.setCompEmail(rs.getString(14));
          pd.setEndTraining(rs.getDate(15));

          pd.setPic(rs.getBytes(16));
          pd.setBirth(rs.getDate(17));
          pd.setPlaceOB(rs.getString(18));
          pd.setRecentAdd(rs.getString(19));

          pd.setFolkID(rs.getShort(20));
          pd.setReligionID(rs.getShort(21));
          pd.setNationID(rs.getShort(22));
          pd.setPersonalEmail(rs.getString(23));
          pd.setMariage(rs.getBoolean(24));
          pd.setEducation(rs.getString(25));
          pd.setAdd(rs.getString(26));
          pd.setIDNum(rs.getString(27));
          pd.setIDDay(rs.getDate(28));
          pd.setRegionID(rs.getShort(29));
          //                    System.out.println(rs.getShort(29));
          pd.setBankCode(rs.getString(30));
          pd.setBankID(rs.getShort(31));
          pd.setIDPlace(rs.getString(32));
          pd.setTaxCode(rs.getString(33));
        }
        dbc.close(ps, rs);
      } catch (SQLException ex) {
        Logger.getLogger(PersonnelDAO.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
    return pd;
  }
 @Override
 public FloatingRateData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum)
     throws SQLException {
   final Long id = rs.getLong("id");
   final String name = rs.getString("name");
   final boolean isBaseLendingRate = rs.getBoolean("isBaseLendingRate");
   final boolean isActive = rs.getBoolean("isActive");
   final String createdBy = rs.getString("createdBy");
   final LocalDate createdOn = JdbcSupport.getLocalDate(rs, "createdOn");
   final String modifiedBy = rs.getString("modifiedBy");
   final LocalDate modifiedOn = JdbcSupport.getLocalDate(rs, "modifiedOn");
   List<FloatingRatePeriodData> ratePeriods = null;
   if (addRatePeriods) {
     FloatingRatePeriodRowMapper ratePeriodMapper = new FloatingRatePeriodRowMapper();
     final String sql =
         "select "
             + ratePeriodMapper.schema()
             + " where period.is_active = 1 and period.floating_rates_id = ? "
             + " order by period.from_date desc ";
     ratePeriods = jdbcTemplate.query(sql, ratePeriodMapper, new Object[] {id});
   }
   return new FloatingRateData(
       id,
       name,
       isBaseLendingRate,
       isActive,
       createdBy,
       createdOn,
       modifiedBy,
       modifiedOn,
       ratePeriods,
       null);
 }
Ejemplo n.º 12
0
  public List<Artefato> readAll(String logado) throws SQLException {
    int i = 0;
    String loga = logado;
    ResultSet rs;
    String sql = "SELECT * FROM ARTEFATO";

    PreparedStatement stm = dataSource.getConnection().prepareStatement(sql);

    stm.setString(1, loga);

    System.out.println(stm.toString());

    rs = stm.executeQuery();
    LinkedList<Artefato> lista = new LinkedList<Artefato>();

    while (rs.next()) {
      Artefato tmp = new Artefato();
      tmp.setAprovado(rs.getBoolean("aprovado"));
      tmp.setBloqueado(rs.getBoolean("bloqueado"));
      tmp.setConteudo(rs.getString("conteudo"));
      tmp.setData_aprovacao(rs.getString("data_aprovacao"));
      tmp.setData_criacao(rs.getString("data_criacao"));
      tmp.setIdAprovador(rs.getInt("idAprovador"));
      tmp.setIdArtefato(rs.getInt("idArtefato"));
      tmp.setIdAutor(rs.getInt("idAutor"));
      tmp.setIdCategoria(rs.getInt("idCategoria"));
      tmp.setTags(rs.getString("tags"));
      tmp.setTipo(rs.getInt("tipo"));
      tmp.setTitulo(rs.getString("titulo"));
      tmp.setVersao(rs.getFloat("versao"));

      lista.add(tmp);
    }
    return lista;
  }
Ejemplo n.º 13
0
  /* consulta no BD */
  @Override
  public Object read(Object key) throws SQLException {
    String nome = (String) key;

    String sql = "SELECT * FROM ARTEFATO WHERE idArtefato=?";
    PreparedStatement stm = dataSource.getConnection().prepareStatement(sql);
    stm.setString(1, nome);
    System.out.println(nome);
    ResultSet rs = stm.executeQuery();

    if (rs.next()) {
      Artefato tmp = new Artefato();
      tmp.setAprovado(rs.getBoolean("aprovado"));
      tmp.setBloqueado(rs.getBoolean("bloqueado"));
      tmp.setConteudo(rs.getString("conteudo"));
      tmp.setData_aprovacao(rs.getString("data_aprovacao"));
      tmp.setData_criacao(rs.getString("data_criacao"));
      tmp.setIdAprovador(rs.getInt("idAprovador"));
      tmp.setIdArtefato(rs.getInt("idArtefato"));
      tmp.setIdAutor(rs.getInt("idAutor"));
      tmp.setIdCategoria(rs.getInt("idCategoria"));
      tmp.setTags(rs.getString("tags"));
      tmp.setTipo(rs.getInt("tipo"));
      tmp.setTitulo(rs.getString("titulo"));
      tmp.setVersao(rs.getFloat("versao"));
      return tmp;
    }

    return null;
  }
Ejemplo n.º 14
0
 public String getStatuses() {
   ResultSet rs = null;
   try (CallableStatement cs = conn.prepareCall("{call ti_getStatuses}")) {
     rs = cs.executeQuery();
     if (rs != null) {
       ArrayList<Status> statuses = new ArrayList<>();
       while (rs.next()) {
         // (int status_id, String displayName, boolean isSystem, boolean isNotify, boolean
         // isClosed, String notifyMsg)
         statuses.add(
             new Status(
                 rs.getInt("status_id"),
                 rs.getString("display_name"),
                 rs.getBoolean("isSystem"),
                 rs.getBoolean("isNotify"),
                 rs.getBoolean("isClosed"),
                 rs.getString("notify_msg")));
       }
       StatusList statusList = new StatusList(statuses);
       return generateGson(statusList);
     }
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return "";
 }
Ejemplo n.º 15
0
  /**
   * Gets a fireman with a given ID
   *
   * @param ID the id
   * @return the fireman witht he given id
   * @throws SQLException if an error has occured executing the sql query
   */
  public Fireman getFiremanByID(int ID)
      throws SQLException { // gets Information on fireman on the ID
    Connection con = null;
    Fireman fireman = null;

    try {
      con = getConnection();

      Statement query = con.createStatement();
      ResultSet result = query.executeQuery("SELECT * FROM Fireman WHERE employeeId = " + ID + ";");
      if (result.next()) {
        int userId = result.getInt("employeeId");
        String firstName = result.getString("firstName");
        String lastName = result.getString("lastName");
        boolean teamleader = result.getBoolean("teamleader");
        boolean driver = result.getBoolean("driver");

        fireman = new Fireman(userId, firstName, lastName, teamleader, driver);
      }

    } finally {
      if (con != null) {
        con.close();
      }
    }
    return fireman;
  }
  /**
   * Return Order statuses of Reports
   *
   * @ejb.interface-method
   * @ejb.transaction type="Supports"
   */
  public List findOrderStates() throws LocalException {

    OrderStatesValue object = null;

    String sql =
        "select ID,ORDER_STATES,ICC_OR_UP_ISENABLED,MNR_ISENABLED from Order_States_For_Reports";
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet result = null;
    List l = new ArrayList();
    try {
      con = getConnection();
      ps = con.prepareStatement(sql);
      result = ps.executeQuery();
      while (result.next()) {
        object = new OrderStatesValue();
        object.setId(result.getInt(1));
        object.setOrder_states((result.getString(2)));
        object.setIcc_or_up_iseditable(result.getBoolean(3));
        object.setMnr_iseditable((result.getBoolean(4)));
        l.add(object);
      }

    } catch (Exception e) {
      // getLog().warn("####EX:" + e.getMessage());
      throw new LocalException(e);

    } finally {
      closeQuietly(result, ps, con);
    }
    return l;
  }
Ejemplo n.º 17
0
 @Override
 public CommandEntity mapRow(ResultSet resultSet, int rowNum) throws SQLException {
   CommandEntity result = new CommandEntity();
   result.setUserId(Guid.createGuidFromString(resultSet.getString("user_id")));
   result.setId(Guid.createGuidFromString(resultSet.getString("command_id")));
   result.setJobId(Guid.createGuidFromString(resultSet.getString("job_id")));
   result.setStepId(Guid.createGuidFromString(resultSet.getString("step_id")));
   result.setCreatedAt(DbFacadeUtils.fromDate(resultSet.getTimestamp("created_at")));
   result.setCommandType(VdcActionType.forValue(resultSet.getInt("command_type")));
   result.setParentCommandId(
       Guid.createGuidFromString(resultSet.getString("parent_command_id")));
   result.setRootCommandId(
       Guid.createGuidFromString(resultSet.getString("root_command_id")));
   result.setCommandParameters(
       deserializeParameters(
           resultSet.getString("command_parameters"),
           resultSet.getString("command_params_class")));
   result.setReturnValue(
       deserializeReturnValue(
           resultSet.getString("return_value"), resultSet.getString("return_value_class")));
   result.setCommandStatus(getCommandStatus(resultSet.getString("status")));
   result.setExecuted(resultSet.getBoolean("executed"));
   result.setCallbackEnabled(resultSet.getBoolean("callback_enabled"));
   result.setCallbackNotified(resultSet.getBoolean("callback_notified"));
   return result;
 }
  /**
   * @ejb.interface-method
   * @ejb.transaction type="Supports"
   */
  public OrderStatesValue findByPrimaryKey(PrimaryKey primaryKey) throws LocalException {
    OrderStatesPK opk = (OrderStatesPK) primaryKey;

    int id = opk.getId();
    OrderStatesValue object = null;

    String sql =
        "select ID,ORDER_STATES,ICC_OR_UP_ISENABLED,MNR_ISENABLED from Order_States_For_Reports where ID=? ";
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet result = null;

    try {
      con = getConnection();
      ps = con.prepareStatement(sql);
      ps.setInt(1, id);
      result = ps.executeQuery();
      if (result.next()) {
        object = new OrderStatesValue();
        object.setId(result.getInt(1));
        object.setOrder_states(result.getString(2));
        object.setIcc_or_up_iseditable(result.getBoolean(3));
        object.setMnr_iseditable(result.getBoolean(4));
      }

    } catch (Exception e) {
      // getLog().warn("####EX:" + e.getMessage());
      throw new LocalException(e);

    } finally {
      closeQuietly(result, ps, con);
    }
    return object;
  }
Ejemplo n.º 19
0
 @Override
 public Subscriber getSubByUserId(int userId) throws SQLException {
   Connection connection = null;
   String sql = "select * from subscribers where userId = " + userId;
   try {
     connection = MysqlDaoFactory.getConnection();
     Statement stmt = connection.createStatement();
     ResultSet resSet = stmt.executeQuery(sql);
     if (null != resSet && resSet.next()) {
       Subscriber sub = new Subscriber();
       sub.setId(resSet.getInt(1));
       sub.setUserId(userId);
       sub.setScore(resSet.getFloat(3));
       sub.setFname(resSet.getString(4));
       sub.setLname(resSet.getString(5));
       sub.setMname(resSet.getString(6));
       sub.setAddress(resSet.getString(7));
       sub.setBlocked(resSet.getBoolean(8));
       sub.setDisabled(resSet.getBoolean(9));
       return sub;
     }
   } catch (SQLException e) {
     throw new EJBException("Select failed due " + "SQLException: " + e.getMessage());
   } finally {
     if (connection != null) {
       MysqlDaoFactory.putBackConnection(connection);
     }
   }
   return null;
 }
Ejemplo n.º 20
0
    @Override
    public UserData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum)
        throws SQLException {

      final Long id = rs.getLong("id");
      final String name = rs.getString("name");
      final String email = rs.getString("email");
      final String phoneNum = rs.getString("phone_num");
      final String imageUrl = rs.getString("image_url");
      final Boolean isActive = rs.getBoolean("is_active");
      final Boolean isPasswordChangeNeeded =
          rs.getBoolean("is_password_change_needed_on_next_login");
      final Boolean isPublicUser = rs.getBoolean("is_public_user");

      final Collection<RoleData> roles = this.roleReadService.retrieveUserRoles(id);

      return UserData.createNew(
          id,
          name,
          email,
          phoneNum,
          imageUrl,
          isActive,
          isPasswordChangeNeeded,
          isPublicUser,
          roles);
    }
Ejemplo n.º 21
0
 /**
  * Creates a list of ContactListItem from a result set
  *
  * @param rs - result set from getAllContactsSQL
  */
 public static List<ContactListItem> processResults(ResultSet rs) {
   List<ContactListItem> contacts = new ArrayList<ContactListItem>();
   try {
     while (rs.next()) {
       ContactListItem contactListItem = new ContactListItem();
       contactListItem.setName(rs.getString("name"));
       contactListItem.setUserId(rs.getString("user_id"));
       contactListItem.setTitle(rs.getString("title"));
       contactListItem.setMemberId(rs.getLong("member_id"));
       contactListItem.setCompany(rs.getString("company_name"));
       contactListItem.setJoinedOn(rs.getDate("joined_on"));
       contactListItem.setLastVisited(rs.getDate("last_visited"));
       contactListItem.setLastAttended(rs.getDate("last_attended"));
       contactListItem.setTotalRSVP(rs.getLong("total_rsvp"));
       contactListItem.setRSVPYes(rs.getLong("rsvp_yes"));
       contactListItem.setRSVPMaybe(rs.getLong("rsvp_maybe"));
       contactListItem.setRSVPNo(rs.getLong("rsvp_no"));
       contactListItem.setAttended(rs.getLong("attended"));
       contactListItem.setNoShow(rs.getLong("no_show"));
       contactListItem.setIntro(rs.getBoolean("intro"));
       contactListItem.setPhoto(rs.getBoolean("photo"));
       contactListItem.setAssistant(rs.getBoolean("assistant_org"));
       contactListItem.setMailingList(rs.getString("mailing_list"));
       contactListItem.setUrl(rs.getString("url"));
       contacts.add(contactListItem);
     }
   } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   }
   return contacts;
 }
Ejemplo n.º 22
0
  @Test
  public void testBooleanString() throws SQLException {
    Statement stmt = sharedConnection.createStatement();
    stmt.execute("insert into booleanvalue values(true)");
    stmt.execute("insert into booleanvalue values(false)");
    stmt.execute("insert into booleanvalue values(4)");
    ResultSet rs = stmt.executeQuery("select * from booleanvalue");

    if (rs.next()) {
      assertTrue(rs.getBoolean(1));
      assertEquals("1", rs.getString(1));
      if (rs.next()) {
        assertFalse(rs.getBoolean(1));
        assertEquals("0", rs.getString(1));
        if (rs.next()) {
          assertTrue(rs.getBoolean(1));
          assertEquals("4", rs.getString(1));
        } else {
          fail("must have a result !");
        }
      } else {
        fail("must have a result !");
      }
    } else {
      fail("must have a result !");
    }
  }
    @Override
    public VmTemplate mapRow(ResultSet rs, int rowNum) throws SQLException {
      final VmTemplate entity = new VmTemplate();
      map(rs, entity);

      entity.setId(getGuidDefaultEmpty(rs, "vmt_guid"));
      entity.setChildCount(rs.getInt("child_count"));
      entity.setName(rs.getString("name"));
      entity.setVdsGroupId(getGuid(rs, "vds_group_id"));
      entity.setAllowConsoleReconnect(rs.getBoolean("allow_console_reconnect"));
      entity.setStatus(VmTemplateStatus.forValue(rs.getInt("status")));
      entity.setVdsGroupName(rs.getString("vds_group_name"));
      entity.setStoragePoolId(getGuid(rs, "storage_pool_id"));
      entity.setStoragePoolName(rs.getString("storage_pool_name"));
      entity.setQuotaName(rs.getString("quota_name"));
      entity.setQuotaEnforcementType(
          QuotaEnforcementTypeEnum.forValue(rs.getInt("quota_enforcement_type")));
      entity.setDisabled(rs.getBoolean("is_disabled"));
      entity.setTrustedService(rs.getBoolean("trusted_service"));
      entity.setTemplateType(VmEntityType.valueOf(rs.getString("entity_type")));
      entity.setClusterArch(ArchitectureType.forValue(rs.getInt("architecture")));
      entity.setBaseTemplateId(getGuidDefaultEmpty(rs, "base_template_id"));
      entity.setTemplateVersionNumber(rs.getInt("template_version_number"));
      entity.setTemplateVersionName(rs.getString("template_version_name"));
      return entity;
    }
Ejemplo n.º 24
0
 public TypeInfo(ResultSet rs) throws SQLException {
   typeName = rs.getString(1);
   dataType = rs.getInt(2);
   precision = rs.getInt(3);
   literalPrefix = rs.getString(4);
   literalSuffix = rs.getString(5);
   createParams = rs.getString(6);
   nullable = rs.getShort(7);
   caseSensitive = rs.getBoolean(8);
   searchable = rs.getShort(9);
   unsigned = rs.getBoolean(10);
   fixedPrecScale = rs.getBoolean(11);
   autoIncrement = rs.getBoolean(12);
   localTypeName = rs.getString(13);
   if (rs.getMetaData().getColumnCount() >= 18) {
     // Some servers provide more information
     minimumScale = rs.getShort(14);
     maximumScale = rs.getShort(15);
     sqlDataType = rs.getInt(16);
     sqlDatetimeSub = rs.getInt(17);
     numPrecRadix = rs.getInt(18);
   } else {
     // Must initialize final fields
     minimumScale = 0;
     maximumScale = 0;
     sqlDataType = 0;
     sqlDatetimeSub = 0;
     numPrecRadix = 0;
   }
   normalizedType = normalizeDataType(dataType);
   distanceFromJdbcType = determineDistanceFromJdbcType();
 }
Ejemplo n.º 25
0
  @Override
  public int read(Object pk, java.sql.Statement statement) {
    int[] key = (int[]) pk;
    String sql = "";
    java.sql.ResultSet resultSet = null;

    mnLastDbActionResult = SLibConstants.UNDEFINED;
    reset();

    try {
      sql =
          "SELECT d.*, un.usr, ue.usr, ud.usr "
              + "FROM trn_dsm_nts AS d "
              + "INNER JOIN erp.usru_usr AS un ON "
              + "d.fid_usr_new = un.id_usr "
              + "INNER JOIN erp.usru_usr AS ue ON "
              + "d.fid_usr_edit = ue.id_usr "
              + "INNER JOIN erp.usru_usr AS ud ON "
              + "d.fid_usr_del = ud.id_usr "
              + "WHERE id_year = "
              + key[0]
              + " AND id_doc = "
              + key[1]
              + " AND id_nts = "
              + key[2]
              + " ";
      resultSet = statement.executeQuery(sql);
      if (!resultSet.next()) {
        throw new Exception(SLibConstants.MSG_ERR_REG_FOUND_NOT);
      } else {
        mnPkYearId = resultSet.getInt("d.id_year");
        mnPkDocId = resultSet.getInt("d.id_doc");
        mnPkNotesId = resultSet.getInt("d.id_nts");
        msNotes = resultSet.getString("d.nts");
        mbIsPrintable = resultSet.getBoolean("d.b_prt");
        mbIsDeleted = resultSet.getBoolean("d.b_del");
        mnFkUserNewId = resultSet.getInt("d.fid_usr_new");
        mnFkUserEditId = resultSet.getInt("d.fid_usr_edit");
        mnFkUserDeleteId = resultSet.getInt("d.fid_usr_del");
        mtUserNewTs = resultSet.getTimestamp("d.ts_new");
        mtUserEditTs = resultSet.getTimestamp("d.ts_edit");
        mtUserDeleteTs = resultSet.getTimestamp("d.ts_del");

        msDbmsUserNew = resultSet.getString("un.usr");
        msDbmsUserEdit = resultSet.getString("ue.usr");
        msDbmsUserDelete = resultSet.getString("ud.usr");

        mbIsRegistryNew = false;
        mnLastDbActionResult = SLibConstants.DB_ACTION_READ_OK;
      }
    } catch (java.sql.SQLException e) {
      mnLastDbActionResult = SLibConstants.DB_ACTION_READ_ERROR;
      SLibUtilities.printOutException(this, e);
    } catch (java.lang.Exception e) {
      mnLastDbActionResult = SLibConstants.DB_ACTION_READ_ERROR;
      SLibUtilities.printOutException(this, e);
    }

    return mnLastDbActionResult;
  }
Ejemplo n.º 26
0
  public List<Filter> queryAllFilters() {
    List<Filter> filterList = new ArrayList<Filter>();
    PreparedStatement pstm = null;
    Connection conn = null;
    ResultSet rs = null;
    try {
      conn = DbPoolConnection.getInstance().getReadConnection();
      pstm = conn.prepareStatement("SELECT * FROM filter WHERE is_valid = true");
      rs = pstm.executeQuery();
      while (rs.next()) {
        UUID id = DataAccessFactory.getInstance().createUUID(Long.toString(rs.getLong("id")));
        Timestamp createTime = rs.getTimestamp("create_time");
        String createUser = rs.getString("create_user");
        Filter filter = new FilterImpl(id, createUser, createTime, null);
        filter.setName(rs.getString("name"));
        filter.setXml(rs.getString("xml"));
        filter.setAnd(rs.getBoolean("is_and"));
        filter.setPublic(rs.getBoolean("is_public"));
        filter.setVisible(rs.getBoolean("is_visible"));
        filterList.add(filter);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      DbPoolConnection.getInstance().closeResultSet(rs);
      DbPoolConnection.getInstance().closeStatment(pstm);
      DbPoolConnection.getInstance().closeConn(conn);
    }

    return filterList;
  }
Ejemplo n.º 27
0
  public static ArrayList getActions(int id) {
    try {
      ArrayList<String> arraylist = new ArrayList<String>();
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet actions;

      actions = s.executeQuery("select * from actions where project_id = " + id);

      if (actions.next()) {
        actions.first();
        arraylist.add(
            actions.getInt("id")
                + ", "
                + actions.getString("description")
                + ", "
                + actions.getString("notes")
                + ", "
                + actions.getInt("status_id")
                + ", "
                + actions.getInt("contexts_id")
                + ", "
                + actions.getInt("project_id")
                + ", "
                + actions.getString("action_date")
                + ", "
                + actions.getString("statuschange_date")
                + ", "
                + actions.getBoolean("done"));

        while (actions.next()) {
          arraylist.add(
              actions.getInt("id")
                  + ", "
                  + actions.getString("description")
                  + ", "
                  + actions.getString("notes")
                  + ", "
                  + actions.getInt("status_id")
                  + ", "
                  + actions.getInt("contexts_id")
                  + ", "
                  + actions.getInt("project_id")
                  + ", "
                  + actions.getString("action_date")
                  + ", "
                  + actions.getString("statuschange_date")
                  + ", "
                  + actions.getBoolean("done"));
        }
      }
      return arraylist;
    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;
  }
Ejemplo n.º 28
0
 public Collection<Cuota> getCuotasAlumnoInscripcion(long idAlumno, long idInscripcion) {
   ResultSet resu = null;
   Statement stmte = null;
   Connection conne = null;
   try {
     conne = super.getConection();
     stmte = conne.createStatement();
     resu =
         stmte.executeQuery(
             "SELECT cuotas_id, periodo, alumnos_id, inscripciones_id, monto, pagado, facturas_serie, facturas_numero, cuota, productos_id FROM cuotas "
                 + "where pagado = false "
                 + "AND alumnos_id = '"
                 + idAlumno
                 + "' "
                 + "AND inscripciones_id = '"
                 + idInscripcion
                 + "'");
   } catch (SQLException ex) {
     while (ex != null) {
       ex.printStackTrace();
       ex = ex.getNextException();
     }
   }
   Collection<Cuota> co = new ArrayList<Cuota>();
   try {
     while (resu.next()) {
       Cuota c = new Cuota();
       c.setId(resu.getLong(1));
       c.setPeriodo(resu.getTimestamp(2));
       c.setIdAlumno(resu.getLong(3));
       c.setInscripcion(resu.getLong(4));
       c.setMonto(resu.getDouble(5));
       c.setPagado(resu.getBoolean(6));
       c.setSerieFactura(resu.getInt(7));
       c.setNumeroFactura(resu.getLong(8));
       c.setCuota(resu.getBoolean(9));
       c.setPago(this.getPago(c.getId()));
       c.setProducto(this.getProducto(resu.getInt(10)));
       co.add(c);
     }
   } catch (SQLException ex) {
     while (ex != null) {
       ex.printStackTrace();
       ex = ex.getNextException();
     }
   }
   try {
     resu.close();
     stmte.close();
     conne.close();
   } catch (SQLException e) {
     while (e != null) {
       e.printStackTrace();
       e = e.getNextException();
     }
   }
   return co;
 }
 public Pipeline mapRow(ResultSet rs, int rowNum) throws SQLException {
   return new Pipeline(
       rs.getInt("id"),
       blobToString(rs.getBlob("label")),
       blobToString(rs.getBlob("description")),
       rs.getBoolean("isDefault"),
       rs.getBoolean("isLocked"),
       rs.getInt("authorId"),
       blobToString(rs.getBlob("username")));
 }
Ejemplo n.º 30
0
 private void loadWeaponSkills(HashMap<Integer, L1WeaponSkill> weaponSkills) {
   Connection con = null;
   PreparedStatement pstm = null;
   ResultSet rs = null;
   try {
     PerformanceTimer timer = new PerformanceTimer();
     con = L1DatabaseFactory.getInstance().getConnection();
     pstm = con.prepareStatement("SELECT * FROM weapon_skills");
     rs = pstm.executeQuery();
     while (rs.next()) {
       int weaponId = rs.getInt("item_id");
       int skillId = rs.getInt("skill_id");
       boolean isErr = false;
       if (ItemTable.getInstance().getTemplate(weaponId) == null) {
         System.out.println(String.format(I18N_DOES_NOT_EXIST_ITEM_LIST, weaponId));
         // %s はアイテムリストに存在しません。
         isErr = true;
       }
       if (SkillTable.getInstance().findBySkillId(skillId) == null) {
         System.out.println(String.format(I18N_DOES_NOT_EXIST_SKILL_LIST, skillId));
         // %s はスキルリストに存在しません。
         isErr = true;
       }
       if (isErr) {
         continue;
       }
       int probability = rs.getInt("probability");
       int probEnchant = rs.getInt("prob_enchant");
       int fixDamage = rs.getInt("fix_damage");
       int randomDamage = rs.getInt("random_damage");
       boolean isArrowType = rs.getBoolean("arrow_type");
       boolean enableMr = rs.getBoolean("enable_mr");
       boolean enableAttrMr = rs.getBoolean("enable_attr_mr");
       L1WeaponSkill weaponSkill =
           new L1WeaponSkill(
               weaponId,
               probability,
               probEnchant,
               fixDamage,
               randomDamage,
               skillId,
               isArrowType,
               enableMr,
               enableAttrMr);
       weaponSkills.put(weaponId, weaponSkill);
     }
     _log.fine("Loaded weapon skill: " + weaponSkills.size() + "records");
     System.out.println("loading weapon skills...OK! " + timer.elapsedTimeMillis() + "ms");
   } catch (SQLException e) {
     _log.log(Level.SEVERE, "error while creating weapon_skills table", e);
   } finally {
     SqlUtil.close(rs, pstm, con);
   }
 }