Using @Bind for Dropwizard API results in SQL syntax error

Question

I am developing a Dropwizard RESTfull app based on the pdf available here (http://it-ebooks.directory/book-1783289538.html). I've tried researching this problem and haven't found anyone with the same problem, bear with me here though because I am not too familiar with using @Bind or interfaces.

In any case, I have a MYsql server set up on my computer, in order to query different values from the students table of my SQL database via the browser, I have to type in localhost:8080/students/[A number]. In order to implement this, the documentation says to use an Database Access Object interface with the following code:

public interface StudentsDAO {

@Mapper(StudentMapper.class)
@SqlQuery("select * from students where studentId = :studentId") 
Student getStudentById(@Bind("studentId") int studentId);

}

This is used by my studentResource class with the following code:

@Path("/students")
@Produces(MediaType.APPLICATION_JSON)
public class StudentResource {

private final StudentsDAO studentsDao;



public StudentResource(DBI jdbi) {
    studentsDao = jdbi.onDemand(StudentsDAO.class);
}


@GET
@Path("/{studentId}")
public Response getStudent(@PathParam("studentId") int studentId) {
// retrieve information about the contact with the provided id
Student student = studentsDao.getStudentById(studentId);
return Response.ok(student).build();
}
...

I have verified in my MYsql server that running "Select * from students where studentId = 123" returns the proper student, but when I do localhost:8080/students/123 it gives me the following error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1"

The line that triggers the error is:

Student student = studentsDao.getStudentById(studentId);

It's in the studentResource class, so I presume the error is related to something in my DAO interface...

Thanks for the help!

Answer 1

The issue, it seems is with your mysql version or JDBC connector. Try updating your mysql to latest version. I am using mysql 5.6 and this code runs flawlessly. However to make it work with your current mysql setup, try adding 'LIMIT' to query. For example:-

@SqlQuery("select * from students where studentId = :studentId LIMIT 1")