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;
}

can u paste here the end query from sqlprofiler?

MLS
– MLS

2011-05-30 11:56:21 +00:00
Commented May 30, 2011 at 11:56 — MLS
– MLS, Commented May 30, 2011 at 11:56
I'm using postgreSQL unfortunately, is there an equivalent?

Milk
– Milk

2011-05-30 12:13:47 +00:00
Commented May 30, 2011 at 12:13 — Milk
– Milk, Commented May 30, 2011 at 12:13
stackoverflow.com/questions/365103/…

MLS
– MLS

2011-05-30 12:18:27 +00:00
Commented May 30, 2011 at 12:18 — MLS
– MLS, Commented May 30, 2011 at 12:18

Lukas Eder · Accepted Answer · 2011-05-30 11:50:36Z

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, 2011 at 11:50

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

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

13 Comments

Milk Over a year ago

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

slothrop Over a year ago

...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.

Milk Over a year ago

it catches a sql exception, and im using the same query successfully in pgadminIII on the same db.

slothrop Over a year ago

You need a space before then here: ...when stat_nrOfBookings/date_part('days', now()-stat_since)<0.07142858then 2...

Lukas Eder Over a year ago

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.

|

Collectives™ on Stack Overflow

JDBC SQL query string problem

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related