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

Question

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

I suggest baby steps. Intead of trying to select many fields, try to select just one. That makes troublehooting easier. — Dan Bracuk
– Dan Bracuk, Commented Feb 14, 2014 at 17:39

rgettman · Accepted Answer · 2014-02-14 17:41:18Z

11

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

edited Feb 14, 2014 at 17:41

answered Feb 14, 2014 at 17:34

rgettman

179k30 gold badges282 silver badges365 bronze badges

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

1 Comment

mattsmith5 Over a year ago

I put an answer below, feel free to code review or edit

mattsmith5 · Accepted Answer · 2022-06-25 04:56:43Z

1

Use the following code:

public static final Path SQL_QUERY_FILE_PATH = Paths.get("SqlFolder", "productQuery.sql");

String sqlQuery = FileResourceUtil.readFileAsString(getClass(), SQL_QUERY_FILE_PATH

answered Jun 25, 2022 at 4:56

mattsmith5

1,3277 gold badges57 silver badges113 bronze badges

1 Comment

mattsmith5 Over a year ago

removing answers from all other posts, and keeping this one, thanks

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related