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:Model.Picture.java

public static Picture getModel(java.sql.ResultSet resultSet)
        throws java.sql.SQLException, javax.naming.NamingException {
    return new Picture(resultSet.getInt("id"), resultSet.getString("name"), resultSet.getInt("patient_number"),
            resultSet.getInt("uploaded"), resultSet.getInt("DSLR_cellscope"), resultSet.getInt("HDR"),
            resultSet.getInt("plus_one_exposure"), resultSet.getInt("right_left"));
}

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

/**
 * returns the first system that authorized keys has not been tried
 *
 * @param userId user id//from   w w w  .  j ava 2  s . c  o 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.keybox.manage.db.SystemStatusDB.java

/**
 * returns key placement status of system
 *
 * @param systemId system id//w w  w  . j  a  va2s  . c  o  m
 * @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;

}

From source file:com.oracle.tutorial.jdbc.CoffeesTable.java

public static void alternateViewTable(Connection con) throws SQLException {
    Statement stmt = null;//w  w w .  j  a  va  2 s.  co m
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try {
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String coffeeName = rs.getString(1);
            int supplierID = rs.getInt(2);
            float price = rs.getFloat(3);
            int sales = rs.getInt(4);
            int total = rs.getInt(5);
            System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total);
        }
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }
}

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

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

    final Statement statement = conn.createStatement();

    ResultSet resultSet = statement
            .executeQuery("SELECT concat('DROP VIEW IF EXISTS ', table_name, ' CASCADE;')"
                    + "FROM information_schema.views;");
    final List<String> drops = new ArrayList<String>();
    while (resultSet.next()) {
        drops.add(resultSet.getString(1));
    }//w ww .  ja  v  a2  s.  c  o  m
    resultSet.close();

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

    drops.add("SET FOREIGN_KEY_CHECKS = 0;");
    resultSet = statement.executeQuery("SELECT concat('DROP TABLE IF EXISTS ', table_name, ' CASCADE;')"
            + "FROM information_schema.tables;");
    while (resultSet.next()) {
        drops.add(resultSet.getString(1));
    }
    resultSet.close();
    drops.add("SET FOREIGN_KEY_CHECKS = 1;");

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

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

From source file:com.oracle.tutorial.jdbc.CoffeesTable.java

public static void viewTable(Connection con) throws SQLException {
    Statement stmt = null;//  w  ww  . ja v a  2s . c  o  m
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try {
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            String coffeeName = rs.getString("COF_NAME");
            int supplierID = rs.getInt("SUP_ID");
            float price = rs.getFloat("PRICE");
            int sales = rs.getInt("SALES");
            int total = rs.getInt("TOTAL");
            System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total);
        }

    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }
}

From source file:CreateNewTable.java

private static Vector getDataTypes(Connection con) 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;//from www .jav a 2s  . c o m
        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);
                }
            }
        }
    }

    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.keybox.manage.db.ProfileDB.java

/**
 * method to do order by based on the sorted set object for profiles
 * @return list of profiles//ww w.j av a  2s .  com
 */
public static SortedSet getProfileSet(SortedSet sortedSet) {

    ArrayList<Profile> profileList = new ArrayList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = " order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select distinct p.* from  profiles p ";
    if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM))) {
        sql = sql + ", system_map m, system s where m.profile_id = p.id and m.system_id = s.id"
                + " and (lower(s.display_nm) like ? or lower(s.host) like ?)";
    } else if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER))) {
        sql = sql + ", user_map m, users u where m.profile_id = p.id and m.user_id = u.id"
                + " and (lower(u.first_nm) like ? or lower(u.last_nm) like ?"
                + " or lower(u.email) like ? or lower(u.username) like ?)";
    }
    sql = sql + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM))) {
            stmt.setString(1, "%" + sortedSet.getFilterMap().get(FILTER_BY_SYSTEM).toLowerCase() + "%");
            stmt.setString(2, "%" + sortedSet.getFilterMap().get(FILTER_BY_SYSTEM).toLowerCase() + "%");
        } else if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER))) {
            stmt.setString(1, "%" + sortedSet.getFilterMap().get(FILTER_BY_USER).toLowerCase() + "%");
            stmt.setString(2, "%" + sortedSet.getFilterMap().get(FILTER_BY_USER).toLowerCase() + "%");
            stmt.setString(3, "%" + sortedSet.getFilterMap().get(FILTER_BY_USER).toLowerCase() + "%");
            stmt.setString(4, "%" + sortedSet.getFilterMap().get(FILTER_BY_USER).toLowerCase() + "%");
        }
        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);
    }

    sortedSet.setItemList(profileList);
    return sortedSet;
}

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

/**
 * returns all key placement statuses/*from w ww.  j a  va 2 s  . c om*/
 *
 * @param con DB connection object
 * @param userId user id
 */
private static List<HostSystem> getAllSystemStatus(Connection con, Long userId) {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();
    try {

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

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

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    return hostSystemList;

}

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

/**
 * returns all key placement statuses//from w w  w  .  j  a v a 2 s  . c om
 *
 * @param con DB connection object
 * @param userId user id
 */
private static List<HostSystem> getAllSystemStatus(Connection con, Long userId) {

    List<HostSystem> hostSystemList = new ArrayList<>();
    try {

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

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

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    return hostSystemList;

}