Using room database to retrieve salaries for employees

Question

I encountered a problem while learning the room database, I need help I have created the room database well and write the employees name in recyclerView There is a query that I want to implement, and it collects all employees' salaries and displays them in recyclerView

I will shorten the codes

The Problem

Attempt to invoke virtual method 'void com.app.roomdatabase.database.MyViewModel.getSalariesSum(long, com.app.roomdatabase.database.GetSalarySumListener)' on a null object reference
    at com.app.roomdatabase.Adapters.EmployeeSalaryAdapter$viewHolder.bind(EmployeeSalaryAdapter.java:68)
    at com.app.roomdatabase.Adapters.EmployeeSalaryAdapter.onBindViewHolder(EmployeeSalaryAdapter.java:49)
    at com.app.roomdatabase.Adapters.EmployeeSalaryAdapter.onBindViewHolder(EmployeeSalaryAdapter.java:19)

The query that i used for retrieve salaries

@Query("select sum(amount) from salary where empId=:emp_id")
double getSalariesSum(long emp_id);

Repository

 public void getSalariesSum(long emp_id, GetSalarySumListener listener ) {
    MyRoomDatabase.databaseWriteExecuter.execute(new Runnable() {
        @Override
        public void run() {
            double value = salaryDAO.getSalariesSum(emp_id);
            listener.onValueSubmit(value);
        }
    });

**GetSalarySumListener **

public interface GetSalarySumListener {
void onValueSubmit(double value);}

ViewModel

public void getSalariesSum(long emp_id, GetSalarySumListener listener ){
    repository.getSalariesSum(emp_id,listener);
}

Adapter

public class EmployeeSalaryAdapter extends RecyclerView.Adapter<EmployeeSalaryAdapter.viewHolder> {

List <Employee> employees;
private MyViewModel mvm;

public EmployeeSalaryAdapter(List <Employee> employees, MyViewModel mvm) {
    this.employees = employees;
    this.mvm = mvm;
}

public List <Employee> getEmployees() {
    return employees;
}

public void setEmployees(List <Employee> employees) {
    this.employees = employees;
    notifyDataSetChanged();
}

@NonNull
@Override
public viewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {

    return new viewHolder(LayoutInflater.from(parent.getContext())
            .inflate(R.layout.emp_sal_item,parent,false));
}

@Override
public void onBindViewHolder(@NonNull EmployeeSalaryAdapter.viewHolder holder, int position) {
    Employee employee = employees.get(position);
    holder.bind(employee,mvm);
}

@Override
public int getItemCount() {
    return employees.size();
}

public class viewHolder extends RecyclerView.ViewHolder {
    EmpSalItemBinding binding;
    Employee employee;
    public viewHolder(@NonNull View itemView) {
        super(itemView);
        binding= EmpSalItemBinding.bind(itemView);

    }
    public void bind(Employee employee, MyViewModel mvm) {
        this.employee = employee;
        binding.empNameTv.setText(employee.getName());
        mvm.getSalariesSum(employee.getId(), new GetSalarySumListener() {
            @Override
            public void onValueSubmit(double value) {
                binding.empSalaryTv.setText(String.valueOf(value));
            }
        });
    }
}

MikeT · Accepted Answer · 2022-08-19T23:19:26.260

I'd suggest that rather than getting the Employees (Employee list) and then try to get the Salary for each employee, that you instead have a POJO, say EmployeeWithSalary that embeds (using the @Embedded annotation) the Employee class with the salary. Along the lines of:-

class EmployeeWithSalary {
   @Embedded
   Employee employee;
   double salary;
}

You could then have a query in the respective @Dao annotated interface/abstract class, such as :-

@Query("SELECT *, (SELECT sum(amount) FROM salary WHERE empId=employee.id) AS salary  FROM employee")
abstract List<EmployeeWithSalary> getEmployeesWithSalary();

You could then have your adapter use the List and the salary would then be available without the need to access the database a second time to extract the salary.

As an example/demo consider the following based upon your code :-

@Entity
class Employee {
   @PrimaryKey
   Long id = null;
   @ColumnInfo(index = true)
   String employeeName;
   // etc...

   Employee(){}
   @Ignore
   Employee(String employeeName) {
      this.id = null;
      this.employeeName = employeeName;
   }
   @Ignore
   Employee(Long employeeId, String employeeName) {
      this.id = employeeId;
      this.employeeName = employeeName;
   }
}

@Entity
class Salary {
   @PrimaryKey
   Long salaryId = null;
   long empId;
   double amount;
   // etc ....

   Salary(){}
   @Ignore
   Salary(long empId, double amount) {
      this.salaryId = null;
      this.empId = empId;
      this.amount = amount;
   }
}

The new POJO

class EmployeeWithSalary {
   @Embedded
   Employee employee;
   double salary;
}

A basic @Dao annotated abstract class (could be an interface, abstract classes can include methods with bodies)

@Dao
abstract class AllDao {

   @Insert(onConflict = OnConflictStrategy.IGNORE)
   abstract long insert(Employee employee);
   @Insert(onConflict = OnConflictStrategy.IGNORE)
   abstract long insert(Salary salary);

   @Query("SELECT *, (SELECT sum(amount) FROM salary WHERE empId=employee.id) AS salary  FROM employee")
   abstract List<EmployeeWithSalary> getEmployeesWithSalary();

}

And to demonstrate (note that .allowMainThreadQueries has been used in the databaseBuilder for brevity of the demo) the following in an activity:-

    db = TheDatabase.getInstance(this);
    dao = db.getAllDao();

    long fredId = dao.insert(new Employee("Fred"));
    long maryId = dao.insert(new Employee("Mary"));

    dao.insert(new Salary(fredId,111.33));
    dao.insert(new Salary(fredId,222.66));
    dao.insert(new Salary(fredId,333.999));

    dao.insert(new Salary(maryId,115.55));
    dao.insert(new Salary(maryId,230.10));

    for (EmployeeWithSalary ews: dao.getEmployeesWithSalary()) {
        Log.d("EMPINFO","Employee is " + ews.employee.employeeName + " Salary is $" + ews.salary);
    }

and the log includes:-

D/EMPINFO: Employee is Fred Salary is $667.989
D/EMPINFO: Employee is Mary Salary is $345.65

Using room database to retrieve salaries for employees

1 Answers1