Example usage for java.sql ResultSet getTimestamp

List of usage examples for java.sql ResultSet getTimestamp

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getTimestamp

Introduction

In this page you can find the example usage for java.sql ResultSet getTimestamp.

Prototype

java.sql.Timestamp getTimestamp(String columnLabel) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp object in the Java programming language.

Usage

From source file:com.alfaariss.oa.engine.requestor.jdbc.JDBCFactory.java

/**
 * Returns the requestor pool were the supplied request id is a part from.
 * @see com.alfaariss.oa.engine.core.requestor.factory.IRequestorPoolFactory#getRequestorPool(java.lang.String)
 *//*from w  w  w.j a  v a 2s  .  c  om*/
@Override
public RequestorPool getRequestorPool(String sRequestor) throws RequestorException {
    JDBCRequestorPool oRequestorPool = null;
    Connection oConnection = null;
    PreparedStatement oPreparedStatement = null;
    ResultSet oResultSet = null;
    try {
        oConnection = _oDataSource.getConnection();

        oPreparedStatement = oConnection.prepareStatement(_sQuerySelectPool);
        oPreparedStatement.setString(1, sRequestor);
        oResultSet = oPreparedStatement.executeQuery();
        if (oResultSet.next()) {
            String sPoolId = oResultSet.getString(JDBCRequestorPool.COLUMN_ID);

            // Work with cache:
            if (_isCacheEnabled) {
                RequestorPoolEntry oRPE = _mRequestorPoolMap.get(sPoolId);

                if (oRPE != null) {
                    long lCachedDateLastModified = 0;
                    if (oRPE._dLastModified != null)
                        lCachedDateLastModified = oRPE._dLastModified.getTime();

                    long lRealDateLastModified = getMostRecentDate(
                            oResultSet.getTimestamp("date_last_modified"),
                            oResultSet.getTimestamp("requestor_date"));

                    if (lRealDateLastModified > 0 && (lRealDateLastModified <= lCachedDateLastModified)) {
                        // Cached version is recent; RequestorPool established: return it
                        _logger.debug("Retrieved requestorpool from cache: " + sPoolId);
                        return oRPE._oRequestorPool;
                    }
                }
                // No cached RequestorPool, or RequestorPool is expired
            }

            oRequestorPool = new JDBCRequestorPool(oResultSet, _oDataSource, _sPoolsTable, _sRequestorsTable,
                    _sRequestorPropertiesTable, _sAuthenticationTable, _sPoolPropertiesTable);

            if (_isCacheEnabled) {
                // Add to or update cache
                Date dLastModified = oResultSet.getDate(JDBCRequestorPool.COLUMN_DATE_LAST_MODIFIED);
                RequestorPoolEntry oRPE = new RequestorPoolEntry(dLastModified, oRequestorPool);

                _mRequestorPoolMap.put(oRequestorPool.getID(), oRPE);
            }
        }

        if (oRequestorPool != null)
            _logger.debug("Retrieved requestorpool: " + oRequestorPool);
        else
            _logger.debug("No requestorpool found for requestor: " + sRequestor);
    } catch (SQLException e) {
        _logger.error("Can not read from database", e);
        throw new RequestorException(SystemErrors.ERROR_RESOURCE_RETRIEVE);
    } catch (RequestorException e) {
        throw e;
    } catch (Exception e) {
        _logger.fatal("Internal error during retrieval of requestor: " + sRequestor, e);
        throw new RequestorException(SystemErrors.ERROR_INTERNAL);
    } finally {
        try {
            if (oResultSet != null)
                oResultSet.close();
        } catch (Exception e) {
            _logger.error("Could not close resultset", e);
        }

        try {
            if (oPreparedStatement != null)
                oPreparedStatement.close();
        } catch (Exception e) {
            _logger.error("Could not close statement", e);
        }

        try {
            if (oConnection != null)
                oConnection.close();
        } catch (Exception e) {
            _logger.error("Could not close connection", e);
        }
    }
    return oRequestorPool;
}

From source file:com.mirth.connect.donkey.test.util.TestUtils.java

public static Map<String, Object> getCustomMetaData(String channelId, long messageId, int metaDataId)
        throws SQLException {
    Map<String, Object> map = new HashMap<String, Object>();
    List<MetaDataColumn> columns = getExistingMetaDataColumns(channelId);

    if (columns.size() > 0) {
        long localChannelId = ChannelController.getInstance().getLocalChannelId(channelId);
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet result = null;

        try {/*from w  ww .  ja  v  a 2 s .  co  m*/
            connection = getConnection();
            statement = connection.prepareStatement(
                    "SELECT * FROM d_mcm" + localChannelId + " WHERE message_id = ? AND metadata_id = ?");
            statement.setLong(1, messageId);
            statement.setInt(2, metaDataId);
            result = statement.executeQuery();

            if (result.next()) {
                for (MetaDataColumn column : columns) {
                    result.getObject(column.getName());

                    if (!result.wasNull()) {
                        // @formatter:off
                        switch (column.getType()) {
                        case BOOLEAN:
                            map.put(column.getName(), result.getBoolean(column.getName()));
                            break;
                        case NUMBER:
                            map.put(column.getName(), result.getBigDecimal(column.getName()));
                            break;
                        case STRING:
                            map.put(column.getName(), result.getString(column.getName()));
                            break;
                        case TIMESTAMP:
                            Calendar calendar = Calendar.getInstance();
                            calendar.setTimeInMillis(result.getTimestamp(column.getName()).getTime());
                            map.put(column.getName(), calendar);
                            break;
                        }
                        // @formatter:on
                    }
                }
            }
        } finally {
            close(result);
            close(statement);
            close(connection);
        }
    }

    return map;
}

From source file:com.concursive.connect.web.modules.login.dao.User.java

/**
 * Updates the database with information about the user's current successful login
 *
 * @param db       Description of the Parameter
 * @param password Description of the Parameter
 * @return Description of the Return Value
 * @throws SQLException Description of the Exception
 *///ww  w . jav  a2 s.  c  o  m
public boolean updateLogin(Connection db, HttpServletRequest request, ApplicationPrefs prefs, String password)
        throws SQLException {
    // Update the last login date
    PreparedStatement pst = db
            .prepareStatement("UPDATE users " + "SET last_login = CURRENT_TIMESTAMP " + "WHERE user_id = ? ");
    pst.setInt(1, id);
    pst.execute();
    pst.close();
    try {
        if (password != null) {
            // Update the permanent password if user is using a temporary password
            pst = db.prepareStatement("UPDATE users " + "SET password = ? " + "WHERE user_id = ? "
                    + "AND password <> ? AND temporary_password = ? ");
            String passwordHash = PasswordHash.encrypt(password);
            pst.setString(1, passwordHash);
            pst.setInt(2, id);
            pst.setString(3, passwordHash);
            pst.setString(4, passwordHash);
            pst.execute();
            pst.close();

            // Update the webdav passwords
            pst = db.prepareStatement("UPDATE users " + "SET webdav_password = ? " + "WHERE user_id = ? "
                    + "AND (webdav_password IS NULL OR webdav_password <> ?) ");
            String webdav = PasswordHash.encrypt(username + ":" + WebdavServlet.USER_REALM + ":" + password);
            pst.setString(1, webdav);
            pst.setInt(2, id);
            pst.setString(3, webdav);
            pst.execute();
            pst.close();

            // Update the htpasswd
            if ("true".equals(prefs.get("HTPASSWD"))) {
                pst = db.prepareStatement(
                        "UPDATE users " + "SET htpasswd = ?, htpasswd_date = CURRENT_TIMESTAMP "
                                + "WHERE user_id = ? " + "AND (htpasswd IS NULL OR htpasswd <> ?) ");
                String htpasswd = PasswordHash.htpasswd(username, password);
                pst.setString(1, htpasswd);
                pst.setInt(2, id);
                pst.setString(3, htpasswd);
                pst.execute();
                pst.close();
            }
        }
    } catch (Exception e) {
        // This column might not exist yet so catch the fail
    }

    // Load the user's updated login value
    pst = db.prepareStatement("SELECT last_login " + "FROM users " + "WHERE user_id = ? ");
    pst.setInt(1, id);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        lastLogin = rs.getTimestamp("last_login");
    }
    rs.close();
    pst.close();

    // Record the user login event
    if (System.getProperty("DEBUG") != null) {
        System.out.println("User-> Logging user IP: " + request.getRemoteAddr());
    }
    pst = db.prepareStatement("INSERT INTO user_log (user_id, ip_address, browser) VALUES (?, ?, ?)");
    pst.setInt(1, id);
    pst.setString(2, request.getRemoteAddr());
    pst.setString(3, request.getHeader("USER-AGENT"));
    pst.execute();
    pst.close();
    CacheUtils.invalidateValue(Constants.SYSTEM_USER_CACHE, id);
    return true;
}

From source file:com.webpagebytes.cms.local.WPBLocalDataStoreDao.java

private <T> T copyResultSetToObject(ResultSet resultSet, Class<T> kind)
        throws SQLException, WPBSerializerException {
    try {//from  w ww.j a va2 s.c  om
        T result = kind.newInstance();
        Field[] fields = kind.getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            boolean storeField = (field.getAnnotation(WPBAdminFieldKey.class) != null)
                    || (field.getAnnotation(WPBAdminFieldStore.class) != null)
                    || (field.getAnnotation(WPBAdminFieldTextStore.class) != null);
            if (storeField) {
                String fieldName = field.getName();
                String fieldNameUpperCase = field.getName().toUpperCase();
                PropertyDescriptor pd = new PropertyDescriptor(fieldName, kind);
                // get the field type
                if (field.getType() == Long.class) {
                    Long value = resultSet.getLong(fieldNameUpperCase);
                    pd.getWriteMethod().invoke(result, value);
                } else if (field.getType() == String.class) {
                    String value = resultSet.getString(fieldNameUpperCase);
                    pd.getWriteMethod().invoke(result, value);
                } else if (field.getType() == Integer.class) {
                    Integer value = resultSet.getInt(fieldNameUpperCase);
                    pd.getWriteMethod().invoke(result, value);
                } else if (field.getType() == Date.class) {
                    Timestamp ts = resultSet.getTimestamp(fieldNameUpperCase);
                    Date value = new Date(ts.getTime());
                    pd.getWriteMethod().invoke(result, value);

                }
            }
        }
        return result;
    } catch (Exception e) {
        throw new WPBSerializerException("Cannot deserialize from Result Set", e);
    }
}

From source file:com.nway.spring.jdbc.bean.JavassistBeanProcessor.java

private Object processColumn(ResultSet rs, int index, Class<?> propType, String writer, StringBuilder handler)
        throws SQLException {
    if (propType.equals(String.class)) {
        handler.append("bean.").append(writer).append("(").append("$1.getString(").append(index).append("));");
        return rs.getString(index);
    } else if (propType.equals(Integer.TYPE)) {
        handler.append("bean.").append(writer).append("(").append("$1.getInt(").append(index).append("));");
        return rs.getInt(index);
    } else if (propType.equals(Integer.class)) {
        handler.append("bean.").append(writer).append("(").append("integerValue($1.getInt(").append(index)
                .append("),$1.wasNull()));");
        return JdbcUtils.getResultSetValue(rs, index, Integer.class);
    } else if (propType.equals(Long.TYPE)) {
        handler.append("bean.").append(writer).append("(").append("$1.getLong(").append(index).append("));");
        return rs.getLong(index);
    } else if (propType.equals(Long.class)) {
        handler.append("bean.").append(writer).append("(").append("longValue($1.getLong(").append(index)
                .append("),$1.wasNull()));");
        return JdbcUtils.getResultSetValue(rs, index, Long.class);
    } else if (propType.equals(java.sql.Date.class)) {
        handler.append("bean.").append(writer).append("(").append("$1.getDate(").append(index).append("));");
        return rs.getDate(index);
    } else if (propType.equals(java.util.Date.class) || propType.equals(Timestamp.class)) {
        handler.append("bean.").append(writer).append("(").append("$1.getTimestamp(").append(index)
                .append("));");
        return rs.getTimestamp(index);
    } else if (propType.equals(Double.TYPE)) {
        handler.append("bean.").append(writer).append("(").append("$1.getDouble(").append(index).append("));");
        return rs.getDouble(index);
    } else if (propType.equals(Double.class)) {
        handler.append("bean.").append(writer).append("(").append("doubleValue($1.getDouble(").append(index)
                .append("),$1.wasNull()));");
        return JdbcUtils.getResultSetValue(rs, index, Double.class);
    } else if (propType.equals(Float.TYPE)) {
        handler.append("bean.").append(writer).append("(").append("$1.getFloat(").append(index).append("));");
        return rs.getFloat(index);
    } else if (propType.equals(Float.class)) {
        handler.append("bean.").append(writer).append("(").append("floatValue($1.getFloat(").append(index)
                .append("),$1.wasNull()));");
        return JdbcUtils.getResultSetValue(rs, index, Float.class);
    } else if (propType.equals(Time.class)) {
        handler.append("bean.").append(writer).append("(").append("$1.getTime(").append(index).append("));");
        return rs.getTime(index);
    } else if (propType.equals(Boolean.TYPE)) {
        handler.append("bean.").append(writer).append("(").append("$1.getBoolean(").append(index).append("));");
        return rs.getBoolean(index);
    } else if (propType.equals(Boolean.class)) {
        handler.append("bean.").append(writer).append("(").append("booleanValue($1.getBoolean(").append(index)
                .append("),$1.wasNull()));");
        return JdbcUtils.getResultSetValue(rs, index, Boolean.class);
    } else if (propType.equals(byte[].class)) {
        handler.append("bean.").append(writer).append("(").append("$1.getBytes(").append(index).append("));");
        return rs.getBytes(index);
    } else if (BigDecimal.class.equals(propType)) {
        handler.append("bean.").append(writer).append("(").append("$1.getBigDecimal(").append(index)
                .append("));");
        return rs.getBigDecimal(index);
    } else if (Blob.class.equals(propType)) {
        handler.append("bean.").append(writer).append("(").append("$1.getBlob(").append(index).append("));");
        return rs.getBlob(index);
    } else if (Clob.class.equals(propType)) {
        handler.append("bean.").append(writer).append("(").append("$1.getClob(").append(index).append("));");
        return rs.getClob(index);
    } else if (propType.equals(Short.TYPE)) {
        handler.append("bean.").append(writer).append("(").append("$1.getShort(").append(index).append("));");
        return rs.getShort(index);
    } else if (propType.equals(Short.class)) {
        handler.append("bean.").append(writer).append("(").append("shortValue($1.getShort(").append(index)
                .append("),$1.wasNull()));");
        return JdbcUtils.getResultSetValue(rs, index, Short.class);
    } else if (propType.equals(Byte.TYPE)) {
        handler.append("bean.").append(writer).append("(").append("$1.getByte(").append(index).append("));");
        return rs.getByte(index);
    } else if (propType.equals(Byte.class)) {
        handler.append("bean.").append(writer).append("(").append("byteValue($1.getByte(").append(index)
                .append("),$1.wasNull()));");
        return JdbcUtils.getResultSetValue(rs, index, Byte.class);
    } else {/*from  ww  w.  j av a  2 s  . co m*/
        handler.append("bean.").append(writer).append("(").append("(").append(propType.getName()).append(")")
                .append("$1.getObject(").append(index).append("));");
        return rs.getObject(index);
    }
}

From source file:dk.netarkivet.harvester.datamodel.DomainDBDAO.java

/**
 * Read history info entries for the domain.
 * @param c /*from  w  w w .j  a va 2s . c  om*/
 *            A connection to the database
 * @param d
 *            The domain being read. Its ID must be set.
 * @throws SQLException
 *             If database errors occur.
 */
private void readHistoryInfo(Connection c, Domain d) throws SQLException {
    // Read history info
    PreparedStatement s = c.prepareStatement("SELECT historyinfo_id, stopreason, " + "objectcount, bytecount, "
            + "name, job_id, harvest_id, harvest_time " + "FROM historyinfo, configurations "
            + "WHERE configurations.domain_id = ?" + "  AND historyinfo.config_id = configurations.config_id");
    s.setLong(1, d.getID());
    ResultSet res = s.executeQuery();
    while (res.next()) {
        long hiID = res.getLong(1);
        int stopreasonNum = res.getInt(2);
        StopReason stopreason = StopReason.getStopReason(stopreasonNum);
        long objectCount = res.getLong(3);
        long byteCount = res.getLong(4);
        String configName = res.getString(5);
        Long jobId = res.getLong(6);
        if (res.wasNull()) {
            jobId = null;
        }
        long harvestId = res.getLong(7);
        Date harvestTime = new Date(res.getTimestamp(8).getTime());
        HarvestInfo hi;
        // XML DAOs didn't keep the job id in harvestinfo, so some
        // entries will be null.
        hi = new HarvestInfo(harvestId, jobId, d.getName(), configName, harvestTime, byteCount, objectCount,
                stopreason);
        hi.setID(hiID);
        d.getHistory().addHarvestInfo(hi);
    }
}

From source file:com.sangupta.fileanalysis.db.DBResultViewer.java

/**
 * View resutls of a {@link ResultSet}.//from   w  w  w  . j  a  v a  2 s  . c o  m
 * 
 * @param resultSet
 * @throws SQLException 
 */
public void viewResult(ResultSet resultSet) throws SQLException {
    if (resultSet == null) {
        // nothing to do
        return;
    }

    // collect the meta
    ResultSetMetaData meta = resultSet.getMetaData();

    final int numColumns = meta.getColumnCount();
    final int[] displaySizes = new int[numColumns + 1];
    final int[] colType = new int[numColumns + 1];

    for (int index = 1; index <= numColumns; index++) {
        colType[index] = meta.getColumnType(index);
        displaySizes[index] = getColumnSize(meta.getTableName(index), meta.getColumnName(index),
                colType[index]);
    }

    // display the header row
    for (int index = 1; index <= numColumns; index++) {
        center(meta.getColumnLabel(index), displaySizes[index]);
    }
    System.out.println("|");
    for (int index = 1; index <= numColumns; index++) {
        System.out.print("+" + StringUtils.repeat('-', displaySizes[index] + 2));
    }
    System.out.println("+");

    // start iterating over the result set
    int rowsDisplayed = 0;
    int numRecords = 0;
    while (resultSet.next()) {
        // read and display the value
        rowsDisplayed++;
        numRecords++;

        for (int index = 1; index <= numColumns; index++) {
            switch (colType[index]) {
            case Types.DECIMAL:
            case Types.DOUBLE:
            case Types.REAL:
                format(resultSet.getDouble(index), displaySizes[index]);
                continue;

            case Types.INTEGER:
            case Types.SMALLINT:
                format(resultSet.getInt(index), displaySizes[index]);
                continue;

            case Types.VARCHAR:
                format(resultSet.getString(index), displaySizes[index], false);
                continue;

            case Types.TIMESTAMP:
                format(resultSet.getTimestamp(index), displaySizes[index]);
                continue;

            case Types.BIGINT:
                format(resultSet.getBigDecimal(index), displaySizes[index]);
                continue;
            }
        }

        // terminator for row and new line
        System.out.println("|");

        // check for rows displayed
        if (rowsDisplayed == 20) {
            // ask the user if more data needs to be displayed
            String cont = ConsoleUtils.readLine("Type \"it\" for more: ", true);
            if (!"it".equalsIgnoreCase(cont)) {
                break;
            }

            // continue;
            rowsDisplayed = 0;
            continue;
        }
    }

    System.out.println("\nTotal number of records found: " + numRecords);
}

From source file:morphy.command.LLoginsCommand.java

public void process(String arguments, UserSession userSession) {
    /* Sun Aug  7, 12:00 MDT 2011: GuestGYKQ(U)         logout */

    boolean empty = StringUtils.isEmpty(arguments);
    boolean numeric = StringUtils.isNumeric(arguments);

    int limit = 10; //200
    arguments = arguments.trim();/*from w  w  w . j av  a  2s .c o m*/
    if (empty) {
        limit = 10;
    } else if (numeric && !empty) {
        limit = Integer.parseInt(arguments);
    } else if (!numeric && !empty) {
        userSession.send(getContext().getUsage());
        return;
    }

    boolean isAdmin = UserService.getInstance().isAdmin(userSession.getUser().getUserName());
    StringBuilder b = new StringBuilder();

    final java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("EEE MMM dd, HH:mm z yyyy");

    int count = 0;
    String query = "SELECT COUNT(*) FROM `logins`";
    ResultSet rs = DatabaseConnectionService.getInstance().getDBConnection().executeQueryWithRS(query);
    try {
        if (rs.next()) {
            count = rs.getInt(1);
        }
    } catch (SQLException e) {
        Morphy.getInstance().onError(e);
    }

    if (limit > count)
        limit = count;

    query = "SELECT `id` FROM `logins` ORDER BY `id` DESC LIMIT " + limit;
    rs = DatabaseConnectionService.getInstance().getDBConnection().executeQueryWithRS(query);
    int[] arr = new int[limit];
    try {
        int index = 0;
        while (rs.next()) {
            arr[index++] = rs.getInt(1);
        }
    } catch (SQLException e) {
        Morphy.getInstance().onError(e);
    }
    java.util.Arrays.sort(arr);

    Map<String, Boolean> registeredCache = new HashMap<String, Boolean>();

    query = "SELECT `id`,`username`,CONVERT_TZ(`timestamp`,'UTC','SYSTEM'),`type`"
            + (isAdmin ? ",`ipAddress`" : "") + " FROM logins WHERE "
            + MessagesCommand.formatIdListForQuery("id", arr) + " ORDER BY id ASC";
    rs = DatabaseConnectionService.getInstance().getDBConnection().executeQueryWithRS(query);
    try {
        while (rs.next()) {
            String line = "";
            String username = rs.getString(2);
            if (!registeredCache.containsKey(username.toLowerCase())) {
                boolean registered = UserService.getInstance().isRegistered(username);
                if (!registered)
                    username += "(U)";
                registeredCache.put(username.toLowerCase(), registered);
            } else /* we have cached information about whether this user is registered */ {
                boolean registered = registeredCache.get(username.toLowerCase());
                if (!registered)
                    username += "(U)";
            }

            if (!isAdmin)
                line = String.format("%26s: %-20s %s", sdf.format(rs.getTimestamp(3).getTime()), username,
                        rs.getString(4));
            if (isAdmin)
                line = String.format("%26s: %-20s %7s from %s", sdf.format(rs.getTimestamp(3).getTime()),
                        username, rs.getString(4), rs.getString(5));
            if (rs.next()) {
                line += "\n";
                rs.previous();
            }
            b.append(line);
        }
    } catch (SQLException e) {
        Morphy.getInstance().onError(e);
    }

    userSession.send(b.toString());
    return;

    /*UserSession uS = null;
    String[] array = UserService.getInstance().completeHandle(arguments);
    if (array.length == 0) {
       userSession.send(arguments + " is not logged in.");
       return;
    }
    if (array.length > 1) {
       StringBuilder b = new StringBuilder();
       b.append("-- Matches: " + array.length + " player(s) --\n");
       for(int i=0;i<array.length;i++) {
    b.append(array[i] + "  ");
       }
       userSession.send(b.toString());
       return;
    }
    uS = UserService.getInstance().getUserSession(array[0]);
    */
}

From source file:org.ohmage.query.impl.CampaignQueries.java

@Override
public QueryResultsList<Campaign> getCampaignInformation(final String username,
        final Collection<String> campaignIds, final Collection<String> classIds,
        final Collection<String> nameTokens, final Collection<String> descriptionTokens,
        final DateTime startDate, final DateTime endDate, final Campaign.PrivacyState privacyState,
        final Campaign.RunningState runningState, final Campaign.Role role) throws DataAccessException {

    try {//from  www.j av a2s.  c om
        // Begin with a common set of elements to select, and the tables to
        // which those elements belong.
        StringBuilder builder = new StringBuilder("SELECT ca.urn, ca.name, ca.description, "
                + "ca.icon_url, ca.authored_by, " + "crs.running_state, cps.privacy_state, "
                + "ca.creation_timestamp, " + "ca.xml " + "FROM " + "user u, " + "campaign ca, "
                + "campaign_running_state crs, " + "campaign_privacy_state cps " + "WHERE u.username = ? "
                + "AND ca.running_state_id = crs.id " + "AND ca.privacy_state_id = cps.id " +
                // ACL
                "AND (" + "(u.admin = true)" + " OR " + "EXISTS (" + "SELECT id "
                + "FROM user_role_campaign urc " + "WHERE u.id = urc.user_id " + "AND ca.id = urc.campaign_id"
                + ")" + ")");

        List<Object> parameters = new LinkedList<Object>();
        parameters.add(username);

        if (campaignIds != null) {
            if (campaignIds.size() == 0) {
                return (new QueryResultListBuilder<Campaign>()).getQueryResult();
            }

            builder.append(" AND ca.urn IN ").append(StringUtils.generateStatementPList(campaignIds.size()));

            parameters.addAll(campaignIds);
        }

        if (classIds != null) {
            if (classIds.size() == 0) {
                return (new QueryResultListBuilder<Campaign>()).getQueryResult();
            }

            builder.append(" AND (" + "ca.id IN (" + "SELECT cc.campaign_id " + "FROM campaign_class cc "
                    + "WHERE cc.class_id IN (" + "SELECT cl.id " + "FROM class cl " + "WHERE cl.urn IN "
                    + StringUtils.generateStatementPList(classIds.size()) + ")" + ")" + ")");

            parameters.addAll(classIds);
        }

        if (nameTokens != null) {
            if (nameTokens.size() == 0) {
                return (new QueryResultListBuilder<Campaign>()).getQueryResult();
            }

            boolean firstPass = true;
            builder.append(" AND (");
            for (String nameToken : nameTokens) {
                if (firstPass) {
                    firstPass = false;
                } else {
                    builder.append(" OR ");
                }

                builder.append("ca.name LIKE ?");
                parameters.add('%' + nameToken + '%');
            }
            builder.append(")");
        }

        if (descriptionTokens != null) {
            if (descriptionTokens.size() == 0) {
                return (new QueryResultListBuilder<Campaign>()).getQueryResult();
            }

            boolean firstPass = true;
            builder.append(" AND (");
            for (String descriptionToken : descriptionTokens) {
                if (firstPass) {
                    firstPass = false;
                } else {
                    builder.append(" OR ");
                }

                builder.append("ca.description LIKE ?");
                parameters.add('%' + descriptionToken + '%');
            }
            builder.append(")");
        }

        if (startDate != null) {
            builder.append(" AND creation_timestamp >= ?");

            parameters.add(DateTimeUtils.getIso8601DateString(startDate, true));
        }

        if (endDate != null) {
            builder.append(" AND creation_timestamp <= ?");

            parameters.add(DateTimeUtils.getIso8601DateString(endDate, true));
        }

        if (runningState != null) {
            builder.append(" AND crs.running_state = ?");

            parameters.add(runningState.toString());
        }

        if (privacyState != null) {
            builder.append(" AND cps.privacy_state = ?");

            parameters.add(privacyState.toString());
        }

        if (role != null) {
            builder.append(" AND (" + "ca.id IN (" + "SELECT urc.campaign_id "
                    + "FROM user_role ur, user_role_campaign urc " + "WHERE u.id = urc.user_id "
                    + "AND ur.id = urc.user_role_id " + "AND ur.role = ?" + ")" + ")");

            parameters.add(role.toString());
        }

        return getJdbcTemplate().query(builder.toString(), parameters.toArray(),
                new ResultSetExtractor<QueryResultsList<Campaign>>() {
                    /**
                     * Counts the total number of results and converts each
                     * of the actual results into a Campaign object.
                     */
                    @Override
                    public QueryResultsList<Campaign> extractData(ResultSet rs)
                            throws SQLException, org.springframework.dao.DataAccessException {

                        try {
                            QueryResultListBuilder<Campaign> result = new QueryResultListBuilder<Campaign>();

                            while (rs.next()) {
                                result.addResult(new Campaign(null, null, rs.getString("description"),
                                        Campaign.RunningState
                                                .valueOf(rs.getString("running_state").toUpperCase()),
                                        Campaign.PrivacyState
                                                .valueOf(rs.getString("privacy_state").toUpperCase()),
                                        new DateTime(rs.getTimestamp("creation_timestamp").getTime()).toDate(),
                                        rs.getString("xml")));
                            }

                            return result.getQueryResult();
                        } catch (DomainException e) {
                            throw new SQLException(e);
                        }
                    }
                });
    } catch (org.springframework.dao.DataAccessException e) {
        throw new DataAccessException(e);
    }
}

From source file:dk.netarkivet.harvester.datamodel.DomainDBDAO.java

@Override
public DomainHistory getDomainHistory(String domainName) {
    ArgumentNotValid.checkNotNullOrEmpty(domainName, "String domainName");
    Connection c = HarvestDBConnection.get();
    DomainHistory history = new DomainHistory();
    // Read history info
    PreparedStatement s = null;/* w ww.  j a  v  a  2  s .co m*/
    try {
        s = c.prepareStatement("SELECT historyinfo_id, stopreason, " + "objectcount, bytecount, "
                + "name, job_id, harvest_id, harvest_time " + "FROM historyinfo, configurations "
                + "WHERE configurations.domain_id = " + "(SELECT domain_id FROM domains WHERE name=?)"
                + "  AND historyinfo.config_id " + " = configurations.config_id");
        s.setString(1, domainName);
        ResultSet res = s.executeQuery();
        while (res.next()) {
            long hiID = res.getLong(1);
            int stopreasonNum = res.getInt(2);
            StopReason stopreason = StopReason.getStopReason(stopreasonNum);
            long objectCount = res.getLong(3);
            long byteCount = res.getLong(4);
            String configName = res.getString(5);
            Long jobId = res.getLong(6);
            if (res.wasNull()) {
                jobId = null;
            }
            long harvestId = res.getLong(7);
            Date harvestTime = new Date(res.getTimestamp(8).getTime());
            HarvestInfo hi;

            hi = new HarvestInfo(harvestId, jobId, domainName, configName, harvestTime, byteCount, objectCount,
                    stopreason);
            hi.setID(hiID);
            history.addHarvestInfo(hi);
        }
    } catch (SQLException e) {
        throw new IOFailure("Error while fetching DomainHistory for domain '" + domainName + "': "
                + ExceptionUtils.getSQLExceptionCause(e), e);
    } finally {
        DBUtils.closeStatementIfOpen(s);
        HarvestDBConnection.release(c);
    }

    return history;
}