Set parameter for identical subquery in PreparedStatement

Question

I have PreparedStatement like this:

PreparedStatement st = conn
                    .prepareStatement("Select * from "
                            + "(select Count(*) from uch_otstyp b,prov_uch p "
                            + "where b.\"ID_Poezd\"=?"
                            + "and (b.\"NachKm\"*1000+b.\"NachM\")>? "
                            + "and (b.\"NachKm\"*1000+b.\"NachM\")<=? "
                            + "and b.\"ID_Poezd\"=p.\"ID_Poezd\" "
                            + "and b.\"ID_Uch\"=p.\"ID_Uch\" "
                            + "and p.\"MES\"=? "
                            + "and p.\"GOD\"=? "
                            + "and p.\"Nput\"=? "
                            + "and b.\"Kod_Otstup\"=? "
                            + "and b.\"DEPTH\"<1),"
                            + ""
                            + "(select Count(*) from uch_otstyp b,prov_uch p "
                            + "where b.\"ID_Poezd\"=?"
                            + "and (b.\"NachKm\"*1000+b.\"NachM\")>? "
                            + "and (b.\"NachKm\"*1000+b.\"NachM\")<=? "
                            + "and b.\"ID_Poezd\"=p.\"ID_Poezd\" "
                            + "and b.\"ID_Uch\"=p.\"ID_Uch\" "
                            + "and p.\"MES\"=? "
                            + "and p.\"GOD\"=? "
                            + "and p.\"Nput\"=? "
                            + "and b.\"Kod_Otstup\"=? "
                            + "and b.\"DEPTH\">=1)"
                            + "and b.\"DEPTH\"<2)");

Parameter values of each subquery are identic. How can I set parameters only for one subquery and automaticaly fill parameters of another(not for each subquery)?

I tried loops and it works but I look for more efficient way — Tkachuk_Evgen, Dec 12 '14 at 17:34
Thank you! I don't know SQL but now I can upvote with confidence and hope someone finds your answer. — snickers10m, Dec 12 '14 at 17:38

score 1 · Accepted Answer · edited May 23 '17 at 10:33

You could use named parameters, so that when a parameter appears multiple times in a query you would only have to specify it once. JDBC doesn't support named parameters, but you can write code to find the parameter names in a SQL string and figure out the order they appear so that the arguments can be added to the PreparedStatement. Spring-Jdbc does this for you with the NamedParameterJdbcTemplate, here's an example. This is in core spring, the package that implements the parameter-munging is org.springframework.jdbc.core.namedparam.

Set parameter for identical subquery in PreparedStatement

1 Answers1