How do I ignore the null values?

Question

In my database I got 4 fields of data, I also got a method (getWalletName) where I send an SQL query to retrieve only one piece of data out of 4, but after retrieving and displaying the result. I can see the other data beside the desired one I want like this :

SELECT Name FROM wallets WHERE Location ='ROOME' ;

[unknown,unknown,unknown,Omar]

Where I want it to be displayed like that [Omar], keep in mind to not modify my constructors because I need them to be in this way. I have tried the code in wallet class below but didn't work ! Any help would be much appreciated

wallet class :

public class wallet {
private String Name;
private String Location;
private String Tag;
private String Reader;


public wallet(String Name , String Location, String Tag, String Reader)
{       
    this.Name = Name;
    this.Location = Location;
    this.Tag = Tag; 
    this.Reader = Reader;   

}


public wallet(String Reader)
{       
    this.Reader = Reader;
    this.Name = "unknown";
    this.Location = "unknown";
    this.Tag = "unknown";   
}

public wallet(String Location,String Tag) {
    this.Location = Location;
    this.Tag = Tag;
}

public String getWalletName()
{
    return Name;
}
public void setWalletName(String Name)
{
    this.Name = Name;
}

public String getWalletLocation()
{
    return Location;
}
public void setWalletLocation(String Location)
{
    this.Location = Location;
}

public String getWalletTag()
{
    return Tag;
}
public void setWalletTag(String Tag)
{
    this.Tag = Tag;
}


@Override
public String toString()
{
     StringBuilder sb = new StringBuilder();

        if(Objects.nonNull(this.Name)) {
            sb.append(this.Name+",");
        }
        if(Objects.nonNull(this.Location)) {
            sb.append(this.Location+",");
        }
        if(Objects.nonNull(this.Tag)) {
            sb.append(this.Tag+",");
        }
        if(Objects.nonNull(this.Reader)) {
            sb.append(this.Reader);
        }

        return sb.toString();
}

}

getWalletName method:

public ArrayList<wallet> getWalletName(String Location) throws SQLException {
    System.out.println("----------------");
    System.out.println("Retrieve the wallet name");

    //Connection dbConnection = null;
    //Statement statement = null;
    ResultSet resultset = null;     
    String query = "SELECT Name FROM wallets WHERE Location ='" + Location + "' ;";
    ArrayList<wallet> WalletsList = new ArrayList<>();      
    try {
        getConnection();
        //statement = dbConnection.createStatement();
        System.out.println(query);
        resultset = stmt.executeQuery(query);

        while (resultset.next()) {

            String Name =  resultset.getString("Name");
            WalletsList.add(new wallet (Name));
        }
    } 
    finally {

    //  closeConnection();

        if (resultset !=null){
            resultset.close();
        }
        if (stmt !=null) {
            stmt.close();
        }

        if (resultset !=null) {
            closeConnection();
        }
    }
    return WalletsList;

}

`SELECT Name FROM wallets WHERE Location ='ROOME' AND Name IS NOT NULL` — Andreas, Dec 23 '19 at 02:50
before `WalletsList.add(new wallet (Name));` judge and skip the name is null. — jin, Dec 23 '19 at 03:18
@KhaledSalem Seems the text "unknown" is actually the value of column `Name`, so try `AND Name <> 'unknown'` — Andreas, Dec 23 '19 at 04:11

score 0 · Accepted Answer · answered Dec 23 '19 at 04:50

If you don't want change your constructor, have only way:

class wallet {
    private String Name;
    private String Location;
    private String Tag;
    private String Reader;


    public wallet(String Name, String Location, String Tag, String Reader) {
        this.Name = Name;
        this.Location = Location;
        this.Tag = Tag;
        this.Reader = Reader;

    }


    public wallet(String Reader) {
        this.Reader = Reader;
        this.Name = "unknown";
        this.Location = "unknown";
        this.Tag = "unknown";
    }

    public wallet(String Location, String Tag) {
        this.Location = Location;
        this.Tag = Tag;
    }

    public String getWalletName() {
        return Name;
    }

    public void setWalletName(String Name) {
        this.Name = Name;
    }

    public String getWalletLocation() {
        return Location;
    }

    public void setWalletLocation(String Location) {
        this.Location = Location;
    }

    public String getWalletTag() {
        return Tag;
    }

    public void setWalletTag(String Tag) {
        this.Tag = Tag;
    }


    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();

        if (isPrintableValue(this.Name)) {
            sb.append(this.Name + ",");
        }
        if (isPrintableValue(this.Location)) {
            sb.append(this.Location + ",");
        }
        if (isPrintableValue(this.Tag)) {
            sb.append(this.Tag + ",");
        }
        if (isPrintableValue(this.Reader)) {
            sb.append(this.Reader);
        }

        return sb.toString();
    }

    protected boolean isPrintableValue(String value) {
        return (value != null && !value.equals("unknown"));
    }
}

public class Test {

    public static void main(String[] args) {
        wallet wallet = new wallet("Omar");
        System.out.println(wallet);
    }
}

Ouput:

Omar

Thank you a lot I appreciate it – Yamen Dec 23 '19 at 12:57 — Yamen, Dec 23 '19 at 12:57

score 0 · Answer 2 · answered Dec 24 '19 at 11:05

I propose an alternate version for toString() for wallet class, maybe more compact:

public String toString() {
    StringBuilder sb = new StringBuilder();

    sb.append("[");
    sb.append(checkValue(this.Name,","));
    sb.append(checkValue(this.Location,","));
    sb.append(checkValue(this.Tag,","));
    sb.append(checkValue(this.Reader,""));
    sb.append("]");

    return sb.toString();
}

protected String checkValue(String value, String separator) {
    return (value != null && !value.equals("unknown")) ? (value+separator) : "";
}

Moreover, firstly I suggest to use CamelCase to write names of methods, variables, classes, packages and constants: it is a standard notation (you're not obliged, it works the same, but it's more understandable for the developer community). So it would be better if each word or abbreviation begins with a capital letter or first word with a lowercase letter, rest all with capital. Secondly I suggest to user PreparedStatement instead of simple Statement, because is preferrable for security reasons: building a Statement dynamically is vulnerable to SQL injection (and with PreparedStatement you can avoid syntax errors that arise quoting values in predicates).

So instead of:

String query = "SELECT Name FROM wallets WHERE Location ='" + Location + "' ;";

You can try to use:

String query = "SELECT Name FROM wallets WHERE Location = ? ";

together with something like (assuming conn is you db connection):

try {
    String query = "SELECT Name FROM wallets WHERE Location = ? ";
    try (PreparedStatement stmt = conn.prepareStatement(query); ResultSet resultset = stmt.executeQuery(query)) {
        while (resultset.next()) {
            String Name = resultset.getString("Name");
            WalletsList.add(new wallet(Name));
        }
    }
} catch (SQLException sQLException) {
    sQLException.printStackTrace();
}

Please note that I used the "try-with-resource" syntax to avoid the problems of closing resultset and statements (i.e. the finally clause with resultset.close(), stament.close() and so on that is verbose and sometimes could lead to some coding errors if not faced correctly).

Best of all it would be

thatOneDude · Answer 3 · 2019-12-23T03:47:50.787

-1

Put this below String Name = resultset.getString("Name");, it should do

If resultset.getString("Name") != null {
    WalletsList.add(new wallet (Name));
}

edited Dec 23 '19 at 03:47

answered Dec 23 '19 at 03:40

thatOneDude

99
5

`Nothing`? This is a Java question, not a VB.NET question. – Andreas Dec 23 '19 at 03:42

How do I ignore the null values?

3 Answers3