0

I have made dropwizzard application using jdbi2. I am doing an update on my table and have annotated the dao method which does this with @GetGeneratedKeys and return type int. The documentation here says

If the return type is int, then the value will be the number of rows changed. Alternatively, if the method is annotated with @GetGeneratedKeys, then the return value with be the auto-generated keys. The insert method returns me correct 'id'. But my dao update method always returns me 0. Is there anything wrong here?

Here is my table

CREATE TABLE `User` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`FIRSTNAME` varchar(30) DEFAULT NULL,
`LASTNAME` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`ID`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

This is my POJO

@Data
@Builder
@AllArgsConstructor
public class User {

    @JsonProperty
    private int id;
    @JsonProperty
    private String firstName;
    @JsonProperty
    private String lastName;
}

Here is my dao class with dao method to update

@RegisterMapper(UserMapper.class)
public interface UserDao  {

    @SqlUpdate("Insert into User (FIRSTNAME, LASTNAME) values (:firstName, :lastName)")
    @GetGeneratedKeys
    int insert(@BindBean User user); //returns correct autogenerated id

    @SqlUpdate("Update User set FIRSTNAME = :firstName," +
            "LASTNAME = :lastName where ID = :id")
    @GetGeneratedKeys
    int update(@BindBean User User); //Shouldn't it return the id of updated user?
}

And here is my resource class:

@Path("/user")
@Consumes({MediaType.APPLICATION_JSON})
@Produces({MediaType.APPLICATION_JSON})
public class UserResource {

    UserDao dao;

    public UserResource(UserDao UserDao)
    {
        this.dao = UserDao;
    }

    @POST
    public User add(@Valid User user) {
        int i = dao.insert(user); //returns correct autogenerated id
        user.setId(i);
        return user;
    }

    @PUT
    @Path("/{id}")
    public User update(@PathParam("id") Integer id, @Valid User user) {
        User updateUser = new User(id, user.getFirstName(),user.getLastName());
        int i = dao.update(updateUser); // --> i is always 0, why?
        System.out.println(i);
        return updateUser;
    }

}

I am sending request using postman in this way:

Put:  localhost:7466/user/5
Body:(JSON/application/json) {
"firstName": "new_first_name",
"lastName": "new_last_name"
}

Please comment if anything is not clear. TIA
EDIT : Shouldn't I get at least the number of rows updated here? I am getting 0 always.

Community
  • 1
  • 1
shane
  • 449
  • 4
  • 17
  • 1
    I am using mysql database. – shane Jun 10 '18 at 06:41
  • 1
    Jdbi project member here. Strictly speaking, no keys are being generated in your update. It also depends on how mysql behaves for updates--you might need a `RETURNING ID` clause. – qualidafial Jun 10 '18 at 14:37
  • 1
    It's also possible that no rows are being returned because your change wouldn't modify anything. Try changing int to Integer and see if it returns null instead. – qualidafial Jun 10 '18 at 14:38
  • @qualidafial I changed "int update(...)" to "Integer update(...)" in my dao class, (and changed "int i" in resource class to "Integer i"), but it didn't make any difference. While debugging, i values comes as null. – shane Jun 10 '18 at 16:19
  • @qualidafial Can you elaborate more on `RETURNING ID` ? When I run an update query in the db directly, I get 'No errors; 1 row affected.' – shane Jun 10 '18 at 16:21
  • I use PostgreSQL, not MySQL, so I'm not confident that you can use `@GetGeneratedKeys` to do this with MySQL. Looking around on StackOverflow, most of the answers I see suggest that you cannot return a value from the updated row if it wasn't generated: https://stackoverflow.com/questions/11477121/mysql-return-updated-rows. – qualidafial Jun 11 '18 at 17:27
  • In your case, the insert statement is generating the ID (from the auto-increment counter), whereas the update statement is just updating a row based on the ID--you're not actually generating keys in the latter case. – qualidafial Jun 11 '18 at 17:29
  • @qualidafial I checked other answers on SO on how to return a value for `Update` query. At present, this is working for me: `@SqlUpdate("Update User set FIRSTNAME = :firstName," +` `"LASTNAME = :lastName where ID = :id and last_insert_id(ID)")` `@GetGeneratedKeys` `int update(@BindBean User User);` – shane Jun 14 '18 at 10:27
  • @qualidafial Shouldn't I at least get the number of rows updated without modifying my query? – shane Jun 19 '18 at 12:29
  • If you exclude the `@GetGeneratedKeys` annotation, you should get the update count. – qualidafial Jun 25 '18 at 21:57

0 Answers0