使用java在sqlite SELECT中的列之间添加带有空格的逗号

Question

我在 sqlite 表中有这些列 ( MemberId, City, Stateand Postal)和( FirstNameand LastName) tblMember。

我想在java中创建一个这样的查询：

SELECT (LastName FirstName) AS 'LastName FirstName', 

       (City, State Postal) AS 'City, State Postal' FROM tblMember;

因此，我搜索并尝试了以下代码，以便在 Java 中创建上述查询，如下所示：

String[] columns = new String[]{"LastName FirstName", "City, State Postal"};

String fields = "";

for (int i = 0; i < columns.length; i++) {

    // replace comma Plus space

    String field = columns[i].replaceAll(", ", " || \", \" || ");

    // replace space

    field = field.replaceAll(" ", " || \" \" || "); 

    fields += ",("+field+") AS '" + columns[i] + "' ";

}

String query = "SELECT MemberId"+fields+" FROM tblMember ";

注意：在上面的代码中，我使用了||sqlite 运算符进行列连接。但是，它成功地合并了空格，但没有将逗号与空格合并，并显示以下语法错误：

严重：空 java.sql.SQLException：[SQLITE_ERROR]

org.sqlite.core.DB.newSQLException(DB.java:890)

处的SQL 错误或缺少数据库（“||”附近：语法错误）在 org.sqlite.core .DB.newSQLException(DB.java:901)

请帮我解决这个问题！

Answer 1

我相信您的主要问题是第一个replaceAll引入了空格，然后由第二个replaceAll.我相信以下可能是您想要的：-&nbsp; &nbsp; for (int i = 0; i < columns.length; i++) {&nbsp; &nbsp; &nbsp; &nbsp; // replace comma Plus space&nbsp; &nbsp; &nbsp; &nbsp; String field = columns[i].replaceAll(", ", "||',ASPACEHERE'||");&nbsp; &nbsp; &nbsp; &nbsp; // replace space&nbsp; &nbsp; &nbsp; &nbsp; field = field.replaceAll(" ", "||' '|| ");&nbsp; &nbsp; &nbsp; &nbsp; field = field.replaceAll("ASPACEHERE"," ");&nbsp; &nbsp; &nbsp; &nbsp; fields += ",("+field+") AS '" + columns[i] + "' ";&nbsp; &nbsp; }&nbsp; &nbsp; String query = "SELECT MemberId"+fields+" FROM tblMember ";"使用单引号代替双引号'，因为它们更易于使用。所有没有特别要求的空格都被删除了。第replaceAll一个需要空格的地方使用了需要空格的指示符，以便添加的空格不会被第二个替换replaceAll（ASPACEHERE 用作指示符）。replaceAll添加了第三个以用空格替换指示符 ( ASPACEHERE )。这导致查询：-SELECT MemberId,(LastName||' '|| FirstName) AS 'LastName FirstName' ,&nbsp; &nbsp; (City||', '||State||' '|| Postal) AS 'City, State Postal'&nbsp; FROM tblMember代替 ：-SELECT MemberId,(LastName || " " || FirstName) AS 'LastName FirstName' ,&nbsp; &nbsp; (City || " " || || || " " || ", || " " || " || " " || || || " " || State || " " || Postal) AS 'City, State Postal'&nbsp; FROM tblMember第一个（更正后的查询）结果（使用如下示例数据）：-使用的整个测试 SQL 是（从正在输出的*query变量中复制的 SELECT SQL ）：-DROP TABLE If EXISTS tblMember;CREATE TABLE IF NOT EXISTS tblMember (MemberId INTEGER, City TEXT, State TEXT, POSTAL TEXT, LastName TEXT, FirstName TEXT);INSERT INTO tblMember VALUES(1,'Oxford','Oxfordshire','OX12 0ND','Bloggs','Fred');SELECT MemberId,(LastName||' '|| FirstName) AS 'LastName FirstName' ,(City||', '||State||' '|| Postal) AS 'City, State Postal'&nbsp; FROM tblMember;