JDBC SQL query string problem

Question

Im having some issues inserting a sql query into java as a string. When I paste the text straight in it doesnt keep it as a string (not sure why because theres no double quotations in it), so i tried formatting it line by line but then i get a sql exception (and i know the query works). Can anyone spot whats giving me issues? If not, is there a better way i can insert the sql code? thanks

public FratReport getFratReport() throws SQLException {
            FratReport newfrat = new FratReport();
            Statement stmt;
            ResultSet results;
            String myQuery;
            myQuery = String.format("select m.nickname,"
                    +" case"
                    +" when stat_nrOfBookings/date_part('days', now()-stat_since)<0.035714 then 1"
                    +" when stat_nrOfBookings/date_part('days', now()-stat_since)<0.07142858 then 2"
                    +" when stat_nrOfBookings/date_part('days', now()-stat_since)<0.1785715 then 3"
                    +" when stat_nrOfBookings/date_part('days', now()-stat_since)<0.2857143 then 4"
                    +" else 5 end as freq,"
                    +" case"
                    +" when stat_sumPayments < 10000 then 1"
                    +" when stat_sumPayments < 100000 then 2"
                    +" when stat_sumPayments < 250000 then 3"
                    +" when stat_sumPayments < 500000 then 4"
                    +" else 5 end as am,"
                    +" case"
                    +" when count = null then 'weekday'"
                    +" when stat_nrOfBookings-count > count then 'weekday'"
                    +" else 'weekend' end as typ,"
                    +" case"
                    +" when max = null then 0"
                    +" when max<(now()-interval '4 weeks') then 1"
                    +" when max<(now()-interval '2 weeks') then 2"
                    +" when max<(now()-interval '1 week') then 3"
                    +" when max<(now()-interval '1 days') then 4"
                    +" else 5 end as rece"
                    +" from (member natural join memberstats) as m join"
                    +" (select rece.nickname, max, count from"
                    +" (select nickname , max(whenbooked) from member full join booking on member.memberNo = booking.madeBy group by member.nickname) as rece"
                    +" full join"
                    +" (select nickname, count from member full join (select nickname as nn, count(*) from (select nickname, to_char(whenbooked, 'D') from member left outer join booking on member.memberno = booking.madeBy) as days where to_char = '7' or to_char = '6' group by nickname) as c on member.nickname = c.nn) as daycount"
                    +" on rece.nickname = daycount.nickname)"
                    +" as n"
                    +" on m.nickname = n.nickname"
                    +" order by freq desc, rece desc, am desc, typ desc");


            try {
                stmt = conn.createStatement();
                results = stmt.executeQuery(myQuery);
                String newString;
                while (results.next()) {
                            newString = results.getString("nickname") + " " + 
                            Integer.toString(results.getInt("rec"))+ " " + 
                            Integer.toString(results.getInt("am")) +" "+ 
                            results.getString("type");
                    newfrat.addLine(newString);
                }
                stmt.close();
                results.close();
            }
            catch(SQLException e){System.out.println("yep");}
            return newfrat;
}

http://stackoverflow.com/questions/365103/how-to-profile-postgresql-database — MLS, May 30 '11 at 12:18

score 1 · Answer 1 · answered May 30 '11 at 11:50

1

A random line:

+"when stat_sumPayments < 10000 then 1"
+"when stat_sumPayments < 100000 then 2"

Obviously, this will give you syntax errors when concatenated:

when stat_sumPayments < 10000 then 1when stat_sumPayments < 100000 then 2
                                   ^^

The solution is to add a blank at the end of every line.

+"when stat_sumPayments < 10000 then 1 "
+"when stat_sumPayments < 100000 then 2 "

Apart from that, if you insist on using JDBC and not any framework built on top of JDBC, then that's "state-of-the-art". Otherwise, I can recommend my database abstraction framework jOOQ. It will help you phrase complex queries like the above in a Java DSL, without risking such syntax errors:

http://www.jooq.org

answered May 30 '11 at 11:50

Lukas Eder

211,314
129
689
1,509

unfortunately its for uni so i don't really have a choice. Thanks ive changed the code but still get same problem. Revised code above – Milk May 30 '11 at 11:59
1

...or you can add the space at the beginning of each line, if that works for you - personally I find it easier to spot any missing spaces this way. Also, it's worth logging (either do it every time at the 'debug' level, or do it selectively only when you get an exception) the SQL string that you are sending to the DB. That way you get the explicit query in the log, and it's usually quite easy to see this kind of thing. – slothrop May 30 '11 at 12:01
it catches a sql exception, and im using the same query successfully in pgadminIII on the same db. – Milk May 30 '11 at 12:02
1

You need a space before `then` here: `...when stat_nrOfBookings/date_part('days', now()-stat_since)<0.07142858then 2...` – slothrop May 30 '11 at 12:03
I agree with slothrop about putting spaces at the beginning of lines. It's much easier that way. And he correctly spotted an additional syntax error – Lukas Eder May 30 '11 at 12:07
ah well spotted! however, sqlexception continues to haunt me. code updated – Milk May 30 '11 at 12:08
1

Now, I'd remove String.format() because you don't really use that. Just to prevent side-effects. In any case, the way to go for you is to log or `System.out.println()` your SQL and try executing it directly in pgAdmin III. It wouldn't be working there if you have syntax errors in JDBC. That way, it might be easier for you to track down the problem. – Lukas Eder May 30 '11 at 12:15
ok so I just used System.out.println() and pasted into pgadmin and it worked fine. If its not syntax what could it be? – Milk May 30 '11 at 12:23
Numerous problems. e.g. missing grants (i.e. you use different connection parameters in JDBC and in pgAdmin). Then again, you select `results.getInt("rec")`, the same with `results.getString("type")` which you didn't select in your query... – Lukas Eder May 30 '11 at 12:27
Have you got the right JDBC driver for your version of postgres? That's one thing to check. Also, not sure if it causes an exception, but should `results.close()` come before `stmt.close()`? – slothrop May 30 '11 at 12:28
Can you add a line to your `catch` block to print the stack trace? That would help track down the issue. – slothrop May 30 '11 at 12:29
THANKYOU! that led me to the solution. I mustn't have updated the column names in the while loop. So the problem was that there was no rec or type columns, rather 'rece' and 'typ'. stupid mistake, thankyou all for the help – Milk May 30 '11 at 12:40
didnt realise you asked me for the stack trace Lukas! thanks again +1 cyber beer – Milk May 30 '11 at 12:43

JDBC SQL query string problem

1 Answers1