Java : error in SQL syntax

Question

I am trying to retrieve information from my database, with following query :

 String Retrieve 
                    = "SELECT o.order_id , o.betalingsKenmerk , o.betaalMethode , o.tijdstipBezorging , o.datum"
                    + ", o.adres , o.huisnummer , o.postcode , o.woonplaats , o.landCode , o.extrainfo , o.opmerking"
                    + ", o.altEmail , o.voornaam , o.achternaam , o.bedrijfsnaam , o.telnummer , o.betaald , o.bezorgkosten"
                    + ", o.totaalExBzg , o.siteCode , o.opmerking"
                    + ", IF(o.transactieID = -1, 1,0) "
                    + "AS pickup, r.rest_id, TRIM(CONCAT(r.naam, ' ', r.filiaal)) AS restname"
                    + "FROM thuis.order o IGNORE INDEX(connectieid, doorberekenen)"
                    + "INNER JOIN connectie c IGNORE INDEX(methode)"
                    + "ON o.connectieId = c.id"
                    + "AND c.methode = 26 "
                    + "INNER JOIN restaurant r"
                    + "ON o.restaurantRest_id = r.rest_id"
                    + "WHERE o.datum >= TIMESTAMP(CURDATE())"
                    + "AND o.order_statusOrder_status_id = '1'"
                    + "AND o.order_statusOrder_device_id = ?"
                    + "AND o.doorberekenen = '1'"
                    + "AND ((o.bevestigingsId IS NULL OR o.bevestigingsId = 0) AND o.bevestigdDoor IS NULL)";

But I get an error:

[17-03-2015 10:20:57] [FATAL] [OrderCollector]
Error : com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'thuis.order o IGNORE INDEX(connectieid, doorberekenen)INNER JOIN connectie c IGN' at line 1

I have tried this query on the database from Sql manager within netbeans and it works just fine, but when I try to retype it for java it just keeps giving out the error above.

I am unable to find the solution !

Answer 1

You are missing a space

change

"FROM thuis.order o IGNORE INDEX(connectieid, doorberekenen)"

to

" FROM thuis.order o IGNORE INDEX(connectieid, doorberekenen)"

Without it restnameFROM becomes a single word and the FROM clause is not found.

Similarly you should add a space at the start or end of most of your lines.

This should work :

String Retrieve 
                    = "SELECT o.order_id , o.betalingsKenmerk , o.betaalMethode , o.tijdstipBezorging , o.datum"
                    + ", o.adres , o.huisnummer , o.postcode , o.woonplaats , o.landCode , o.extrainfo , o.opmerking"
                    + ", o.altEmail , o.voornaam , o.achternaam , o.bedrijfsnaam , o.telnummer , o.betaald , o.bezorgkosten"
                    + ", o.totaalExBzg , o.siteCode , o.opmerking"
                    + ", IF(o.transactieID = -1, 1,0) "
                    + "AS pickup, r.rest_id, TRIM(CONCAT(r.naam, ' ', r.filiaal)) AS restname"
                    + " FROM thuis.order o IGNORE INDEX(connectieid, doorberekenen)"
                    + " INNER JOIN connectie c IGNORE INDEX(methode)"
                    + " ON o.connectieId = c.id"
                    + " AND c.methode = 26 "
                    + " INNER JOIN restaurant r"
                    + " ON o.restaurantRest_id = r.rest_id"
                    + " WHERE o.datum >= TIMESTAMP(CURDATE())"
                    + " AND o.order_statusOrder_status_id = '1'"
                    + " AND o.order_statusOrder_device_id = ?"
                    + " AND o.doorberekenen = '1'"
                    + " AND ((o.bevestigingsId IS NULL OR o.bevestigingsId = 0) AND o.bevestigdDoor IS NULL)";

Answer 2

add a backtick in order – Abhik Chakraborty

Did the trick, but since you answered in a comment I can't accept it as an answer but thank you !

Query looks like this now :

String Retrieve 
                    = "SELECT `o`.`order_id` , `o`.`betalingsKenmerk` , `o`.`betaalMethode` , `o`.`tijdstipBezorging` , `o`.`datum`"
                    + ", `o`.`adres` , `o`.`huisnummer` , `o`.`postcode` , `o`.`woonplaats` , `o`.`landCode` , `o`.`extrainfo` , `o`.`opmerking`"
                    + ", `o`.`altEmail` , `o`.`voornaam` , `o`.`achternaam` , `o`.`bedrijfsnaam` , `o`.`telnummer` , `o`.`betaald` , `o`.`bezorgkosten`"
                    + ", `o`.`totaalExBzg` , `o`.`siteCode` , `o`.`opmerking`"
                    + ", IF(`o`.`transactieID` = -1, 1,0) "
                    + "AS pickup, `r`.`rest_id`, TRIM(CONCAT(`r`.`naam`, ' ', `r`.`filiaal`)) AS restname"
                    + " FROM `thuis`.`order` o IGNORE INDEX(connectieid, doorberekenen)"
                    + "INNER JOIN `connectie` c IGNORE INDEX(methode)"
                    + "ON `o`.`connectieId` = `c`.`id`"
                    + "AND `c`.`methode` = 26 "
                    + "INNER JOIN `restaurant` `r`"
                    + "ON `o`.`restaurantRest_id` = `r`.`rest_id`"
                    + "WHERE `o`.`datum` >= TIMESTAMP(CURDATE())"
                    + "AND o.order_statusOrder_status_id = '1'"
                    + "AND o.order_statusOrder_device_id = ?"
                    + "AND o.doorberekenen = '1'"
                    + "AND ((o.bevestigingsId IS NULL OR o.bevestigingsId = 0) AND o.bevestigdDoor IS NULL)";