As I struggle with this issue for more than a day now, a scenario unfolds where a user clicks on a link of a book name triggering me to read that book's name. Subsequently, an Ajax request is made to a Jersey resource within which a method in a POJO class interacts with the database to retrieve data for sending back to the Jersey resource. Despite encountering numerous errors along the way and resolving them one by one, I find myself currently stuck at a specific error:
The dreaded SQL syntax error looming over my query execution: 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 '?' at line 1
Let’s delve into my JavaScript code:
function dealWithData(nameOfBook){
var bookName = encodeURI(nameOfBook);
console.log("http://localhost:8080/library/rest/books/allBooks/"+bookName);
var requestData = {
"contentType": "application/json",
"dataType": "text",
"type": "GET",
"url": "http://localhost:8080/library/rest/books/allBooks/"+bookName
**//beforeSend has been added as an edit to original code**
beforeSend: function (jqXHR, settings) {
var theUrlBeingSent = settings.url;
alert(theUrlBeingSent);
}
};
var request = $.ajax(requestData);
request.success(function(data) {
alert("Success!!");
});
request.fail(function(jqXHR, status, errorMessage) {
if((errorMessage = $.trim(errorMessage)) === "") {
alert("An unspecified error occurred. Check the server error log for details.");
}
else {
alert("An error occurred: " + errorMessage);
}
});
}
In the aforementioned code snippet, the inconsistency between the url being shown in the console.log line—encoded with %20 for spaces—and the unencoded URL in the 'requestData' variable baffles me.
Moving on to the resource code:
@GET
@Path("/allBooks/{bookName}")
@Produces(MediaType.APPLICATION_JSON)
public Response getBook(@PathParam("bookName") String bookName){
System.out.println("Book name is: "+ bookName);
BookInformation bookInfo = new BookInformation();
String bookInformation =bookInfo.bookInformation(bookName);
ResponseBuilder responseBuilder = Response.status(Status.OK);
responseBuilder.entity(bookInformation);
Response response = responseBuilder.build();
return response;
}
Lastly, the bookInformation method:
public String bookInformation(String bookName){
String infoQuery = "Select * from bookinfo where name = ?";
ResultSet result = null;
conn = newConnection.dbConnection();
try
{
preparedStatement = conn.prepareStatement(infoQuery);
preparedStatement.setString(1, bookName);
result = preparedStatement.executeQuery(infoQuery);
}
catch (SQLException e)
{
e.printStackTrace();
}
try
{
if(result != null){
while(result.next()){
availability = result.getString("availability");
isbn = result.getInt("isbn");
hardback = result.getString("hardback");
paperback = result.getString("paperback");
name = result.getString("name");
}
}
else{
System.out.println("No result set obtained");
}
}
catch (SQLException e)
{
e.printStackTrace();
}
//Constructing the final output using StringBuilder
String finalBookInformation = information.toString();
return finalBookInformation;
}
I had initially set dataType to json resulting in a different error until I realized JSON construction was incomplete hence changed it to text, remedying the issue. While querying with parameters remains unsuccessful, hardcoding database values works flawlessly but prepared statements thwart success. Eventually aiming for JSON output, I seek help primarily to make the current setup functional. Any insights are welcomed. Appreciation extended for any assistance provided thus far in tackling potential encoding or Ajax-related complications. Thank you.