Use a value in MySQL query in multiple lines without a variable

Question

I have this mysql query that uses the same value I'm passing, 3 times over.

select p.*,
       ROUND(sum(unitsPerBlock * blocks) / datediff(date(now()), date(?)), 2) as avg
from batches b
inner join products p on b.productID = p.id
where (
          select sum(b1.availableQty)
          from batches b1
          where b1.productID = p.id
            and b1.addedDate between date(?) and date(now())
      ) = 0
and b.addedDate between date(?) and date(now())
group by b.productID
order by avg desc

Is there any way to make this query without me having to type the below

preparedStatement.setString(1, date);
preparedStatement.setString(2, date);
preparedStatement.setString(3, date);

And just use this

preparedStatement.setString(1, date);

Can you show are is the preparedStatement created? -- the actual variable declaration/assignment — lealceldeiro
– lealceldeiro, Commented Dec 23, 2021 at 9:16
I think this is what you're looking for: stackoverflow.com/a/1097957/5640649 — lealceldeiro
– lealceldeiro, Commented Dec 23, 2021 at 9:18
@lealceldeiro is there any way that I could use NamedParameterJdbcTemplate here? I'm not using Spring atm — Vizard
– Vizard, Commented Dec 23, 2021 at 9:22
It seems no, but I'm not a regular user of JDBC, so I might be missing something — lealceldeiro
– lealceldeiro, Commented Dec 23, 2021 at 9:24

Akina · Accepted Answer · 2021-12-23 09:18:13Z

1

select p.*,
       ROUND(sum(unitsPerBlock * blocks) / datediff(CURRENT_DATE, input.parameter), 2) as avg
from batches b
inner join products p on b.productID = p.id

CROSS JOIN (SELECT date(?) AS parameter) AS input

where (
          select sum(b1.availableQty)
          from batches b1
          where b1.productID = p.id
            and b1.addedDate between input.parameter and CURRENT_DATE)
      ) = 0
and b.addedDate between input.parameter and CURRENT_DATE
group by b.productID
order by avg desc

answered Dec 23, 2021 at 9:18

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

lealceldeiro Over a year ago

As a regular dev reading the code-only answer posted here, I'd ask: where does input.parameter come from? where's CURRENT_DATE coming from? If you could add an (at least a minimal) explanation to this post, it'll help better future readers.

Akina Over a year ago

@lealceldeiro I'd ask: where does input.parameter come from? ?? You do not see? There is only one ? char in the query text... and preparedStatement.setString(1, date); will replace this placeholder with provided value. where's CURRENT_DATE coming from? dev.mysql.com/doc/refman/8.0/en/…

lealceldeiro Over a year ago

I saw the ? in the code you posted just after searching for it in my browser just because you mentioned now, which tells the answer is not self-explanatory. If it helped the OP, great, that's the main purpose of posting here. IMO, the answer can be improved, thing I tried to encouraged you to do. But I won't waste my time with someone who does not take well a good recommendation/critic. Peace out!

Collectives™ on Stack Overflow

Use a value in MySQL query in multiple lines without a variable

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related