CODEWITHSUNDEEP
javasqlite
user1773603
user1773603

Reputation:

Add comma with space between columns in sqlite SELECT using java

I have these columns (MemberId, City, State and Postal) and (FirstName and LastName) in sqlite table tblMember.

I want to create a query in java like this:

SELECT (LastName FirstName) AS 'LastName FirstName', 
       (City, State Postal) AS 'City, State Postal' FROM tblMember;

So for I have searched and tried below code for creating above query in java as like this:

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

Note: In above code I've used || sqlite operator for columns concatenation. But, it combines the spaces successfully but not combining the comma with space and showing below syntax error:

SEVERE: null java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "||": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:890)
at org.sqlite.core.DB.newSQLException(DB.java:901)

Please help me in this problem!

Upvotes: 1

Views: 990

Answers (1)

MikeT
MikeT

Reputation: 56948

I believe your primary issue is that the first replaceAll introduces spaces which are then replaced by the 2nd replaceAll.

I believe that the following may be what you want :-

    for (int i = 0; i < columns.length; i++) {
        // replace comma Plus space
        String field = columns[i].replaceAll(", ", "||',ASPACEHERE'||");
        // replace space
        field = field.replaceAll(" ", "||' '|| ");
        field = field.replaceAll("ASPACEHERE"," ");
        fields += ",("+field+") AS '" + columns[i] + "' ";
    }
    String query = "SELECT MemberId"+fields+" FROM tblMember ";
  • Instead of double quotes "'s, single quotes were used ', as they are simpler to use.
  • All spaces were removed where they were not specifically required.
  • Where a space was required in the first replaceAll an indicator for the need of a space was used so that the space added was not replaced by the 2nd replaceAll (ASPACEHERE was used as the inidcator).
  • A third replaceAll was added to replace the inidcator (ASPACEHERE) with a space.

This results in a query :-

SELECT MemberId,(LastName||' '|| FirstName) AS 'LastName FirstName' ,
    (City||', '||State||' '|| Postal) AS 'City, State Postal'  FROM tblMember

Instead of :-

SELECT MemberId,(LastName || " " || FirstName) AS 'LastName FirstName' ,
    (City || " " || || || " " || ", || " " || " || " " || || || " " || State || " " || Postal) AS 'City, State Postal'  FROM tblMember

The first (the corrected query) results in (using sample data as below) :-

enter image description here

The entire testing SQL used was (the SELECT SQL being copied from the *query variable being output):-

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'  FROM tblMember;

Upvotes: 0

Related Questions