how to handle result set = null

[Steve Rodgers]

Database was set up prior to me coming on board. If the data exists, it is loaded into the database table otherwise it is left off. No flags. For example, we have a users table and we have a cars table. If a user has a car, then the user_id is on the car table. If a user does not have a car, the user_id is not on the car table.

I have to return whether or not a user has a car. If they have a car, I need to return 'Car', otherwise 'No Car'. I cannot change the tables. Any suggestions?

[Gregg Bolinger]

Moving to the JDBC forum...

[Steve Rodgers]

Apologies for the code. Let me know if there is a simpler more efficient way. Basically I am reading through a collection if users fed in elsewhere and determining if they have a car.

 
public static final String CAR_QUERY = "SELECT USER_KEY FROM USER_CAR WHERE USER_KEY = ?";
 

String nocar = "No Car";
String car = "Car";
 
try{
   PreparedStmtParms [] psp = {new PreparedStmtParms(aUserID, 'S')};
   rs = carDao.getResults(CAR_QUERY, psp);
   if(rs==null) {
userRepo.setCar(nocar);
   }
   if(rs!=null) {
     int rCnt = 0;
     while(rs.next()) {
       rCnt++;
       RepoVO userRepo =(RepoVO)(getValueObjs().iterator().next());
       String count = rs.getString("USER_KEY)");
       userRepo.setCar(car);
      }
      if(rCnt==0) {
        userRepo.setCar(nocar);
}
     }
}
 

[Jeanne Boyarsky]

Steve,
A resultset can never be null. If there aren't any results, you will get a resultset object whose next() method returns false on the first call.

[Steve Rodgers]

Jeanne,

Thanks! Ahhhhh what was I thinking? :roll:

My new approach is to write an isCar() and setCar().

Then I can do something like (foo.isCar()?"Car":"No Car")

This way I can go through my collection of Users and determine if they have a car or not. Implementing this is taking me a bit though. I haven't figured it out. How do I put this in? Any pointers would be helpful. Thanks

[Jeanne Boyarsky]

I would actually make car a boolean field in your object, rather than a string. It saves you the string comparison as you can just use the boolean directly.

[Jeanne Boyarsky]

Steve,
I just re-read the initial post and want to clarify what I just posted. While you can't change the database, you can still have your java object store a boolean if you want. You can even have an isCar() method that returns the boolean and a getCar() method that returns 'Car' or 'No Car'. That way the logic is more centralized. But that's more refactoring than your actual question.

For the query, you can do something like this:

select count(*) from car where user_id = ? 

This query will return a result set with one row and one column. That entry is zero if the answer is no car and more than zero if the answer is car.