Java MySQL null resultset

Question

I'm trying to write a small java application that returns the details for an employee. Here's my Employee class.

public class Employees {

private int id;
private Date dateofBirth;
private String firstName;
private String lastName;
private enum gender{
    M, F;
}
private gender employeeGender;
private Date dateHired;

public String getEmployeeGender() {
    return this.employeeGender.name();
}

public void setEmployeeGender(String employeeGender) {
    this.employeeGender = gender.valueOf(employeeGender);
}

/*Getters, setters omitted*/

Here's my DAO class

public class EmployeeDao {

final String TABLE_EMPLOYEES = "employees";
final String COLUMN_EMPLOYEES_ID = "emp_no";
final String COLUMN_EMPLOYEES_DOB = "birth_date";
final String COLUMN_EMPLOYEES_FIRST_NAME = "first_name";
final String COLUMN_EMPLOYEES_LAST_NAME = "last_name";
final String COLUMN_EMPLOYEES_GENDER = "gender";
final String COLUMN_EMPLOYEES_HIRE_DATE = "hire_date";

final String QUERY_EMPLOYEES = "SELECT * FROM " + TABLE_EMPLOYEES + " WHERE " + COLUMN_EMPLOYEES_ID + " = ?";

public Employees getEmployeeDetails(int employeeId) {
    Employees employee = new Employees();
    try (DbConnection dbConnection = new DbConnection();
         Connection databaseConnection = dbConnection.getConn();
         PreparedStatement selectFromEmployees = databaseConnection.prepareStatement(QUERY_EMPLOYEES)) {
        selectFromEmployees.setInt(1, employeeId);
        try (ResultSet result = selectFromEmployees.executeQuery()) {

            if (result.next() == false) {
                System.out.println("Empty Resultset");
            }
            while (result.next()) {
                employee.setId(result.getInt(COLUMN_EMPLOYEES_ID));
                employee.setFirstName(result.getString(COLUMN_EMPLOYEES_FIRST_NAME));
                employee.setLastName(result.getString(COLUMN_EMPLOYEES_LAST_NAME));
                employee.setDateofBirth(result.getDate(COLUMN_EMPLOYEES_DOB));
                employee.setEmployeeGender(result.getString(COLUMN_EMPLOYEES_GENDER));
                employee.setDateHired(result.getDate(COLUMN_EMPLOYEES_HIRE_DATE));
            }
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
    return employee;
}

}

But when I try to run the app in main method like this, I get an output with null values.

public static void main(String[] args) {
    EmployeeDao employeeDao = new EmployeeDao();
    Employees employees = employeeDao.getEmployeeDetails(39256);
    System.out.println(employees.getId() + " \n" + employees.getFirstName() + " \n" + employees.getLastName() + " \n" + employees.getDateofBirth() + " \n" + employees.getDateHired());
}

This is the output.

This is how the corresponding row looks like in the database

Mark Rotteveel · Accepted Answer · 2018-07-08 07:38:28Z

4

You should not call next twice, since it will move the cursor forward again. Try this:

if (result.next() == false) {
    System.out.println("Empty Resultset");
} else {
    employee.setId(result.getInt(COLUMN_EMPLOYEES_ID));
    employee.setFirstName(result.getString(COLUMN_EMPLOYEES_FIRST_NAME));
    employee.setLastName(result.getString(COLUMN_EMPLOYEES_LAST_NAME));
    employee.setDateofBirth(result.getDate(COLUMN_EMPLOYEES_DOB));
    employee.setEmployeeGender(result.getString(COLUMN_EMPLOYEES_GENDER));
    employee.setDateHired(result.getDate(COLUMN_EMPLOYEES_HIRE_DATE));
}

edited Jul 8, 2018 at 7:38

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Jul 7, 2018 at 17:44

xingbin

28.4k12 gold badges62 silver badges111 bronze badges

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

1 Comment

xingbin Over a year ago

@RoshanUpreti No problem :)

Mureinik · Accepted Answer · 2018-07-07 17:46:30Z

2

Calling ResultSet#next moves the cursor forward a row, so your if condition loses the first row. Since you know your query can return at most one row, you don't need the while loop at all, however:

public Employees getEmployeeDetails(int employeeId) throws SQLException {
    Employees employee = null;
    try (DbConnection dbConnection = new DbConnection();
         Connection databaseConnection = dbConnection.getConn();
         PreparedStatement selectFromEmployees = 
            databaseConnection.prepareStatement(QUERY_EMPLOYEES)) {

        selectFromEmployees.setInt(1, employeeId);
        try (ResultSet result = selectFromEmployees.executeQuery()) {

            if (result.next()) {
                employee = new Employees();
                employee.setId(result.getInt(COLUMN_EMPLOYEES_ID));
                employee.setFirstName(result.getString(COLUMN_EMPLOYEES_FIRST_NAME));
                employee.setLastName(result.getString(COLUMN_EMPLOYEES_LAST_NAME));
                employee.setDateofBirth(result.getDate(COLUMN_EMPLOYEES_DOB));
                employee.setEmployeeGender(result.getString(COLUMN_EMPLOYEES_GENDER));
                employee.setDateHired(result.getDate(COLUMN_EMPLOYEES_HIRE_DATE));
            }
        }
    }
    return employee;
}

answered Jul 7, 2018 at 17:46

Mureinik

316k54 gold badges399 silver badges405 bronze badges

1 Comment

Roshan Upreti Over a year ago

Thank you for the explanation. It really helped me understand what I was doing wrong.

Atul Sharma · Accepted Answer · 2018-07-08 14:22:47Z

2

No need to add extra result.next() comparison.

if (result.next() == false) {
 System.out.println("Empty Resultset");
}
while (result.next()){

}

while will execute only if there are any rows.

Check the size of list generated before using to check if it contains value or not.

edited Jul 8, 2018 at 14:22

answered Jul 7, 2018 at 17:47

Atul Sharma

11k10 gold badges42 silver badges69 bronze badges

Collectives™ on Stack Overflow

Java MySQL null resultset

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related