List of usage examples for java.sql CallableStatement setString
void setString(String parameterName, String x) throws SQLException;
String value. From source file:com.intuit.it.billing.data.BillingDAOImpl.java
/** * getBillingHistory is to be used to get get the billing history of a given customer. * <p/>/*from w w w .j a va2 s . c om*/ * <p/> * <b>DATABASE PROCEDURE:</b> * * @code * FUNCTION fn_get_history( * customer IN VARCHAR2, * start_date IN DATE, * end_date IN DATE, * page IN INTEGER, * records IN INTEGER ) * RETURN ref_cursor; * @endcode * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>ITEM_ID,</li> * <li>BILL_ITEM_NO,</li> * <li>AR_ACCOUNT_NO,</li> * <li>ACCOUNT_NO,</li> * <li>ORDER_NO,</li> * <li>ORDER_LINE_NO,</li> * <li>EVENT_TYPE,</li> * <li>CHARGE_TYPE,</li> * <li> CURRENCY,</li> * <li>CREATED_DATE,</li> * <li>BILL_DATE,</li> * <li>DUE_DATE,</li> * <li>STATUS,</li> * <li>REASON_CODE,</li> * <li>ITEM_TOTAL,</li> * <li>ITEM_DUE,</li> * <li>ITEM_DISPUTED,</li> * <li>ITEM_BASE,</li> * <li>ITEM_TAX,</li> * <li>ITEM_DESCRIPTION,</li> * <li>ITEM_CODE,</li> * <li>LICENSE,</li> * <li>PAY_TYPE,</li> * <li>PAY_DESCR,</li> * <li>PAY_ACCT_TYPE, * <li>PAY_PSON,</li> * <li>QUANTITY </li> * </ul> * * @param customer : The Customer.accountNo of the customer we want history for * @param startDate : The starting date of the allocation - to be merged with a billing history record set * @param endDate : The ending date of the allocation - to be merged with a billing history record set * @param skip : Starting record for server side paging * @param pageSize : How many records to retrieve * * @return A list of LineItem objects in reverse date order sort */ @Override public List<LineItem> getBillingHistory(String cust, Date startDate, Date endDate, Integer startPage, Integer pageSize) throws JSONException { List<LineItem> history = new ArrayList<LineItem>(); java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime()); java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime()); String query = "begin ? := billing_inquiry.fn_get_history( ?, ?, ?, ?, ? ); end;"; Connection conn = null; ResultSet rs = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, cust); stmt.setDate(3, sqlStartDate); stmt.setDate(4, sqlEndDate); stmt.setInt(5, startPage); stmt.setInt(6, pageSize); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { LineItem l = new LineItem(); l.setRowId(rs.getInt("ROW_ID")); l.setBaseAmount(rs.getBigDecimal("ITEM_BASE")); l.setItemTotal(rs.getBigDecimal("ITEM_TOTAL")); l.setItemDue(rs.getBigDecimal("ITEM_DUE")); l.setItemDisputed(rs.getBigDecimal("ITEM_DISPUTED")); l.setTaxAmount(rs.getBigDecimal("ITEM_TAX")); l.setBillDate(rs.getTimestamp("BILL_DATE")); l.setBillItemNo(rs.getString("BILL_ITEM_NO")); l.setBillTo(rs.getString("AR_ACCOUNT_NO")); l.setChargeType(rs.getString("CHARGE_TYPE")); l.setCreatedDate(rs.getTimestamp("CREATED_DATE")); l.setCurrency(rs.getString("CURRENCY")); l.setDueDate(rs.getTimestamp("DUE_DATE")); l.setEventType(rs.getString("EVENT_TYPE")); l.setItemCode(rs.getString("ITEM_CODE")); l.setItemDescription(rs.getString("ITEM_DESCRIPTION")); l.setLicense(rs.getString("LICENSE")); l.setItemID(rs.getString("ITEM_ID")); l.setOrderLine(rs.getString("ORDER_LINE_NO")); l.setOrderNo(rs.getString("ORDER_NO")); l.setPayAccountType(rs.getString("PAY_ACCT_TYPE")); l.setPayDescription(rs.getString("PAY_DESCR")); l.setPayType(rs.getString("PAY_TYPE")); l.setpSON(rs.getString("PAY_PSON")); l.setQuantity(rs.getInt("QUANTITY")); l.setReasonCode(rs.getString("REASON_CODE")); l.setStatus(rs.getInt("STATUS")); history.add(l); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (history == null || history.isEmpty()) { throw JSONException.noDataFound("Null set returned - no data found"); } return history; }
From source file:com.cws.esolutions.core.dao.impl.WebMessagingDAOImpl.java
/** * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#insertMessage(List) *///from ww w . j av a 2s . c o m public synchronized boolean insertMessage(final List<Object> messageList) throws SQLException { final String methodName = IWebMessagingDAO.CNAME + "#insertMessage(final List<Object> messageList) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("messageList: {}", messageList); } Connection sqlConn = null; CallableStatement stmt = null; boolean isComplete = false; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall("{CALL submitSvcMessage(?, ?, ?, ?, ?, ?, ?, ?)}"); stmt.setString(1, (String) messageList.get(0)); // message id stmt.setString(2, (String) messageList.get(1)); // message title stmt.setString(3, (String) messageList.get(2)); // message text stmt.setString(4, (String) messageList.get(3)); // author email stmt.setBoolean(5, (Boolean) messageList.get(4)); // is active stmt.setBoolean(6, (Boolean) messageList.get(5)); // is alert stmt.setBoolean(7, (Boolean) messageList.get(6)); // does expire stmt.setLong(8, (messageList.get(7) == null) ? 0 : (Long) messageList.get(7)); // expiry date isComplete = (!(stmt.execute())); if (DEBUG) { DEBUGGER.debug("isComplete: {}", isComplete); } } catch (SQLException sqx) { ERROR_RECORDER.error(sqx.getMessage(), sqx); throw new SQLException(sqx.getMessage(), sqx); } finally { if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return isComplete; }
From source file:com.cws.esolutions.core.dao.impl.WebMessagingDAOImpl.java
/** * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#retrieveMessage(String) *//* ww w .ja v a 2 s . c o m*/ public synchronized List<Object> retrieveMessage(final String messageId) throws SQLException { final String methodName = IWebMessagingDAO.CNAME + "#retrieveMessage(final String messageId) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug(messageId); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<Object> svcMessage = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall("{CALL retrServiceMessage(?)}"); stmt.setString(1, messageId); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (resultSet.next()) { resultSet.first(); svcMessage = new ArrayList<Object>(); svcMessage.add(resultSet.getString(1)); // svc_message_id svcMessage.add(resultSet.getString(2)); // svc_message_title svcMessage.add(resultSet.getString(3)); // svc_message_txt svcMessage.add(resultSet.getString(4)); // svc_message_author svcMessage.add(resultSet.getTimestamp(5)); // svc_message_submitdate svcMessage.add(resultSet.getBoolean(6)); // svc_message_active svcMessage.add(resultSet.getBoolean(7)); // svc_message_alert svcMessage.add(resultSet.getBoolean(8)); // svc_message_expires svcMessage.add(resultSet.getTimestamp(9)); // svc_message_expirydate svcMessage.add(resultSet.getTimestamp(10)); // svc_message_modifiedon svcMessage.add(resultSet.getString(11)); // svc_message_modifiedby if (DEBUG) { DEBUGGER.debug("svcMessage: {}", svcMessage); } } } } catch (SQLException sqx) { ERROR_RECORDER.error(sqx.getMessage(), sqx); throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return svcMessage; }
From source file:com.cws.esolutions.core.dao.impl.ServerDataDAOImpl.java
/** * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#addServer(java.util.List) *//* w w w .j a va2s.c om*/ public synchronized boolean addServer(final List<Object> serverData) throws SQLException { final String methodName = IServerDataDAO.CNAME + "#addServer(final List<Object> serverData) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); for (Object str : serverData) { DEBUGGER.debug("Value: {}", str); } } Connection sqlConn = null; boolean isComplete = false; CallableStatement stmt = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall( "{CALL insertNewServer(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}"); stmt.setString(1, (String) serverData.get(0)); // systemGuid stmt.setString(2, (String) serverData.get(1)); // systemOs stmt.setString(3, (String) serverData.get(2)); // systemStatus stmt.setString(4, (String) serverData.get(3)); // systemRegion stmt.setString(5, (String) serverData.get(4)); // networkPartiton stmt.setString(6, (String) serverData.get(5)); // datacenter stmt.setString(7, (String) serverData.get(6)); // systemType stmt.setString(8, (String) serverData.get(7)); // domainName stmt.setString(9, (String) serverData.get(8)); // cpuType stmt.setInt(10, (Integer) serverData.get(9)); // cpuCount stmt.setString(11, (String) serverData.get(10)); // serverModel stmt.setString(12, (String) serverData.get(11)); // serialNumber stmt.setInt(13, (Integer) serverData.get(12)); // installedMemory stmt.setString(14, (String) serverData.get(13)); // operIp stmt.setString(15, (String) serverData.get(14)); // operHostname stmt.setString(16, (String) serverData.get(15)); // mgmtIp stmt.setString(17, (String) serverData.get(16)); // mgmtHostname stmt.setString(18, (String) serverData.get(17)); // backupIp stmt.setString(19, (String) serverData.get(18)); // backupHostname stmt.setString(20, (String) serverData.get(19)); // nasIp stmt.setString(21, (String) serverData.get(20)); // nasHostname stmt.setString(22, (String) serverData.get(21)); // natAddr stmt.setString(23, (String) serverData.get(22)); // systemComments stmt.setString(24, (String) serverData.get(23)); // engineer stmt.setString(25, (String) serverData.get(24)); // mgrEntry stmt.setInt(26, (Integer) serverData.get(25)); // dmgrPort stmt.setString(27, (String) serverData.get(26)); // serverRack stmt.setString(28, (String) serverData.get(27)); // rackPosition stmt.setString(29, (String) serverData.get(28)); // owningDmgr if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } isComplete = (!(stmt.execute())); if (DEBUG) { DEBUGGER.debug("isComplete: {}", isComplete); } } catch (SQLException sqx) { throw new SQLException(sqx.getMessage(), sqx); } finally { if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return isComplete; }
From source file:com.cws.esolutions.core.dao.impl.ServerDataDAOImpl.java
/** * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#updateServer(java.lang.String, java.util.List) *///w ww .j a v a 2 s . c om public synchronized boolean updateServer(final String serverGuid, final List<Object> serverData) throws SQLException { final String methodName = IServerDataDAO.CNAME + "#updateServer(final String serverGuid, final List<Object> serverData) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("Value: {}", serverGuid); for (Object str : serverData) { DEBUGGER.debug("Value: {}", str); } } Connection sqlConn = null; boolean isComplete = false; CallableStatement stmt = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall( "{CALL updateServerData(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}"); stmt.setString(1, serverGuid); // systemGuid stmt.setString(2, (String) serverData.get(1)); // systemOs stmt.setString(3, (String) serverData.get(2)); // systemStatus stmt.setString(4, (String) serverData.get(3)); // systemRegion stmt.setString(5, (String) serverData.get(4)); // networkPartiton stmt.setString(6, (String) serverData.get(5)); // datacenter stmt.setString(7, (String) serverData.get(6)); // systemType stmt.setString(8, (String) serverData.get(7)); // domainName stmt.setString(9, (String) serverData.get(8)); // cpuType stmt.setInt(10, (Integer) serverData.get(9)); // cpuCount stmt.setString(11, (String) serverData.get(10)); // serverModel stmt.setString(12, (String) serverData.get(11)); // serialNumber stmt.setInt(13, (Integer) serverData.get(12)); // installedMemory stmt.setString(14, (String) serverData.get(13)); // operIp stmt.setString(15, (String) serverData.get(14)); // operHostname stmt.setString(16, (String) serverData.get(15)); // mgmtIp stmt.setString(17, (String) serverData.get(16)); // mgmtHostname stmt.setString(18, (String) serverData.get(17)); // backupIp stmt.setString(19, (String) serverData.get(18)); // backupHostname stmt.setString(20, (String) serverData.get(19)); // nasIp stmt.setString(21, (String) serverData.get(20)); // nasHostname stmt.setString(22, (String) serverData.get(21)); // natAddr stmt.setString(23, (String) serverData.get(22)); // systemComments stmt.setString(24, (String) serverData.get(23)); // engineer stmt.setString(25, (String) serverData.get(24)); // mgrEntry stmt.setInt(26, (Integer) serverData.get(25)); // dmgrPort stmt.setString(27, (String) serverData.get(26)); // serverRack stmt.setString(28, (String) serverData.get(27)); // rackPosition stmt.setString(29, (String) serverData.get(28)); // owningDmgr if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } isComplete = (!(stmt.execute())); if (DEBUG) { DEBUGGER.debug("isComplete: {}", isComplete); } } catch (SQLException sqx) { throw new SQLException(sqx.getMessage(), sqx); } finally { if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return isComplete; }
From source file:com.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java
/** * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#getService(java.lang.String) *//* w w w . j ava 2 s . co m*/ public synchronized List<String> getService(final String attribute) throws SQLException { final String methodName = IServiceDataDAO.CNAME + "#getService(final String attribute) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("attribute: {}", attribute); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<String> responseData = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); // we dont know what we have here - it could be a guid or it could be a hostname // most commonly it'll be a guid, but we're going to search anyway stmt = sqlConn.prepareCall("{ CALL getServiceData(?) }"); stmt.setString(1, attribute); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("resultSet: {}", resultSet); } if (resultSet.next()) { resultSet.first(); responseData = new ArrayList<String>(Arrays.asList(resultSet.getString(1), // SERVICE_TYPE resultSet.getString(2), // NAME resultSet.getString(3), // REGION resultSet.getString(4), // NWPARTITION resultSet.getString(5), // STATUS resultSet.getString(6), // SERVERS resultSet.getString(7))); // DESCRIPTION if (DEBUG) { DEBUGGER.debug("responseData: {}", responseData); } } } } catch (SQLException sqx) { throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return responseData; }
From source file:jongo.jdbc.JDBCExecutor.java
/** * Utility method which registers in a CallableStatement object the different {@link jongo.jdbc.StoredProcedureParam} * instances in the given list. Returns a List of {@link jongo.jdbc.StoredProcedureParam} with all the OUT parameters * registered in the CallableStatement//from ww w .j ava2s . co m * @param cs the CallableStatement object where the parameters are registered. * @param params a list of {@link jongo.jdbc.StoredProcedureParam} * @return a list of OUT {@link jongo.jdbc.StoredProcedureParam} * @throws SQLException if we fail to register any of the parameters in the CallableStatement */ private static List<StoredProcedureParam> addParameters(final CallableStatement cs, final List<StoredProcedureParam> params) throws SQLException { List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>(); int i = 1; for (StoredProcedureParam p : params) { final Integer sqlType = p.getType(); if (p.isOutParameter()) { l.debug("Adding OUT parameter " + p.toString()); cs.registerOutParameter(i++, sqlType); outParams.add(p); } else { l.debug("Adding IN parameter " + p.toString()); switch (sqlType) { case Types.BIGINT: case Types.INTEGER: case Types.TINYINT: // case Types.NUMERIC: cs.setInt(i++, Integer.valueOf(p.getValue())); break; case Types.DATE: cs.setDate(i++, (Date) JongoUtils.parseValue(p.getValue())); break; case Types.TIME: cs.setTime(i++, (Time) JongoUtils.parseValue(p.getValue())); break; case Types.TIMESTAMP: cs.setTimestamp(i++, (Timestamp) JongoUtils.parseValue(p.getValue())); break; case Types.DECIMAL: cs.setBigDecimal(i++, (BigDecimal) JongoUtils.parseValue(p.getValue())); break; case Types.DOUBLE: cs.setDouble(i++, Double.valueOf(p.getValue())); break; case Types.FLOAT: cs.setLong(i++, Long.valueOf(p.getValue())); break; default: cs.setString(i++, p.getValue()); break; } } } return outParams; }
From source file:com.cws.esolutions.core.dao.impl.ServerDataDAOImpl.java
/** * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#getServer(java.lang.String) *//*from w w w. j av a 2 s . c om*/ public synchronized List<Object> getServer(final String attribute) throws SQLException { final String methodName = IServerDataDAO.CNAME + "#getServer(final String attribute) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("attribute: {}", attribute); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<Object> responseData = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); // we dont know what we have here - it could be a guid or it could be a hostname // most commonly it'll be a guid, but we're going to search anyway stmt = sqlConn.prepareCall("{ CALL retrServerData(?) }"); stmt.setString(1, attribute); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("resultSet: {}", resultSet); } if (resultSet.next()) { resultSet.first(); responseData = new ArrayList<Object>(Arrays.asList(resultSet.getString(1), // T1.SYSTEM_GUID resultSet.getString(2), // T1.SYSTEM_OSTYPE resultSet.getString(3), // T1.SYSTEM_STATUS resultSet.getString(4), // T1.SYSTEM_REGION resultSet.getString(5), // T1.NETWORK_PARTITION resultSet.getString(6), // T1.SYSTEM_TYPE resultSet.getString(7), // T1.DOMAIN_NAME resultSet.getString(8), // T1.CPU_TYPE resultSet.getInt(9), // T1.CPU_COUNT resultSet.getString(10), // T1.SERVER_RACK resultSet.getString(11), // T1.RACK_POSITION resultSet.getString(12), // T1.SERVER_MODEL resultSet.getString(13), // T1.SERIAL_NUMBER resultSet.getInt(14), // T1.INSTALLED_MEMORY resultSet.getString(15), // T1.OPER_IP resultSet.getString(16), // T1.OPER_HOSTNAME resultSet.getString(17), // T1.MGMT_IP resultSet.getString(18), // T1.MGMT_HOSTNAME resultSet.getString(19), // T1.BKUP_IP resultSet.getString(20), // T1.BKUP_HOSTNAME resultSet.getString(21), // T1.NAS_IP resultSet.getString(22), // T1.NAS_HOSTNAME resultSet.getString(23), // T1.NAT_ADDR resultSet.getString(24), // T1.COMMENTS resultSet.getString(25), // T1.ASSIGNED_ENGINEER resultSet.getTimestamp(26), // T1.ADD_DATE resultSet.getTimestamp(27), // T1.DELETE_DATE resultSet.getInt(28), // T1.DMGR_PORT resultSet.getString(29), // T1.OWNING_DMGR resultSet.getString(30), // T1.MGR_ENTRY resultSet.getString(31), // T2.GUID resultSet.getString(32))); // T2.NAME if (DEBUG) { DEBUGGER.debug("responseData: {}", responseData); } } } } catch (SQLException sqx) { throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return responseData; }
From source file:dbProcs.Getter.java
/** * Use to return the current progress of a class in JSON format with information like userid, user name and score * @param applicationRoot The current running context of the application * @param classId The identifier of the class to use in lookup * @return A JSON representation of a class's score in the order {id, username, userTitle, score, scale, place, order, * goldmedalcount, goldDisplay, silverMedalCount, silverDisplay, bronzeDisplay, bronzeMedalCount} *///from ww w .ja va 2s . com @SuppressWarnings("unchecked") public static String getJsonScore(String applicationRoot, String classId) { log.debug("classId: " + classId); String result = new String(); Connection conn = Database.getCoreConnection(applicationRoot); try { Encoder encoder = ESAPI.encoder(); //Returns User's: Name, # of Completed modules and Score CallableStatement callstmnt = null; if (ScoreboardStatus.getScoreboardClass().isEmpty() && !ScoreboardStatus.isClassSpecificScoreboard()) callstmnt = conn.prepareCall("call totalScoreboard()"); //Open Scoreboard not based on a class else { callstmnt = conn.prepareCall("call classScoreboard(?)"); //Class Scoreboard based on classId callstmnt.setString(1, classId); } //log.debug("Executing classScoreboard"); ResultSet resultSet = callstmnt.executeQuery(); JSONArray json = new JSONArray(); JSONObject jsonInner = new JSONObject(); int resultAmount = 0; int prevPlace = 0; int prevScore = 0; int prevGold = 0; int prevSilver = 0; int prevBronze = 0; float baseBarScale = 0; // float tieBreaker = 0; while (resultSet.next()) //For each user in a class { resultAmount++; jsonInner = new JSONObject(); if (resultSet.getString(1) != null) { int place = resultAmount; int score = resultSet.getInt(3); int goldMedals = resultSet.getInt(4); int silverMedals = resultSet.getInt(5); int bronzeMedals = resultSet.getInt(6); if (resultAmount == 1) //First Place is Returned First, so this will be the biggest bar on the scoreboard { int highscore = score; //log.debug("Current Highscore Listing is " + highscore); //Use the high score to scale the width of the bars for the whole scoreboard float maxBarScale = 1.02f; //High Score bar will have a scale of 1 //This will get used when a scale is added to the scoreboard baseBarScale = highscore * maxBarScale; //setting up variables for Tie Scenario Placings prevPlace = 1; prevScore = score; } else { //Does this score line match the one before (Score and Medals)? if so the place shouldnt change if (score == prevScore && goldMedals == prevGold && silverMedals == prevSilver && bronzeMedals == prevBronze) { place = prevPlace; tieBreaker = tieBreaker + 0.01f; } else { prevScore = score; prevPlace = place; prevGold = goldMedals; prevSilver = silverMedals; prevBronze = bronzeMedals; tieBreaker = 0; } } String displayMedal = new String("display: inline;"); String goldDisplayStyle = new String("display: none;"); String silverDisplayStyle = new String("display: none;"); String bronzeDisplayStyle = new String("display: none;"); if (goldMedals > 0) goldDisplayStyle = displayMedal; if (silverMedals > 0) silverDisplayStyle = displayMedal; if (bronzeMedals > 0) bronzeDisplayStyle = displayMedal; int barScale = (int) ((score * 100) / baseBarScale); //bar scale is the percentage the bar should be of the row's context (Highest Possible is depends on scale set in maxBarScale. eg: maxBarScale = 1.1 would mean the max scale would be 91% for a single row) String userMedalString = new String(); if (goldMedals > 0 || silverMedals > 0 || bronzeMedals > 0) { userMedalString += " holding "; if (goldMedals > 0) userMedalString += goldMedals + " gold"; if (silverMedals > 0) { if (goldMedals > 0) //Medals Before, puncuate { if (bronzeMedals > 0) //more medals after silver? Comma { userMedalString += ", "; } else //Say And { userMedalString += " and "; } } userMedalString += silverMedals + " silver"; } if (bronzeMedals > 0) { if (goldMedals > 0 || silverMedals > 0) //Medals Before? { userMedalString += " and "; } userMedalString += bronzeMedals + " bronze"; } //Say Medal(s) at the end of the string userMedalString += " medal"; if (goldMedals + silverMedals + bronzeMedals > 1) userMedalString += "s"; } jsonInner.put("id", new String(encoder.encodeForHTML(resultSet.getString(1)))); //User Id jsonInner.put("username", new String(encoder.encodeForHTML(resultSet.getString(2)))); //User Name jsonInner.put("userTitle", new String(encoder.encodeForHTML(resultSet.getString(2)) + " with " + score + " points" + userMedalString)); //User name encoded for title attribute jsonInner.put("score", new Integer(score)); //Score jsonInner.put("scale", barScale); //Scale of score bar jsonInner.put("place", place); //Place on board jsonInner.put("order", (place + tieBreaker)); //Order on board jsonInner.put("goldMedalCount", new Integer(goldMedals)); jsonInner.put("goldDisplay", goldDisplayStyle); jsonInner.put("silverMedalCount", new Integer(silverMedals)); jsonInner.put("silverDisplay", silverDisplayStyle); jsonInner.put("bronzeMedalCount", new Integer(bronzeMedals)); jsonInner.put("bronzeDisplay", bronzeDisplayStyle); //log.debug("Adding: " + jsonInner.toString()); json.add(jsonInner); } } if (resultAmount > 0) result = json.toString(); else result = new String(); } catch (SQLException e) { log.error("getJsonScore Failure: " + e.toString()); result = null; } catch (Exception e) { log.error("getJsonScore Unexpected Failure: " + e.toString()); result = null; } Database.closeConnection(conn); //log.debug("*** END getJsonScore ***"); return result; }
From source file:com.hackengine_er.muslumyusuf.DBOperations.java
/** * Calculates KKK vaccines dates and create a callable statement * * @param connection current connection/*from w ww . ja v a 2 s . c om*/ * @param date_of_birth of baby * @return a callableStatement included necessary informations or null if * catches SQLException or ParseException */ private CallableStatement calculateKKK(Connection connection, String date_of_birth) { try { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = dateFormat.parse(dateFormat.format(dateFormat.parse(date_of_birth))); Calendar calendar = Calendar.getInstance(); calendar.setTime(date); CallableStatement tempCall = connection.prepareCall(DbStoredProcedures.ADD_KKK_VACCINES); for (int i = 0; i < KKK_DATES.length; i++) { calendar.add(Calendar.DATE, KKK_DATES[i]); tempCall.setString(i + 1, dateFormat.format(calendar.getTime())); calendar.setTime(date); } return tempCall; } catch (SQLException | ParseException ex) { Logger.getLogger(DBOperations.class.getName()).log(Level.SEVERE, null, ex); } return null; }