SQL Objects API - Get all columns?

I am using the SQL objects API in the following lines of code in my data access object to get the name field from a row in an SQL table where the ID matches the one provided.

@SqlQuery("select name from users where id = :id")
String findNameById(@Bind("id") int id);

That works all well and good, but what on earth do I do if I want to return all of the fields for that row in the table, not just the name, but all colums for that record?

I did try using :

"select * from users where id = :id"

But that isn't right, it only seemed to return the id field (possibly as it is the first column in the table, and my method returns a string?)

Anyhow, I am all out of ideas on this and would appreciate all the help I can get.

Additional Info as I get requests for information :

This is the method signature of my method in the controller that calls my dao.

public String retrieveUserRecord(@PathParam("id") int id)

As you can see, it returns a string, as does the findNameByID method in the dao...

What I want to do, is pass the id using my method signature, and get back all of my columns as one nice JSON object if possible?

Solution

Return type should not be string when you want to get all the fields. I am assuming you have 'USER' model like this.

  public class User {
     private Integer id;
     private String name;

    public User(id, name) {
      this.id = id;
      this.name = name;
   }
  }

There are some ways to map DB result to Java model. You should create mapper for class user.

 public class UserMapper<User>  implements ResultSetMapper<User> {
         public User map(int row, ResultSet rs, StatementContext ctx) throws SQLException { 
         return new User(rs.getInt("id"), rs.getString("name") );
         }
}

You should mention this mapper in sqlObject api.

@SqlQuery("select * from users where id = :id")
@Mapper(UserMapper.class)
String retrieveUserRecord(@Bind("id") int id);

You can also use MapResultAsBean if you have setters & getters in the model.

You can also use this library.