I've looked around for a question like mine, but all of them were about adding array values to a SQL table. I'm asking about how to do the opposite.

Let's say I have a table of items, as shown below. A sold value of 0 means the item hasn't been sold, and vice versa for a value of 1.

I want to use a RecyclerView to display these items, but in order to do so, I need to get all of the itemID values into an array, where sold == 0.

My problem is that I don't know how to go through each itemID value in the SQL table and then add them to the array of itemID's. Would I have to use a cursor here? I don't really know where to start; any sort of help would be most appreciated.

CodePudding user response：

USE the WHERE clause to select the items. I prefer list over arrays since list are more flexible and you can get an array from list using toArray() method.

To get the items where sold is 0 you can use two methods;

Method 1:

Get all rows and then filter if the sold value is 0 then add it to list.

ResultSet resultSet = statement.executeQuery("SELECT itemID, itemName, sold FROM sales;");
...
if (sold == 0) {//to get where sold is 0
    items.add(new Item(id, itemName, sold));
}

Method 2:

Use the WHERE clause

resultSet = statement.executeQuery("SELECT itemID, itemName, sold FROM sales WHERE sold=0;");

Code:

package post;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

//Item class
class Item {
    final String itemId;
    String itemName;
    int sold;

    public Item(final String id) {
        itemId = id;
        itemName = "";
        sold = 0;
    }

    public Item(final String id, String name, int sold) {
        itemId = id;
        itemName = name;
        this.sold = sold;
    }

    public String toString() {
        return (itemId   " "   itemName   " "   sold);

    }
}

public class TSQL {

    public static void main(String... $) {
        Connection connection = null;
        List<Item> items = new ArrayList<>();
        var out = System.out;
        try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres", "yourpassword");
            Statement statement = connection.createStatement();
            //Method 1
            //get all the items and the filter
            ResultSet resultSet = statement.executeQuery("SELECT itemID, itemName, sold FROM sales;");
            while (resultSet.next()) {
                String id = resultSet.getString("itemId");
                String itemName = resultSet.getString("itemName");
                int sold = resultSet.getInt("sold");
//                System.out.println(id   " "   itemName   " "   sold);
                if (sold == 0) {//to get where sold is 0
                    items.add(new Item(id, itemName, sold));
                }
            }
            resultSet.close();
            for (Item item : items)
                out.println(item);
            out.println();
            items.clear();//clearing the list
            //Method 2
            //Filter using the WHERE clause
            //Notice the WHERE clause
            resultSet = statement.executeQuery("SELECT itemID, itemName, sold FROM sales WHERE sold=0;");
            while (resultSet.next()) {
                String id = resultSet.getString("itemId");
                String itemName = resultSet.getString("itemName");
                int sold = resultSet.getInt("sold");
//                System.out.println(id   " "   itemName   " "   sold);
                items.add(new Item(id, itemName, sold));
            }

            statement.close();
            connection.close();//closing the connection
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        for (Item item : items)
            out.println(item);

        //Bonus to get an array from list use toArray method
        Item[] arrayItems = items.toArray(new Item[0]);//We did it

    }
}

Output:

item02 secondItem 0
item03 thirdItem 0

item02 secondItem 0
item03 thirdItem 0