CODEWITHSUNDEEP
javamysqljakarta-eejpa-2.0sql-delete
Mr.J4mes
Mr.J4mes

Reputation: 9266

Native query produces MySQL syntax error and unexpected result

In my application, when a User removes a Message, I need to remove the relationship between the 2 entities while keeping them intact. Hence, I tried to delete rows directly from the relationship table using the following ways:

1.

Query q = em.createNativeQuery("DELETE FROM User_Inbox WHERE User_USERNAME = :username AND Message_ID = :messageID");
q.setParameter("username", username);
q.setParameter("messageID", messageID);
q.executeUpdate();

2.

Query q = em.createNativeQuery("DELETE FROM User_Inbox WHERE User_USERNAME = :username AND Message_ID = :messageID");
q.setParameter("username", "'" + username + "'");
q.setParameter("messageID", "'" + messageID + "'");
q.executeUpdate();

The 1st and 2nd approaches produced the following exception:

Internal Exception: 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 ':username AND 
Message_ID = :messageID' at line 1
Error Code: 1064

3.

Query q = em.createNativeQuery("DELETE FROM User_Inbox WHERE User_USERNAME = ':username' AND Message_ID = ':messageID'");
q.setParameter("username", username);
q.setParameter("messageID", messageID);
q.executeUpdate();

The 3rd approach did not produce any exceptions. It went through normally. However, nothing was deleted from the User_Inbox table.

4.

Query q = em.createNativeQuery("DELETE FROM User_Inbox WHERE User_USERNAME = '" + username + "' AND Message_ID = '" + messageID + "'");
q.executeUpdate();

The 4th approach worked perfectly. The query went through smoothly and a record was deleted properly.

The last approach worked but the code doesn't look very neat to me. I'd be very grateful if someone could show me what I did wrong with the 1st three approaches.

UPDATE:

Based on the answer from D. Moore, I've just found out that named parameters cannot be used with native queries. This has been mentioned in this answer by Pascal Thivent.

Named parameters follow the rules for identifiers defined in Section 4.4.1. The use of named parameters applies to the Java Persistence query language, and is not defined for native queries. Only positional parameter binding may be portably used for native queries.

Upvotes: 0

Views: 1937

Answers (1)

D. Moore
D. Moore

Reputation: 124

I agree that (4.) is certainly not the right way to be doing things. It's tedious and unsafe.

(3.) are working, but not producing the expected results since they have extra quotes.

As to why (1.) isn't working, you would want to look into the SQL that is being generated (I believe the settings are specific to the JPA system you are using). You seem to have the code correct, but the error implies that the parameters are not being substituted.

Do positional parameters do anything different? 

Query q = em.createNativeQuery("DELETE FROM User_Inbox WHERE User_USERNAME = ?1 AND Message_ID = ?2");
q.setParameter(1, username);
q.setParameter(2, messageID);
q.executeUpdate();

Upvotes: 1

Related Questions