CODEWITHSUNDEEP
javamysqlsqldatabasejdbc
Franklin Memet
Franklin Memet

Reputation: 91

Selecting a certain row SQL

So I have a data base (UserInfo) that has the attributes: Username, Email, Password etc. But I'm trying to SELECT all values from username, but separately so I can use them individually as strings. But I cannot find any syntax that allows you to pick from a certain row.

example:

SELECT Username FROM UserInfo WHERE **row number = 1**

so that it selects the username where the row number is 1 for example. any ideas?

Here is the code: The last method allows me to check the number of fields under the attribute Username so then I can create the array size with that number x.

public void declaringDataBase() throws SQLException{

    conObj = DriverManager.getConnection
    ("jdbc:derby://localhost:1527/UserInfo","UserInfo","1234");
    statObj = conObj.createStatement();

    }
public void DisplayUsers() throws SQLException{



   String query = "SELECT Username FROM WHERE rownum = 2";  

   declaringDataBase();
   rsObj = statObj.executeQuery(query);
    System.out.println(rsObj.getString(1));

}

public void returnNumberInUsernameColumn() throws SQLException{

    String queryCount = "SELECT COUNT(Username) FROM UserInfo";


   declaringDataBase();
   rsObj = statObj.executeQuery(queryCount);

 String x = null;

 while(rsObj.next()){
     x = rsObj.getString(1); 

     }

   int returnValue = Integer.parseInt(x);
    System.out.println(returnValue);


}
}

The data base looks something like this:

Username(PK)     EmailAddress              Password
Joe01            [email protected]         34590834ffjfdnkdfd
Frank22          [email protected]           ert543897ut034noegn
Ellie334         [email protected]       345903ejfbdvkdfvvdg

where the passwords are hashed.

I want to select say just Frank22 or Joe01 using some sort of integer so that I can increment it to get the other values at Username

Upvotes: 0

Views: 1818

Answers (5)

Mayur Prajapati
Mayur Prajapati

Reputation: 627

You have to use offset for that.

Selects 1st Username of table Table1

SELECT Username FROM Table1 LIMIT 1 OFFSET 0;

Note that offset value starts at 0 and limit means how many row(s) you want to select at once.

As my convenience you should use one more column ID for that CREATE TABLE TABLE3(ID INT PRIMARY KEY AUTO_INCREMENT,Name TEXT, Age INT); And use ALTER TABLE TABLE3 AUTO_INCREMENT=1 And after this access each row with id

Upvotes: 1

mehdi touil
mehdi touil

Reputation: 1

Use the keyword rownum.

Select username from userinfo where rownum = 1

Upvotes: -1

Sashi yadav
Sashi yadav

Reputation: 358

SELECT
    *
FROM
    mytable
ORDER BY
    somefield
LIMIT 1 OFFSET 5;

this example is giving you the 6th row, offset is telling it to skip first 5 rows Works in PostgreSQL and mySQL

Upvotes: 1

daniu
daniu

Reputation: 14999

You can just select them all and then move the cursor.

ResultSet rs = statement.executeQuery("SELECT Username FROM UserInfo");
rs.absolute(yourIndex);
String username = rs.getString(1);

To "get the other values at Username", you can iterate through the set.

rs.absolute(yourIndex);
System.out.printf("username at %d: %s%n", yourIndex, rs.getString(1));
while (rs.next()) {
    yourIndex++;
    System.out.printf("username at %d: %s%n", yourIndex, rs.getString(1));
}

Upvotes: 1

Jérémy Schaefer
Jérémy Schaefer

Reputation: 63

If you don't, add an ID attribute to your db table.

Then you can select any Username by his ID.

Example

SELECT Username FROM UserInfo WHERE ID = 1

Upvotes: 0

Related Questions