如何从 Java 中的 txt 文件中读取 sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21785847/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read sql query from a txt-file in Java
提问by mrk2
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
java.sql.SQLException: ORA-00900: 无效的 SQL 语句
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);
采纳答案by rgettman
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.
您只需要转义 Java 字符串文字中的双引号。如果您从文件中读取 SQL 查询，Java 不会对文件中未转义的双引号产生任何问题。
Take out all the escapes on the double-quotes in your file, and it should work fine.
取出文件中双引号的所有转义符，它应该可以正常工作。
p.first_name "FirstName",
Also, create your StringBuilderbefore your whileloop, so you you don't start over each time, and that you don't read two lines per iteration:
此外，StringBuilder在while循环之前创建您的循环，这样您就不会每次都重新开始，并且每次迭代都不会阅读两行：
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 PreparedStatementto bind the session ID before you execute the query. That would prevent possible SQL injection attempts, e.g. if sessionIDwas the string:
此外，不要替换会话 ID 值末尾的单引号（这会起作用），而是使用?JDBC 占位符，并PreparedStatement在执行查询之前使用 a绑定会话 ID。这将阻止可能的 SQL 注入尝试，例如，如果sessionID是字符串：
Robert'); DROP TABLE players; --
