/** * 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"); }
@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; }
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; }
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; }
@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); }
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; }
/* 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; }
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 ""; }
/** * 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; }
@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; }
@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; }
@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); }
/** * 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; }
@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; }
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(); }
@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; }
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; }
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; }
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"))); }
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); } }