CODEWITHSUNDEEP
javamysqldatabasejdbc
Damyan
Damyan

Reputation: 307

JDBC Discover row based on a value in column

Need help from you all in writing up this query.

I have two columns, X and Y.

I have found a value in column Y and I am trying to find its row number.

Alternatively, I was trying to do:

SELECT ID in COLUMN_NAME from TABLE_NAME WHERE COLUMN_NAME2 contains some value I have already retrieved!

private int findRow(int value) throws SQLException {
    Connection mysqlConn = DriverManager.getConnection(DB_URL, USER, PASS);

    try {
        String query ="SELECT BUILDING FROM ALLBUILDINGS WHERE BUILDINGNUMBER = 'value'";
        Statement st = mysqlConn.prepareStatement(query);
        ResultSet rs = st.executeQuery(query);
        while (rs.next()) {
            value = rs.getInt(value);
        }
    } catch (Exception ex) {
        System.out.println(ex.getMessage());
    }

    return value;   
}

Can someone tell me how I could do either of the aforementioned?

Thanks in advance!

Upvotes: 3

Views: 1363

Answers (2)

rootExplorr
rootExplorr

Reputation: 575

This should solve your problem.

private int findRow(int value) throws SQLException { Connection mysqlConn = DriverManager.getConnection(DB_URL, USER, PASS);

try {
    String query ="SELECT BUILDING FROM ALLBUILDINGS WHERE BUILDINGNUMBER = ?";
    PreparedStatement st = mysqlConn.prepareStatement(query);
    st.setInt(1,value);

    ResultSet rs = st.executeQuery(query);
    while (rs.next()) {
        value = rs.getString(1);
    }
} catch (Exception ex) {
    System.out.println(ex.getMessage());
}

return value;

}

Upvotes: 3

Chara
Chara

Reputation: 1075

I assume you're not trying to find the row index, but instead what the value of the X column is, in the row where Y column = "some_value".

SELECT X FROM ALL_BUILDINGS WHERE Y = "some_value";

This will match all rows where the Y column is "some_value", and return a corresponding set of values from column X.

Upvotes: 2

Related Questions