CODEWITHSUNDEEP
mysqlsqlmybatis
Jin Park
Jin Park

Reputation: 461

how to create a Mysql dynamic query statement with conditions

I'm trying to create a dynamic query statement with some condition for Mybatis.

This is not working query statement but this describes my current conditions.

SELECT * FROM myTemp WHERE name="Jin" if (
(name is null AND type = 0 ANd status = 2) OR (name is NOT NULL AND 
type = 0 AND status =2 ) );

assume, the value "name" is dynamic value and if the name is not empty(not null) then execute a query with name value such as,

SELECT * FROM myTemp Where type = 0 AND status = 2 AND name ="someValue"

else if the name is empty or null then execute a query without name value such as,

SELECT * FROM myTemp where type = 0 AND status = 2.

How can I make a MySQL query statement with above conditions?

Upvotes: 1

Views: 1075

Answers (2)

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6289

Try something like this:

SELECT *
FROM myTemp
WHERE type = 0 AND status = 2 AND (name = "someValue" OR name IS NULL)

Upvotes: 0

Ari Manninen
Ari Manninen

Reputation: 528

You could use COALESCE function to select first non-null value. Like this:

SELECT * FROM myTemp Where type = 0 AND status = 2 AND name = COALESCE("someValue", name)

This way, if you give "someValue", it will be matched to name. If you give NULL instead of "someValue", it will match to name which is always equal.

Upvotes: 2

Related Questions