List of usage examples for java.sql ResultSet getTimestamp
java.sql.Timestamp getTimestamp(String columnLabel) throws SQLException;
ResultSet object as a java.sql.Timestamp object in the Java programming language. From source file:dao.PblogSearchBizAwareQuery.java
/** * This method lists all the results for the search text from pblogs * @param conn the connection/* w w w . j av a 2 s. c o m*/ * @param sString - search text * @param bid - bid * @return HashSet the set that has the list of search result * @throws BaseDaoException - when error occurs **/ public HashSet run(Connection conn, String sString, String bid) throws BaseDaoException { if (RegexStrUtil.isNull(sString) || RegexStrUtil.isNull(bid) || conn == null) { return null; } ResultSet rs = null; StringBuffer sb = new StringBuffer( "select hdlogin.loginid, hdlogin.login, hdlogin.fname, lname, LEFT(message, 160) as info, entrydate, tid, hits, hdlogin.bid, business.bsearch from business, hdlogin left join pblogtopics on hdlogin.loginid=pblogtopics.pblogid left join pblog on hdlogin.loginid=pblog.loginid where business.bid=hdlogin.bid and ("); ArrayList columns = new ArrayList(); columns.add("topic"); columns.add("message"); sb.append(sqlSearch.getConstraint(columns, sString)); sb.append(") group by login order by hits DESC"); logger.info("search query string" + sb.toString()); try { PreparedStatement stmt = conn.prepareStatement(sb.toString()); rs = stmt.executeQuery(); Vector columnNames = null; Blog pblog = null; HashSet pendingSet = new HashSet(); if (rs != null) { columnNames = dbutils.getColumnNames(rs); } else { return null; } while (rs.next()) { pblog = (Blog) eop.newObject(DbConstants.BLOG); for (int j = 0; j < columnNames.size(); j++) { if (((String) (columnNames.elementAt(j))).equalsIgnoreCase("entrydate")) { try { pblog.setValue("entrydate", GlobalConst.dncalendar.getDisplayDate(rs.getTimestamp("entrydate"))); } catch (ParseException e) { throw new BaseDaoException( "could not parse the date for entrydate in PblogSearchBizAwareQuery()" + rs.getTimestamp("entrydate"), e); } } else { pblog.setValue((String) columnNames.elementAt(j), (String) rs.getString((String) columnNames.elementAt(j))); } } pendingSet.add(pblog); } return pendingSet; } catch (Exception e) { throw new BaseDaoException("Error occured while executing search in pblog run query " + sb.toString(), e); } }
From source file:jeeves.resources.dbms.Dbms.java
private Element buildElement(ResultSet rs, int col, String name, int type, Hashtable<String, String> formats) throws SQLException { String value = null;//w w w.j a v a 2s . c om switch (type) { case Types.DATE: Date date = rs.getDate(col + 1); if (date == null) value = null; else { String format = formats.get(name); SimpleDateFormat df = (format == null) ? new SimpleDateFormat(DEFAULT_DATE_FORMAT) : new SimpleDateFormat(format); value = df.format(date); } break; case Types.TIME: Time time = rs.getTime(col + 1); if (time == null) value = null; else { String format = formats.get(name); SimpleDateFormat df = (format == null) ? new SimpleDateFormat(DEFAULT_TIME_FORMAT) : new SimpleDateFormat(format); value = df.format(time); } break; case Types.TIMESTAMP: Timestamp timestamp = rs.getTimestamp(col + 1); if (timestamp == null) value = null; else { String format = formats.get(name); SimpleDateFormat df = (format == null) ? new SimpleDateFormat(DEFAULT_TIMESTAMP_FORMAT) : new SimpleDateFormat(format); value = df.format(timestamp); } break; case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: long l = rs.getLong(col + 1); if (rs.wasNull()) value = null; else { String format = formats.get(name); if (format == null) value = l + ""; else { DecimalFormat df = new DecimalFormat(format); value = df.format(l); } } break; case Types.DECIMAL: case Types.FLOAT: case Types.DOUBLE: case Types.REAL: case Types.NUMERIC: double n = rs.getDouble(col + 1); if (rs.wasNull()) value = null; else { String format = formats.get(name); if (format == null) { value = n + ""; // --- this fix is mandatory for oracle // --- that shit returns integers like xxx.0 if (value.endsWith(".0")) value = value.substring(0, value.length() - 2); } else { DecimalFormat df = new DecimalFormat(format); value = df.format(n); } } break; default: value = rs.getString(col + 1); if (value != null) { value = stripIllegalChars(value); } break; } return new Element(name).setText(value); }
From source file:com.pinterest.pinlater.backends.mysql.PinLaterMySQLBackend.java
@Override protected List<PinLaterJobInfo> scanJobsFromShard(final String queueName, final String shardName, final Set<Integer> priorities, final PinLaterJobState jobState, final boolean scanFutureJobs, final String continuation, final int limit, final String bodyRegexTomatch) throws Exception { final String scanQuery = scanFutureJobs ? MySQLQueries.SCAN_FUTURE_JOBS : MySQLQueries.SCAN_CURRENT_JOBS; Connection conn = null;/*from w w w . ja va2s . c o m*/ try { ImmutableMap<String, MySQLDataSources> shardMap = shardMapRef.get(); conn = shardMap.get(shardName).getGeneralDataSource().getConnection(); // First scan some jobs for the specified priorities. List<List<PinLaterJobInfo>> jobsPerPriority = Lists.newArrayListWithCapacity(priorities.size()); for (final int priority : priorities) { jobsPerPriority.add(JdbcUtils.select(conn, String.format(scanQuery, MySQLBackendUtils.constructJobsTableName(queueName, shardName, priority), getBodyRegexClause(bodyRegexTomatch)), new RowProcessor<PinLaterJobInfo>() { @Override public PinLaterJobInfo process(ResultSet rs) throws IOException, SQLException { PinLaterJobInfo ji = new PinLaterJobInfo(); ji.setJobDescriptor( new PinLaterJobDescriptor(queueName, shardName, priority, rs.getLong(1)) .toString()); String claimDescriptor = rs.getString(2); if (claimDescriptor != null) { ji.setClaimDescriptor(claimDescriptor); } ji.setAttemptsAllowed(rs.getInt(3)); ji.setAttemptsRemaining(rs.getInt(4)); ji.setCustomStatus(Strings.nullToEmpty(rs.getString(5))); ji.setCreatedAtTimestampMillis(rs.getTimestamp(6).getTime()); ji.setRunAfterTimestampMillis(rs.getTimestamp(7).getTime()); ji.setUpdatedAtTimestampMillis(rs.getTimestamp(8).getTime()); ji.setJobState(jobState); return ji; } }, jobState.getValue(), limit)); } // Merge jobsPerPriority and return the merged result. return PinLaterBackendUtils.mergeIntoList(jobsPerPriority, PinLaterBackendUtils.JobInfoComparator.getInstance()); } finally { JdbcUtils.closeConnection(conn); } }
From source file:net.pms.dlna.DLNAMediaDatabase.java
public void cleanup() { Connection conn = null;//from w w w . ja v a2 s . c om PreparedStatement ps = null; ResultSet rs = null; try { conn = getConnection(); ps = conn.prepareStatement("SELECT COUNT(*) FROM FILES"); rs = ps.executeQuery(); dbCount = 0; if (rs.next()) { dbCount = rs.getInt(1); } rs.close(); ps.close(); PMS.get().getFrame().setStatusLine(Messages.getString("DLNAMediaDatabase.2") + " 0%"); int i = 0; int oldpercent = 0; if (dbCount > 0) { ps = conn.prepareStatement("SELECT FILENAME, MODIFIED, ID FROM FILES", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); rs = ps.executeQuery(); while (rs.next()) { String filename = rs.getString("FILENAME"); long modified = rs.getTimestamp("MODIFIED").getTime(); File file = new File(filename); if (!file.exists() || file.lastModified() != modified) { rs.deleteRow(); } i++; int newpercent = i * 100 / dbCount; if (newpercent > oldpercent) { PMS.get().getFrame() .setStatusLine(Messages.getString("DLNAMediaDatabase.2") + newpercent + "%"); oldpercent = newpercent; } } } } catch (SQLException se) { logger.error(null, se); } finally { close(rs); close(ps); close(conn); } }
From source file:org.ohmage.query.impl.DocumentQueries.java
@Override public List<Document> getDocumentInformation(final String username, final Boolean personalDocuments, final Collection<String> campaignIds, final Collection<String> classIds, final Collection<String> nameTokens, final Collection<String> descriptionTokens) throws DataAccessException { StringBuilder sql = new StringBuilder("SELECT d.uuid, d.name, d.description, d.size, " + "d.last_modified_timestamp, d.creation_timestamp, " + "dps.privacy_state, duc.username " + "FROM user u, document d, " + "document_privacy_state dps, document_user_creator duc " + "WHERE u.username = ? " + "AND d.privacy_state_id = dps.id " + "AND d.id = duc.document_id " + "AND ("); List<Object> parameters = new LinkedList<Object>(); parameters.add(username);//from w w w. j av a2s .c o m boolean needOr = false; if (personalDocuments == null) { // If campaignIds and classIds are null, get all of the documents // visible to the user. if ((campaignIds == null) && (classIds == null)) { sql.append("(" + "u.admin = true" + ") OR (" + "d.id IN (" + "SELECT dur.document_id " + "FROM document_user_role dur " + "WHERE u.id = dur.user_id" + ")" + ") OR (" + "d.id IN (" + "SELECT dcr.document_id " + "FROM user_role_campaign urc, " + "document_campaign_role dcr " + "WHERE u.id = urc.user_id " + "AND urc.campaign_id = dcr.campaign_id" + ")" + ") OR (" + "d.id IN (" + "SELECT dcr.document_id " + "FROM user_class uc, document_class_role dcr " + "WHERE u.id = uc.user_id " + "AND uc.class_id = dcr.class_id" + ")" + ")"); } } else if (personalDocuments) { // Get all of the personal documents. sql.append("d.id IN (" + "SELECT dur.document_id " + "FROM document_user_role dur " + "WHERE u.id = dur.user_id" + ")"); needOr = true; } else { // If campaignIds and classIds are null and they are specifically // asking for not their personal documents, then return nothing. if ((campaignIds == null) && (classIds == null)) { return Collections.emptyList(); } } if (campaignIds != null) { if (campaignIds.size() == 0) { return Collections.emptyList(); } if (needOr) { sql.append(" OR "); } sql.append("d.id IN (" + "SELECT dcr.document_id " + "FROM campaign c, user_role_campaign urc, " + "document_campaign_role dcr " + "WHERE c.urn IN ") .append(StringUtils.generateStatementPList(campaignIds.size())) .append(" AND c.id = urc.campaign_id " + "AND u.id = urc.user_id " + "AND c.id = dcr.campaign_id" + ")"); needOr = true; parameters.addAll(campaignIds); } if (classIds != null) { if (classIds.size() == 0) { return Collections.emptyList(); } if (needOr) { sql.append(" OR "); } sql.append("d.id IN (" + "SELECT dcr.document_id " + "FROM class c, user_class uc, " + "document_class_role dcr " + "WHERE c.urn IN ") .append(StringUtils.generateStatementPList(classIds.size())).append(" AND c.id = uc.class_id " + "AND u.id = uc.user_id " + "AND c.id = dcr.class_id" + ")"); parameters.addAll(classIds); } sql.append(")"); if (nameTokens != null) { if (nameTokens.size() == 0) { return Collections.emptyList(); } sql.append(" AND ("); boolean firstPass = true; for (String nameToken : nameTokens) { if (firstPass) { firstPass = false; } else { sql.append(" OR "); } sql.append("d.name LIKE ?"); parameters.add('%' + nameToken + '%'); } sql.append(")"); } if (descriptionTokens != null) { if (descriptionTokens.size() == 0) { return Collections.emptyList(); } sql.append(" AND ("); boolean firstPass = true; for (String descriptionToken : descriptionTokens) { if (firstPass) { firstPass = false; } else { sql.append(" OR "); } sql.append("d.description LIKE ?"); parameters.add('%' + descriptionToken + '%'); } sql.append(")"); } // Now, we will tack on the ACL's to limit the results to only those // that are visible to the requesting user. The whole work flow of this // function is bad and should be optimized. But, we have a deadline and // the performance isn't bad. sql.append(" AND (" + // The user is an admin. "(u.admin = true)" + " OR " + // The document is shared. "(dps.privacy_state = '" + Document.PrivacyState.SHARED + "'" + ")" + // If the document is not public then it must be private, // so the user must have the role of owner and/or writer. " OR (" + "EXISTS (" + "SELECT dr.id " + "FROM document_role dr " + "WHERE dr.role IN (" + "'" + Document.Role.OWNER.toString() + "', " + "'" + Document.Role.WRITER.toString() + "'" + ")" + "AND (" + // See if the user is directly related to the // document with the specified roles. "EXISTS (" + "SELECT dur.id " + "FROM document_user_role dur " + "WHERE d.id = dur.document_id " + "AND u.id = dur.user_id " + "AND dr.id = dur.document_role_id" + ")" + // See if the user is related to the document // through a class. " OR EXISTS (" + "SELECT dcr.id " + "FROM document_class_role dcr, " + "user_class uc, user_class_role ucr " + "WHERE d.id = dcr.document_id " + "AND u.id = uc.user_id " + "AND uc.class_id = dcr.class_id " + // The class has the appropriate role or // the user is privileged in that class. "AND (" + "(dr.id = dcr.document_role_id)" + " OR " + "(" + "uc.user_class_role_id = ucr.id" + " AND " + "ucr.role = '" + Clazz.Role.PRIVILEGED.toString() + "'" + ")" + ")" + ")" + // See if the user is related to the document // through a campaign. " OR EXISTS (" + "SELECT dcr.id " + "FROM document_campaign_role dcr, " + "user_role ur, " + "user_role_campaign urc " + "WHERE d.id = dcr.document_id " + "AND u.id = urc.user_id " + "AND urc.campaign_id = dcr.campaign_id " + // The campaign has the appropriate role or // the user is a supervisor in that //campaign. "AND (" + "(dr.id = dcr.document_role_id)" + " OR " + "(" + "urc.user_role_id = ur.id" + " AND " + "ur.role = '" + Campaign.Role.SUPERVISOR.toString() + "'" + ")" + ")" + ")" + ")" + ")" + ")" + ")"); try { return getJdbcTemplate().query(sql.toString(), parameters.toArray(), new RowMapper<Document>() { @Override public Document mapRow(final ResultSet rs, final int rowNum) throws SQLException { try { return new Document(rs.getString("uuid"), rs.getString("name"), rs.getString("description"), Document.PrivacyState.getValue(rs.getString("privacy_state")), new DateTime(rs.getTimestamp("last_modified_timestamp").getTime()), new DateTime(rs.getTimestamp("creation_timestamp").getTime()), rs.getInt("size"), rs.getString("username")); } catch (DomainException e) { throw new SQLException("A document is broken: " + rs.getString("uuid"), e); } } }); } catch (org.springframework.dao.DataAccessException e) { throw new DataAccessException( "Error executing SQL '" + sql.toString() + "' with parameters: " + parameters, e); } }
From source file:co.nubetech.hiho.mapreduce.lib.db.apache.DateSplitter.java
public List<InputSplit> split(Configuration conf, ResultSet results, String colName) throws SQLException { long minVal;//from w w w .j a v a 2 s .co m long maxVal; int sqlDataType = results.getMetaData().getColumnType(1); minVal = resultSetColToLong(results, 1, sqlDataType); maxVal = resultSetColToLong(results, 2, sqlDataType); String lowClausePrefix = colName + " >= "; String highClausePrefix = colName + " < "; int numSplits = conf.getInt(MRJobConfig.NUM_MAPS, 1); if (numSplits < 1) { numSplits = 1; } if (minVal == Long.MIN_VALUE && maxVal == Long.MIN_VALUE) { // The range of acceptable dates is NULL to NULL. Just create a single split. List<InputSplit> splits = new ArrayList<InputSplit>(); splits.add( new DataDrivenDBInputFormat.DataDrivenDBInputSplit(colName + " IS NULL", colName + " IS NULL")); return splits; } // Gather the split point integers List<Long> splitPoints = split(numSplits, minVal, maxVal); List<InputSplit> splits = new ArrayList<InputSplit>(); // Turn the split points into a set of intervals. long start = splitPoints.get(0); Date startDate = longToDate(start, sqlDataType); if (sqlDataType == Types.TIMESTAMP) { // The lower bound's nanos value needs to match the actual lower-bound nanos. try { ((java.sql.Timestamp) startDate).setNanos(results.getTimestamp(1).getNanos()); } catch (NullPointerException npe) { // If the lower bound was NULL, we'll get an NPE; just ignore it and don't set nanos. } } for (int i = 1; i < splitPoints.size(); i++) { long end = splitPoints.get(i); Date endDate = longToDate(end, sqlDataType); if (i == splitPoints.size() - 1) { if (sqlDataType == Types.TIMESTAMP) { // The upper bound's nanos value needs to match the actual upper-bound nanos. try { ((java.sql.Timestamp) endDate).setNanos(results.getTimestamp(2).getNanos()); } catch (NullPointerException npe) { // If the upper bound was NULL, we'll get an NPE; just ignore it and don't set nanos. } } // This is the last one; use a closed interval. splits.add(new DataDrivenDBInputFormat.DataDrivenDBInputSplit( lowClausePrefix + dateToString(startDate), colName + " <= " + dateToString(endDate))); } else { // Normal open-interval case. splits.add(new DataDrivenDBInputFormat.DataDrivenDBInputSplit( lowClausePrefix + dateToString(startDate), highClausePrefix + dateToString(endDate))); } start = end; startDate = endDate; } if (minVal == Long.MIN_VALUE || maxVal == Long.MIN_VALUE) { // Add an extra split to handle the null case that we saw. splits.add( new DataDrivenDBInputFormat.DataDrivenDBInputSplit(colName + " IS NULL", colName + " IS NULL")); } return splits; }
From source file:com.greatmancode.craftconomy3.utils.OldFormatConverter.java
public void run() throws SQLException, IOException, ParseException { String dbType = Common.getInstance().getMainConfig().getString("System.Database.Type"); HikariConfig config = new HikariConfig(); if (dbType.equalsIgnoreCase("mysql")) { config.setMaximumPoolSize(10);// ww w.j a va 2 s . c o m config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource"); config.addDataSourceProperty("serverName", Common.getInstance().getMainConfig().getString("System.Database.Address")); config.addDataSourceProperty("port", Common.getInstance().getMainConfig().getString("System.Database.Port")); config.addDataSourceProperty("databaseName", Common.getInstance().getMainConfig().getString("System.Database.Db")); config.addDataSourceProperty("user", Common.getInstance().getMainConfig().getString("System.Database.Username")); config.addDataSourceProperty("password", Common.getInstance().getMainConfig().getString("System.Database.Password")); config.addDataSourceProperty("autoDeserialize", true); config.setConnectionTimeout(5000); db = new HikariDataSource(config); } else if (dbType.equalsIgnoreCase("sqlite")) { config.setDriverClassName("org.sqlite.JDBC"); config.setJdbcUrl("jdbc:sqlite:" + Common.getInstance().getServerCaller().getDataFolder() + File.separator + "database.db"); db = new HikariDataSource(config); } else { Common.getInstance().sendConsoleMessage(Level.SEVERE, "Unknown database type for old format converter!"); return; } Connection connection = db.getConnection(); this.tablePrefix = Common.getInstance().getMainConfig().getString("System.Database.Prefix"); File accountFile = new File(Common.getInstance().getServerCaller().getDataFolder(), "accounts.json"); Common.getInstance().sendConsoleMessage(Level.INFO, "Doing a backup in a xml file before doing the conversion."); //Document setup JSONObject mainObject = new JSONObject(); Common.getInstance().sendConsoleMessage(Level.INFO, "Saving currency table"); //Currencies PreparedStatement statement = connection.prepareStatement("SELECT * FROM " + tablePrefix + "currency"); ResultSet set = statement.executeQuery(); JSONArray array = new JSONArray(); while (set.next()) { JSONObject entry = new JSONObject(); entry.put("id", set.getInt("id")); entry.put("name", set.getString("name")); entry.put("plural", set.getString("plural")); entry.put("minor", set.getString("minor")); entry.put("minorPlural", set.getString("minorPlural")); entry.put("sign", set.getString("sign")); entry.put("status", set.getBoolean("status")); array.add(entry); } statement.close(); mainObject.put("currencies", array); //World groups Common.getInstance().sendConsoleMessage(Level.INFO, "Saving world group table"); array = new JSONArray(); statement = connection.prepareStatement("SELECT * FROM " + tablePrefix + "worldgroup"); set = statement.executeQuery(); while (set.next()) { JSONObject entry = new JSONObject(); entry.put("groupName", set.getString("groupName")); entry.put("worldList", set.getString("worldList")); array.add(entry); } statement.close(); mainObject.put("worldgroups", array); //Exchange table Common.getInstance().sendConsoleMessage(Level.INFO, "Saving exchange table"); array = new JSONArray(); statement = connection.prepareStatement("SELECT * FROM " + tablePrefix + "exchange"); set = statement.executeQuery(); while (set.next()) { JSONObject entry = new JSONObject(); entry.put("from_currency_id", set.getInt("from_currency_id")); entry.put("to_currency_id", set.getInt("to_currency_id")); entry.put("amount", set.getDouble("amount")); array.add(entry); } statement.close(); mainObject.put("exchanges", array); //config table Common.getInstance().sendConsoleMessage(Level.INFO, "Saving config table"); array = new JSONArray(); statement = connection.prepareStatement("SELECT * FROM " + tablePrefix + "config"); set = statement.executeQuery(); while (set.next()) { JSONObject entry = new JSONObject(); entry.put("name", set.getString("name")); entry.put("value", set.getString("value")); array.add(entry); } statement.close(); mainObject.put("configs", array); //account table Common.getInstance().sendConsoleMessage(Level.INFO, "Saving account table"); array = new JSONArray(); statement = connection.prepareStatement("SELECT * FROM " + tablePrefix + "account"); set = statement.executeQuery(); while (set.next()) { JSONObject entry = new JSONObject(); entry.put("name", set.getString("name")); entry.put("infiniteMoney", set.getBoolean("infiniteMoney")); entry.put("ignoreACL", set.getBoolean("ignoreACL")); entry.put("uuid", set.getString("uuid")); JSONArray balanceArray = new JSONArray(); PreparedStatement internalStatement = connection .prepareStatement("SELECT * FROM " + tablePrefix + "balance WHERE username_id=?"); internalStatement.setInt(1, set.getInt("id")); ResultSet internalSet = internalStatement.executeQuery(); while (internalSet.next()) { JSONObject object = new JSONObject(); object.put("currency_id", internalSet.getInt("currency_id")); object.put("worldName", internalSet.getString("worldName")); object.put("balance", internalSet.getDouble("balance")); balanceArray.add(object); } internalStatement.close(); entry.put("balances", balanceArray); internalStatement = connection .prepareStatement("SELECT * FROM " + tablePrefix + "log WHERE username_id=?"); internalStatement.setInt(1, set.getInt("id")); internalSet = internalStatement.executeQuery(); JSONArray logArray = new JSONArray(); while (internalSet.next()) { JSONObject object = new JSONObject(); object.put("type", internalSet.getObject("type")); object.put("cause", internalSet.getObject("cause")); object.put("timestamp", internalSet.getTimestamp("timestamp")); object.put("causeReason", internalSet.getString("causeReason")); object.put("currencyName", internalSet.getString("currencyName")); object.put("worldName", internalSet.getString("worldName")); object.put("amount", internalSet.getDouble("amount")); logArray.add(object); } internalStatement.close(); entry.put("logs", logArray); internalStatement = connection .prepareStatement("SELECT * FROM " + tablePrefix + "acl WHERE account_id=?"); internalStatement.setInt(1, set.getInt("id")); internalSet = internalStatement.executeQuery(); JSONArray aclArray = new JSONArray(); while (internalSet.next()) { JSONObject object = new JSONObject(); object.put("playerName", internalSet.getString("playerName")); object.put("deposit", internalSet.getBoolean("deposit")); object.put("withdraw", internalSet.getBoolean("withdraw")); object.put("acl", internalSet.getBoolean("acl")); object.put("balance", internalSet.getBoolean("balance")); object.put("owner", internalSet.getBoolean("owner")); aclArray.add(object); } internalStatement.close(); entry.put("acls", aclArray); array.add(entry); } statement.close(); mainObject.put("accounts", array); Common.getInstance().sendConsoleMessage(Level.INFO, "Writing json file"); FileWriter writer = new FileWriter(accountFile); writer.write(mainObject.toJSONString()); writer.flush(); writer.close(); Common.getInstance().sendConsoleMessage(Level.INFO, "File written! Dropping all tables"); //The backup is now saved. Let's drop everything statement = connection.prepareStatement("DROP TABLE " + tablePrefix + "config"); statement.execute(); statement.close(); statement = connection.prepareStatement("DROP TABLE " + tablePrefix + "acl"); statement.execute(); statement.close(); statement = connection.prepareStatement("DROP TABLE " + tablePrefix + "balance"); statement.execute(); statement.close(); statement = connection.prepareStatement("DROP TABLE " + tablePrefix + "log"); statement.execute(); statement.close(); statement = connection.prepareStatement("DROP TABLE " + tablePrefix + "worldgroup"); statement.execute(); statement.close(); statement = connection.prepareStatement("DROP TABLE " + tablePrefix + "exchange"); statement.execute(); statement.close(); statement = connection.prepareStatement("DROP TABLE " + tablePrefix + "account"); statement.execute(); statement.close(); statement = connection.prepareStatement("DROP TABLE " + tablePrefix + "currency"); statement.execute(); statement.close(); statement = connection.prepareStatement("DROP TABLE " + tablePrefix + "payday"); statement.execute(); statement.close(); connection.close(); step2(); }
From source file:org.apache.syncope.core.util.ImportExport.java
private String getValues(final ResultSet rs, final String columnName, final Integer columnType) throws SQLException { String res = null;/*w ww .java2 s . c om*/ try { switch (columnType) { case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: final InputStream is = rs.getBinaryStream(columnName); if (is != null) { res = new String(Hex.encode(IOUtils.toByteArray(is))); } break; case Types.BLOB: final Blob blob = rs.getBlob(columnName); if (blob != null) { res = new String(Hex.encode(IOUtils.toByteArray(blob.getBinaryStream()))); } break; case Types.BIT: case Types.BOOLEAN: if (rs.getBoolean(columnName)) { res = "1"; } else { res = "0"; } break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: final Timestamp timestamp = rs.getTimestamp(columnName); if (timestamp != null) { res = DATE_FORMAT.get().format(new Date(timestamp.getTime())); } break; default: res = rs.getString(columnName); } } catch (IOException e) { LOG.error("Error retrieving hexadecimal string", e); } return res; }
From source file:edu.jhu.pha.vosync.rest.DropboxService.java
@GET @Path("transfers/info") @Produces(MediaType.APPLICATION_JSON)/*from ww w. ja v a 2s . c o m*/ @RolesAllowed({ "user" }) public byte[] getTransfersInfo() { final SciDriveUser user = ((SciDriveUser) security.getUserPrincipal()); return DbPoolServlet.goSql("Get transfers queue", "select state, direction, starttime, endtime, target from jobs JOIN user_identities ON jobs.user_id = user_identities.user_id WHERE identity = ? order by starttime DESC", new SqlWorker<byte[]>() { @Override public byte[] go(Connection conn, PreparedStatement stmt) throws SQLException { stmt.setString(1, user.getName()); ByteArrayOutputStream byteOut = null; try { JsonFactory f = new JsonFactory(); byteOut = new ByteArrayOutputStream(); JsonGenerator g2 = f.createJsonGenerator(byteOut); g2.writeStartArray(); ResultSet resSet = stmt.executeQuery(); while (resSet.next()) { g2.writeStartObject(); g2.writeStringField("state", resSet.getString("state")); g2.writeStringField("direction", resSet.getString("direction")); g2.writeStringField("starttime", (null != resSet.getTimestamp("starttime") ? resSet.getTimestamp("starttime").toString() : "")); g2.writeStringField("endtime", (null != resSet.getTimestamp("endtime") ? resSet.getTimestamp("starttime").toString() : "")); g2.writeStringField("path", resSet.getString("target")); g2.writeEndObject(); } g2.writeEndArray(); g2.close(); byteOut.close(); return byteOut.toByteArray(); } catch (IOException ex) { throw new InternalServerErrorException(ex); } } }); }
From source file:eionet.meta.dao.mysql.VocabularyFolderDAOImpl.java
@Override public VocabularyResult searchVocabularies(VocabularyFilter filter) { Map<String, Object> params = new HashMap<String, Object>(); StringBuilder sql = new StringBuilder(); sql.append(//from w w w . ja v a 2 s.com "select v.VOCABULARY_ID, v.IDENTIFIER, v.LABEL, v.REG_STATUS, v.WORKING_COPY, v.BASE_URI, v.VOCABULARY_TYPE, "); sql.append("v.WORKING_USER, v.DATE_MODIFIED, v.USER_MODIFIED, v.CHECKEDOUT_COPY_ID, v.CONTINUITY_ID, "); sql.append("v.CONCEPT_IDENTIFIER_NUMERIC, f.ID, f.IDENTIFIER, f.LABEL "); sql.append("from VOCABULARY v "); sql.append("left join VOCABULARY_SET f on f.ID=v.FOLDER_ID where 1=1 "); if (StringUtils.isNotEmpty(filter.getText())) { if (filter.isWordMatch()) { params.put("text", "[[:<:]]" + filter.getText() + "[[:>:]]"); sql.append("AND (v.LABEL REGEXP :text "); sql.append("or v.IDENTIFIER REGEXP :text) "); } else if (filter.isExactMatch()) { params.put("text", filter.getText()); sql.append("AND (v.LABEL = :text "); sql.append("or v.IDENTIFIER = :text) "); } else { params.put("text", "%" + filter.getText() + "%"); sql.append("AND (v.LABEL like :text "); sql.append("or v.IDENTIFIER like :text) "); } } else if (StringUtils.isNotEmpty(filter.getIdentifier())) { params.put("identifier", filter.getIdentifier()); sql.append("AND v.IDENTIFIER like :identifier "); } if (filter.getVocabularyWorkingCopyId() != null) { if (BooleanUtils.isFalse(filter.isWorkingCopy())) { params.put("workingCopyVocabularyId", filter.getVocabularyWorkingCopyId()); sql.append( "AND ((WORKING_COPY = 0 AND (CHECKEDOUT_COPY_ID IS NULL OR CHECKEDOUT_COPY_ID <> :workingCopyVocabularyId)) " + "OR (WORKING_COPY = 1 AND VOCABULARY_ID = :workingCopyVocabularyId)) "); } } else if (filter.isWorkingCopy() != null) { params.put("workingCopy", filter.isWorkingCopy() ? 1 : 0); sql.append("AND WORKING_COPY = :workingCopy"); } if (filter.getStatus() != null) { params.put("regStatus", filter.getStatus().getLabel()); sql.append("AND REG_STATUS = :regStatus"); } // related concepts text: if (StringUtils.isNotEmpty(filter.getConceptText())) { if (filter.isWordMatch()) { params.put("text", "[[:<:]]" + filter.getConceptText() + "[[:>:]]"); sql.append( " AND EXISTS (SELECT 1 FROM VOCABULARY_CONCEPT vc WHERE vc.VOCABULARY_ID = v.VOCABULARY_ID "); sql.append(" AND (vc.LABEL REGEXP :conceptText OR vc.IDENTIFIER REGEXP :conceptText "); sql.append(" OR vc.DEFINITION REGEXP :conceptText)) "); } else if (filter.isExactMatch()) { params.put("conceptText", filter.getConceptText()); sql.append( " AND EXISTS (SELECT 1 FROM VOCABULARY_CONCEPT vc WHERE vc.VOCABULARY_ID = v.VOCABULARY_ID "); sql.append( " AND (vc.LABEL = :conceptText OR vc.IDENTIFIER = :conceptText OR vc.DEFINITION = :conceptText)) "); } else { params.put("conceptText", "%" + filter.getConceptText() + "%"); sql.append( " AND EXISTS (SELECT 1 FROM VOCABULARY_CONCEPT vc WHERE vc.VOCABULARY_ID = v.VOCABULARY_ID "); sql.append(" AND (vc.LABEL like :conceptText "); sql.append(" OR vc.IDENTIFIER like :conceptText OR vc.DEFINITION like :conceptText) ) "); } } if (StringUtils.isNotBlank(filter.getBaseUri())) { params.put("baseUri", filter.getBaseUri()); sql.append(" AND v.BASE_URI like :baseUri "); } sql.append(" ORDER BY v.IDENTIFIER"); List<VocabularyFolder> items = getNamedParameterJdbcTemplate().query(sql.toString(), params, new RowMapper<VocabularyFolder>() { @Override public VocabularyFolder mapRow(ResultSet rs, int rowNum) throws SQLException { VocabularyFolder vf = new VocabularyFolder(); vf.setId(rs.getInt("v.VOCABULARY_ID")); vf.setIdentifier(rs.getString("v.IDENTIFIER")); vf.setLabel(rs.getString("v.LABEL")); vf.setRegStatus(RegStatus.fromString(rs.getString("v.REG_STATUS"))); vf.setWorkingCopy(rs.getBoolean("v.WORKING_COPY")); vf.setBaseUri(rs.getString("v.BASE_URI")); vf.setType(VocabularyType.valueOf(rs.getString("v.VOCABULARY_TYPE"))); vf.setWorkingUser(rs.getString("v.WORKING_USER")); vf.setDateModified(rs.getTimestamp("v.DATE_MODIFIED")); vf.setUserModified(rs.getString("v.USER_MODIFIED")); vf.setCheckedOutCopyId(rs.getInt("v.CHECKEDOUT_COPY_ID")); vf.setContinuityId(rs.getString("v.CONTINUITY_ID")); vf.setNumericConceptIdentifiers(rs.getBoolean("v.CONCEPT_IDENTIFIER_NUMERIC")); vf.setFolderId(rs.getInt("f.ID")); vf.setFolderName(rs.getString("f.IDENTIFIER")); vf.setFolderLabel(rs.getString("f.LABEL")); return vf; } }); String totalSql = "SELECT FOUND_ROWS()"; int totalItems = getJdbcTemplate().queryForInt(totalSql); VocabularyResult result = new VocabularyResult(items, totalItems, filter); return result; }