CODEWITHSUNDEEP
sqljspservlets
k0mikus
k0mikus

Reputation: 33

How to query database and return single row only

I'm new to programming and I'm trying to do simple projects to learn more. I've done researching, but I can't seem to find a solution to my problem. Perhaps, my program is not properly structured, but here it goes:

THIS BLOCK WILL VALIDATE IF THE ENTERED EMPLOYEE ID ALREADY EXISTS IN DATABASE. THIS IS CALLED IN A SERVLET

public boolean login(String employeeID) throws SQLException {

    String sql = "select count(*) as count from employees where emp_id=?";
    statement = connection.prepareStatement(sql);
    statement.setString(1, employeeID);

    rs = statement.executeQuery();

    if (rs.next()) {
        count = rs.getInt("count");
    }

    rs.close();

    if (count == 0) {
        return false;
    } else {
        return true;
    }

}

/* METHOD BELOW ITERATES THE FIELDS FROM MYSQL DATABASE, BUT IT DISPLAYS ALL OF IT. 
I JUST WANT TO GET A SINGLE ROW MATCHING THE EMPLOYEE ID PARAMETER ENTERED.*/

public List<EmployeeNumber> _list() throws SQLException {

    List<EmployeeNumber> result = new ArrayList<>();
    String sql = "select * from employees";
    statement = connection.prepareStatement(sql);
    rs = statement.executeQuery();

    if (rs.next()) {
        EmployeeNumber emp = new EmployeeNumber();
        emp.setEmployeeNumber(rs.getString(1));
        emp.setFirstName(rs.getString(2));
        emp.setLastName(rs.getString(3));
        emp.setEmail(rs.getString(4));
        emp.setDepartment(rs.getString(5));
        emp.setFirstApprover(rs.getString(6));
        emp.setSecondApprover(rs.getString(7));
        result.add(emp);
    }

    if (rs != null) {
        rs.close();
    }

    if (statement != null) {
        statement.close();
    }

    if (connection != null) {
        connection.close();
    }

    return result;
    }
}

I think it has something to do with my SQL Query statement but I can't figure out how to fix it.

So in a nutshell, when I submit the employee ID from JSP page, it will validate if that exists, and if it does, I want to display all the column fields within the same row where this employee ID is positioned. How do I do that? Results will be displayed on another JSP page.

Thank you.

Upvotes: 0

Views: 2450

Answers (2)

Relevant
Relevant

Reputation: 169

Maybe try something like this?

String sql = "select count(*) as count from employees where emp_id=?";
statement = connection.prepareStatement(sql);
statement.setString(1, employeeID);

int count = statement.ExecuteScalar();

if (count == 0) {
    return false;
} else {
    return true;
}

}

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx

You can also set a breakpoint and step through your code and see where the exception is being thrown. That would help us, knowing the exception message and where it's breaking.

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691903

You're first counting how many employees have the given ID. Then you're selecting all the rows from the employee table.

Skip the first query, and only use the second one, but by adding a where clause, just as you did with the first query:

select * from employees where emp_id=?

Then after you've bound the parameter (as you did for the first query), test if there is a row returned:

if (rs.next()) {
    // get the data, and return an EmployeeNumber instance containing the data
}
else {
    // no employee with the given ID exists: return null
}

Note that the method shouldn't return a List<EmployeeNumber>, but an EmployeeNumber, since you only want to get 1 employee from the table.

Upvotes: 1

Related Questions