List of usage examples for java.sql ResultSet getDate
java.sql.Date getDate(String columnLabel) throws SQLException;
ResultSet object as a java.sql.Date object in the Java programming language. From source file:com.chiorichan.database.DatabaseEngine.java
public static Map<String, Object> convertRow(ResultSet rs) throws SQLException { Map<String, Object> result = Maps.newLinkedHashMap(); ResultSetMetaData rsmd = rs.getMetaData(); int numColumns = rsmd.getColumnCount(); for (int i = 1; i < numColumns + 1; i++) { String columnName = rsmd.getColumnName(i); // Loader.getLogger().info( "Column: " + columnName + " <-> " + rsmd.getColumnTypeName( i ) ); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { result.put(columnName, rs.getArray(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIT) // Sometimes tinyints are read as bits {// w w w .java2 s .c o m result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { result.put(columnName, rs.getBoolean(columnName)); } else if (rsmd.getColumnTypeName(i).contains("BLOB") || rsmd.getColumnType(i) == java.sql.Types.BINARY) { // BLOG = Max Length 65,535. Recommended that you use a LONGBLOG. byte[] bytes = rs.getBytes(columnName); result.put(columnName, bytes); /* * try * { * result.put( columnName, new String( bytes, "ISO-8859-1" ) ); * } * catch ( UnsupportedEncodingException e ) * { * e.printStackTrace(); * } */ } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { result.put(columnName, rs.getDouble(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { result.put(columnName, rs.getFloat(columnName)); } else if (rsmd.getColumnTypeName(i).equals("INT")) { result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { result.put(columnName, rs.getNString(columnName)); } else if (rsmd.getColumnTypeName(i).equals("VARCHAR")) { result.put(columnName, rs.getString(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { result.put(columnName, rs.getInt(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { result.put(columnName, rs.getDate(columnName)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { result.put(columnName, rs.getTimestamp(columnName)); } else { result.put(columnName, rs.getObject(columnName)); } } return result; }
From source file:com.krawler.esp.servlets.AdminServlet.java
public static String getCompanySubscriptionDetails(Connection conn, HttpServletRequest request, String companyid) throws ServiceException { PreparedStatement pstmt = null; String res = null;//from w ww . j a va 2 s .c o m try { KWLJsonConverter KWL = new KWLJsonConverter(); JSONObject resObj = new JSONObject(); boolean flg = false; pstmt = conn.prepareStatement("SELECT featurelist.featureid, featurename, subscriptiondate, expdate " + "FROM companysubscription INNER JOIN featurelist ON companysubscription.featureid=featurelist.featureid " + "WHERE companyid = ?"); pstmt.setString(1, companyid); ResultSet rs = pstmt.executeQuery(); pstmt = conn.prepareStatement("SELECT * FROM featurelist"); ResultSet rs1 = pstmt.executeQuery(); while (rs1.next()) { JSONObject temp = new JSONObject(); int fid = rs1.getInt("featureid"); while (rs.next()) { if (fid == rs.getInt("featureid")) { temp.put("featureid", fid); temp.put("subscriptiondate", rs.getDate("subscriptiondate")); temp.put("featurename", rs.getString("featurename")); temp.put("expdate", rs.getDate("expdate")); temp.put("subscribed", true); flg = true; break; } } if (!flg) { temp.put("featureid", fid); // temp.put("subscriptiondate", rs.getDate("subscriptiondate")); temp.put("featurename", rs1.getString("featurename")); // temp.put("expdate", rs.getDate("expdate")); temp.put("subscribed", false); } rs.first(); flg = false; resObj.append("data", temp); } res = resObj.toString(); } catch (SQLException e) { res = KWLErrorMsgs.rsSuccessFalse; // System.out.print("dfg"); } catch (JSONException ex) { res = KWLErrorMsgs.rsSuccessFalse; } return res; }
From source file:com.sfs.whichdoctor.dao.RotationDAOImpl.java
/** * Load the RotationBean data./*from w w w. ja v a 2 s. com*/ * * @param rs the result set * @param loadDetails the load details * * @return the rotation bean * * @throws SQLException the SQL exception */ private RotationBean loadRotation(final ResultSet rs, final BuilderBean loadDetails) throws SQLException { RotationBean rotation = new RotationBean(); rotation.setId(rs.getInt("RotationId")); rotation.setGUID(rs.getInt("GUID")); rotation.setDescription(rs.getString("Description")); rotation.setRotationType(rs.getString("Type")); rotation.setRotationTypeAbbreviation(rs.getString("TypeAbbreviation")); rotation.setTrainingClass(rs.getString("TrainingClass")); rotation.setTrainingType(rs.getString("TrainingType")); rotation.setTrainingMapping(rs.getString("TrainingMapping")); rotation.setOrganisation1Name(rs.getString("OtherOrganisation1Name")); rotation.setOrganisation2Name(rs.getString("OtherOrganisation2Name")); rotation.setOrganisation1Type(rs.getString("Organisation1Type")); rotation.setOrganisation1TypeMapping(rs.getString("Organisation1TypeMapping")); rotation.setOrganisation2Type(rs.getString("Organisation2Type")); rotation.setOrganisation2TypeMapping(rs.getString("Organisation2TypeMapping")); rotation.setYear(rs.getInt("RotationYear")); try { rotation.setStartDate(rs.getDate("StartDate")); } catch (SQLException sqe) { dataLogger.debug("Error loading StartDate: " + sqe.getMessage()); } try { rotation.setEndDate(rs.getDate("EndDate")); } catch (SQLException sqe) { dataLogger.debug("Error loading StartDate: " + sqe.getMessage()); } rotation.setLeaveDays(rs.getInt("LeaveDays")); rotation.setTrainingTime(rs.getDouble("TrainingTime")); rotation.setCommitteeName(rs.getString("CommitteeName")); // Load the supervisors for this rotation try { rotation.setSupervisors(loadSupervisors(rotation.getGUID(), loadDetails)); } catch (WhichDoctorDaoException wde) { dataLogger.error("Error loading supervisors: " + wde.getMessage()); } rotation.setPersonId(rs.getInt("PersonId")); if (rotation.getPersonId() > 0) { rotation.setPerson(loadPerson(rotation.getPersonId(), loadDetails, rs)); } rotation.setOrganisation1Id(rs.getInt("Organisation1Id")); if (rotation.getOrganisation1Id() > 0) { OrganisationBean organisation = new OrganisationBean(); organisation.setId(rotation.getOrganisation1Id()); organisation.setName(rs.getString("Organisation1Name")); organisation.setGUID(rs.getInt("Organisation1GUID")); rotation.setOrganisation1(organisation); } rotation.setOrganisation2Id(rs.getInt("Organisation2Id")); if (rotation.getOrganisation2Id() > 0) { OrganisationBean organisation = new OrganisationBean(); organisation.setId(rotation.getOrganisation2Id()); organisation.setName(rs.getString("Organisation2Name")); organisation.setGUID(rs.getInt("Organisation2GUID")); rotation.setOrganisation2(organisation); } try { rotation.setReports(reportDAO.load(rotation.getGUID(), loadDetails.getBoolean("REPORTS_FULL"))); } catch (WhichDoctorDaoException wde) { dataLogger.error("Error loading reports: " + wde.getMessage()); } if (loadDetails.getBoolean("ASSESSMENTS")) { try { rotation.setAssessment(this.assessmentDAO.load(rotation.getGUID())); } catch (Exception e) { dataLogger.error("Error loading assessment (" + rotation.getGUID() + "): " + e.getMessage()); } } rotation.setActive(rs.getBoolean("Active")); if (loadDetails.getBoolean("HISTORY")) { try { rotation.setCreatedDate(rs.getTimestamp("CreatedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading CreatedDate: " + sqe.getMessage()); } rotation.setCreatedBy(rs.getString("CreatedBy")); try { rotation.setModifiedDate(rs.getTimestamp("ModifiedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ModifiedDate: " + sqe.getMessage()); } rotation.setModifiedBy(rs.getString("ModifiedBy")); try { rotation.setExportedDate(rs.getTimestamp("ExportedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ExportedDate: " + sqe.getMessage()); } rotation.setExportedBy(rs.getString("ExportedBy")); } if (loadDetails.getBoolean("ACCREDITATIONS")) { try { rotation.setAccreditation(this.accreditationDAO.load(rotation.getGUID(), loadDetails.getBoolean("ACCREDITATION_FULL"))); } catch (Exception e) { dataLogger.error( "Error loading accreditation details (" + rotation.getGUID() + "): " + e.getMessage()); } } if (loadDetails.getBoolean("ONLINETOOLS")) { try { rotation.setOnlineTools(this.onlineToolDAO.loadRotation(rotation.getGUID())); } catch (Exception e) { dataLogger.error("Error loading online tools for rotation: " + e.getMessage()); } } if (loadDetails.getBoolean("TAGS")) { try { rotation.setTags(this.getTagDAO().load(rotation.getGUID(), loadDetails.getString("USERDN"), true)); } catch (Exception e) { dataLogger.error("Error loading tags for rotation: " + e.getMessage()); } } if (loadDetails.getBoolean("MEMO")) { try { rotation.setMemo(this.getMemoDAO().load(rotation.getGUID(), loadDetails.getBoolean("MEMO_FULL"))); } catch (Exception e) { dataLogger.error("Error loading memos for rotation: " + e.getMessage()); } } if (loadDetails.getBoolean("GROUPS")) { rotation.setGroups(loadGroups(rotation.getGUID())); } if (loadDetails.getBoolean("CREATED")) { UserBean user = new UserBean(); user.setDN(rs.getString("CreatedBy")); user.setPreferredName(rs.getString("CreatedFirstName")); user.setLastName(rs.getString("CreatedLastName")); rotation.setCreatedUser(user); } if (loadDetails.getBoolean("MODIFIED")) { UserBean user = new UserBean(); user.setDN(rs.getString("ModifiedBy")); user.setPreferredName(rs.getString("ModifiedFirstName")); user.setLastName(rs.getString("ModifiedLastName")); rotation.setModifiedUser(user); } if (loadDetails.getBoolean("EXPORTED")) { UserBean user = new UserBean(); user.setDN(rs.getString("ExportedBy")); user.setPreferredName(rs.getString("ExportedFirstName")); user.setLastName(rs.getString("ExportedLastName")); rotation.setExportedUser(user); } return rotation; }
From source file:Logica.Usuario.java
@Override public ArrayList<informeDescargos> generarInformePorRA(String area, BigDecimal id) throws RemoteException { EntityManagerFactory emf = Persistence.createEntityManagerFactory("Biot_ServerPU"); Connection con = null;/*from w ww . ja v a2 s . c o m*/ PreparedStatement ps = null; ResultSet rs = null; String statement = "select D.CINTERNO, \"-\", item.DESCRIPCION, item.CANTIDAD, sum(D.CANTIDAD), D.FECHA\n" + "from item, DESCARGO d\n" + "where item.CINTERNO = D.CINTERNO and D.ID_usuario = ?\n" + "group by D.CINTERNO, item.DESCRIPCION, item.CANTIDAD, D.FECHA"; informeDescargos fila = null; System.out.println(statement); ArrayList<informeDescargos> listado = new ArrayList<>(); try { con = Conexion.conexion.getConnection(); ps = con.prepareStatement(statement); ps.setBigDecimal(1, id); rs = ps.executeQuery(); while (rs.next()) { GregorianCalendar c = new GregorianCalendar(); c.setTime(rs.getDate(6)); fila = new informeDescargos(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4), rs.getFloat(5), c); listado.add(fila); } } catch (SQLException ex) { Logger.getLogger(Usuario.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (ps != null) { ps.close(); } if (rs != null) { rs.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { System.out.println("Error cerrando conexion"); } } return listado; }
From source file:database.DataLoader.java
private void moveOrders() throws SQLException, ClassNotFoundException, Exception { // /*from ww w . ja v a 2 s .c o m*/ final String tableName = ORDER; final ResultSet orderSet = getFromOldBase(getSelectAll(tableName, "id")); while (orderSet.next()) { // ? TransactionTemplate temp = new TransactionTemplate(transactionManager); temp.execute(new TransactionCallbackWithoutResult() { @Override protected void doInTransactionWithoutResult(TransactionStatus ts) { Long oldId = 0L; try { oldId = orderSet.getLong("id"); String subject = orderSet.getString("theme"); if (subject.length() > 250) { subject = subject.substring(0, 250); } Long oldBranchId = orderSet.getLong("branch_id"); Long oldOrderTypeId = orderSet.getLong("type_id"); String city = orderSet.getString("university"); Double price = orderSet.getDouble("price"); Double authorSalary = orderSet.getDouble("reward"); Date redline = orderSet.getDate("redline"); Date deadline = orderSet.getDate("deadline"); Long oldAuthorId = orderSet.getLong("author_id"); Long clientId = orderSet.getLong("client_id"); String rewardComment = orderSet.getString("reward_comment"); Boolean flag_0 = orderSet.getBoolean("flag_0"); Boolean flag_1 = orderSet.getBoolean("flag_1"); Timestamp timestamp = orderSet.getTimestamp("ready_date"); String readyComment = orderSet.getString("ready_comment"); String comment = orderSet.getString("description"); Date readyDate = null; if (timestamp != null) { readyDate = new Date(timestamp.getTime()); } Long newBranchId = getNewId(oldBranchId, BRANCH); Branch branch = branchDao.find(newBranchId); Long newAuthorId = getNewId(oldAuthorId, USERS); Author author = authorDao.find(newAuthorId); Long newOrderTypeId = getNewId(oldOrderTypeId, TYPE); OrderType orderType = orderTypeDao.find(newOrderTypeId); Integer statusInt = orderSet.getInt("status"); OrderStatus status = getStatus(statusInt); if (status == null) { status = OrderStatus.NEW; } Date orderDate = getDateOrToday(orderSet, "created"); Order order = new Order(); order.setStatus(status); order.setOrderDate(orderDate); order.setAuthor(author); order.setBranch(branch); order.setOrderType(orderType); order.setSubject(subject); order.setCity(city); order.setCost(price); order.setAuthor_salary(authorSalary); // ? ? , ? order.setRealDate(deadline); order.setDeadlineDate(redline); order.setAuthorComment(readyComment); order.setComment(comment); order.setReadyDate(readyDate); order.setFirstFlag(flag_0); order.setSecondFlag(flag_1); order.setCommentToAuthorSalary(rewardComment); order.setOldId(StringAdapter.getString(oldId)); ResultSet clientSet = getFromOldBase("select * from " + CLIENT + " where id = " + clientId); String clientEmail = null; String clientFio = null; String clientPhone = null; while (clientSet.next()) { clientEmail = trim(clientSet.getString("email")); clientFio = clientSet.getString("firstname") + " " + clientSet.getString("fathername") + " " + clientSet.getString("lastname"); clientPhone = clientSet.getString("phone"); } if (ValidatorUtils.isEmail(clientEmail)) { order.setClientEmail(clientEmail); } order.setClientFio(clientFio); order.setClientPhone(clientPhone); saveObjectAndLink(order, oldId, tableName); } catch (Throwable e) { ts.setRollbackOnly(); addErrorMessage("order: " + oldId + " " + StringAdapter.getStackExeption(e)); log.warn("order: " + oldId + " " + StringAdapter.getStackExeption(e)); } } }); } }
From source file:com.sfs.whichdoctor.dao.PersonDAOImpl.java
/** * Load person.//from ww w . j a v a 2 s . com * * @param rs the rs * @param loadDetails the load details * * @return the person bean * * @throws SQLException the SQL exception */ private PersonBean loadPerson(final ResultSet rs, final BuilderBean loadDetails) throws SQLException { PersonBean person = new PersonBean(); person.setId(rs.getInt("PersonId")); person.setGUID(rs.getInt("GUID")); person.setPersonIdentifier(rs.getInt("PersonIdentifier")); person.setFirstName(rs.getString("FirstName")); person.setMiddleName(rs.getString("MiddleName")); person.setLastName(rs.getString("LastName")); person.setMaidenName(rs.getString("MaidenName")); if (rs.getString("PreferredName") != null) { person.setPreferredName(rs.getString("PreferredName")); } else { person.setPreferredName(person.getFirstName()); } person.setTitle(rs.getString("Title")); person.setHonors(rs.getString("Honors")); person.setGender(rs.getString("Gender")); try { person.setBirthDate(rs.getDate("BirthDate")); } catch (SQLException e) { person.setBirthDate(null); } try { person.setDeceasedDate(rs.getDate("DeceasedDate")); } catch (SQLException e) { person.setDeceasedDate(null); } person.setRegion(rs.getString("Region")); person.setResidentCountry(rs.getString("ResidentCountry")); person.setTrainingStatus(rs.getString("TrainingStatus")); person.setTrainingStatusDetail(rs.getString("TrainingStatusDetail")); person.setTrainingStatusMapping(rs.getString("TrainingStatusMapping")); person.setPassword(rs.getString("Password")); person.setActive(rs.getBoolean("Active")); if (loadDetails.getBoolean("MEMBERSHIP")) { /** Load the membership details **/ try { person.setMembershipDetails(this.membershipDAO.load(person.getGUID(), loadDetails.getBoolean("MEMBERSHIP_FULL"), loadDetails.getString("MEMBERSHIP_CLASS"), loadDetails.getString("MEMBERSHIP_TYPE"))); } catch (Exception e) { dataLogger.error("Error loading memberships for person: " + e.getMessage(), e); } } if (loadDetails.getBoolean("TAGS")) { try { person.setTags(this.getTagDAO().load(person.getGUID(), loadDetails.getString("USERDN"), true)); } catch (Exception e) { dataLogger.error("Error loading tags for person: " + e.getMessage()); } } if (loadDetails.getBoolean("QUALIFICATIONS")) { try { person.setQualifications(this.qualificationDAO.load(person.getGUID(), true)); } catch (Exception e) { dataLogger.error("Error loading qualifications for person: " + e.getMessage()); } } if (loadDetails.getBoolean("ADDRESS")) { try { person.setAddress( this.getAddressDAO().load(person.getGUID(), loadDetails.getBoolean("ADDRESS_FULL"), loadDetails.getString("ADDRESS_CLASS"), loadDetails.getString("ADDRESS_TYPE"))); } catch (Exception e) { dataLogger.error("Error loading addresses for person: " + e.getMessage()); } } if (loadDetails.getBoolean("PHONE")) { try { person.setPhone(this.getPhoneDAO().load(person.getGUID(), loadDetails.getBoolean("PHONE_FULL"))); } catch (Exception e) { dataLogger.error("Error loading phone numbers for person: " + e.getMessage()); } } if (loadDetails.getBoolean("EMAIL")) { try { person.setEmail(this.getEmailDAO().load(person.getGUID(), loadDetails.getBoolean("EMAIL_FULL"), loadDetails.getString("EMAIL_CLASS"), loadDetails.getString("EMAIL_TYPE"))); } catch (Exception e) { dataLogger.error("Error loading email addresses for person: " + e.getMessage()); } } if (loadDetails.getBoolean("HISTORY")) { try { person.setCreatedDate(rs.getTimestamp("CreatedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading CreatedDate: " + sqe.getMessage()); } person.setCreatedBy(rs.getString("CreatedBy")); try { person.setModifiedDate(rs.getTimestamp("ModifiedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ModifiedDate: " + sqe.getMessage()); } person.setModifiedBy(rs.getString("ModifiedBy")); try { person.setExportedDate(rs.getTimestamp("ExportedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ExportedDate: " + sqe.getMessage()); } person.setExportedBy(rs.getString("ExportedBy")); } if (loadDetails.getBoolean("MEMO")) { try { person.setMemo(this.getMemoDAO().load(person.getGUID(), loadDetails.getBoolean("MEMO_FULL"))); } catch (Exception e) { dataLogger.error("Error loading memos for person: " + e.getMessage()); } } if (loadDetails.getBoolean("SPECIALTY")) { try { person.setSpecialtyList(this.getSpecialtyDAO().load(person.getGUID(), true)); } catch (Exception e) { dataLogger.error("Error loading specialties for person: " + e.getMessage()); } } if (loadDetails.getBoolean("MENTORS")) { try { person.setMentors(this.getItemDAO().load(person.getGUID(), true, "Mentor", "Mentors")); } catch (Exception e) { dataLogger.error("Error loading mentors for person: " + e.getMessage()); } } if (loadDetails.getBoolean("EMPLOYERS")) { try { person.setEmployers(this.getItemDAO().load(person.getGUID(), true, "Employer", "Employers", 0, loadDetails.getString("EMPLOYERS_STARTDATE"), loadDetails.getString("EMPLOYERS_ENDDATE"))); } catch (Exception e) { dataLogger.info("Error loading employers for this person: " + e.getMessage()); } } if (loadDetails.getBoolean("TRAINING_BASIC")) { /* Load the person's Basic Training summary */ try { person.setTrainingSummary("Basic Training", this.accreditationDAO.getTrainingSummary(person.getGUID(), "Basic Training")); } catch (Exception e) { dataLogger.error("Error loading accrediation summary: " + e.getMessage()); } } if (loadDetails.getBoolean("TRAINING_ADVANCED")) { /* Load the person's Advanced Training summary */ try { person.setTrainingSummary("Advanced Training", this.accreditationDAO.getTrainingSummary(person.getGUID(), "Advanced Training")); } catch (Exception e) { dataLogger.error("Error loading accrediation summary: " + e.getMessage()); } } if (loadDetails.getBoolean("TRAINING_POSTFRACP")) { /* Load the person's Post-FRACP Training summary */ try { person.setTrainingSummary("Post-FRACP Training", this.accreditationDAO.getTrainingSummary(person.getGUID(), "Post-FRACP Training")); } catch (Exception e) { dataLogger.error("Error loading accrediation summary: " + e.getMessage()); } } if (loadDetails.getBoolean("EXAMS")) { try { person.setExams(this.examDAO.load(person.getGUID(), false)); } catch (Exception e) { dataLogger.error("Error loading exams for person: " + e.getMessage()); } person.setExamDeadline(this.examDAO.calculateExamDeadline(person.getExams())); } if (loadDetails.getBoolean("PROJECTS")) { try { person.setProjects(this.projectDAO.load(person.getGUID(), false)); } catch (Exception e) { dataLogger.error("Error loading projects for person: " + e.getMessage()); } } if (loadDetails.getBoolean("WORKSHOPS")) { try { person.setWorkshops(this.workshopDAO.load(person.getGUID(), false)); } catch (Exception e) { dataLogger.error("Error loading workshops for person: " + e.getMessage()); } } if (loadDetails.getBoolean("ONLINETOOLS")) { try { person.setOnlineTools(this.onlineToolDAO.load(person.getGUID())); } catch (WhichDoctorDaoException wde) { dataLogger.error("Error loading online tools for person: " + wde.getMessage()); } } if (loadDetails.getBoolean("TRAINING_ROTATIONS")) { // Find all Rotations for the person SearchBean search = this.getSearchDAO().initiate("rotation", null); RotationBean searchCriteria = (RotationBean) search.getSearchCriteria(); RotationBean consts = (RotationBean) search.getSearchConstraints(); searchCriteria.setPersonId(person.getGUID()); if (!loadDetails.getBoolean("LOAD_ALL") && loadDetails.getBoolean("CURRENT_ROTATION")) { Calendar currentDate = Calendar.getInstance(); searchCriteria.setStartDate(currentDate.getTime()); searchCriteria.setEndDate(currentDate.getTime()); consts.setStartDate(DataFilter.parseDate("1/1/1900", false)); consts.setEndDate(DataFilter.parseDate("31/12/2050", false)); search.setSearchConstraints(consts); } search.setSearchCriteria(searchCriteria); search.setLimit(0); BuilderBean loadRotation = new BuilderBean(); loadRotation.setParameter("ASSESSMENTS", true); loadRotation.setParameter("ONLINETOOLS", true); loadRotation.setParameter("ACCREDITATIONS", true); loadRotation.setParameter("SUPERVISORS", true); loadRotation.setParameter("MEMO", true); loadRotation.setParameter("MEMO_FULL", true); try { final Collection<Object> results = this.getSearchDAO().search(search, loadRotation) .getSearchResults(); if (results != null) { Collection<RotationBean> rotations = new ArrayList<RotationBean>(); for (Object rotationObj : results) { rotations.add((RotationBean) rotationObj); } person.setRotations(rotations); dataLogger.info("Successfully loaded for rotation information"); } } catch (Exception e) { dataLogger.error("Error loading person rotations: " + e.getMessage()); } } /** Only perform this search if no rotations have been loaded above **/ if (loadDetails.getBoolean("LAST_ROTATION") && person.getRotations().size() == 0) { // Find the last rotation this person undertook SearchBean search = this.getSearchDAO().initiate("rotation", null); RotationBean searchCriteria = (RotationBean) search.getSearchCriteria(); searchCriteria.setPersonId(person.getGUID()); search.setSearchCriteria(searchCriteria); search.setLimit(1); search.setOrderColumn("rotation.EndDate"); search.setOrderAscending(false); try { final Collection<Object> results = this.getSearchDAO().search(search).getSearchResults(); if (results != null) { Collection<RotationBean> rotations = new ArrayList<RotationBean>(); for (Object rotationObj : results) { rotations.add((RotationBean) rotationObj); } person.setRotations(rotations); dataLogger.info("Successfully loaded last rotation for person"); } } catch (Exception e) { dataLogger.error("Error loading last rotation for person: " + e.getMessage()); } } if (loadDetails.getBoolean("REIMBURSEMENTS")) { SearchBean search = this.getSearchDAO().initiate("reimbursement", null); ReimbursementBean searchCriteria = (ReimbursementBean) search.getSearchCriteria(); searchCriteria.setPersonId(person.getGUID()); search.setLimit(0); search.setSearchCriteria(searchCriteria); try { final Collection<Object> results = this.getSearchDAO().search(search).getSearchResults(); if (results != null) { Collection<ReimbursementBean> reimbursements = new ArrayList<ReimbursementBean>(); for (Object reimbursementObj : results) { reimbursements.add((ReimbursementBean) reimbursementObj); } person.setReimbursements(reimbursements); } dataLogger.info("Successfully loaded reimbursement information"); } catch (Exception e) { dataLogger.error("ERROR searching database: " + e.getMessage()); } } if (loadDetails.getBoolean("FINANCIAL_SUMMARY")) { FinancialSummaryBean search = new FinancialSummaryBean(); search.setLimit(0); search.setRequestedPage(1); search.addPerson(person); search.setSecurity("Standard"); search.setOpeningDate(DataFilter.parseDate(loadDetails.getString("FINANCIAL_SUMMARY_OPENING"), false)); search.setClosingDate(DataFilter.parseDate(loadDetails.getString("FINANCIAL_SUMMARY_CLOSING"), true)); BuilderBean summaryDetails = new BuilderBean(); summaryDetails.setParameter("LOAD_DETAILS", loadDetails.getBoolean("FINANCIAL_SUMMARY_TRANSACTIONS")); summaryDetails.setParameter("SHOW_CANCELLED", loadDetails.getBoolean("FINANCIAL_SUMMARY_USER")); summaryDetails.setParameter("RESULT_COUNT", true); FinancialSummaryBean results = null; try { results = this.financialSummaryAnalysisDAO.search(search, summaryDetails); } catch (Exception e) { dataLogger.error("Error loading financial summary results: " + e.getMessage()); } if (results != null && loadDetails.getBoolean("FINANCIAL_SUMMARY_LASTTEN")) { /* Limit to the last 10 results */ if (results.getTotalResults() > 10) { ArrayList<TransactionSummaryBean> limitedResults = new ArrayList<TransactionSummaryBean>(); ArrayList<TransactionSummaryBean> transactions = new ArrayList<TransactionSummaryBean>(); for (TransactionSummaryBean transaction : results.getTransactions()) { transactions.add(transaction); } for (int i = transactions.size() - 11; i < transactions.size(); i++) { try { TransactionSummaryBean transaction = (TransactionSummaryBean) transactions.get(i); if (i == transactions.size() - 11) { /* Get total and set as opening balance */ results.setBalanceBroughtForward(transaction.getTotal()); } else { limitedResults.add(transaction); } } catch (Exception e) { dataLogger.error("Error getting last ten financial entries:" + e.getMessage()); } } results.setTransactions(limitedResults); } } person.setFinancialSummary(results); if (loadDetails.getBoolean("FINANCIAL_SUMMARY_USER")) { FinancialSummaryBean securedFinancialSearch = search; securedFinancialSearch.setSecurity("Restricted"); try { person.setRestrictedFinancialSummary( this.financialSummaryAnalysisDAO.search(securedFinancialSearch, summaryDetails)); dataLogger.info("Successfully loaded restricted" + " financial summary information"); } catch (Exception e) { dataLogger.error("Error loading restricted" + " financial summary results: " + e.getMessage()); } } if (loadDetails.getBoolean("FINANCIAL_SUMMARY_OUTSTANDING")) { double balance = 0; if (person.getFinancialSummary() != null) { balance += person.getFinancialSummary().getClosingBalance(); } if (person.getRestrictedFinancialSummary() != null) { balance += person.getRestrictedFinancialSummary().getClosingBalance(); } if (balance > 0) { // Perform a search for outstanding debits associated with person SearchBean dbSearch = this.getSearchDAO().initiate("debit", null); DebitBean dbCriteria = (DebitBean) dbSearch.getSearchCriteria(); DebitBean dbConstraints = (DebitBean) dbSearch.getSearchConstraints(); dbCriteria.setPersonId(person.getGUID()); dbCriteria.setOutstandingValue(0.01); dbConstraints.setOutstandingValue(10000000); dbSearch.setSearchCriteria(dbCriteria); dbSearch.setSearchConstraints(dbConstraints); dbSearch.setLimit(0); SearchResultsBean dbResults = new SearchResultsBean(); try { dbResults = this.getSearchDAO().search(dbSearch); } catch (WhichDoctorSearchDaoException wde) { dataLogger.error("Error searching for outstanding debits: " + wde.getMessage()); } Collection<DebitBean> debits = new ArrayList<DebitBean>(); for (Object debit : dbResults.getSearchResults()) { debits.add((DebitBean) debit); } person.setDebits(debits); } } } if (loadDetails.getBoolean("GROUPS")) { // Create new SearchBean of type Member and default values SearchResultsBean results = new SearchResultsBean(); SearchBean groupSearch = this.getSearchDAO().initiate("group", null); groupSearch.setLimit(0); GroupBean searchCriteria = (GroupBean) groupSearch.getSearchCriteria(); searchCriteria.setObjectType("Members"); ItemBean item = new ItemBean(); item.setObject2GUID(person.getGUID()); TreeMap<String, ItemBean> items = new TreeMap<String, ItemBean>(); items.put("Supervisor", item); searchCriteria.setItems(items); groupSearch.setSearchCriteria(searchCriteria); try { BuilderBean loadGroup = new BuilderBean(); loadGroup.setParameter("ITEMS", true); loadGroup.setParameter("REFERENCEID", String.valueOf(person.getGUID())); results = this.getSearchDAO().search(groupSearch, loadGroup); } catch (WhichDoctorSearchDaoException wdse) { dataLogger.error("Error loading groups for person: " + wdse.getMessage()); } Collection<GroupBean> groups = new ArrayList<GroupBean>(); for (Object group : results.getSearchResults()) { groups.add((GroupBean) group); } person.setGroups(groups); } if (loadDetails.getBoolean("RELATIONSHIPS")) { BuilderBean loadRelationships = new BuilderBean(); if (loadDetails.getBoolean("RELATIONSHIPS_OBJECTS")) { loadRelationships.setParameter("RELATED_PERSON", true); } try { person.setRelationships(this.relationshipDAO.load(person.getGUID(), loadRelationships)); } catch (WhichDoctorDaoException wde) { dataLogger.error("Error loading relationships for person: " + wde.getMessage()); } } if (this.getFunctionality().isEnabled("Region Representatives") && loadDetails.getBoolean("REPRESENTATIVES")) { // Load the region representatives associated to this person if (StringUtils.isNotBlank(person.getRegion())) { ArrayList<PersonBean> representatives = new ArrayList<PersonBean>(); // Create new SearchBean of type Member and default values SearchBean search = this.getSearchDAO().initiate("person", null); PersonBean searchCriteria = (PersonBean) search.getSearchCriteria(); /* Set first and last name requirements */ MembershipBean membershipCriteria = this.membershipDAO.getDefaultInstance(); ObjectTypeBean object = new ObjectTypeBean(); object.setClassName(person.getRegion()); membershipCriteria.setField("Region Representative", object); ArrayList<MembershipBean> membershipCriteriaDetails = new ArrayList<MembershipBean>(); membershipCriteriaDetails.add(membershipCriteria); searchCriteria.setMembershipDetails(membershipCriteriaDetails); search.setSearchCriteria(searchCriteria); SearchResultsBean results = new SearchResultsBean(); try { results = this.getSearchDAO().search(search); } catch (Exception e) { dataLogger.error("Error searching for representatives: " + e.getMessage()); } if (results.getSearchResults() != null) { for (Object representative : results.getSearchResults()) { representatives.add((PersonBean) representative); } } person.setRegionRepresentatives(representatives); } } if (loadDetails.getBoolean("ISBENTITIES")) { // Load the ISB entity references this person has try { final Collection<IsbEntityBean> isbEntities = this.isbEntityDAO.loadAll(person.getGUID()); person.setIsbEntities(isbEntities); } catch (WhichDoctorDaoException wde) { dataLogger.error("Error loading ISB entities: " + wde.getMessage()); } } if (loadDetails.getBoolean("SUPERVISED")) { final HashMap<String, ArrayList<PersonBean>> supervised = loadSupervisedPeople(person.getGUID(), loadDetails.getBoolean("SUPERVISED_ALLROTATIONS")); person.setCurrentlySupervising(supervised.get("current")); person.setPreviouslySupervised(supervised.get("previous")); } if (loadDetails.getBoolean("CREATED")) { UserBean user = new UserBean(); user.setDN(rs.getString("CreatedBy")); user.setPreferredName(rs.getString("CreatedFirstName")); user.setLastName(rs.getString("CreatedLastName")); person.setCreatedUser(user); } if (loadDetails.getBoolean("MODIFIED")) { UserBean user = new UserBean(); user.setDN(rs.getString("ModifiedBy")); user.setPreferredName(rs.getString("ModifiedFirstName")); user.setLastName(rs.getString("ModifiedLastName")); person.setModifiedUser(user); } if (loadDetails.getBoolean("EXPORTED")) { UserBean user = new UserBean(); user.setDN(rs.getString("ExportedBy")); user.setPreferredName(rs.getString("ExportedFirstName")); user.setLastName(rs.getString("ExportedLastName")); person.setExportedUser(user); } return person; }
From source file:ece356.UserDBAO.java
public static DoctorData queryDoctor(String userName) throws ClassNotFoundException, SQLException { Connection con = null;/*from w ww . ja v a2 s.com*/ PreparedStatement pstmt = null; DoctorData ret; try { con = getConnection(); // Query for general doctor information String query = "SELECT * FROM doctorView where username = ?"; pstmt = con.prepareStatement(query); pstmt.setString(1, userName); ResultSet resultSet; resultSet = pstmt.executeQuery(); resultSet.next(); ret = new DoctorData(); ret.userName = resultSet.getString("username"); ret.firstName = resultSet.getString("first_name"); ret.lastName = resultSet.getString("last_name"); ret.middleInitial = resultSet.getString("middle_initial"); ret.gender = resultSet.getString("gender"); ret.emailAddress = resultSet.getString("email_address"); ret.yearsLicensed = resultSet.getInt("yearsLicensed"); ret.averageRating = resultSet.getInt("averageRating"); ret.numberOfReviews = resultSet.getInt("numberOfReviews"); // Query for work addresses of doctor query = "SELECT * FROM doctorWorkAddressView where doc_address_username = ?"; pstmt = con.prepareStatement(query); pstmt.setString(1, userName); resultSet = pstmt.executeQuery(); ArrayList<WorkAddressData> workAddressList = new ArrayList<WorkAddressData>(); ret.workAddressList = workAddressList; while (resultSet.next()) { WorkAddressData workAddress = new WorkAddressData(); workAddress.city = resultSet.getString("city"); workAddress.state = resultSet.getString("state"); workAddress.postalCode = resultSet.getString("postal_code"); workAddress.streetName = resultSet.getString("street_name"); workAddress.streetNumber = resultSet.getInt("street_number"); workAddress.unitNumber = resultSet.getString("street_unit_number"); workAddressList.add(workAddress); } // Query for specializations of doctor query = "SELECT * FROM doctorSpecializationView where doc_spec_username = ?"; pstmt = con.prepareStatement(query); pstmt.setString(1, userName); resultSet = pstmt.executeQuery(); ArrayList<String> specializationList = new ArrayList<String>(); ret.specializationList = specializationList; while (resultSet.next()) { String specialization = resultSet.getString("specTypeName"); specializationList.add(specialization); } // Query for reviews of doctor query = "SELECT * FROM review where doc_username = ? order by date desc"; pstmt = con.prepareStatement(query); pstmt.setString(1, userName); resultSet = pstmt.executeQuery(); ArrayList<ReviewData> reviewList = new ArrayList<ReviewData>(); ret.reviewList = reviewList; while (resultSet.next()) { ReviewData review = new ReviewData(); review.comment = resultSet.getString("comment"); review.reviewId = resultSet.getString("reviewId"); review.doctorUsername = resultSet.getString("doc_username"); review.patientUsername = resultSet.getString("patient_username"); review.date = resultSet.getDate("date"); review.rating = resultSet.getInt("rating"); reviewList.add(review); } return ret; } catch (Exception e) { System.out.println("EXCEPTION:%% " + e); } finally { if (pstmt != null) { pstmt.close(); } if (con != null) { con.close(); } } return null; }
From source file:com.skilrock.lms.coreEngine.scratchService.inventoryMgmt.common.SalesReturnHelper.java
public ArrayList getGameList(int orgId) { ArrayList<GameBean> list = new ArrayList<GameBean>(); GameBean gameBean = null;/*from w ww . j a v a2s. co m*/ Connection con; con = DBConnect.getConnection(); //Statement stmt1 = null; Statement stmt2 = null; //ResultSet rs1 = null; ResultSet rs2 = null; try { //stmt1 = con.createStatement(); stmt2 = con.createStatement(); logger.info("Url>>>>>>>>>>>>>>>..." + orgId); /*String owid = "select organization_id from st_lms_organization_master where name = '" + orgName + "'"; rs1 = stmt1.executeQuery(owid); int orgId = 0; while (rs1.next()) { orgId = rs1.getInt("organization_id"); }*/ // java.sql.Date currentDate= new java.sql.Date(new // Date().getTime()); String gmdetail = "select distinct p.game_id, p.game_name,p.game_nbr,p.sale_end_date from st_se_game_master p, st_se_game_inv_status e where e.game_id = p.game_id and e.current_owner_id='" + orgId + "' and p.game_status in('OPEN','SALE_HOLD','SALE_CLOSE')"; rs2 = stmt2.executeQuery(gmdetail); while (rs2.next()) { gameBean = new GameBean(); gameBean.setGameId(rs2.getInt("game_id")); gameBean.setGameNbr(rs2.getInt("game_nbr")); gameBean.setGameName(rs2.getString("game_name")); gameBean.setSaleEndDate(rs2.getDate("sale_end_date")); list.add(gameBean); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (con != null) { con.close(); } } catch (SQLException se) { se.printStackTrace(); } } return list; }
From source file:mom.trd.opentheso.bdd.helper.RelationsHelper.java
/** * Cette fonction permet de rcuprer la liste de l'historique des relations * d'un concept d'un concept/*from w ww.jav a 2 s. co m*/ * * @param ds * @param idConcept * @param idThesaurus * @param lang * @return Objet class Concept */ public ArrayList<Relation> getRelationHistoriqueAll(HikariDataSource ds, String idConcept, String idThesaurus, String lang) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<Relation> listRel = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "select lexical_value, id_concept2, role, action, hierarchical_relationship_historique.modified, username " + "from hierarchical_relationship_historique, users, preferred_term, term" + " where hierarchical_relationship_historique.id_thesaurus = '" + idThesaurus + "'" + " and hierarchical_relationship_historique.id_concept1=preferred_term.id_concept" + " and preferred_term.id_term=term.id_term" + " and term.lang='" + lang + "'" + " and term.id_thesaurus='" + idThesaurus + "'" + " and ( id_concept1 = '" + idConcept + "'" + " or id_concept2 = '" + idConcept + "' )" + " and hierarchical_relationship_historique.id_user=users.id_user" + " order by hierarchical_relationship_historique.modified DESC"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { listRel = new ArrayList<>(); while (resultSet.next()) { Relation r = new Relation(); r.setId_relation(resultSet.getString("role")); r.setId_concept1(resultSet.getString("lexical_value")); r.setId_concept2(resultSet.getString("id_concept2")); r.setModified(resultSet.getDate("modified")); r.setIdUser(resultSet.getString("username")); r.setAction(resultSet.getString("action")); r.setId_thesaurus(idThesaurus); listRel.add(r); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting all relation historique of Concept : " + idConcept, sqle); } return listRel; }
From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java
public Field resultToField(ResultSetMetaData md, ResultSet rs, int columnIndex, int maxClobSize, int maxBlobSize, DataType userSpecifiedType, UnknownTypeAction unknownTypeAction, boolean timestampToString) throws SQLException, IOException, StageException { Field field;//from ww w. ja v a2 s . co m if (userSpecifiedType != DataType.USE_COLUMN_TYPE) { // If user specifies the data type, overwrite the column type returned by database. field = Field.create(Field.Type.valueOf(userSpecifiedType.getLabel()), rs.getObject(columnIndex)); } else { // All types as of JDBC 2.0 are here: // https://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Types.ARRAY // Good source of recommended mappings is here: // http://www.cs.mun.ca/java-api-1.5/guide/jdbc/getstart/mapping.html switch (md.getColumnType(columnIndex)) { case Types.BIGINT: field = Field.create(Field.Type.LONG, rs.getObject(columnIndex)); break; case Types.BINARY: case Types.LONGVARBINARY: case Types.VARBINARY: field = Field.create(Field.Type.BYTE_ARRAY, rs.getBytes(columnIndex)); break; case Types.BIT: case Types.BOOLEAN: field = Field.create(Field.Type.BOOLEAN, rs.getObject(columnIndex)); break; case Types.CHAR: case Types.LONGNVARCHAR: case Types.LONGVARCHAR: case Types.NCHAR: case Types.NVARCHAR: case Types.VARCHAR: field = Field.create(Field.Type.STRING, rs.getObject(columnIndex)); break; case Types.CLOB: case Types.NCLOB: field = Field.create(Field.Type.STRING, getClobString(rs.getClob(columnIndex), maxClobSize)); break; case Types.BLOB: field = Field.create(Field.Type.BYTE_ARRAY, getBlobBytes(rs.getBlob(columnIndex), maxBlobSize)); break; case Types.DATE: field = Field.create(Field.Type.DATE, rs.getDate(columnIndex)); break; case Types.DECIMAL: case Types.NUMERIC: field = Field.create(Field.Type.DECIMAL, rs.getBigDecimal(columnIndex)); field.setAttribute(HeaderAttributeConstants.ATTR_SCALE, String.valueOf(rs.getMetaData().getScale(columnIndex))); field.setAttribute(HeaderAttributeConstants.ATTR_PRECISION, String.valueOf(rs.getMetaData().getPrecision(columnIndex))); break; case Types.DOUBLE: field = Field.create(Field.Type.DOUBLE, rs.getObject(columnIndex)); break; case Types.FLOAT: case Types.REAL: field = Field.create(Field.Type.FLOAT, rs.getObject(columnIndex)); break; case Types.INTEGER: field = Field.create(Field.Type.INTEGER, rs.getObject(columnIndex)); break; case Types.ROWID: field = Field.create(Field.Type.STRING, rs.getRowId(columnIndex).toString()); break; case Types.SMALLINT: case Types.TINYINT: field = Field.create(Field.Type.SHORT, rs.getObject(columnIndex)); break; case Types.TIME: field = Field.create(Field.Type.TIME, rs.getObject(columnIndex)); break; case Types.TIMESTAMP: final Timestamp timestamp = rs.getTimestamp(columnIndex); if (timestampToString) { field = Field.create(Field.Type.STRING, timestamp == null ? null : timestamp.toString()); } else { field = Field.create(Field.Type.DATETIME, timestamp); if (timestamp != null) { final long actualNanos = timestamp.getNanos() % NANOS_TO_MILLIS_ADJUSTMENT; if (actualNanos > 0) { field.setAttribute(FIELD_ATTRIBUTE_NANOSECONDS, String.valueOf(actualNanos)); } } } break; // Ugly hack until we can support LocalTime, LocalDate, LocalDateTime, etc. case Types.TIME_WITH_TIMEZONE: OffsetTime offsetTime = rs.getObject(columnIndex, OffsetTime.class); field = Field.create(Field.Type.TIME, Date.from(offsetTime.atDate(LocalDate.MIN).toInstant())); break; case Types.TIMESTAMP_WITH_TIMEZONE: OffsetDateTime offsetDateTime = rs.getObject(columnIndex, OffsetDateTime.class); field = Field.create(Field.Type.ZONED_DATETIME, offsetDateTime.toZonedDateTime()); break; //case Types.REF_CURSOR: // JDK8 only case Types.SQLXML: case Types.STRUCT: case Types.ARRAY: case Types.DATALINK: case Types.DISTINCT: case Types.JAVA_OBJECT: case Types.NULL: case Types.OTHER: case Types.REF: default: if (unknownTypeAction == null) { return null; } switch (unknownTypeAction) { case STOP_PIPELINE: throw new StageException(JdbcErrors.JDBC_37, md.getColumnType(columnIndex), md.getColumnLabel(columnIndex)); case CONVERT_TO_STRING: Object value = rs.getObject(columnIndex); if (value != null) { field = Field.create(Field.Type.STRING, rs.getObject(columnIndex).toString()); } else { field = Field.create(Field.Type.STRING, null); } break; default: throw new IllegalStateException("Unknown action: " + unknownTypeAction); } } } return field; }