CODEWITHSUNDEEP
javasqlspring-data-jpa
FishingIsLife
FishingIsLife

Reputation: 2372

Spring Data Insert Data when entry not yet exists

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?

Upvotes: 2

Views: 12747

Answers (3)

Amir SHIRALI POUR
Amir SHIRALI POUR

Reputation: 21

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

Upvotes: 2

Schnielz
Schnielz

Reputation: 61

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

INSERT IGNORE INTO table

Upvotes: 2

Schnielz
Schnielz

Reputation: 61

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.

Upvotes: 0

Related Questions