JDBC SQL query string problem

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'"
          开发者_StackOverflow中文版          +" 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;
}

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