How to read sql query from a txt-file in Java

I am having some some class that is sending queries to data base. Everything was working fine when the query was in the code, but as it’s pretty big, I decided to put it in a file and to read it with buffered reader, but it’s not working, I always get this:

java.sql.SQLException: ORA-00900: invalid SQL statement

Here is my query:

SELECT 
             p.first_name           "FirstName",  
             p.middle_name          "MiddleName",  
             p.last_name            "LastName", 
             p.birth_name           "BirthName",
             p.mothers_name         "MothersName",
             p.nick_name            "NickName",
             p.username             "Username",
             p.currency_id          "Currency",
             p.preferred_language_id    "PreferredLanguage",
             p.accepts_email        "AcceptsEmail",
             p.birth_date           "BirthDate",
             p.hear_about_us        "HeardAboutUs",
             p.tax_category_id      "TaxCategory",
             p.birth_place          "BirthPlace",
             p.accepts_id_verification      "AcceptsIdentityVerification",
             p.security_prompt      "SecurityPrompt",
             p.gender_id            "Gender",
             p.tracking_campaign_name   "TrackingCampaign", 
             p.accepts_sms          "AcceptsSMS",
             p.accepts_promotional_sms  "AcceptsPromotionalSMS", 
             p.identification_number    "IdentificationNumber", 
             p.id_verified_id       "IdentificationVerified", 
             p.security_word        "SecurityWord", 
             p.ident_manual_verified_until  "IdentificationManualVerified",
             p.accepts_chat         "AcceptsChat", 
             p.frequent_player_level_id     "FrequentPlayerLevel", 
             p.preferred_comm_channel   "PreferredCommunicationChannel", 
             p.is_reward_abuser         "IsRewardAbuser", 
             p.newsletter_id        "Newsletter", 
             p.accepts_rewards      "AcceptsRewards", 
             ci.postal_code         "PostalCode", 
             ci.country_id          "Country", 
             ci.region          "Region", 
             ci.email           "Email",
             ci.address1            "Address1", 
             ci.address2            "Address2", 
             ci.address3            "Address3", 
             ci.phone1          "Phone1",
             ci.phone2          "Phone2",
             ci.city            "City", 
             ci.mobile_phone        "MobilePhone", 
             ci.address_state_id        "AddressVerified" 

     FROM 
             player p 
             JOIN contact_info ci     ON p.CONTACT_INFO_ID = ci.CONTACT_INFO_ID 
             JOIN player_session ps  ON p.PLAYER_ID = ps.PLAYER_ID 
     WHERE 
             ps.external_client_session_id = '$sessionID'

Here is the code I am using:

String query = "";
    try{
        BufferedReader bufferedReader = new BufferedReader(
                                        new FileReader("templates\sqlQueries\GetPlayerDetails.txt")
                                                            );
        while(bufferedReader.readLine()!=null){
             query = new StringBuilder().append(query).append(bufferedReader.readLine()).toString();
        }
    }
    catch (FileNotFoundException e){
        e.printStackTrace();
    }
    catch (IOException e){
        e.printStackTrace();
    }
    query = query.replace("$sessionID", sessionID);

Answer

You only need to escape your double-quotes in Java string literals. If you’re reading the SQL query from a file, Java will have no problem with the double-quotes unescaped in the file.

Take out all the escapes on the double-quotes in your file, and it should work fine.

p.first_name           "FirstName",  

Also, create your StringBuilder before your while loop, so you you don’t start over each time, and that you don’t read two lines per iteration:

StringBuilder sb = new StringBuilder();
String line;
while ((line = bufferedReader.readLine()) != null)
{
    sb.append(line);
}
query = sb.toString();

Additionally, instead of replacing the single-quotes at the end for the session ID value (which would work), use a ? JDBC placeholder, and use a PreparedStatement to bind the session ID before you execute the query. That would prevent possible SQL injection attempts, e.g. if sessionID was the string:

Robert'); DROP TABLE players; --