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:net.duckling.ddl.service.resource.dao.ResourceDAOImpl.java
@Override public synchronized int create(final Resource res) { GeneratedKeyHolder keyHolder = new GeneratedKeyHolder(); this.getJdbcTemplate().update(new PreparedStatementCreator() { @Override// w w w. j a va 2s. co m public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement ps = null; ps = conn.prepareStatement(SQL_CREATE, PreparedStatement.RETURN_GENERATED_KEYS); int i = 0; //tid,item_type,title,creator,creator_name,create_time,last_editor,last_editor_name,last_edit_time,last_version,tags,marked_users,bid ps.setInt(++i, res.getTid()); ps.setString(++i, res.getItemType()); ps.setString(++i, res.getTitle()); ps.setString(++i, res.getCreator()); ps.setTimestamp(++i, new Timestamp(res.getCreateTime().getTime())); ps.setString(++i, res.getLastEditor()); ps.setString(++i, res.getLastEditorName()); ps.setTimestamp(++i, new Timestamp(res.getLastEditTime().getTime())); ps.setInt(++i, res.getLastVersion()); ps.setString(++i, JsonUtil.getJSONString(res.getTagMap())); String fileType = (res.getFileType() != null) ? res.getFileType().toLowerCase() : res.getFileType(); ps.setString(++i, fileType); ps.setObject(++i, res.getMarkedUserSet()); ps.setInt(++i, res.getBid()); ps.setInt(++i, res.getOrderType()); ps.setString(++i, res.getStatus()); ps.setLong(++i, res.getSize()); ps.setBoolean(++i, res.isShared()); return ps; } }, keyHolder); Number key = keyHolder.getKey(); return (key == null) ? -1 : key.intValue(); }
From source file:org.rhq.enterprise.server.measurement.util.MeasurementDataManagerUtility.java
private PreparedStatement getFullQuery(String methodName, Connection connection, long beginTime, long endTime, int numDataPoints, String conditions, Object... bindParameters) throws SQLException { if (LOG.isDebugEnabled()) { LOG.debug("getFullQuery for " + methodName); }/* ww w . j ava2s . c o m*/ // ensure valid number of data points numDataPoints = (numDataPoints <= 0) ? DEFAULT_NUM_DATA_POINTS : numDataPoints; long interval = (endTime - beginTime) / numDataPoints; String valuesClause; if (isRawTimePeriod(beginTime)) { valuesClause = "avg(value), max(value) as peak, min(value) as low"; } else { valuesClause = "avg(value), max(maxvalue) as peak, min(minvalue) as low"; } StringBuilder unions = new StringBuilder(); String[] tables = MeasurementDataManagerUtility.getTables(beginTime, endTime); for (String table : tables) { if (unions.length() != 0) { unions.append(" UNION ALL \n "); } unions.append(getTableString(table, valuesClause, conditions)); } String sql = "SELECT timestamp, max(av), max(peak), max(low) FROM ( \n" + " (SELECT timestamp, avg(value) as av, max(value) as peak, min(value) as low FROM (\n" + unions.toString() + " ) data GROUP BY timestamp) \n" + " UNION ALL (select ? + (? * i) as timestamp, null as av, null as peak, null as low from RHQ_numbers where i < ?) ) alldata \n" + "GROUP BY timestamp ORDER BY timestamp"; PreparedStatement ps = connection.prepareStatement(sql); StringBuilder fullSql = null; if (LOG.isDebugEnabled()) { fullSql = new StringBuilder(sql); } int i = 1; for (int tableIndex = 0; tableIndex < tables.length; tableIndex++) { ps.setLong(i++, beginTime); ps.setLong(i++, interval); // 2) interval ps.setInt(i++, numDataPoints); // 3) points ps.setLong(i++, interval); // 4) interval if (LOG.isDebugEnabled()) { replaceNextPlaceHolders(fullSql, beginTime, interval, numDataPoints, interval); } for (Object param : bindParameters) { if (param.getClass().isArray()) { int length = Array.getLength(param); if (length > MAX_IN_CLAUSE_PARAMS) { throw new IllegalArgumentException("Number of resource id's must be less than or equal to " + MAX_IN_CLAUSE_PARAMS + "."); } for (int x = 0; x < length; x++) { Object bindValue = Array.get(param, x); ps.setObject(i++, bindValue); if (LOG.isDebugEnabled()) { replaceNextPlaceHolders(fullSql, bindValue); } } } else { ps.setObject(i++, param); if (LOG.isDebugEnabled()) { replaceNextPlaceHolders(fullSql, param); } } } } ps.setLong(i++, beginTime); // 1) begin ps.setLong(i++, interval); // 2) interval ps.setInt(i++, numDataPoints); // 3) points if (LOG.isDebugEnabled()) { replaceNextPlaceHolders(fullSql, beginTime, interval, numDataPoints); LOG.debug(fullSql); } return ps; }
From source file:at.alladin.rmbt.controlServer.RegistrationResource.java
@Post("json") public String request(final String entity) { long startTime = System.currentTimeMillis(); final String secret = getContext().getParameters().getFirstValue("RMBT_SECRETKEY"); addAllowOrigin();/* w ww . j a v a2 s . c o m*/ JSONObject request = null; final ErrorList errorList = new ErrorList(); final JSONObject answer = new JSONObject(); String answerString; final String clientIpRaw = getIP(); final InetAddress clientAddress = InetAddresses.forString(clientIpRaw); final String clientIpString = InetAddresses.toAddrString(clientAddress); System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST"), clientIpRaw)); final String geoIpCountry = GeoIPHelper.lookupCountry(clientAddress); // public_ip_asn final Long asn = Helperfunctions.getASN(clientAddress); // public_ip_as_name // country_asn (2 digit country code of AS, eg. AT or EU) final String asName; final String asCountry; if (asn == null) { asName = null; asCountry = null; } else { asName = Helperfunctions.getASName(asn); asCountry = Helperfunctions.getAScountry(asn); } if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); int typeId = 0; final 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)) { errorList.setLanguage(lang); labels = ResourceManager.getSysMsgBundle(new Locale(lang)); } // System.out.println(request.toString(4)); if (conn != null) { final Client clientDb = new Client(conn); if (!request.optString("type").isEmpty()) { typeId = clientDb.getTypeId(request.getString("type")); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } final List<String> clientNames = Arrays .asList(settings.getString("RMBT_CLIENT_NAME").split(",\\s*")); final List<String> clientVersions = Arrays .asList(settings.getString("RMBT_VERSION_NUMBER").split(",\\s*")); if (clientNames.contains(request.optString("client")) && clientVersions.contains(request.optString("version")) && typeId > 0) { UUID uuid = null; final String uuidString = request.optString("uuid", ""); if (uuidString.length() != 0) uuid = UUID.fromString(uuidString); final String clientName = request.getString("client"); final String clientVersion = request.getString("version"); String timeZoneId = request.getString("timezone"); // String tmpTimeZoneId = timeZoneId; final long clientTime = request.getLong("time"); final Timestamp clientTstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); final JSONObject location = request.optJSONObject("location"); long geotime = 0; double geolat = 0; double geolong = 0; float geoaccuracy = 0; double geoaltitude = 0; float geobearing = 0; float geospeed = 0; String geoprovider = ""; if (!request.isNull("location")) { geotime = location.optLong("time", 0); geolat = location.optDouble("lat", 0); geolong = location.optDouble("long", 0); geoaccuracy = (float) location.optDouble("accuracy", 0); geoaltitude = location.optDouble("altitude", 0); geobearing = (float) location.optDouble("bearing", 0); geospeed = (float) location.optDouble("speed", 0); geoprovider = location.optString("provider", ""); } Calendar timeWithZone = null; if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); long clientUid = 0; /* * if (uuid == null) { * clientDb.setTimeZone(timeWithZone); * clientDb.setTime(tstamp); * clientDb.setClient_type_id(typeId); uuid = * clientDb.storeClient(); if (clientDb.hasError()) { * errorList.addError(clientDb.getError()); } else { * answer.put("uuid", uuid.toString()); } } */ if (errorList.getLength() == 0 && uuid != null) { clientUid = clientDb.getClientByUuid(uuid); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } if (clientUid > 0) { final String testUuid = UUID.randomUUID().toString(); final String testOpenUuid = UUID.randomUUID().toString(); boolean testServerEncryption = true; // default is // true // hack for android api <= 10 (2.3.x) // using encryption with test doesn't work if (request.has("plattform") && request.optString("plattform").equals("Android")) if (request.has("api_level")) { final String apiLevelString = request.optString("api_level"); try { final int apiLevel = Integer.parseInt(apiLevelString); if (apiLevel <= 10) testServerEncryption = false; } catch (final NumberFormatException e) { } } final String serverType; if (request.optString("client").equals("RMBTws")) serverType = "RMBTws"; else serverType = "RMBT"; final Boolean ipv6; if (clientAddress instanceof Inet6Address) ipv6 = true; else if (clientAddress instanceof Inet4Address) ipv6 = false; else // should never happen, unless ipv > 6 is available ipv6 = null; final TestServer server = getNearestServer(errorList, geolat, geolong, geotime, clientIpString, asCountry, geoIpCountry, serverType, testServerEncryption, ipv6); try { if (server == null) throw new JSONException("could not find server"); if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); answer.put("test_server_address", server.address); answer.put("test_server_port", server.port); answer.put("test_server_name", server.name); answer.put("test_server_encryption", testServerEncryption); answer.put("test_duration", getSetting("rmbt_duration")); answer.put("test_numthreads", getSetting("rmbt_num_threads")); answer.put("test_numpings", getSetting("rmbt_num_pings")); answer.put("client_remote_ip", clientIpString); final String resultUrl = new Reference(getURL(), settings.getString("RMBT_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_url", resultUrl); final String resultQoSUrl = new Reference(getURL(), settings.getString("RMBT_QOS_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_qos_url", resultQoSUrl); } catch (final JSONException e) { System.out.println("Error generating Answer " + e.toString()); errorList.addError("ERROR_RESPONSE_JSON"); } if (errorList.getLength() == 0) try { PreparedStatement st; st = conn.prepareStatement( "INSERT INTO test(time, uuid, open_test_uuid, client_id, client_name, client_version, client_software_version, client_language, client_public_ip, client_public_ip_anonymized, country_geoip, server_id, port, use_ssl, timezone, client_time, duration, num_threads_requested, status, software_revision, client_test_counter, client_previous_test_status, public_ip_asn, public_ip_as_name, country_asn, public_ip_rdns, run_ndt)" + "VALUES(NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS); int i = 1; // uuid st.setObject(i++, UUID.fromString(testUuid)); // open_test_uuid st.setObject(i++, UUID.fromString(testOpenUuid)); // client_id st.setLong(i++, clientUid); // client_name st.setString(i++, clientName); // client_version st.setString(i++, clientVersion); // client_software_version st.setString(i++, request.optString("softwareVersion", null)); // client_language st.setString(i++, lang); // client_public_ip st.setString(i++, clientIpString); // client_public_ip_anonymized st.setString(i++, Helperfunctions.anonymizeIp(clientAddress)); // country_geoip (2digit country code derived from public IP of client) st.setString(i++, geoIpCountry); // server_id st.setInt(i++, server.id); // port st.setInt(i++, server.port); // use_ssl st.setBoolean(i++, testServerEncryption); // timezone (of client) st.setString(i++, timeZoneId); // client_time (local time of client) st.setTimestamp(i++, clientTstamp, timeWithZone); // duration (requested) st.setInt(i++, Integer.parseInt(getSetting("rmbt_duration"))); // num_threads_requested st.setInt(i++, Integer.parseInt(getSetting("rmbt_num_threads"))); // status (of test) st.setString(i++, "STARTED"); //was "RUNNING" before // software_revision (of client) st.setString(i++, request.optString("softwareRevision", null)); // client_test_counter (number of tests the client has performed) final int testCounter = request.optInt("testCounter", -1); if (testCounter == -1) // older clients did not support testCounter st.setNull(i++, Types.INTEGER); else st.setLong(i++, testCounter); // client_previous_test_status (outcome of previous test) st.setString(i++, request.optString("previousTestStatus", null)); // AS name if (asn == null) st.setNull(i++, Types.BIGINT); else st.setLong(i++, asn); if (asName == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asName); // AS country if (asCountry == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asCountry); //public_ip_rdns String reverseDNS = Helperfunctions.reverseDNSLookup(clientAddress); if (reverseDNS == null || reverseDNS.isEmpty()) st.setNull(i++, Types.VARCHAR); else { reverseDNS = reverseDNS.replaceFirst("\\.$", ""); st.setString(i++, reverseDNS); // cut off last dot (#332) } // run_ndt if (request.has("ndt")) st.setBoolean(i++, request.getBoolean("ndt")); else st.setNull(i++, Types.BOOLEAN); final int affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_STORE_TEST"); else { long key = 0; final ResultSet rs = st.getGeneratedKeys(); if (rs.next()) // Retrieve the auto generated // key(s). key = rs.getLong(1); rs.close(); final PreparedStatement getProviderSt = conn .prepareStatement("SELECT rmbt_set_provider_from_as(?)"); getProviderSt.setLong(1, key); String provider = null; if (getProviderSt.execute()) { final ResultSet rs2 = getProviderSt.getResultSet(); if (rs2.next()) provider = rs2.getString(1); } if (provider != null) answer.put("provider", provider); final PreparedStatement testSlotStatement = conn .prepareStatement("SELECT rmbt_get_next_test_slot(?)"); testSlotStatement.setLong(1, key); int testSlot = -1; if (testSlotStatement.execute()) { final ResultSet rs2 = testSlotStatement.getResultSet(); if (rs2.next()) testSlot = rs2.getInt(1); } if (testSlot < 0) errorList.addError("ERROR_DB_STORE_GENERAL"); else { final String data = testUuid + "_" + testSlot; final String hmac = Helperfunctions.calculateHMAC(secret, data); if (hmac.length() == 0) errorList.addError("ERROR_TEST_TOKEN"); final String token = data + "_" + hmac; final PreparedStatement updateSt = conn .prepareStatement("UPDATE test SET token = ? WHERE uid = ?"); updateSt.setString(1, token); updateSt.setLong(2, key); updateSt.executeUpdate(); answer.put("test_token", token); answer.put("test_uuid", testUuid); answer.put("test_id", key); final long now = System.currentTimeMillis(); int wait = testSlot - (int) (now / 1000); if (wait < 0) wait = 0; answer.put("test_wait", wait); if (geotime != 0 && geolat != 0 && geolong != 0) { final GeoLocation clientLocation = new GeoLocation(conn); clientLocation.setTest_id(key); final Timestamp geotstamp = java.sql.Timestamp .valueOf(new Timestamp(geotime).toString()); clientLocation.setTime(geotstamp, timeZoneId); clientLocation.setAccuracy(geoaccuracy); clientLocation.setAltitude(geoaltitude); clientLocation.setBearing(geobearing); clientLocation.setSpeed(geospeed); clientLocation.setProvider(geoprovider); clientLocation.setGeo_lat(geolat); clientLocation.setGeo_long(geolong); clientLocation.storeLocation(); if (clientLocation.hasError()) errorList.addError(clientLocation.getError()); } } } st.close(); } catch (final SQLException e) { errorList.addError("ERROR_DB_STORE_GENERAL"); e.printStackTrace(); } } else errorList.addError("ERROR_CLIENT_UUID"); } else errorList.addError("ERROR_CLIENT_VERSION"); } else errorList.addError("ERROR_DB_CONNECTION"); // System.out.println(answer.toString(4)); } catch (final JSONException e) { errorList.addError("ERROR_REQUEST_JSON"); System.out.println("Error parsing JSDON Data " + e.toString()); } else errorList.addErrorString("Expected request is missing."); try { answer.putOpt("error", errorList.getList()); } catch (final JSONException e) { System.out.println("Error saving ErrorList: " + e.toString()); } answerString = answer.toString(); long elapsedTime = System.currentTimeMillis() - startTime; System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST_SUCCESS"), clientIpRaw, Long.toString(elapsedTime))); return answerString; }
From source file:com.thinkmore.framework.orm.hibernate.SimpleHibernateDao.java
public void setParameters(PreparedStatement ps, int j, Object value) throws SQLException { if (value != null) { if (value instanceof java.lang.Integer) { ps.setInt(j, (Integer) value); } else if (value instanceof java.lang.Long) { ps.setLong(j, (Long) value); } else if (value instanceof java.util.Date) { ps.setTimestamp(j, new java.sql.Timestamp(((Date) value).getTime())); } else if (value instanceof java.sql.Date) { ps.setDate(j, new java.sql.Date(((Date) value).getTime())); } else if (value instanceof java.lang.String) { ps.setString(j, value.toString()); } else if (value instanceof java.lang.Double) { ps.setDouble(j, (Double) value); } else if (value instanceof java.lang.Byte) { ps.setByte(j, (Byte) value); } else if (value instanceof java.lang.Character) { ps.setString(j, value.toString()); } else if (value instanceof java.lang.Float) { ps.setFloat(j, (Float) value); } else if (value instanceof java.lang.Boolean) { ps.setBoolean(j, (Boolean) value); } else if (value instanceof java.lang.Short) { ps.setShort(j, (Short) value); } else {/* w w w .j a v a 2 s . co m*/ ps.setObject(j, value); } } else { ps.setNull(j, Types.NULL); } }
From source file:org.entrystore.rowstore.store.impl.PgDataset.java
/** * @see Dataset#populate(File)/*from w w w . ja v a2s.c om*/ */ @Override public boolean populate(File csvFile) throws IOException { if (csvFile == null) { throw new IllegalArgumentException("Argument must not be null"); } String dataTable = getDataTable(); if (dataTable == null) { log.error("Dataset has no data table assigned"); return false; } setStatus(EtlStatus.PROCESSING); Connection conn = null; PreparedStatement stmt = null; CSVReader cr = null; try { conn = rowstore.getConnection(); cr = new CSVReader(new FileReader(csvFile), ',', '"'); int lineCount = 0; String[] labels = null; String[] line; conn.setAutoCommit(false); stmt = conn.prepareStatement("INSERT INTO " + dataTable + " (rownr, data) VALUES (?, ?)"); while ((line = cr.readNext()) != null) { if (lineCount == 0) { labels = line; } else { JSONObject jsonLine = null; try { jsonLine = csvLineToJsonObject(line, labels); } catch (Exception e) { log.error(e.getMessage()); log.info("Rolling back transaction"); conn.rollback(); setStatus(EtlStatus.ERROR); return false; } stmt.setInt(1, lineCount); PGobject jsonb = new PGobject(); jsonb.setType("jsonb"); jsonb.setValue(jsonLine.toString()); stmt.setObject(2, jsonb); log.debug("Adding to batch: " + stmt); stmt.addBatch(); // we execute the batch every 100th line if ((lineCount % 100) == 0) { log.debug("Executing: " + stmt); stmt.executeBatch(); } } lineCount++; } // in case there are some inserts left to be sent (i.e. // batch size above was smaller than 100 when loop ended) log.debug("Executing: " + stmt); stmt.executeBatch(); // we create an index over the data createIndex(conn, dataTable, labels); // we commit the transaction and free the resources of the statement conn.commit(); setStatus(EtlStatus.AVAILABLE); return true; } catch (SQLException e) { SqlExceptionLogUtil.error(log, e); try { log.info("Rolling back transaction"); conn.rollback(); } catch (SQLException e1) { SqlExceptionLogUtil.error(log, e1); } setStatus(EtlStatus.ERROR); return false; } finally { if (cr != null) { try { cr.close(); } catch (IOException e) { log.error(e.getMessage()); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { SqlExceptionLogUtil.error(log, e); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { SqlExceptionLogUtil.error(log, e); } } } }
From source file:com.splout.db.engine.MySQLOutputFormat.java
@Override public void write(ITuple tuple) throws IOException, InterruptedException { int partition = (Integer) tuple.get(PARTITION_TUPLE_FIELD); try {// w w w.ja v a2 s. c om /* * Key performance trick: Cache PreparedStatements when possible. We will have one PreparedStatement per each * different Tuple Schema (table). */ Map<String, PreparedStatement> stMap = stCache.get(partition); PreparedStatement pS = stMap.get(tuple.getSchema().getName()); if (pS == null) { Connection conn = connCache.get(partition); // Create a PreparedStatement according to the received Tuple String preparedStatement = "INSERT INTO " + tuple.getSchema().getName() + " VALUES ("; // NOTE: tuple.getSchema().getFields().size() - 1 : quick way of skipping "_partition" fields here for (int i = 0; i < tuple.getSchema().getFields().size() - 1; i++) { preparedStatement += "?, "; } preparedStatement = preparedStatement.substring(0, preparedStatement.length() - 2) + ");"; pS = conn.prepareStatement(preparedStatement); stMap.put(tuple.getSchema().getName(), pS); } int count = 1, tupleCount = 0; for (Field field : tuple.getSchema().getFields()) { if (field.getName().equals(PARTITION_TUPLE_FIELD)) { tupleCount++; continue; } if (field.getType().equals(Type.STRING)) { boolean autoTrim = autoTrim(field); int fieldSize = fixedSizeStringField(field); String str = tuple.getString(tupleCount); if (fieldSize > -1 && autoTrim && str != null && str.length() > fieldSize) { str = str.substring(0, fieldSize); } pS.setObject(count, str); } else { pS.setObject(count, tuple.get(tupleCount)); } count++; tupleCount++; } pS.execute(); records++; if (records == getBatchSize()) { Connection conn = connCache.get(partition); Statement st = conn.createStatement(); st.execute("COMMIT"); st.execute("BEGIN"); st.close(); records = 0; } } catch (Exception e) { throw new IOException(e); } }
From source file:com.dynamobi.db.conn.couchdb.CouchUdx.java
/** * Called by a custom LucidDB function for each view. * @param userName - CouchDB user name//from ww w . j a v a 2 s. co m * @param pw - CouchDB password * @param url - CouchDB REST URL * @param view - CouchDB REST view -- concatenated on the end of URL with * a slash prefix if necessary. * @param limit - Limit parameter passed to couchdb * @param reduce - if false, we pass &reduce=false to the view. * @param groupLevel - sent to view for group reduction, default 'EXACT' * Possible values: 'EXACT', sends &group=true. * 'NONE': sends &group=false. * 1-N: sends &group_level=x to the view or summarizer. 1 says to group * on the first index of the array key, 2 says the first two indexes, * N all indexes (equivalent to 'EXACT'). * * (the following should be done in logic rewrite rule?) TODO: * 'CALCULATE': typically set by the pushdown optimizer, instructs * this udx to best-guess what group level we can/should push down. * The basic idea is that if the columns in a GROUP BY statement belong * to objects defined as elements of the key array for the first key-value * pair returned by a view, we will push down the number of columns being * grouped by and ignore the grouping on the LucidDB end. Otherwise all * group by's will still be done by LucidDB. * @param resultInserter - Table for inserting results. Assumed to have the * necessary column names in the order we get them. */ public static void query(String userName, String pw, String url, String view, String limit, boolean reduce, String groupLevel, boolean outputJson, PreparedStatement resultInserter) throws SQLException { // Specialize so we can column names for our resultInserter // instead of assuming an order. ParameterMetaData pmd = resultInserter.getParameterMetaData(); FarragoParameterMetaData fpmd = (FarragoParameterMetaData) pmd; int paramCount = fpmd.getParameterCount(); String[] paramNames = new String[paramCount]; for (int i = 0; i < paramCount; i++) { paramNames[i] = fpmd.getFieldName(i + 1); // JDBC offset } RowProducer producer = new RowProducer(); JSONParser parser = new JSONParser(); InputStreamReader in = getViewStream(userName, pw, url, view, limit, reduce, groupLevel, true); while (!producer.isDone()) { try { parser.parse(in, producer, true); } catch (Throwable e) { // IOException, ParseException throw new SQLException(e); } if (!producer.getKey().equals("key")) continue; Object key = producer.getValue(); try { parser.parse(in, producer, true); } catch (Throwable e) { // IOException, ParseException throw new SQLException(e); } assert (producer.getKey().equals("value")); Object value = producer.getValue(); if (outputJson) { // put key in first col, val in second col, escape. resultInserter.setString(1, key.toString()); resultInserter.setString(2, value.toString()); resultInserter.executeUpdate(); continue; } Map<String, Object> params = new HashMap<String, Object>(paramNames.length); int dupes = mergeParams(params, key, "KEY"); dupes += mergeParams(params, value, "VALUE"); if (params.size() - dupes != paramNames.length) { // We have more params than columns.. throw new SQLException("Read " + params.size() + " params and " + paramNames.length + " columns, which need to match. Did you " + "add column(s) for both the key and value?"); } for (int c = 0; c < paramNames.length; c++) { Object o = params.get(paramNames[c]); if (o != null) { resultInserter.setObject(c + 1, o); } } resultInserter.executeUpdate(); } }
From source file:org.apache.jackrabbit.core.fs.db.DatabaseFileSystem.java
/** * Executes the given SQL statement with the specified parameters. * If a <code>SQLException</code> is encountered <i>one</i> attempt is made * to re-establish the database connection and re-execute the statement. * * @param sql statement to execute//from w w w. j av a 2 s. c om * @param params parameters to set * @return the <code>Statement</code> object that had been executed * @throws SQLException if an error occurs */ protected Statement executeStmt(String sql, Object[] params) throws SQLException { int trials = 2; while (true) { PreparedStatement stmt = (PreparedStatement) preparedStatements.get(sql); try { for (int i = 0; i < params.length; i++) { if (params[i] instanceof SizedInputStream) { SizedInputStream in = (SizedInputStream) params[i]; stmt.setBinaryStream(i + 1, in, (int) in.getSize()); } else { stmt.setObject(i + 1, params[i]); } } stmt.execute(); resetStatement(stmt); return stmt; } catch (SQLException se) { if (--trials == 0) { // no more trials, re-throw throw se; } log.warn("execute failed, about to reconnect...", se.getMessage()); // try to reconnect if (reestablishConnection()) { // reconnect succeeded; check whether it's possible to // re-execute the prepared stmt with the given parameters for (int i = 0; i < params.length; i++) { if (params[i] instanceof SizedInputStream) { SizedInputStream in = (SizedInputStream) params[i]; if (in.isConsumed()) { // we're unable to re-execute the prepared stmt // since an InputStream paramater has already // been 'consumed'; // re-throw previous SQLException throw se; } } } // try again to execute the statement continue; } else { // reconnect failed, re-throw previous SQLException throw se; } } } }
From source file:org.efaps.admin.user.Person.java
/** * The method reads directly from eFaps all stored groups for the this * person. The found groups are returned as instance of {@link Set}. * * @param _jaasSystem JAAS system for which the groups must fetched from * eFaps (if value is <code>null</code>, all groups independent * from the related JAAS system are returned) * @throws EFapsException on error/*from w w w . ja v a2 s . com*/ * @return set of all found groups for given JAAS system */ public Set<Group> getGroupsFromDB(final JAASSystem _jaasSystem) throws EFapsException { final Set<Group> ret = new HashSet<Group>(); ConnectionResource rsrc = null; try { final List<Long> groupIds = new ArrayList<Long>(); rsrc = Context.getThreadContext().getConnectionResource(); PreparedStatement stmt = null; try { if (_jaasSystem == null) { stmt = rsrc.getConnection().prepareStatement(SQL_GROUP); } else { stmt = rsrc.getConnection().prepareStatement(SQL_GROUPJAASKEY); stmt.setObject(2, _jaasSystem.getId()); } stmt.setObject(1, getId()); final ResultSet resultset = stmt.executeQuery(); while (resultset.next()) { groupIds.add(resultset.getLong(1)); } resultset.close(); } catch (final SQLException e) { throw new EFapsException(getClass(), "getGroupsFromDB.SQLException", e, getName()); } finally { try { if (stmt != null) { stmt.close(); } } catch (final SQLException e) { throw new EFapsException(getClass(), "getGroupsFromDB.SQLException", e, getName()); } } rsrc.commit(); for (final Long groupId : groupIds) { ret.add(Group.get(groupId)); } } finally { if (rsrc != null && rsrc.isOpened()) { rsrc.abort(); } } return ret; }
From source file:org.sakaiproject.nakamura.lite.storage.jdbc.JDBCStorageClient.java
public DisposableIterator<Map<String, Object>> find(final String keySpace, final String columnFamily, Map<String, Object> properties) throws StorageClientException { checkClosed();/*from ww w. j ava 2s. com*/ String[] keys = null; if (properties != null && properties.containsKey(StorageConstants.CUSTOM_STATEMENT_SET)) { String customStatement = (String) properties.get(StorageConstants.CUSTOM_STATEMENT_SET); keys = new String[] { customStatement + "." + keySpace + "." + columnFamily, customStatement + "." + columnFamily, customStatement, "block-find." + keySpace + "." + columnFamily, "block-find." + columnFamily, "block-find" }; } else { keys = new String[] { "block-find." + keySpace + "." + columnFamily, "block-find." + columnFamily, "block-find" }; } final boolean rawResults = properties != null && properties.containsKey(StorageConstants.RAWRESULTS); String sql = null; for (String statementKey : keys) { sql = getSql(statementKey); if (sql != null) { break; } } if (sql == null) { throw new StorageClientException("Failed to locate SQL statement for any of " + Arrays.toString(keys)); } String[] statementParts = StringUtils.split(sql, ';'); StringBuilder tables = new StringBuilder(); StringBuilder where = new StringBuilder(); StringBuilder order = new StringBuilder(); StringBuilder extraColumns = new StringBuilder(); // collect information on paging long page = 0; long items = 25; if (properties != null) { if (properties.containsKey(StorageConstants.PAGE)) { page = Long.valueOf(String.valueOf(properties.get(StorageConstants.PAGE))); } if (properties.containsKey(StorageConstants.ITEMS)) { items = Long.valueOf(String.valueOf(properties.get(StorageConstants.ITEMS))); } } long offset = page * items; // collect information on sorting String[] sorts = new String[] { null, "asc" }; String _sortProp = (String) properties.get(StorageConstants.SORT); if (_sortProp != null) { String[] _sorts = StringUtils.split(_sortProp); if (_sorts.length == 1) { sorts[0] = _sorts[0]; } else if (_sorts.length == 2) { sorts[0] = _sorts[0]; sorts[1] = _sorts[1]; } } List<Object> parameters = Lists.newArrayList(); int set = 0; for (Entry<String, Object> e : properties.entrySet()) { Object v = e.getValue(); String k = e.getKey(); if (shouldFind(keySpace, columnFamily, k) || (v instanceof Map)) { if (v != null) { // check for a value map and treat sub terms as for OR terms. // Only go 1 level deep; don't recurse. That's just silly. if (v instanceof Map) { // start the OR grouping where.append(" ("); @SuppressWarnings("unchecked") Set<Entry<String, Object>> subterms = ((Map<String, Object>) v).entrySet(); for (Iterator<Entry<String, Object>> subtermsIter = subterms.iterator(); subtermsIter .hasNext();) { Entry<String, Object> subterm = subtermsIter.next(); String subk = subterm.getKey(); Object subv = subterm.getValue(); // check that each subterm should be indexed if (shouldFind(keySpace, columnFamily, subk)) { set = processEntry(statementParts, tables, where, order, extraColumns, parameters, subk, subv, sorts, set); // as long as there are more add OR if (subtermsIter.hasNext()) { where.append(" OR"); } } } // end the OR grouping where.append(") AND"); } else { // process a first level non-map value as an AND term if (v instanceof Iterable<?>) { for (Object vo : (Iterable<?>) v) { set = processEntry(statementParts, tables, where, order, extraColumns, parameters, k, vo, sorts, set); where.append(" AND"); } } else { set = processEntry(statementParts, tables, where, order, extraColumns, parameters, k, v, sorts, set); where.append(" AND"); } } } else if (!k.startsWith("_")) { LOGGER.debug("Search on {}:{} filter dropped due to null value.", columnFamily, k); } } else { if (!k.startsWith("_")) { LOGGER.warn("Search on {}:{} is not supported, filter dropped ", columnFamily, k); } } } if (where.length() == 0) { return new DisposableIterator<Map<String, Object>>() { private Disposer disposer; public boolean hasNext() { return false; } public Map<String, Object> next() { return null; } public void remove() { } public void close() { if (disposer != null) { disposer.unregisterDisposable(this); } } public void setDisposer(Disposer disposer) { this.disposer = disposer; } }; } if (sorts[0] != null && order.length() == 0) { if (shouldFind(keySpace, columnFamily, sorts[0])) { String t = "a" + set; if (statementParts.length > STMT_EXTRA_COLUMNS) { extraColumns.append(MessageFormat.format(statementParts[STMT_EXTRA_COLUMNS], t)); } tables.append(MessageFormat.format(statementParts[STMT_TABLE_JOIN], t)); parameters.add(sorts[0]); where.append(MessageFormat.format(statementParts[STMT_WHERE_SORT], t)).append(" AND"); order.append(MessageFormat.format(statementParts[STMT_ORDER], t, sorts[1])); } else { LOGGER.warn("Sort on {}:{} is not supported, sort dropped", columnFamily, sorts[0]); } } final String sqlStatement = MessageFormat.format(statementParts[STMT_BASE], tables.toString(), where.toString(), order.toString(), items, offset, extraColumns.toString()); PreparedStatement tpst = null; ResultSet trs = null; try { LOGGER.debug("Preparing {} ", sqlStatement); tpst = jcbcStorageClientConnection.getConnection().prepareStatement(sqlStatement); inc("iterator"); tpst.clearParameters(); int i = 1; for (Object params : parameters) { tpst.setObject(i, params); LOGGER.debug("Setting {} ", params); i++; } long qtime = System.currentTimeMillis(); trs = tpst.executeQuery(); qtime = System.currentTimeMillis() - qtime; if (qtime > slowQueryThreshold && qtime < verySlowQueryThreshold) { SQL_LOGGER.warn("Slow Query {}ms {} params:[{}]", new Object[] { qtime, sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) }); } else if (qtime > verySlowQueryThreshold) { SQL_LOGGER.error("Very Slow Query {}ms {} params:[{}]", new Object[] { qtime, sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) }); } inc("iterator r"); LOGGER.debug("Executed "); // pass control to the iterator. final PreparedStatement pst = tpst; final ResultSet rs = trs; final ResultSetMetaData rsmd = rs.getMetaData(); tpst = null; trs = null; return registerDisposable(new PreemptiveIterator<Map<String, Object>>() { private Map<String, Object> nextValue = Maps.newHashMap(); private boolean open = true; @Override protected Map<String, Object> internalNext() { return nextValue; } @Override protected boolean internalHasNext() { try { if (open && rs.next()) { if (rawResults) { Builder<String, Object> b = ImmutableMap.builder(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { b.put(String.valueOf(i), rs.getObject(i)); } nextValue = b.build(); } else { String id = rs.getString(1); nextValue = internalGet(keySpace, columnFamily, id); LOGGER.debug("Got Row ID {} {} ", id, nextValue); } return true; } close(); nextValue = null; LOGGER.debug("End of Set "); return false; } catch (SQLException e) { LOGGER.error(e.getMessage(), e); close(); nextValue = null; return false; } catch (StorageClientException e) { LOGGER.error(e.getMessage(), e); close(); nextValue = null; return false; } } @Override public void close() { if (open) { open = false; try { if (rs != null) { rs.close(); dec("iterator r"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } try { if (pst != null) { pst.close(); dec("iterator"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } super.close(); } } }); } catch (SQLException e) { LOGGER.error(e.getMessage(), e); throw new StorageClientException(e.getMessage() + " SQL Statement was " + sqlStatement, e); } finally { // trs and tpst will only be non null if control has not been passed // to the iterator. try { if (trs != null) { trs.close(); dec("iterator r"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } try { if (tpst != null) { tpst.close(); dec("iterator"); } } catch (SQLException e) { LOGGER.warn(e.getMessage(), e); } } }