Native query produces MySQL syntax error and unexpected result

Question

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.

The queries use the tables and colums from schema, how it's mapped to java objects? — , Oct 08 '13 at 17:05
@nikpon: There are 2 entities called `User` and `Message` with `@ManyToMany` relationship between them. However, I am not deleting anything from these 2 entities' tables. I need to delete records from the `@JoinTable` created by the `@ManyToMany` relationship. — Mr.J4mes, Oct 08 '13 at 17:16
Entities remove what a simple case can you discover of one join table contains only references that you remove with native queries and not from entity manager. — , Oct 08 '13 at 17:22
@nikpon: I am using native queries produced by `em.createNativeQuery()` to delete from the relationship table. — Mr.J4mes, Oct 08 '13 at 17:26

score 1 · Accepted Answer · edited Oct 08 '13 at 17:39

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();

Is this what kind of parameters used? Why not to use named parameters in JPA? — , Oct 08 '13 at 17:25

Native query produces MySQL syntax error and unexpected result

1 Answers1