Example usage for java.sql ResultSet getString

List of usage examples for java.sql ResultSet getString

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

Introduction

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

Prototype

String getString(String columnLabel) throws SQLException;

Source Link

Document

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

Usage

From source file:com.bluepandora.therap.donatelife.adminpanel.JsonBuilder.java

public static JSONObject adminProfile(ResultSet result) throws JSONException {
    JSONArray jsonArray = new JSONArray();
    JSONObject jsonObject = null;/*from ww  w .jav  a  2s. c  o m*/
    try {
        while (result.next()) {
            jsonObject = new JSONObject();
            jsonObject.put(jsFirstName, result.getString(dbFirstName));
            jsonObject.put(jsLastName, result.getString(dbLastName));
            jsonObject.put(jsEmail, result.getString(dbEmail));
            jsonObject.put(jsMobileNumber, result.getString(dbMobileNumber));
            jsonArray.put(jsonObject);
        }

        if (jsonArray.length() != 0) {
            jsonObject = new JSONObject();
            jsonObject.put("admin", jsonArray);
            jsonObject.put(jsDONE, 1);
        } else {
            jsonObject = new JSONObject();
            jsonObject.put("message", "NO VALID ADMIN FOUND!");
            jsonObject.put(jsDONE, 0);
        }
    } catch (SQLException error) {
        Debug.debugLog("Admin SQL : ", error);
        jsonObject = new JSONObject();
        jsonObject.put(jsDONE, 0);
    }
    return jsonObject;
}

From source file:com.keybox.manage.db.ProfileDB.java

/**
 * returns all profile information//  ww w  . java2  s.c  o  m
 *
 * @return list of profiles
 */
public static List<Profile> getAllProfiles() {

    ArrayList<Profile> profileList = new ArrayList<>();
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from  profiles order by nm asc");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Profile profile = new Profile();
            profile.setId(rs.getLong("id"));
            profile.setNm(rs.getString("nm"));
            profile.setDesc(rs.getString("desc"));
            profileList.add(profile);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    } finally {
        DBUtils.closeConn(con);
    }

    return profileList;
}

From source file:com.bluepandora.therap.donatelife.adminpanel.JsonBuilder.java

public static JSONObject getHospitalListJson(ResultSet result) throws JSONException {
    JSONArray jsonArray = new JSONArray();
    JSONObject jsonObject = null;//from  w ww.j av a  2 s .c om
    try {
        while (result.next()) {
            jsonObject = new JSONObject();
            jsonObject.put(jsHospitalId, result.getString(dbHospitalId));
            jsonObject.put(jsDistName, result.getString(dbDistName));
            jsonObject.put(jsHospitalName, result.getString(dbHospitalName));
            jsonObject.put(jsHospitalBName, result.getString(dbHospitalBName));
            jsonArray.put(jsonObject);
        }

        if (jsonArray.length() != 0) {
            jsonObject = new JSONObject();
            jsonObject.put("hospitalList", jsonArray);
            jsonObject.put(jsDONE, 1);
        } else {
            jsonObject = new JSONObject();
            jsonObject.put("message", "NO HOSPITAL LIST FOUND!");
            jsonObject.put(jsDONE, 0);
        }
    } catch (SQLException error) {
        Debug.debugLog("Hospital List: ", error);
        jsonObject = new JSONObject();
        jsonObject.put(jsDONE, 0);
    }
    return jsonObject;
}

From source file:CreateNewType.java

private static Vector getDataTypes(Connection con, String typeToCreate) throws SQLException {
    String structName = null, distinctName = null, javaName = null;

    // create a vector of class DataType initialized with
    // the SQL code, the SQL type name, and two null entries
    // for the local type name and the creation parameter(s)

    Vector dataTypes = new Vector();
    dataTypes.add(new DataType(java.sql.Types.BIT, "BIT"));
    dataTypes.add(new DataType(java.sql.Types.TINYINT, "TINYINT"));
    dataTypes.add(new DataType(java.sql.Types.SMALLINT, "SMALLINT"));
    dataTypes.add(new DataType(java.sql.Types.INTEGER, "INTEGER"));
    dataTypes.add(new DataType(java.sql.Types.BIGINT, "BIGINT"));
    dataTypes.add(new DataType(java.sql.Types.FLOAT, "FLOAT"));
    dataTypes.add(new DataType(java.sql.Types.REAL, "REAL"));
    dataTypes.add(new DataType(java.sql.Types.DOUBLE, "DOUBLE"));
    dataTypes.add(new DataType(java.sql.Types.NUMERIC, "NUMERIC"));
    dataTypes.add(new DataType(java.sql.Types.DECIMAL, "DECIMAL"));
    dataTypes.add(new DataType(java.sql.Types.CHAR, "CHAR"));
    dataTypes.add(new DataType(java.sql.Types.VARCHAR, "VARCHAR"));
    dataTypes.add(new DataType(java.sql.Types.LONGVARCHAR, "LONGVARCHAR"));
    dataTypes.add(new DataType(java.sql.Types.DATE, "DATE"));
    dataTypes.add(new DataType(java.sql.Types.TIME, "TIME"));
    dataTypes.add(new DataType(java.sql.Types.TIMESTAMP, "TIMESTAMP"));
    dataTypes.add(new DataType(java.sql.Types.BINARY, "BINARY"));
    dataTypes.add(new DataType(java.sql.Types.VARBINARY, "VARBINARY"));
    dataTypes.add(new DataType(java.sql.Types.LONGVARBINARY, "LONGVARBINARY"));
    dataTypes.add(new DataType(java.sql.Types.NULL, "NULL"));
    dataTypes.add(new DataType(java.sql.Types.OTHER, "OTHER"));
    dataTypes.add(new DataType(java.sql.Types.BLOB, "BLOB"));
    dataTypes.add(new DataType(java.sql.Types.CLOB, "CLOB"));

    DatabaseMetaData dbmd = con.getMetaData();
    ResultSet rs = dbmd.getTypeInfo();
    while (rs.next()) {
        int codeNumber = rs.getInt("DATA_TYPE");
        String dbmsName = rs.getString("TYPE_NAME");
        String createParams = rs.getString("CREATE_PARAMS");

        if (codeNumber == Types.STRUCT && structName == null)
            structName = dbmsName;//w w  w .  j  ava  2 s .  c  om
        else if (codeNumber == Types.DISTINCT && distinctName == null)
            distinctName = dbmsName;
        else if (codeNumber == Types.JAVA_OBJECT && javaName == null)
            javaName = dbmsName;
        else {
            for (int i = 0; i < dataTypes.size(); i++) {
                // find entry that matches the SQL code, 
                // and if local type and params are not already set,
                // set them
                DataType type = (DataType) dataTypes.get(i);
                if (type.getCode() == codeNumber) {
                    type.setLocalTypeAndParams(dbmsName, createParams);
                }
            }
        }
    }

    if (typeToCreate.equals("s")) {
        int[] types = { Types.STRUCT, Types.DISTINCT, Types.JAVA_OBJECT };
        rs = dbmd.getUDTs(null, "%", "%", types);
        while (rs.next()) {
            String typeName = null;
            DataType dataType = null;

            if (dbmd.isCatalogAtStart())
                typeName = rs.getString(1) + dbmd.getCatalogSeparator() + rs.getString(2) + "."
                        + rs.getString(3);
            else
                typeName = rs.getString(2) + "." + rs.getString(3) + dbmd.getCatalogSeparator()
                        + rs.getString(1);

            switch (rs.getInt(5)) {
            case Types.STRUCT:
                dataType = new DataType(Types.STRUCT, typeName);
                dataType.setLocalTypeAndParams(structName, null);
                break;
            case Types.DISTINCT:
                dataType = new DataType(Types.DISTINCT, typeName);
                dataType.setLocalTypeAndParams(distinctName, null);
                break;
            case Types.JAVA_OBJECT:
                dataType = new DataType(Types.JAVA_OBJECT, typeName);
                dataType.setLocalTypeAndParams(javaName, null);
                break;
            }
            dataTypes.add(dataType);
        }
    }

    return dataTypes;
}

From source file:com.jaspersoft.jasperserver.api.engine.common.virtualdatasourcequery.VirtualSQLDataSource.java

private static Set<String> getResult(ResultSet rs, String columnName) {
    Set<String> result = new HashSet<String>();
    try {//from   w ww.j  a v  a2  s  . co  m
        while (rs.next()) {
            result.add(rs.getString(columnName));
        }
    } catch (Exception ex) {
    }
    ;
    try {
        rs.close();
    } catch (Exception ex) {
    }
    ;
    return result;
}

From source file:net.tirasa.ilgrosso.resetdb.Main.java

private static void resetOracle(final Connection conn) throws Exception {

    final Statement statement = conn.createStatement();

    ResultSet resultSet = statement.executeQuery(
            "SELECT 'DROP VIEW ' || object_name || ';'" + " FROM user_objects WHERE object_type='VIEW'");
    final List<String> drops = new ArrayList<String>();
    while (resultSet.next()) {
        drops.add(resultSet.getString(1));
    }//from  w w w . ja v a  2  s.  c  o m
    resultSet.close();

    for (String drop : drops) {
        statement.executeUpdate(drop.substring(0, drop.length() - 1));
    }
    drops.clear();

    resultSet = statement.executeQuery("SELECT 'DROP INDEX ' || object_name || ';'"
            + " FROM user_objects WHERE object_type='INDEX'" + " AND object_name NOT LIKE 'SYS_%'");
    while (resultSet.next()) {
        drops.add(resultSet.getString(1));
    }
    resultSet.close();

    for (String drop : drops) {
        try {
            statement.executeUpdate(drop.substring(0, drop.length() - 1));
        } catch (SQLException e) {
            LOG.error("Could not perform: {}", drop);
        }
    }
    drops.clear();

    resultSet = statement.executeQuery("SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;'"
            + " FROM all_TABLES WHERE owner='" + ((String) ctx.getBean("username")).toUpperCase() + "'");
    while (resultSet.next()) {
        drops.add(resultSet.getString(1));
    }
    resultSet.close();

    resultSet = statement
            .executeQuery("SELECT 'DROP SEQUENCE ' || sequence_name || ';'" + " FROM user_SEQUENCES");
    while (resultSet.next()) {
        drops.add(resultSet.getString(1));
    }
    resultSet.close();

    for (String drop : drops) {
        statement.executeUpdate(drop.substring(0, drop.length() - 1));
    }

    statement.close();
    conn.close();
}

From source file:au.org.ala.sds.GeneraliseOccurrenceLocations.java

private static void run(String startAt) throws SQLException, SearchResultException {
    Connection conn = occurrenceDataSource.getConnection();
    PreparedStatement pst = conn.prepareStatement(
            "SELECT id, scientific_name, latitude, longitude, generalised_metres, raw_latitude, raw_longitude FROM raw_occurrence_record LIMIT ?,?");
    int offset = startAt == null ? 0 : Integer.parseInt(startAt);
    int stride = 10000;
    int recCount = 0;
    pst.setInt(2, stride);//w w w.j a  v  a2s.c  o m
    ResultSet rs;

    for (pst.setInt(1, offset); true; offset += stride, pst.setInt(1, offset)) {
        rs = pst.executeQuery();
        if (!rs.isBeforeFirst()) {
            break;
        }
        while (rs.next()) {
            recCount++;

            String rawScientificName = (rs.getString("scientific_name"));
            int id = rs.getInt("id");
            String latitude = rs.getString("latitude");
            String longitude = rs.getString("longitude");
            String generalised_metres = rs.getString("generalised_metres");
            String raw_latitude = rs.getString("raw_latitude");
            String raw_longitude = rs.getString("raw_longitude");

            if (StringUtils.isEmpty(rawScientificName))
                continue;
            if (StringUtils.isEmpty(latitude) || StringUtils.isEmpty(longitude))
                continue;

            // See if it's sensitive
            SensitiveTaxon ss = sensitiveSpeciesFinder.findSensitiveSpecies(rawScientificName);
            if (ss != null) {
                Map<String, String> facts = new HashMap<String, String>();
                facts.put(FactCollection.DECIMAL_LATITUDE_KEY, latitude);
                facts.put(FactCollection.DECIMAL_LONGITUDE_KEY, longitude);

                ValidationService service = ServiceFactory.createValidationService(ss);
                ValidationOutcome outcome = service.validate(facts);
                Map<String, Object> result = outcome.getResult();

                String speciesName = ss.getTaxonName();
                if (StringUtils.isNotEmpty(ss.getCommonName())) {
                    speciesName += " [" + ss.getCommonName() + "]";
                }

                if (!result.get("decimalLatitude").equals(facts.get("decimalLatitude"))
                        || !result.get("decimalLongitude").equals(facts.get("decimalLongitude"))) {
                    if (StringUtils.isEmpty(generalised_metres)) {
                        logger.info("Generalising location for " + id + " '" + rawScientificName
                                + "' using Name='" + speciesName + "', Lat=" + result.get("decimalLatitude")
                                + ", Long=" + result.get("decimalLongitude"));
                        //rawOccurrenceDao.updateLocation(id, result.get("decimalLatitude"), result.get("decimalLongitude"), result.getGeneralisationInMetres(), latitude, longitude);
                    } else {
                        if (generalised_metres != result.get("generalisationInMetres")) {
                            logger.info("Re-generalising location for " + id + " '" + rawScientificName
                                    + "' using Name='" + speciesName + "', Lat=" + result.get("decimalLatitude")
                                    + ", Long=" + result.get("decimalLongitude"));
                            //rawOccurrenceDao.updateLocation(id, result.get("decimalLatitude"), result.get("decimalLongitude"), result.getGeneralisationInMetres());
                        }
                    }
                } else {
                    logger.info("Not generalising location for " + id + " '" + rawScientificName
                            + "' using Name='" + speciesName + "', Lat=" + result.get("decimalLatitude")
                            + ", Long=" + result.get("decimalLongitude") + " - "
                            + result.get("dataGeneralizations"));
                }
            } else {
                // See if was sensitive but not now
                if (StringUtils.isNotEmpty(generalised_metres)) {
                    logger.info("De-generalising location for " + id + " '" + rawScientificName + "', Lat="
                            + raw_latitude + ", Long=" + raw_longitude);
                    //rawOccurrenceDao.updateLocation(id, raw_latitude, raw_longitude, null, null, null);
                }
            }
        }
        rs.close();
        logger.info("Processed " + recCount + " occurrence records.");
    }

    rs.close();
    pst.close();
    conn.close();
}

From source file:las.DBConnector.java

public static ArrayList<Member> getMemberTableIntoList() throws SQLException, ClassNotFoundException {
    ArrayList<Member> mtable = new ArrayList<>();
    String data = "SELECT * FROM LAS.MEMBERS";
    PreparedStatement pt = conn.prepareStatement(data);
    ResultSet rs = pt.executeQuery();
    while (rs.next()) {
        mtable.add(new Member(rs.getInt("MEMBER_ID"), rs.getString("NAME"), rs.getString("EMAIL"),
                rs.getString("PRIVILEGE"), rs.getBoolean("ISSTAFF")));
    }/*from   w w w  .j a va  2s  . co  m*/

    return mtable;
}

From source file:com.tethrnet.manage.db.SystemStatusDB.java

/**
 * returns the first system that authorized keys has not been tried
 *
 * @param userId user id// w w w .jav a  2  s. co m
 * @return hostSystem systems for authorized_keys replacement
 */
public static HostSystem getNextPendingSystem(Long userId) {

    HostSystem hostSystem = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from status where (status_cd like ? or status_cd like ? or status_cd like ?) and user_id=? order by id asc");
        stmt.setString(1, HostSystem.INITIAL_STATUS);
        stmt.setString(2, HostSystem.AUTH_FAIL_STATUS);
        stmt.setString(3, HostSystem.PUBLIC_KEY_FAIL_STATUS);
        stmt.setLong(4, userId);
        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
    return hostSystem;

}

From source file:com.tethrnet.manage.db.SystemStatusDB.java

/**
 * returns key placement status of system
 *
 * @param systemId system id//from  ww  w.ja v  a  2  s. c om
 * @param userId user id
 */
public static HostSystem getSystemStatus(Long systemId, Long userId) {

    Connection con = null;
    HostSystem hostSystem = null;
    try {
        con = DBUtils.getConn();

        PreparedStatement stmt = con.prepareStatement("select * from status where id=? and user_id=?");
        stmt.setLong(1, systemId);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
    return hostSystem;

}