List of usage examples for java.sql PreparedStatement setObject
void setObject(int parameterIndex, Object x) throws SQLException;
Sets the value of the designated parameter using the given object.
From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java
private boolean changeParticipation(Connection con, AccessToken token, EventExtId extId, RecurrenceId recurrenceId, ObmUser calendarOwner, Participation participation) throws SQLException, ParseException { PreparedStatement ps = null; String q = "UPDATE EventLink " + "SET eventlink_state = ?, eventlink_userupdate = ?, eventlink_comment = ? " + "WHERE eventlink_event_id IN " + "(" + "SELECT event_id " + "FROM Event e " + "LEFT JOIN EventException eexp ON e.event_id = eventexception_child_id " + "WHERE event_ext_id = ? " + "AND eexp.eventexception_date = ?" + ") AND " + "eventlink_entity_id IN " + "( SELECT userentity_entity_id FROM UserEntity WHERE userentity_user_id = ? )"; Integer loggedUserId = token.getObmId(); try {/* w w w . j a v a 2 s . c om*/ ps = con.prepareStatement(q); int idx = 1; ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, participation.getState().toString())); ps.setInt(idx++, loggedUserId); ps.setString(idx++, participation.getSerializedCommentToString()); ps.setString(idx++, extId.getExtId()); Date recId = new DateTime(recurrenceId.getRecurrenceId()); ps.setTimestamp(idx++, new Timestamp(recId.getTime())); ps.setInt(idx++, calendarOwner.getUid()); ps.execute(); if (ps.getUpdateCount() > 0) { return true; } } catch (SQLException e) { logger.error(e.getMessage(), e); throw e; } catch (ParseException e) { logger.error(e.getMessage(), e); throw e; } finally { obmHelper.cleanup(null, ps, null); } return false; }
From source file:org.ramadda.repository.database.DatabaseManager.java
/** * _more_/*from www . j a va 2 s. co m*/ * * @param table _more_ * @param colId _more_ * @param id _more_ * @param names _more_ * @param values _more_ * * @throws Exception _more_ */ public void update(String table, String colId, String id, String[] names, Object[] values) throws Exception { PreparedStatement statement = getPreparedStatement(SqlUtil.makeUpdate(table, colId, names)); for (int i = 0; i < values.length; i++) { Object value = values[i]; if (value == null) { statement.setNull(i + 1, java.sql.Types.VARCHAR); } else if (value instanceof Date) { setDate(statement, i + 1, (Date) value); } else if (value instanceof Boolean) { boolean b = ((Boolean) value).booleanValue(); statement.setInt(i + 1, (b ? 1 : 0)); } else { statement.setObject(i + 1, value); } } statement.setString(values.length + 1, id); statement.execute(); closeAndReleaseConnection(statement); }
From source file:at.alladin.rmbt.mapServer.MarkerResource.java
@Post("json") public String request(final String entity) { addAllowOrigin();// w w w . j a va2 s . c om final MapServerOptions mso = MapServerOptions.getInstance(); final Classification classification = Classification.getInstance(); JSONObject request = null; final JSONObject answer = new JSONObject(); if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); String lang = request.optString("language"); // Load Language Files for Client final List<String> langs = Arrays .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*")); if (langs.contains(lang)) labels = ResourceManager.getSysMsgBundle(new Locale(lang)); else lang = settings.getString("RMBT_DEFAULT_LANGUAGE"); // System.out.println(request.toString(4)); final JSONObject coords = request.getJSONObject("coords"); final int zoom; double geo_x = 0; double geo_y = 0; int size = 0; boolean useXY = false; boolean useLatLon = false; if (coords.has("x") && coords.has("y")) useXY = true; else if (coords.has("lat") && coords.has("lon")) useLatLon = true; if (coords.has("z") && (useXY || useLatLon)) { zoom = coords.optInt("z"); if (useXY) { geo_x = coords.optDouble("x"); geo_y = coords.optDouble("y"); } else if (useLatLon) { final double tmpLat = coords.optDouble("lat"); final double tmpLon = coords.optDouble("lon"); geo_x = GeoCalc.lonToMeters(tmpLon); geo_y = GeoCalc.latToMeters(tmpLat); // System.out.println(String.format("using %f/%f", geo_x, geo_y)); } if (coords.has("size")) size = coords.getInt("size"); if (zoom != 0 && geo_x != 0 && geo_y != 0) { double radius = 0; if (size > 0) radius = size * GeoCalc.getResFromZoom(256, zoom); // TODO use real tile size else radius = CLICK_RADIUS * GeoCalc.getResFromZoom(256, zoom); // TODO use real tile size final double geo_x_min = geo_x - radius; final double geo_x_max = geo_x + radius; final double geo_y_min = geo_y - radius; final double geo_y_max = geo_y + radius; String hightlightUUIDString = null; UUID highlightUUID = null; final JSONObject mapOptionsObj = request.getJSONObject("options"); String optionStr = mapOptionsObj.optString("map_options"); if (optionStr == null || optionStr.length() == 0) // set // default optionStr = "mobile/download"; final MapOption mo = mso.getMapOptionMap().get(optionStr); final List<SQLFilter> filters = new ArrayList<>(mso.getDefaultMapFilters()); filters.add(mso.getAccuracyMapFilter()); final JSONObject mapFilterObj = request.getJSONObject("filter"); final Iterator<?> keys = mapFilterObj.keys(); while (keys.hasNext()) { final String key = (String) keys.next(); if (mapFilterObj.get(key) instanceof Object) if (key.equals("highlight")) hightlightUUIDString = mapFilterObj.getString(key); else { final MapFilter mapFilter = mso.getMapFilterMap().get(key); if (mapFilter != null) filters.add(mapFilter.getFilter(mapFilterObj.getString(key))); } } if (hightlightUUIDString != null) try { highlightUUID = UUID.fromString(hightlightUUIDString); } catch (final Exception e) { highlightUUID = null; } if (conn != null) { PreparedStatement ps = null; ResultSet rs = null; final StringBuilder whereSQL = new StringBuilder(mo.sqlFilter); for (final SQLFilter sf : filters) whereSQL.append(" AND ").append(sf.where); final String sql = String.format("SELECT" + (useLatLon ? " geo_lat lat, geo_long lon" : " ST_X(t.location) x, ST_Y(t.location) y") + ", t.time, t.timezone, t.speed_download, t.speed_upload, t.ping_median, t.network_type," + " t.signal_strength, t.lte_rsrp, t.wifi_ssid, t.network_operator_name, t.network_operator," + " t.network_sim_operator, t.roaming_type, t.public_ip_as_name, " //TODO: sim_operator obsoled by sim_name + " pMob.shortname mobile_provider_name," // TODO: obsoleted by mobile_network_name + " prov.shortname provider_text, t.open_test_uuid," + " COALESCE(mnwk.shortname,mnwk.name) mobile_network_name," + " COALESCE(msim.shortname,msim.name) mobile_sim_name" + (highlightUUID == null ? "" : " , c.uid, c.uuid") + " FROM v_test t" + " LEFT JOIN mccmnc2name mnwk ON t.mobile_network_id=mnwk.uid" + " LEFT JOIN mccmnc2name msim ON t.mobile_sim_id=msim.uid" + " LEFT JOIN provider prov" + " ON t.provider_id=prov.uid" + " LEFT JOIN provider pMob" + " ON t.mobile_provider_id=pMob.uid" + (highlightUUID == null ? "" : " LEFT JOIN client c ON (t.client_id=c.uid AND c.uuid=?)") + " WHERE" + " %s" + " AND location && ST_SetSRID(ST_MakeBox2D(ST_Point(?,?), ST_Point(?,?)), 900913)" + " ORDER BY" + (highlightUUID == null ? "" : " c.uid ASC,") + " t.uid DESC" + " LIMIT 5", whereSQL); // System.out.println("SQL: " + sql); ps = conn.prepareStatement(sql); int i = 1; if (highlightUUID != null) ps.setObject(i++, highlightUUID); for (final SQLFilter sf : filters) i = sf.fillParams(i, ps); ps.setDouble(i++, geo_x_min); ps.setDouble(i++, geo_y_min); ps.setDouble(i++, geo_x_max); ps.setDouble(i++, geo_y_max); // System.out.println("SQL: " + ps.toString()); if (ps.execute()) { final Locale locale = new Locale(lang); final Format format = new SignificantFormat(2, locale); final JSONArray resultList = new JSONArray(); rs = ps.getResultSet(); while (rs.next()) { final JSONObject jsonItem = new JSONObject(); JSONArray jsonItemList = new JSONArray(); // RMBTClient Info if (highlightUUID != null && rs.getString("uuid") != null) jsonItem.put("highlight", true); final double res_x = rs.getDouble(1); final double res_y = rs.getDouble(2); final String openTestUUID = rs.getObject("open_test_uuid").toString(); jsonItem.put("lat", res_x); jsonItem.put("lon", res_y); jsonItem.put("open_test_uuid", "O" + openTestUUID); // marker.put("uid", uid); final Date date = rs.getTimestamp("time"); final String tzString = rs.getString("timezone"); final TimeZone tz = TimeZone.getTimeZone(tzString); final DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.MEDIUM, DateFormat.MEDIUM, locale); dateFormat.setTimeZone(tz); jsonItem.put("time_string", dateFormat.format(date)); final int fieldDown = rs.getInt("speed_download"); JSONObject singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_DOWNLOAD")); final String downloadString = String.format("%s %s", format.format(fieldDown / 1000d), labels.getString("RESULT_DOWNLOAD_UNIT")); singleItem.put("value", downloadString); singleItem.put("classification", Classification.classify(classification.THRESHOLD_DOWNLOAD, fieldDown)); // singleItem.put("help", "www.rtr.at"); jsonItemList.put(singleItem); final int fieldUp = rs.getInt("speed_upload"); singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_UPLOAD")); final String uploadString = String.format("%s %s", format.format(fieldUp / 1000d), labels.getString("RESULT_UPLOAD_UNIT")); singleItem.put("value", uploadString); singleItem.put("classification", Classification.classify(classification.THRESHOLD_UPLOAD, fieldUp)); // singleItem.put("help", "www.rtr.at"); jsonItemList.put(singleItem); final long fieldPing = rs.getLong("ping_median"); final int pingValue = (int) Math.round(rs.getDouble("ping_median") / 1000000d); singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_PING")); final String pingString = String.format("%s %s", format.format(pingValue), labels.getString("RESULT_PING_UNIT")); singleItem.put("value", pingString); singleItem.put("classification", Classification.classify(classification.THRESHOLD_PING, fieldPing)); // singleItem.put("help", "www.rtr.at"); jsonItemList.put(singleItem); final int networkType = rs.getInt("network_type"); final String signalField = rs.getString("signal_strength"); if (signalField != null && signalField.length() != 0) { final int signalValue = rs.getInt("signal_strength"); final int[] threshold = networkType == 99 || networkType == 0 ? classification.THRESHOLD_SIGNAL_WIFI : classification.THRESHOLD_SIGNAL_MOBILE; singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_SIGNAL")); singleItem.put("value", signalValue + " " + labels.getString("RESULT_SIGNAL_UNIT")); singleItem.put("classification", Classification.classify(threshold, signalValue)); jsonItemList.put(singleItem); } final String lteRsrpField = rs.getString("lte_rsrp"); if (lteRsrpField != null && lteRsrpField.length() != 0) { final int lteRsrpValue = rs.getInt("lte_rsrp"); final int[] threshold = classification.THRESHOLD_SIGNAL_RSRP; singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_LTE_RSRP")); singleItem.put("value", lteRsrpValue + " " + labels.getString("RESULT_LTE_RSRP_UNIT")); singleItem.put("classification", Classification.classify(threshold, lteRsrpValue)); jsonItemList.put(singleItem); } jsonItem.put("measurement", jsonItemList); jsonItemList = new JSONArray(); singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_NETWORK_TYPE")); singleItem.put("value", Helperfunctions.getNetworkTypeName(networkType)); jsonItemList.put(singleItem); if (networkType == 98 || networkType == 99) // mobile wifi or browser { String providerText = MoreObjects.firstNonNull( rs.getString("provider_text"), rs.getString("public_ip_as_name")); if (!Strings.isNullOrEmpty(providerText)) { if (providerText.length() > (MAX_PROVIDER_LENGTH + 3)) { providerText = providerText.substring(0, MAX_PROVIDER_LENGTH) + "..."; } singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_PROVIDER")); singleItem.put("value", providerText); jsonItemList.put(singleItem); } if (networkType == 99) // mobile wifi { if (highlightUUID != null && rs.getString("uuid") != null) // own test { final String ssid = rs.getString("wifi_ssid"); if (ssid != null && ssid.length() != 0) { singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_WIFI_SSID")); singleItem.put("value", ssid.toString()); jsonItemList.put(singleItem); } } } } else // mobile { String networkOperator = rs.getString("network_operator"); String mobileNetworkName = rs.getString("mobile_network_name"); String simOperator = rs.getString("network_sim_operator"); String mobileSimName = rs.getString("mobile_sim_name"); final int roamingType = rs.getInt("roaming_type"); //network if (!Strings.isNullOrEmpty(networkOperator)) { final String mobileNetworkString; if (roamingType != 2) { //not international roaming - display name of home network if (Strings.isNullOrEmpty(mobileSimName)) mobileNetworkString = networkOperator; else mobileNetworkString = String.format("%s (%s)", mobileSimName, networkOperator); } else { //international roaming - display name of network if (Strings.isNullOrEmpty(mobileSimName)) mobileNetworkString = networkOperator; else mobileNetworkString = String.format("%s (%s)", mobileNetworkName, networkOperator); } singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_MOBILE_NETWORK")); singleItem.put("value", mobileNetworkString); jsonItemList.put(singleItem); } //home network (sim) else if (!Strings.isNullOrEmpty(simOperator)) { final String mobileNetworkString; if (Strings.isNullOrEmpty(mobileSimName)) mobileNetworkString = simOperator; else mobileNetworkString = String.format("%s (%s)", mobileSimName, simOperator); /* if (!Strings.isNullOrEmpty(mobileProviderName)) { mobileNetworkString = mobileProviderName; } else { mobileNetworkString = simOperator; } */ singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_HOME_NETWORK")); singleItem.put("value", mobileNetworkString); jsonItemList.put(singleItem); } if (roamingType > 0) { singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_ROAMING")); singleItem.put("value", Helperfunctions.getRoamingType(labels, roamingType)); jsonItemList.put(singleItem); } } jsonItem.put("net", jsonItemList); resultList.put(jsonItem); if (resultList.length() == 0) System.out.println("Error getting Results."); // errorList.addError(MessageFormat.format(labels.getString("ERROR_DB_GET_CLIENT"), // new Object[] {uuid})); } answer.put("measurements", resultList); } else System.out.println("Error executing SQL."); } else System.out.println("No Database Connection."); } } else System.out.println("Expected request is missing."); } catch (final JSONException e) { System.out.println("Error parsing JSDON Data " + e.toString()); } catch (final SQLException e) { e.printStackTrace(); } else System.out.println("No Request."); return answer.toString(); }
From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java
public void testPrepareStatement() { String sql = "from (select count(1) from " + tableName + " where 'not?param?not?param' <> 'not_param??not_param' and ?=? " + " and 1=? and 2=? and 3.0=? and 4.0=? and 'test\\'string\"'=? and 5=? and ?=? " + " and date '2012-01-01' = date ?" + " ) t select '2011-03-25' ddate,'China',true bv, 10 num limit 10"; /////////////////////////////////////////////// //////////////////// correct testcase //////////////////// executed twice: once with the typed ps setters, once with the generic setObject ////////////////////////////////////////////// try {// www.j a v a2s.co m PreparedStatement ps = createPreapredStatementUsingSetXXX(sql); ResultSet res = ps.executeQuery(); assertPreparedStatementResultAsExpected(res); ps.close(); ps = createPreapredStatementUsingSetObject(sql); res = ps.executeQuery(); assertPreparedStatementResultAsExpected(res); ps.close(); } catch (Exception e) { e.printStackTrace(); fail(e.toString()); } /////////////////////////////////////////////// //////////////////// other failure testcases ////////////////////////////////////////////// // set nothing for prepared sql Exception expectedException = null; try { PreparedStatement ps = con.prepareStatement(sql); ps.executeQuery(); } catch (Exception e) { expectedException = e; } assertNotNull("Execute the un-setted sql statement should throw exception", expectedException); // set some of parameters for prepared sql, not all of them. expectedException = null; try { PreparedStatement ps = con.prepareStatement(sql); ps.setBoolean(1, true); ps.setBoolean(2, true); ps.executeQuery(); } catch (Exception e) { expectedException = e; } assertNotNull("Execute the invalid setted sql statement should throw exception", expectedException); // set the wrong type parameters for prepared sql. expectedException = null; try { PreparedStatement ps = con.prepareStatement(sql); // wrong type here ps.setString(1, "wrong"); assertTrue(true); ResultSet res = ps.executeQuery(); if (!res.next()) { throw new Exception("there must be a empty result set"); } } catch (Exception e) { expectedException = e; } assertNotNull("Execute the invalid setted sql statement should throw exception", expectedException); // setObject to the yet unknown type java.util.Date expectedException = null; try { PreparedStatement ps = con.prepareStatement(sql); ps.setObject(1, new Date()); ps.executeQuery(); } catch (Exception e) { expectedException = e; } assertNotNull("Setting to an unknown type should throw an exception", expectedException); }
From source file:org.globus.workspace.persistence.PersistenceAdapterImpl.java
public void setTerminationTime(int id, Calendar termTime) throws WorkspaceDatabaseException { if (this.dbTrace) { logger.trace("setTerminationTime(): " + Lager.id(id) + ", startTime = " + termTime); }/* w w w. j av a2s .c o m*/ Connection c = null; PreparedStatement pstmt = null; try { c = getConnection(); pstmt = c.prepareStatement(SQL_SET_TERMTIME); if (termTime != null) { pstmt.setObject(1, new Long(termTime.getTimeInMillis())); } else { pstmt.setInt(1, 0); } pstmt.setInt(2, id); final int updated = pstmt.executeUpdate(); if (this.dbTrace) { logger.trace(Lager.id(id) + ": updated " + updated + " rows"); } } catch (SQLException e) { logger.error("", e); throw new WorkspaceDatabaseException(e); } finally { try { if (pstmt != null) { pstmt.close(); } if (c != null) { returnConnection(c); } } catch (SQLException sql) { logger.error("SQLException in finally cleanup", sql); } } }
From source file:org.globus.workspace.persistence.PersistenceAdapterImpl.java
public void setStartTime(int id, Calendar startTime) throws WorkspaceDatabaseException { if (this.dbTrace) { logger.trace("setStartTime(): " + Lager.id(id) + ", startTime = " + startTime); }/*w w w. jav a 2 s .c o m*/ Connection c = null; PreparedStatement pstmt = null; try { c = getConnection(); pstmt = c.prepareStatement(SQL_SET_STARTTIME); if (startTime != null) { pstmt.setObject(1, new Long(startTime.getTimeInMillis())); } else { pstmt.setInt(1, 0); } pstmt.setInt(2, id); final int updated = pstmt.executeUpdate(); if (this.dbTrace) { logger.trace(Lager.id(id) + ": updated " + updated + " rows"); } } catch (SQLException e) { logger.error("", e); throw new WorkspaceDatabaseException(e); } finally { try { if (pstmt != null) { pstmt.close(); } if (c != null) { returnConnection(c); } } catch (SQLException sql) { logger.error("SQLException in finally cleanup", sql); } } }
From source file:org.obm.domain.dao.CalendarDaoJdbcImpl.java
@Override public List<FreeBusy> getFreeBusy(ObmDomain domain, FreeBusyRequest fbr) { String fb = "SELECT e.event_id, e.event_date, e.event_duration, event_allday" + ", e.event_repeatkind, e.event_repeatdays, e.event_repeatfrequence, e.event_endrepeat " + "FROM Event e " + "INNER JOIN EventLink att ON att.eventlink_event_id=e.event_id " + "LEFT JOIN EventLink organizer ON organizer.eventlink_event_id=e.event_id " + "AND organizer.eventlink_entity_id=? " + "WHERE att.eventlink_entity_id = ? " + "AND event_type=? " + "AND ((event_repeatkind != 'none' AND (event_endrepeat IS NULL OR event_endrepeat >= ?)) " + "OR (event_date >= ? AND event_date <= ?) ) " + "AND att.eventlink_state!=? " + "AND event_opacity=? " + "AND (event_privacy!=? OR organizer.eventlink_entity_id IS NOT NULL)"; PreparedStatement ps = null; ResultSet rs = null;// ww w .j a v a 2 s .c o m Connection con = null; List<FreeBusy> ret = new LinkedList<FreeBusy>(); try { con = obmHelper.getConnection(); for (Attendee att : fbr.getAttendees()) { logger.info("freebusy " + att.getEmail() + " dstart: " + fbr.getStart() + " dend: " + fbr.getEnd()); ObmUser attendee = userDao.findUser(att.getEmail(), domain); ObmUser organizer = userDao.findUser(fbr.getOwner(), domain); Calendar cal = getGMTCalendar(); if (attendee != null) { ps = null; rs = null; try { ps = con.prepareStatement(fb); int idx = 1; ps.setInt(idx++, organizer.getEntityId().getId()); ps.setInt(idx++, attendee.getEntityId().getId()); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, EventType.VEVENT.toString())); ps.setTimestamp(idx++, new Timestamp(fbr.getStart().getTime())); ps.setTimestamp(idx++, new Timestamp(fbr.getStart().getTime())); ps.setTimestamp(idx++, new Timestamp(fbr.getEnd().getTime())); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, State.DECLINED.toString())); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VOPACITY, EventOpacity.OPAQUE.toString())); ps.setInt(idx++, EventPrivacy.CONFIDENTIAL.toInteger()); rs = ps.executeQuery(); FreeBusy freebusy = new FreeBusy(); freebusy.setStart(fbr.getStart()); freebusy.setEnd(fbr.getEnd()); freebusy.setOwner(fbr.getOwner()); freebusy.setUid(fbr.getUid()); freebusy.setAtt(att); while (rs.next()) { EventRecurrence er = eventRecurrenceFromCursor(cal, rs); cal.setTimeInMillis(rs.getTimestamp("event_date").getTime()); if (er == null || RecurrenceKind.none.equals(er.getKind())) { cal.setTimeInMillis(rs.getTimestamp("event_date").getTime()); freebusy.addFreeBusyInterval(getFreeBusyInterval(cal.getTime(), rs)); } else { Set<Date> extDate = getAllDateEventException(con, new EventObmId(rs.getInt("event_id"))); List<Date> recurDates = recurrenceHelper.dateInInterval(er, cal.getTime(), fbr.getStart(), fbr.getEnd(), extDate); for (Date rd : recurDates) { FreeBusyInterval line = new FreeBusyInterval(); cal.setTimeInMillis(rd.getTime()); line.setStart(cal.getTime()); line.setAllDay(rs.getBoolean("event_allday")); line.setDuration(rs.getInt("event_duration")); freebusy.addFreeBusyInterval(line); } } } ret.add(freebusy); logger.info("freebusy found " + freebusy.getFreeBusyIntervals().size() + " events."); } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { obmHelper.cleanup(null, ps, rs); } } else { logger.info("User " + att.getEmail() + " doesn't exist"); } } } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { obmHelper.cleanup(con, null, null); } return ret; }
From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java
@Override public List<FreeBusy> getFreeBusy(ObmDomain domain, FreeBusyRequest fbr) { String fb = "SELECT e.event_id, e.event_date, e.event_duration, event_allday" + ", e.event_repeatkind, e.event_repeatdays, e.event_repeatfrequence, e.event_endrepeat " + "FROM Event e " + "INNER JOIN EventLink att ON att.eventlink_event_id=e.event_id " + "LEFT JOIN EventLink organizer ON organizer.eventlink_event_id=e.event_id " + "AND organizer.eventlink_entity_id=? " + "WHERE att.eventlink_entity_id = ? " + "AND event_type=? " + "AND ((event_repeatkind != 'none' AND (event_endrepeat IS NULL OR event_endrepeat >= ?)) " + "OR (event_date >= ? AND event_date <= ?) ) " + "AND att.eventlink_state!=? " + "AND event_opacity=? " + "AND (event_privacy!=? OR organizer.eventlink_entity_id IS NOT NULL)"; PreparedStatement ps = null; ResultSet rs = null;// w w w.jav a 2 s. c o m Connection con = null; List<FreeBusy> ret = new LinkedList<FreeBusy>(); try { con = obmHelper.getConnection(); for (Attendee att : fbr.getAttendees()) { logger.info("freebusy " + att.getEmail() + " dstart: " + fbr.getStart() + " dend: " + fbr.getEnd()); ObmUser attendee = userDao.findUser(att.getEmail(), domain); ObmUser organizer = userDao.findUser(fbr.getOwner(), domain); Calendar cal = getGMTCalendar(); if (attendee != null) { ps = null; rs = null; try { ps = con.prepareStatement(fb); int idx = 1; ps.setInt(idx++, organizer.getEntityId().getId()); ps.setInt(idx++, attendee.getEntityId().getId()); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, EventType.VEVENT.toString())); ps.setTimestamp(idx++, new Timestamp(fbr.getStart().getTime())); ps.setTimestamp(idx++, new Timestamp(fbr.getStart().getTime())); ps.setTimestamp(idx++, new Timestamp(fbr.getEnd().getTime())); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, State.DECLINED.toString())); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VOPACITY, EventOpacity.OPAQUE.toString())); ps.setInt(idx++, EventPrivacy.CONFIDENTIAL.toInteger()); rs = ps.executeQuery(); FreeBusy freebusy = new FreeBusy(); freebusy.setStart(fbr.getStart()); freebusy.setEnd(fbr.getEnd()); freebusy.setOwner(fbr.getOwner()); freebusy.setUid(fbr.getUid()); freebusy.setAtt(att); while (rs.next()) { EventRecurrence er = eventRecurrenceFromCursor(cal, rs); cal.setTimeInMillis(rs.getTimestamp("event_date").getTime()); if (er == null || RecurrenceKind.none.equals(er.getKind())) { cal.setTimeInMillis(rs.getTimestamp("event_date").getTime()); freebusy.addFreeBusyInterval(getFreeBusyInterval(cal.getTime(), rs)); } else { Set<Date> extDate = getAllDateEventException(con, new EventObmId(rs.getInt("event_id"))); List<Date> recurDates = ical4jHelper.dateInInterval(er, cal.getTime(), fbr.getStart(), fbr.getEnd(), extDate); for (Date rd : recurDates) { FreeBusyInterval line = new FreeBusyInterval(); cal.setTimeInMillis(rd.getTime()); line.setStart(cal.getTime()); line.setAllDay(rs.getBoolean("event_allday")); line.setDuration(rs.getInt("event_duration")); freebusy.addFreeBusyInterval(line); } } } ret.add(freebusy); logger.info("freebusy found " + freebusy.getFreeBusyIntervals().size() + " events."); } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { obmHelper.cleanup(null, ps, rs); } } else { logger.info("User " + att.getEmail() + " doesn't exist"); } } } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { obmHelper.cleanup(con, null, null); } return ret; }
From source file:org.globus.workspace.persistence.PersistenceAdapterImpl.java
public boolean updateResourcepoolEntry(String hostname, String pool, String networks, Integer memoryMax, Integer memoryAvail, Boolean active) throws WorkspaceDatabaseException { if (this.dbTrace) { logger.trace("updateResourcepoolEntry()"); }/*from w w w.ja v a 2 s .c o m*/ if (hostname == null) { throw new IllegalArgumentException("hostname may not be null"); } final StringBuilder sb = new StringBuilder(); final List<Object> params = new ArrayList<Object>(5); if (pool != null) { appendUpdatePair(sb, "resourcepool"); params.add(pool); } if (networks != null) { appendUpdatePair(sb, "associations"); params.add(networks); } if (memoryMax != null) { appendUpdatePair(sb, "maximum_memory"); params.add(memoryMax); } if (memoryAvail != null) { appendUpdatePair(sb, "available_memory"); params.add(memoryAvail); } if (active != null) { appendUpdatePair(sb, "active"); params.add(active); } if (params.isEmpty()) { throw new IllegalArgumentException("at least one updated field must be specified"); } Connection c = null; PreparedStatement pstmt = null; try { c = getConnection(); final String q = String.format(SQL_UPDATE_RESOURCE_POOL_ENTRY_SKELETAL, sb.toString()); if (this.dbTrace) { logger.trace("resourcepool_entry update query: " + q); } pstmt = c.prepareStatement(q); int paramIndex = 1; for (Object p : params) { pstmt.setObject(paramIndex, p); paramIndex++; } // add on the hostname param pstmt.setString(paramIndex, hostname); final int updated = pstmt.executeUpdate(); return updated >= 1; } catch (SQLException e) { logger.error("", e); throw new WorkspaceDatabaseException(e); } finally { try { if (pstmt != null) { pstmt.close(); } if (c != null) { returnConnection(c); } } catch (SQLException sql) { logger.error("SQLException in finally cleanup", sql); } } }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildTags.java
/** * //from w ww . j a v a 2s . c o m */ public void process() throws SQLException { int dupAgents = 0; int dupLocality = 0; int unknown = 0; boolean doAll = false; BasicSQLUtils.setDBConnection(dbConn); boolean doTrim = false; if (doTrim || doAll) { String trimNamesSQL = "UPDATE tagger SET first=TRIM(first),last=TRIM(last),company=TRIM(company),address1=TRIM(address1),address2=TRIM(address2),city=TRIM(city),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL); String removeQuote = "UPDATE tagger SET first=SUBSTRING_INDEX(first, '\"', -1),last=SUBSTRING_INDEX(last, '\"', -1),company=SUBSTRING_INDEX(company, '\"', -1),address1=SUBSTRING_INDEX(address1, '\"', -1)," + "address2=SUBSTRING_INDEX(address2, '\"', -1),city=SUBSTRING_INDEX(city, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote); String trimNamesSQL2 = "UPDATE tag SET city=TRIM(city),county=TRIM(county),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL2); String removeQuote2 = "UPDATE tag SET city=SUBSTRING_INDEX(city, '\"', -1), county=SUBSTRING_INDEX(county, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote2); } IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); idMapperMgr.setDBs(srcDBConn2, dbConn); IdHashMapper agentMapper; Division division = (Division) session.get(Division.class, 2); initialPrepareStatements(); BasicSQLUtils.update(srcDBConn, "UPDATE tag SET `Date` = null WHERE Date = '0000-00-00'"); //IdMapperMgr.setSkippingOldTableCheck(true); boolean doAgents = false; if (doAgents || doAll) { agentMapper = new IdTableMapper("agent", "AgentID", false, false); String sql = "SELECT first, last, company, address1, address2, city, state, country, zip, phone, fax, enail, tnum FROM tagger ORDER BY tnum"; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Agents..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; while (rs.next()) { String first = rs.getString(1); String last = rs.getString(2); String company = rs.getString(3); String addr1 = rs.getString(4); String addr2 = rs.getString(5); String city = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); String zip = rs.getString(9); String phone = rs.getString(10); String fax = rs.getString(11); String email = rs.getString(12); Integer oldId = rs.getInt(13); if (oldId == null) { log.error("Null primary Id: " + last + " " + first); continue; } Agent agent = getAgent(first, last, city, state); Integer agentId = null; if (agent == null) { agent = new Agent(); agent.initialize(); agent.setFirstName(first); agent.setLastName(last); agent.setEmail(email); agent.setRemarks(company); agent.setDivision(division); Address addr = new Address(); addr.initialize(); addr.setAddress(addr1); addr.setAddress2(addr2); addr.setCity(city); addr.setState(state); addr.setCountry(country); addr.setPostalCode(zip); addr.setPhone1(phone); addr.setFax(fax); agent.getAddresses().add(addr); addr.setAgent(agent); Transaction trans = null; try { trans = session.beginTransaction(); session.saveOrUpdate(agent); session.saveOrUpdate(addr); trans.commit(); agentId = agent.getId(); } catch (Exception ex) { ex.printStackTrace(); try { if (trans != null) trans.rollback(); } catch (Exception ex2) { ex2.printStackTrace(); } } } else { agentId = agent.getId(); dupAgents++; //System.out.println("Found Agent: "+first+", "+last); } agentMapper.put(oldId, agentId); cnt++; if (cnt % 500 == 0) { System.out.println("Agents: " + cnt); } if (cnt % 400 == 0) { HibernateUtil.closeSession(); session = HibernateUtil.getCurrentSession(); hibSession = new HibernateDataProviderSession(session); } } rs.close(); stmt.close(); division = (Division) session.get(Division.class, 2); } else { //agentMapper = idMapperMgr.addTableMapper("agent", "AgentID", false); IdHashMapper.setEnableDelete(false); agentMapper = new IdTableMapper("agent", "AgentID", null, false, false); } System.out.println("Duplicated Agent: " + dupAgents); boolean doTags = true; if (doTags || doAll) { HashMap<String, Integer> localityHash = new HashMap<String, Integer>(); HashMap<Integer, String> geoFullNameHash = new HashMap<Integer, String>(); int divId = 2; int dspId = 3; int colId = 4; String sql = "SELECT t.tagid, t.`date`, t.wild, t.gender, t.city, t.county, t.state, t.country, t.zip, t.observations, t.lat, t.lon, t.sunangle, p.tnum " + "FROM tag AS t Inner Join page AS p ON t.page = p.page "; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Tags..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; log.debug("Done querying for Tags..."); Calendar cal = Calendar.getInstance(); Timestamp ts = new Timestamp(cal.getTime().getTime()); String common = "TimestampCreated, Version, CreatedByAgentID"; String coStr = String.format( "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?)", common); String ceStr = String.format( "INSERT INTO collectingevent (StartDate, Method, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?,?)", common); String lcStr = String.format( "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", common); String clStr = String.format( "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)", common); PreparedStatement coStmt = dbConn.prepareStatement(coStr); PreparedStatement ceStmt = dbConn.prepareStatement(ceStr); PreparedStatement lcStmt = dbConn.prepareStatement(lcStr); PreparedStatement clStmt = dbConn.prepareStatement(clStr); int recNum = 1; while (rs.next()) { String tag = rs.getString(1); if (tag != null && tag.startsWith("ERR")) continue; Date date = rs.getDate(2); String wild = rs.getString(3); String gender = rs.getString(4); String city = rs.getString(5); String county = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); //String zip = rs.getString(9); String obs = rs.getString(10); double lat = rs.getDouble(11); double lon = rs.getDouble(12); double angle = rs.getDouble(13); Integer taggerId = rs.getInt(14); String locName = null; String fullName = null; Integer locId = null; Integer geoId = getGeography(country, state, county); if (geoId != null) { //locName = localityHash.get(geoId); fullName = geoFullNameHash.get(geoId); if (fullName == null) { fullName = BasicSQLUtils .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId); geoFullNameHash.put(geoId, fullName); } if (StringUtils.isNotEmpty(city)) { locName = city + ", " + fullName; } else { locName = fullName; } locId = localityHash.get(locName); } else { unknown++; fullName = "Unknown"; locName = buildLocalityName(city, fullName); geoId = 27507; // Unknown locId = localityHash.get(locName); //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]"); } if (locId == null) { lcStmt.setDouble(1, lat); lcStmt.setDouble(2, lon); lcStmt.setByte(3, (byte) 0); lcStmt.setString(4, Double.toString(lat)); lcStmt.setString(5, Double.toString(lon)); lcStmt.setString(6, "Point"); lcStmt.setInt(7, dspId); lcStmt.setDouble(8, angle); lcStmt.setString(9, locName); lcStmt.setObject(10, geoId); lcStmt.setTimestamp(11, ts); lcStmt.setInt(12, 1); lcStmt.setInt(13, 1); lcStmt.executeUpdate(); locId = BasicSQLUtils.getInsertedId(lcStmt); localityHash.put(locName, locId); } else { dupLocality++; } // (StartDate, Method, DisciplineID, LocalityID ceStmt.setDate(1, date); ceStmt.setString(2, wild); ceStmt.setInt(3, dspId); ceStmt.setInt(4, locId); ceStmt.setTimestamp(5, ts); ceStmt.setInt(6, 1); ceStmt.setInt(7, 1); ceStmt.executeUpdate(); Integer ceId = BasicSQLUtils.getInsertedId(ceStmt); //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId coStmt.setString(1, String.format("%09d", recNum++)); coStmt.setString(2, tag); coStmt.setString(3, gender); coStmt.setString(4, obs); coStmt.setInt(5, colId); coStmt.setInt(6, colId); coStmt.setInt(7, ceId); coStmt.setTimestamp(8, ts); coStmt.setInt(9, 1); coStmt.setInt(10, 1); coStmt.executeUpdate(); //Integer coId = BasicSQLUtils.getInsertedId(coStmt); //Integer coltrId = null; if (taggerId != null) { Integer agentId = agentMapper.get(taggerId); //System.out.println(agentId); if (agentId != null) { // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID clStmt.setInt(1, 0); clStmt.setBoolean(2, true); clStmt.setInt(3, ceId); clStmt.setInt(4, divId); clStmt.setInt(5, agentId); clStmt.setTimestamp(6, ts); clStmt.setInt(7, 1); clStmt.setInt(8, 1); clStmt.executeUpdate(); //coltrId = BasicSQLUtils.getInsertedId(clStmt); //BasicSQLUtils.getInsertedId(clStmt); } else { log.debug("Couldn't find Agent in DB for tagger id (tnum): " + taggerId + " AgentID:: " + agentId); } } else { log.debug("Couldn't find Mapped Id for tagger id (tnum): " + taggerId); } cnt++; if (cnt % 1000 == 0) { System.out.println("Col Obj: " + cnt); } } coStmt.close(); ceStmt.close(); lcStmt.close(); clStmt.close(); System.out.println("Duplicated Agent: " + dupAgents); System.out.println("Duplicated Localities: " + dupLocality); System.out.println("Unknown Localities: " + unknown); System.out.println("Localities: " + BasicSQLUtils.getCountAsInt(dbConn, "SELECT COUNT(*) FROM locality")); } }