mySql-java simple search console application

Question

I'm trying to do a search program: front end - java and back end - mysql.

I've tried a little bit, here's the code:

public static void searchRecord() throws SQLException{

    Scanner in = new Scanner(System.in);
    int empnum;

    System.out.print("Enter employee number: ");
    empnum = in.nextInt();

    String search = "SELECT fname FROM employees WHERE emp_num='"+ empnum + "'";
        resultSet = statement.executeQuery(search);

    String empnum_rs = null;    

    while(resultSet.next()){
        empnum_rs = resultSet.getString(empnum);



    }

     System.out.print(empnum_rs);   
}

The problem I got here is that when I would type the emp_num eclipse throws these lines:

Exception in thread "main" java.sql.SQLException: Column Index out of range, 2 > 1. 
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:830)
    at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5773)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5692)
    at Test.searchRecord(Test.java:55)
    at Test.main(Test.java:37)

Ravinder Reddy · Accepted Answer · 2012-07-10 13:26:55Z

1

The getXXX... functions take column numbers starting from 1 to n where n is the max number of columns selected in the query. Your query has only one column in selection. And empnum might not be equal to 1 and hence an error is thrown.

Change:

empnum_rs = resultSet.getString(empnum);

to:

empnum_rs = resultSet.getString( 1 );

answered Jul 10, 2012 at 13:26

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1410081 Over a year ago

Oh thanks mate! I should have put the column for my emp_num.. thanks again

Ravinder Reddy Over a year ago

You can use either coumn number or column name in the get... method.

user1410081 Over a year ago

but when I added the lname column, it only displayed the fname. How can I concatenate the lastname?

Ravinder Reddy Over a year ago

You can modify select query with require fields concatenated. select concat(fname, ' ', lname) as user_name from my_table where empnum=?. It is also advised to use PreparedStatement over Statement.

user1410081 Over a year ago

Oh I see sorry such a dumb question, I managed to do it : String empnum_rs = null; String emplname_rs = null; while(resultSet.next()){ empnum_rs = resultSet.getString(1); emplname_rs = resultSet.getString(2); } System.out.print(empnum_rs + " " + emplname_rs); }

Collectives™ on Stack Overflow

mySql-java simple search console application

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related