Spring Data Insert Data when entry not yet exists

Question

SCENARIO: I have a MySQL Database where I have one Table with a Mapping of a user and a product. My Application offers an api so I have to be aware that the mapping will not be done multiple times. Therefor I want to check if the two ids( of the user and the product) are already mapped.

PROBLEM:

I have a query from here where I expect a boolean value that shows me if an entry already exists.

@Query(value ="SELECT case when count(id) > 0 then true else false end FROM user_product_matching WHERE user_id=:userId AND product_id=:productId", nativeQuery = true)
Boolean productAlreadyAdded(@Param("userId") Long userId,@Param("productId") Long productId);

The error I get is:

java.math.BigInteger cannot be cast to java.lang.Boolean

Although I can't run this right now my goal is to write ONE natve query in spring where I can the result of this query to execute an if else case scenario where a can execute the insert or skip. Is this possible and how do I have to write the query?

EDIT: I forgot to mention that thhis has to work also from docker containers where the databases and table are not created by spring. So I guess stored procedures would be a better way to go?

score 1 · Accepted Answer · answered May 23 '19 at 15:15

1

Aperently this question was discussed in this thread too. Depending on the wanted behavior you could use something like

INSERT IGNORE INTO table

answered May 23 '19 at 15:15

Schnielz

51
2

Amir SHIRALI POUR · Answer 2 · 2020-11-29T16:28:12.487

I found this article: Spring Data JPA custom insert query that works for my. It use @SQLInsert(sql = "INSERT IGNORE INTO users(first_name, last_name, email) " + "VALUES (?, ?, ?)" ) in the Entity Class, above your class header public class User {.

    @Entity
    @Table(name = "users")
    @SQLInsert(sql = "INSERT IGNORE INTO users(first_name, last_name, email) " + "VALUES (?, ?, ?)" )
    public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    .
    .
    .

You can also use REPLACE INTO or ON DUPLICATE KEY UPDATE like these examples:

REPLACE INTO Table_name
SET column1 = value1,
column2 = value2;

or

INSERT INTO Table_name (value1, value2, value3)
VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE column1 = value1+1
WHERE condition

score 0 · Answer 3 · answered May 23 '19 at 12:06

Mybe it is because booleans are represented as Bit (0 or 1) values in the DB. I think you could make it more easy when you ask for the count of the occurences and then compare the number values.

You could do something like this:

@Query(value ="SELECT count(id) FROM user_product_matching WHERE user_id=:userId AND product_id=:productId", nativeQuery = true)
int productAlreadyAdded(@Param("userId") Long userId,@Param("productId") Long productId);

then in your code ask

if (productAlreadyAdded(userID, productID) > 0)
{
   ...
}

EDIT: You should be carefully with DB queries for such simple questions. You could run in performance issues when this simple method is called to often.

It would be better when you avoid duplicate entries in that table. You could use unique constraints for the two columns as described here.

Yeah I thought this is a bad practice to query if the entry already exists. Thing is that I have to create the tables from within a dockerfile. So I guess this won't work if I annotate my models. I was thinking aber defing a complex query with EXIST or something like that — FishingIsLife, May 23 '19 at 12:22
I am not familar with "create the tables from within a dockerfile" but the result of this annotation will be native constraints in the database. So when you are able to create tables you should be able to create constraints too. Bad thing is that you will run in SQLExceptions... — Schnielz, May 23 '19 at 15:12

Spring Data Insert Data when entry not yet exists

3 Answers3